假设现在有一张表 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
计算逻辑
结算结果
| datetime | count |
|---|---|
| 2022-11-07 | 4 |
| 2022-11-06 | 3 |
| 2022-11-05 | 2 |