sql - 在postgresql中查找每天每小时每行的列数据总和

Question

我对 PostgreSQL 很陌生。最近我正在研究一个数据库，其中有一个表名“商店”。商店有 item_id 作为主键。这是我的商店表中的演示数据:


ID
item_id
供应商_id
已收到
数量


12
34
6
2019-3-21 11:55:23
54

99
42
4
2019-3-21 11:23:12
98

19
39
6
2019-3-21 12:59:23
21

69
82
3
2019-3-21 10:29:11
32


我想制定一个查询，该查询将返回每天小时收到的耗材数量(耗材 = 数量总和)。输出将包括按小时排序的一天小时。
输出将是这样的:


小时
补给品


2019-3-21 11:00:00 - 11:59:59
152

2019-3-21 12:00:00 - 12:59:59
21


谁能帮我在 postgreSQL 中制定这个查询？

Answer 1

您可以在此处使用日历表方法。假设我们只需要覆盖单个日期 2019-03-21 的 24 小时时间段， 我们能试试:

with dates as (
    select generate_series(
       (date '2019-03-21')::timestamp,
       (date '2019-03-22')::timestamp,
       interval '1 hour'
     ) as dt
)

select
    d.dt::date::text || ' ' ||
        to_char(d.dt::time,'HH24:MM:SS') || ' - ' ||
        to_char(d.dt::time + interval '1 hour' - interval '1 second', 'HH24:MM:SS') as hour,
    coalesce(sum(s.quantity), 0) as supplies
from dates d
left join store s
    on s.received >= d.dt and s.received < d.dt + interval '1 hour'
group by
    d.dt
order by
    d.dt;

Demo
注意:如果您只想查看数量总和非零的小时数，只需添加以下 having上述查询的子句:

having sum(s.quantity) > 0