我有一个clients mysql表,我希望得到按月-年分组的客户机的累积计数。
我试过下一个,但没有看到:
SELECT DATE_FORMAT(datacad,'%m-%y') AS month-year, count(id) OVER(ORDER BY id) AS cumulative_count
FROM clients;
clients
-------
id datacad
1 2001-10-10
2 2001-10-11
3 2002-11-12
4 2001-12-14
5 2003-12-15
6 2003-12-16
7 2003-12-17
//required result
month-year cumulative_count
----------------------------
10-2001 3
11-2002 4
12-2003 10
附加信息:当我使用:
SELECT DATE_FORMAT(datacad,'%m-%y') AS label, count(*) as total FROM clients
GROUP BY label
order by datacad
我得到:
label,total
03-2011,1
04-2011,1268
05-2011,1365
06-2011,1244
07-2011,1102
08-2011,315
02-2013,1
03-2013,1
03-2014,1
06-2014,1
07-2014,1
01-2017,1
02-2017,1
01-2018,4
05-2018,2
08-2018,1
09-2019,1
04-2020,3
06-2020,1
但当我使用:
SELECT DATE_FORMAT(datacad,'%m-%y') AS month_year,
SUM(COUNT(id)) OVER (ORDER BY id) AS cumulative_count
FROM clients
GROUP BY month_year
ORDER BY MIN(datacad);
我得到:
month_year,cumulative_count
03-11,1271
04-11,1268
05-11,2636
06-11,3880
07-11,4982
08-11,5297
02-13,5298
03-13,5299
03-14,5300
06-14,5301
07-14,5302
01-17,5303
02-17,5304
01-18,5308
05-18,1270
08-18,5309
09-19,5310
04-20,5313
06-20,5314
两者的结果并不匹配。
您需要一个group by
和sum()
累计和的计数:
SELECT DATE_FORMAT(datacad,'%m-%y') AS month_year,
SUM(COUNT(id)) OVER (ORDER BY MIN(datacad)) AS cumulative_count
FROM clients
GROUP BY month_year
ORDER BY MIN(datacad);
我还假设您希望数据按日期顺序排列,因此添加了order by
。