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

计算逻辑

方式二

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