gpt4 book ai didi

mysql - 获取按时间间隔按月分组的累计和

转载 作者:行者123 更新时间:2023-11-28 23:48:54 25 4
gpt4 key购买 nike

这是用户表。我从那里获取数据并通过将表 d 添加到查询来填充缺失的月份。

---------------------------------------
| uid | date_register |
---------------------------------------
| 1 | 2011-07-20 02:24:36 |
---------------------------------------
| 2 | 2012-10-03 07:37:43 |
---------------------------------------
| ... | ... ... ... ... ... |
---------------------------------------
| 300000 | 2015-12-19 04:13:51 |
---------------------------------------

我想获取按月分组的累计总和。我正在使用以下查询来执行此操作。

SELECT d.y, d.m, p.cum_sum 
FROM
(SELECT a.y, b.m
FROM (SELECT 2015 AS Y UNION ALL SELECT 2014 AS Y) a
CROSS JOIN (SELECT 1 AS m UNION ALL SELECT 2 AS m UNION ALL SELECT 3 AS m UNION ALL SELECT 4 AS m UNION ALL SELECT 5 AS m UNION ALL SELECT 6 AS m UNION ALL SELECT 7 AS m UNION ALL SELECT 8 AS m UNION ALL SELECT 9 AS m UNION ALL SELECT 10 AS m UNION ALL SELECT 11 AS m UNION ALL SELECT 12 AS m) b
WHERE CONCAT(a.y, '-', b.m, '-01') BETWEEN CURDATE() - INTERVAL 13 MONTH AND CURDATE()
ORDER BY 1, 2) d
LEFT OUTER JOIN
(SELECT year1,
month,
@cnt := @cnt + total cum_sum
FROM (
SELECT YEAR(date_register) year1,
MONTH(date_register) month,
COUNT(*) total
FROM users
WHERE date_register >= DATE_FORMAT(NOW() ,'%Y-%m-01') - INTERVAL 1 YEAR
AND useractivated = 1
GROUP BY YEAR(date_register), MONTH(date_register)
) n, (SELECT @cnt := count(*) from users
where date_register< DATE_FORMAT(NOW() ,'%Y-%m-01') - INTERVAL 1 YEAR AND useractivated = 1) u)
p ON d.m=p.month AND d.y=p.year1
ORDER BY 1 ASC, 2 ASC

但如果该特定月份没有交易,则返回空值。喜欢以下(示例数据)

+------+-------+---------+
| y | m | cum_sum |
+------+-------+---------+
| 2014 | 10 | 12356 |
| 2014 | 11 | 13567 |
| 2014 | 12 | 14239 |
| 2015 | 01 | 15234 |
| 2015 | 02 | 16571 |
| 2015 | 03 | NULL |
| 2015 | 04 | 24239 |
| 2015 | 05 | 34239 |
| 2015 | 06 | 44239 |
| 2015 | 07 | 45239 |
| 2015 | 08 | 46239 |
| 2015 | 09 | 57239 |
| 2015 | 10 | 67239 |
+------+-------+---------+

所以 2015 年 3 月没有交易,所以它返回 null 但它应该像上一行一样返回 16571。我做错了什么?谢谢。

最佳答案

试试这个:

    select a.y, b.m, 
(
SELECT count(*) as cum_count
FROM users
WHERE (YEAR(date_register) = a.y and MONTH(date_register) <= b.m) or year(date_register) < a.y
) as cum_count
from
(
SELECT 2015 AS Y UNION ALL SELECT 2014 AS Y
) a
CROSS JOIN
(
SELECT 1 AS m UNION ALL SELECT 2 AS m UNION ALL SELECT 3 AS m UNION ALL SELECT 4 AS m UNION ALL SELECT 5 AS m UNION ALL SELECT 6 AS m UNION ALL SELECT 7 AS m UNION ALL SELECT 8 AS m UNION ALL SELECT 9 AS m UNION ALL SELECT 10 AS m UNION ALL SELECT 11 AS m UNION ALL SELECT 12 AS m
) b
order by a.y, b.m

关于mysql - 获取按时间间隔按月分组的累计和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32944979/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com