sql计算逐年累加

发表于 更新于 分类于 ， 阅读次数： 评论数：

还算循序渐进的分享了如何计算累计值

原始数据

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1618880400, 55522000.00);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1634691600, 55522000.00);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1640048400, 9322753.19);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1650416400, 55522000.00);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1655773200, 8677689.91);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1666227600, 36072000.00);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1671584400, 7992200.00);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1616202000, 8795298.37);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1679360400, 6191980.41);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1681952400, 522000.00);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1687309200, 6070822.59);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1695258000, 5991727.32);
INSERT INTO `<yry_fcc_accounts_receivable_doc>` (`status`, `project_number`, `planned_repayment_date`, `total_amount`) VALUES (1, 'XXXX-ls-00035', 1697763600, 52200.00);
status project_number planned_repayment_date (UTC) total_amount
1 XXXX-ls-00035 1618880400 (2021-04-20 09:00:00 ) 55,522,000.00
1 XXXX-ls-00035 1634691600 (2021-10-20 09:00:00 ) 55,522,000.00
1 XXXX-ls-00035 1640048400 (2021-12-21 09:00:00 ) 9,322,753.19
1 XXXX-ls-00035 1650416400 (2022-04-20 09:00:00 ) 55,522,000.00
1 XXXX-ls-00035 1655773200 (2022-06-21 09:00:00 ) 8,677,689.91
1 XXXX-ls-00035 1666227600 (2022-10-20 09:00:00 ) 36,072,000.00
1 XXXX-ls-00035 1671584400 (2022-12-21 09:00:00 ) 7,992,200.00
1 XXXX-ls-00035 1616202000 (2021-03-20 09:00:00 ) 8,795,298.37
1 XXXX-ls-00035 1679360400 (2023-03-21 09:00:00 ) 6,191,980.41
1 XXXX-ls-00035 1681952400 (2023-04-20 09:00:00 ) 522,000.00
1 XXXX-ls-00035 1687309200 (2023-06-21 09:00:00 ) 6,070,822.59
1 XXXX-ls-00035 1695258000 (2023-09-21 09:00:00 ) 5,991,727.32
1 XXXX-ls-00035 1697763600 (2023-10-20 09:00:00 ) 52,200.00

其中status, project_number, planned_repayment_date, total_amount的含义分别为数据状态、项目编号、应付日期、应付金额。需求为按照年查询该项目每年的应收款项以及截至当年的累计应收款。
先分析这个需求的难点，需求要求返回的结果要包含每年的应收款和累计应收款，其中每年的合计只需要按年分组求和即可，所以关键在于累计合计。

解法1

这种方法我愿称为瞪眼法，其思路是我先看看有多少个年度，再手动写每个年度的累计并union起来，最后与主表的按年分组进行连接即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SELECT  zb.sk_time
       ,zb.amount
       ,fb.total_amount
FROM
( -- 按年分组合计当年金额 
	SELECT  FROM_UNIXTIME(planned_repayment_date,'%y') AS sk_time
	       ,total_amount                               AS amount
	FROM yry_fcc_accounts_receivable_doc
	WHERE `status` = 1
	AND project_number = 'XXXX-ls-00035'
	GROUP BY  FROM_UNIXTIME(planned_repayment_date,'%y')
) AS zb
LEFT JOIN
( 
 (-- 计算截至21年的累计应收金额
	SELECT  MAX(sk_time) AS sk_time
	       ,SUM(amount)  AS total_amount
	FROM
	(
		SELECT  FROM_UNIXTIME(planned_repayment_date,'%y') AS sk_time
		       ,total_amount                               AS amount
		FROM yry_fcc_accounts_receivable_doc
		WHERE `status` = 1
		AND project_number = 'XXXX-ls-00035'
		GROUP BY  FROM_UNIXTIME(planned_repayment_date,'%y')
	) AS zjb
	WHERE sk_time <= 21 )
	UNION
	
 (-- 计算截至22年的累计应收金额 
	SELECT  MAX(sk_time) AS sk_time
	       ,SUM(amount)  AS total_amount
	FROM
	(
		SELECT  FROM_UNIXTIME(planned_repayment_date,'%y') AS sk_time
		       ,total_amount                               AS amount
		FROM yry_fcc_accounts_receivable_doc
		WHERE `status` = 1
		AND project_number = 'XXXX-ls-00035'
		GROUP BY  FROM_UNIXTIME(planned_repayment_date,'%y')
	) AS zjb
	WHERE sk_time <= 22 )

	UNION
	 
 (-- 计算截至23年的累计应收金额
	SELECT  MAX(sk_time) AS sk_time
	       ,SUM(amount)  AS total_amount
	FROM
	(
		SELECT  FROM_UNIXTIME(planned_repayment_date,'%y') AS sk_time
		       ,total_amount                               AS amount
		FROM yry_fcc_accounts_receivable_doc
		WHERE `status` = 1
		AND project_number = 'XXXX-ls-00035'
		GROUP BY  FROM_UNIXTIME(planned_repayment_date,'%y')
	) AS zjb
	WHERE sk_time <= 23 ) 
) AS fb
ON zb.sk_time = fb.sk_time

