MYSQL SUM，直到最近12个月的每个月的最后一天

Question

我有一个这样的表，两个

表A

date          amount     B_id 
'2020-1-01'     3000000      1
'2019-8-01'     15012        1
'2019-6-21'     90909        1
'2020-1-15'     84562        1
--------

表B

id       type
1         7
2         5

我必须出示过去12个月的每月最后一天之前的金额总和。我准备的查询是这样的。。

SELECT num2.last_dates,
(SELECT SUM(amount) FROM A
INNER JOIN B ON A.B_id = B.id
WHERE B.type = 7 AND A.date<=num2.last_dates
),
(SELECT SUM(amount) FROM A
INNER JOIN B ON A.B_id = B.id
WHERE B.type = 5 AND A.date<=num2.last_dates)
FROM
(SELECT last_dates
FROM (
SELECT LAST_DAY(CURDATE()  - INTERVAL CUSTOM_MONTH MONTH) last_dates
FROM(
SELECT 1 CUSTOM_MONTH UNION
SELECT 0 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7 UNION
SELECT 8 UNION
SELECT 9 UNION
SELECT 10 UNION
SELECT 11 UNION
SELECT 12 )num
) num1
)num2
ORDER BY num2.last_dates

这给了我这样的结果，这正是我所需要的。我需要这个查询执行得更快。有什么更好的方法来做我想做的事情吗？

2019-05-31  33488.69        109.127800
2019-06-30  263.690          1248932.227800
2019-07-31  274.690         131.827800
2019-08-31  627.690         13.687800
2019-09-30  1533.370000     08.347800
2019-10-31  1444.370000     01.327800
2019-11-30  5448.370000     247.227800
2019-12-31  61971.370000    016.990450
2020-01-31  19550.370000    2535.185450
2020-02-29  986.370000      405.123300
2020-03-31  1152.370000     26.793300
2020-04-30  9404.370000     11894.683300
2020-05-31  3404.370000     17894.683300

Answer 1

我会使用条件聚合，并在一次传递中预聚合每月总数，而不是在相同的数据中重复进行二十六次单独传递。

我会从这样的东西开始：

SELECT CASE WHEN A.date < DATE(NOW()) + INTERVAL -14 MONTH
THEN LAST_DAY(     DATE(NOW()) + INTERVAL -14 MONTH )
ELSE LAST_DAY( A.date )
END                                    AS _month_end
, SUM(IF( B.type = 5 , B.amount , NULL)) AS tot_type_5
, SUM(IF( B.type = 7 , B.amount , NULL)) AS tot_type_7
FROM A
JOIN B
ON B.id = A.B_id
WHERE B.type IN (5,7)
GROUP
BY _month_end

