SQL计算每日累计

假设现在有一张表 users

id created_at
1 2022-11-05 15:05:02.102674
2 2022-11-05 07:50:34.753416
3 2022-11-06 08:36:11.619856
4 2022-11-07 21:08:16.120656

求计算每日累计用户数为多少？

方式一

SELECT u1.day as datetime, sum(u2.count) as count from 
	(SELECT created_at::DATE as day, count(id) as count from users GROUP BY created_at::DATE ORDER BY created_at::DATE desc) u1,
	(SELECT created_at::DATE as day, count(id) as count from users GROUP BY created_at::DATE ORDER BY created_at::DATE desc) u2
	WHERE u1.day >= u2.day
	GROUP BY u1.day
	ORDER BY u1.day desc

计算逻辑
image

方式二

SELECT u1.day as datetime, sum(u1.count) OVER (order by u1.day) as count from 
	(SELECT created_at::DATE as day, count(id) as count from test GROUP BY created_at::DATE ORDER BY created_at::DATE desc) u1
	ORDER BY u1.day desc

结算结果

datetime count
2022-11-07 4
2022-11-06 3
2022-11-05 2
posted @ 2022-12-13 11:58  Azure沫  阅读(948)  评论(0)  编辑  收藏  举报