前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL 计算公司的期初资产

SQL 计算公司的期初资产

作者头像
白日梦想家
发布2020-08-07 18:21:22
9140
发布2020-08-07 18:21:22
举报
文章被收录于专栏:SQL实现

问题描述

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

assets 的表结构如下:

字段

类型

描述

corp

String

公司

check_day

Date

核算日期

assets

Integer

总资产

assets 表中的数据:

代码语言:javascript
复制
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 表的基础上,增加一列用于展示期初资产。期初资产的计算规则是:当前季度的期初资产 = 上一年最后一个季度的总资产。

最终得到的结果应该是这样:

代码语言:javascript
复制
公司   核算日期     总资产  期初资产  
----  ----------  -----  --------
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:

代码语言:javascript
复制
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

查询的结果如下:

代码语言:javascript
复制
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 就这么写:

代码语言:javascript
复制
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 子句里。

如果数据库环境不支持使用窗口函数,也可以使用标量子查询,一步到位:

代码语言:javascript
复制
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)

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-08-05,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 SQL实现 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题描述
  • 解决方案
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档