这是来自群友的一个需求,有一张资产表 assets,用来记录每个公司在每个季度结束时的资产信息。
assets 的表结构如下:
字段 | 类型 | 描述 |
---|---|---|
corp | String | 公司 |
check_day | Date | 核算日期 |
assets | Integer | 总资产 |
assets 表中的数据:
corp check_day assets
------ ---------- --------
A 2016-12-31 200
A 2017-03-31 300
A 2017-06-30 400
A 2017-09-30 100
A 2017-12-31 1000
A 2018-03-31 400
A 2018-06-30 500
A 2018-09-30 600
A 2018-12-31 700
B 2016-12-31 400
B 2017-03-31 300
B 2017-06-30 200
B 2017-09-30 100
B 2017-12-31 600
B 2018-03-31 900
B 2018-06-30 500
B 2018-09-30 100
B 2018-12-31 700
assets 表的数据满足两个约束:
群友希望在 assets 表的基础上,增加一列用于展示期初资产。期初资产的计算规则是:当前季度的期初资产 = 上一年最后一个季度的总资产。
最终得到的结果应该是这样:
公司 核算日期 总资产 期初资产
---- ---------- ----- --------
A 2016-12-31 200 (NULL)
A 2017-03-31 300 200
A 2017-06-30 400 200
A 2017-09-30 100 200
A 2017-12-31 1000 200
A 2018-03-31 400 1000
A 2018-06-30 500 1000
A 2018-09-30 600 1000
A 2018-12-31 700 1000
B 2016-12-31 400 (NULL)
B 2017-03-31 300 400
B 2017-06-30 200 400
B 2017-09-30 100 400
B 2017-12-31 600 400
B 2018-03-31 900 600
B 2018-06-30 500 600
B 2018-09-30 100 600
B 2018-12-31 700 600
我们可以把实现的过程分成两步操作:
查找每个公司每年的期末资产可以用下面这个 SQL:
SELECT
corp,
check_day,
assets
FROM
(SELECT
*,
row_number () over (
PARTITION BY corp,
YEAR(check_day)
ORDER BY check_day DESC
) AS rn
FROM
assets) t
WHERE rn = 1
查询的结果如下:
corp check_day assets
------ ---------- --------
A 2016-12-31 200
A 2017-12-31 1000
A 2018-12-31 700
B 2016-12-31 400
B 2017-12-31 600
B 2018-12-31 700
最终的 SQL 就这么写:
SELECT
a.corp AS '公司',
a.check_day AS '核算日期',
a.assets AS '总资产',
b.assets AS '期初资产'
FROM
assets a
LEFT JOIN
(SELECT
*,
row_number () over (
PARTITION BY corp,
YEAR(check_day)
ORDER BY check_day DESC
) AS rn
FROM
assets) b
ON b.corp = a.corp
AND YEAR(b.check_day)
= YEAR(a.check_day) - 1
AND b.rn = 1
需要注意,这里的 SQL 已经把条件 rn = 1
放到 LEFT JOIN
的后面,而不是放在 WHERE
子句里。
如果数据库环境不支持使用窗口函数,也可以使用标量子查询,一步到位:
SELECT
a.corp AS '公司',
a.check_day AS '核算日期',
a.assets AS '总资产',
(SELECT
assets
FROM
assets b
WHERE b.corp = a.corp
AND b.check_day =
CONCAT((YEAR(a.check_day) - 1), '-12-31'))
AS opening_assets
FROM
assets a
子查询里面的条件 b.check_day = CONCAT((YEAR(a.check_day) - 1), '-12-31'))
也可以换成其它写法,比如根据当前季度的最后一天日期倒推出去年最后一天的日期,b.check_day = DATE_SUB(a.check_day,INTERVAL DAYOFYEAR(a.check_day) DAY)
。