解法2

这种方法实现思路与上面一直，优化点在于不用再瞪眼写每一年的累计值了，这一需求通过WHERE fb.sk_time <= zb.sk_time自动计算。
关于简写后的sql，使用到了with子句，这是一些简单描述：

在 SQL 中，WITH 子句（通常称为 公共表表达式，Common Table Expression，CTE）是一种临时命名的查询结果集，可以在后续的 SQL 语句中被多次引用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SELECT  zb.sk_time
       ,zb.amount
       ,(
		SELECT  SUM( fb.amount )
		FROM
		(
			SELECT  FROM_UNIXTIME( planned_repayment_date,'%y' ) AS sk_time
			       ,total_amount                                 AS amount
			FROM yry_fcc_accounts_receivable_doc
			WHERE `status` = 1
			AND project_number = 'XXXX-ls-00035'
			GROUP BY  FROM_UNIXTIME( planned_repayment_date,'%y' )
		) AS fb
		WHERE fb.sk_time <= zb.sk_time   -- 筛选早于或等于当前年份的记录
) AS total_amount
FROM
(
	SELECT  FROM_UNIXTIME( planned_repayment_date,'%y' ) AS sk_time
	        ,SUM(total_amount)                           AS amount
	FROM yry_fcc_accounts_receivable_doc
	WHERE `status` = 1
	AND project_number = 'XXXX-ls-00035'
	GROUP BY  FROM_UNIXTIME( planned_repayment_date,'%y' )
) AS zb

-- 上面的等价简写
WITH zb AS (
  SELECT 
    FROM_UNIXTIME(planned_repayment_date, '%Y') AS sk_time,
    SUM(total_amount) AS amount
  FROM yry_fcc_accounts_receivable_doc
  WHERE `status` = 1
    AND project_number = 'XXXX-ls-00035'
  GROUP BY FROM_UNIXTIME(planned_repayment_date, '%Y')
)
SELECT
  sk_time,
  amount,
  (SELECT SUM(amount) FROM zb AS fb WHERE fb.sk_time <= zb.sk_time) AS total_amount
FROM zb;

解法3

针对这个需求，现代一点的做法是使用窗口函数（MySQL8.0后支持），让我们头疼的累计问题一句SUM(SUM(total_amount)) OVER (ORDER BY FROM_UNIXTIME(planned_repayment_date, '%Y')) AS total_amount搞定。这里因为是按年累计，所以窗口函数中的排序为ORDER BY FROM_UNIXTIME(planned_repayment_date, '%Y')

1
2
3
4
5
6
7
8
9
SELECT
  FROM_UNIXTIME(planned_repayment_date, '%Y') AS sk_time,
  SUM(total_amount) AS amount,
  SUM(SUM(total_amount)) OVER (ORDER BY FROM_UNIXTIME(planned_repayment_date, '%Y')) AS total_amount
FROM yry_fcc_accounts_receivable_doc
WHERE `status` = 1
  AND project_number = 'XXXX-ls-00035'
GROUP BY FROM_UNIXTIME(planned_repayment_date, '%Y');