sql - 在一天/月/周结束时获取值的总和

Question

我每小时将不同用户钱包的余额存储在数据库中:

Table: snapshots

id      wall_id   balance          created_at
--------------------------------------------------
...

1300    30        1461.18721408    2019-07-05 18:00:05
1301    30        2922.37442816    2019-07-05 19:00:05
1302    30        228.31050220     2019-07-05 20:00:05
1303    30        2283.10502200    2019-07-05 21:00:05
1304    30        285.38812775     2019-07-05 22:00:05
1305    30        57.07762555      2019-07-05 23:00:05 <--
1306    25        2511.41552420    2019-07-05 18:00:05
1307    25        251.14155242     2019-07-05 19:00:05
1308    25        21735.61642968   2019-07-05 20:00:05
1309    25        22523.28766227   2019-07-05 21:00:05
1310    25        79.90867577      2019-07-05 22:00:05
1311    25        285.38812775     2019-07-05 23:00:05 <--
1312    100       2511.41552420    2019-07-05 18:00:05
1313    100       251.14155242     2019-07-05 19:00:05
1314    100       21735.61642968   2019-07-05 20:00:05
1315    100       22523.28766227   2019-07-05 21:00:05
1316    100       79.90867577      2019-07-05 22:00:05
1317    100       285.38812775     2019-07-05 23:00:05
1318    30        1461.18721408    2019-07-06 18:00:05
1319    30        2922.37442816    2019-07-06 19:00:05
1320    30        228.31050220     2019-07-06 20:00:05
1321    30        2283.10502200    2019-07-06 21:00:05
1322    30        285.38812775     2019-07-06 22:00:05
1323    30        79.90867577      2019-07-06 23:00:05 <--
1324    25        2511.41552420    2019-07-06 18:00:05
1325    25        251.14155242     2019-07-06 19:00:05
1326    25        21735.61642968   2019-07-06 20:00:05
1327    25        22523.28766227   2019-07-06 21:00:05
1328    25        79.90867577      2019-07-06 22:00:05
1329    25        21735.61642968   2019-07-06 23:00:05 <--
1330    100       2511.41552420    2019-07-06 18:00:05
1331    100       251.14155242     2019-07-06 19:00:05
1332    100       21735.61642968   2019-07-06 20:00:05
1333    100       22523.28766227   2019-07-06 21:00:05
1334    100       79.90867577      2019-07-06 22:00:05
1335    100       285.38812775     2019-07-06 23:00:05

...

现在我想获取每天最后快照的总和，其中 wall.id = 30 && 25。

为了理解算法，我标记了应该添加的值。

例如:

Example result for wall_id 30 & 25 on EOD

day                           sumEOD 
--------------------------------------------------
2019-07-05 00:00:00           ‭342.4657533‬
2019-07-06 00:00:00           21815.52510545

还应该可以修改它并获取每周制作的最后快照的总和，其中 wall.id = 30 && 25。

实现此目标的最简单方法是什么？我正在运行 postgresql。

Answer 1

首先使用group by wall_id, date(created_at) 找到每天制作的最后一张快照，然后加入表并按天获取总和:

select 
  date(s.created_at) "day",
  sum(s.balance) sumEOD
from snapshots s inner join (
  select wall_id, max(created_at) maxdate
  from snapshots
  where wall_id in ('25', '30')
  group by wall_id, date(created_at)
) g on g.wall_id = s.wall_id and g.maxdate = s.created_at  
group by "day"
order by "day"

参见 demo .
结果:

| day                      | sumeod         |
| ------------------------ | -------------- |
| 2019-07-05T00:00:00.000Z | 342.4657533    |
| 2019-07-06T00:00:00.000Z | 21815.52510545 |

如果您想获得每周的总和，您可以更改:

group by wall_id, date(created_at)

到:

group by wall_id, date_trunc('week', created_at)