sql - 在postgresql中查找每天每小时每行的列数据总和
问题描述
我对 PostgreSQL 非常陌生。最近我正在研究一个数据库,其中有一个表名“store”。store 有 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 中制定这个查询?
解决方案
您可以在此处使用日历表方法。假设我们只需要在单个日期覆盖 24 小时2019-03-21
,我们可以尝试:
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;
演示
注意:如果您只想查看数量总和不为零的小时,只需having
在上述查询中添加以下子句:
having sum(s.quantity) > 0