(列数量在原始查询中不合格，所以只需在此处猜测来自哪个表。根据需要进行调整。最佳做法是合格所有列引用。

这为我们提供了每个月的小计，只需通过a和B即可。

我们可以对该查询进行测试和调优。

然后，我们可以将其作为一个内联视图合并到一个外部查询中，该查询将这些月度总数相加。(我会做一个外部连接，以防缺少行，这样我们就不会遗漏行。(

类似这样的东西：

SELECT d.dt + INTERVAL -i.n MONTH + INTERVAL -1 DAY  AS last_date
, SUM(IFNULL(t.tot_type_5,0))                   AS rt_type_5
, SUM(IFNULL(t.tot_type_7,0))                   AS rt_type_7
FROM ( -- first day of next month
SELECT DATE(NOW()) + INTERVAL -DAY(DATE(NOW()))+1 DAY + INTERVAL 1 MONTH AS dt
) d
CROSS
JOIN ( -- thirteen integers, integers 0 thru 12
SELECT 0 AS n
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) i
LEFT
JOIN ( -- totals by month
SELECT CASE WHEN A.date < DATE(NOW()) + INTERVAL -14 MONTH
THEN LAST_DAY(     DATE(NOW()) + INTERVAL -14 MONTH )
ELSE LAST_DAY( A.date )
END                                    AS _month_end
, SUM(IF( B.type = 5 , B.amount , NULL)) AS tot_type_5
, SUM(IF( B.type = 7 , B.amount , NULL)) AS tot_type_7
FROM A
JOIN B
ON B.id = A.B_id
WHERE B.type IN (5,7)
GROUP
BY _month_end
) t
ON t._month_end < d.dt
GROUP BY d.dt + INTERVAL -i.n MONTH + INTERVAL -1 DAY
ORDER BY d.dt + INTERVAL -i.n MONTH + INTERVAL -1 DAY DESC

---

该设计旨在对A JOIN B集合进行一次突袭。我们预计会回到14排左右。我们正在做一个半连接，多次复制最古老的月份，所以大约。14 x 13/2=91行，折叠成13行。

就性能而言，最重要的是将内联视图查询具体化。

Answer 2

这就是我在MySQL 8中使用SUM OVER:的方法

1. 获取最后12个月
2. 使用这些月份将空的月份行添加到原始数据中，因为MySQL不支持完全的外部联接
3. 获取所有月份的运行总数
4. 只显示最近12个月

查询：

with months (date) as
(
select last_day(current_date - interval  1 month) union all
select last_day(current_date - interval  2 month) union all
select last_day(current_date - interval  3 month) union all
select last_day(current_date - interval  4 month) union all
select last_day(current_date - interval  5 month) union all
select last_day(current_date - interval  6 month) union all
select last_day(current_date - interval  7 month) union all
select last_day(current_date - interval  8 month) union all
select last_day(current_date - interval  9 month) union all
select last_day(current_date - interval 10 month) union all
select last_day(current_date - interval 11 month) union all
select last_day(current_date - interval 12 month)
)
, data (date, amount, type) as
(
select last_day(a.date), a.amount, b.type
from a
join b on b.id = a.b_id
where b.type in (5, 7)
union all
select date, null, null from months
)
select
date,
sum(sum(case when type = 5 then amount end)) over (order by date) as t5,
sum(sum(case when type = 7 then amount end)) over (order by date) as t7
from data
group by date
order by date
limit 12;

演示：https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ddeb3ab3e086bfc182f0503615fba74b

我不知道这是否比您自己的查询快。试试看。(通过在表中添加为last_day(date)生成的列并使用它，您可以更快地获得我的查询。如果您经常需要，这可能是一个选项。(

Answer 3

您得到了一些复杂的答案。我认为这更容易。首先要知道我们可以轻松地为每个月进行汇总：

SELECT SUM(amount) as monthtotal,
type,
MONTH(date) as month,
YEAR(date) as year 
FROM A LEFT JOIN B on A.B_id=B.id 
GROUP BY type,month,year

根据这些数据，我们可以使用一个变量来获得运行总数。最好初始化变量，但不是必须的。我们可以像这个一样获得必要的数据

SET @running := 0;
SELECT (@running := @running + monthtotal) as running, type, LAST_DAY(CONCAT(year,'-',month,'-',1))
FROM 
(SELECT SUM(amount) as monthtotal,type,MONTH(date) as month,YEAR(date) as year FROM A LEFT JOIN B on A.B_id=B.id GROUP BY type,month,year) AS totals
ORDER BY year,month

您确实需要一个支持多个语句的连接器，或者进行多个调用来初始化变量。尽管可以对变量进行null检查并默认为0，但如果再次运行查询，仍然会出现问题。

最后一件事，如果你真的想把类型单独相加：

SET @running5 := 0;
SET @running7 := 0;
SELECT 
LAST_DAY(CONCAT(year,'-',month,'-',1)),
(@running5 := @running5 + (CASE WHEN type=5 THEN monthtotal ELSE 0 END)) as running5, 
(@running7 := @running7 + (CASE WHEN type=7 THEN monthtotal ELSE 0 END)) as running7
FROM 
(SELECT SUM(amount) as monthtotal,type,MONTH(date) as month,YEAR(date) as year FROM A LEFT JOIN B on A.B_id=B.id GROUP BY type,month,year) AS totals
ORDER BY year,month

我们仍然没有显示没有数据的月份。我不确定这是一个要求。但这应该只需要通过一次表A.

此外，请确保表B上的id已编入索引。