sql 获取去年_SQL 计算公司的期初资产

问题描述

这是来自群友的一个需求，有一张资产表 assets，用来记录每个公司在每个季度结束时的资产信息。

assets 的表结构如下：

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 表的数据满足两个约束：

1. 从第一个核算周期算起，每个公司每个季度都会有一条核算记录，不会出现漏掉的情况；
2. 每个季度的最后一天作为核算日期，即核算日在每一年里都是固定的，分别是 3月31日、6月30日、9月30日、12月31日。

群友希望在 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

解决方案

我们可以把实现的过程分成两步操作：

1. 找出每个公司每年的期末资产，即每个公司的每年最后一个季度的记录；
2. 利用日期函数，将当前的核算日期倒推出上一年的最后一天，再结合公司名称就能在步骤 1 的临时的结果集获取到期初资产。

查找每个公司每年的期末资产可以用下面这个 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) 。