SQL计算每日累计

SQL计算每日累计SQL计算每日累计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

方式二

使用窗口函数，sum() over计算结果

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

转载：https://www.cnblogs.com/tangxuliang/p/16978017.html