专栏首页SQL实现SQL 计算公司的期初资产

SQL 计算公司的期初资产

问题描述

这是来自群友的一个需求,有一张资产表 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 表的数据满足两个约束:

  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)

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-08-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 订单揽收统计

    创建日期在这里只是起到了过滤数据的作用,我们先把 2020 年 7 月份的数据捞出来。

    白日梦想家
  • SQL 计算中位数

    笔者在 HackerRank 上的 SQL 编程挑战看到这题,这题有 96% 的提交成功率。实际上,使用 SQL 求中位数远远没那么简单。

    白日梦想家
  • SQL 打印九九乘法表

    九九乘法表的 SQL 我曾发布到其它内容平台,现在把 SQL 拷过来,稍微加一些说明。

    白日梦想家
  • Spark DataFrame isin方法使用

    查询DataFrame某列在某些值里面的内容,等于SQL IN ,如 where year in(‘2017’,’2018’)

    董可伦
  • 「小程序JAVA实战」小程序我的个人信息页面开发(41)

    IT故事会
  • 【系列】移动端项目经验 表单兼容(上篇)

    移动端 表单兼容(上篇) HTML5学堂:从这篇文章开始,我们将为大家总结介绍移动端的常见兼容问题,今天要提的是关于表单的一些兼容问题,本文主要包括input文...

    HTML5学堂
  • Spring的声明式事务管理

    在service类前加上@Transactional,声明这个service所有方法需要事务管理。每一个业务方法开始时都会打开一个事务。 Spring默认情...

    xiangzhihong
  • 【漏洞预警】Apache Solr远程代码执行漏洞 (CVE-2019-0193)处置手册及技术分析

    近日,Apache Solr官方发布Apache Solr远程代码执行漏洞(CVE-2019-0193)安全通告,此漏洞存在于可选模块DataImportHan...

    绿盟科技安全情报
  • [Linux] 使用tcpkill杀掉tcp连接

    在使用长连接的过程中,如果有的长连接一直连着,想要杀掉这条连接可以使用tcpkill命令

    陶士涵
  • 信任“之殇:安全软件的“白名单”将放大恶意威胁

    “白名单“,即一系列被信任的对象的集合,与”黑名单“对应,通常被用来实现”排除“类的逻辑。在安全领域中,”白名单“通常被用来优化对信任对象的分析逻辑或解决查杀、...

    用户6477171

扫码关注云+社区

领取腾讯云代金券