postgresql 将 SQL 结果分组/聚合到 1 小时的存储桶中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1174518/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grouping/aggregating SQL results into 1-hour buckets
提问by user144051
Similar to this question, I need to group a large number of records into 1-hour "buckets". For example, let's say I've got a typical ORDER table with a datetime attached to each order. And I want to see the total number of orders per hour. So I'm using SQL roughly like this:
与此问题类似，我需要将大量记录分组为 1 小时的“存储桶”。例如，假设我有一个典型的 ORDER 表，每个订单都附有日期时间。我想查看每小时的订单总数。所以我大致这样使用 SQL：
SELECT datepart(hh, order_date), SUM(order_id)
FROM ORDERS
GROUP BY datepart(hh, order_date)
The problem is that if there are no orders in a given 1-hour "bucket", no row is emitted into the result set. I'd like the resultset to have a row for each of the 24 hour, but if no orders were made during a particular hour, just record the number of orders as O.
问题是，如果给定的 1 小时“桶”中没有订单，则不会向结果集中发送任何行。我希望结果集每 24 小时都有一行，但如果在特定小时内没有下订单，只需将订单数记录为 O。
Is there any way to do this in a single query?
有没有办法在单个查询中做到这一点？
See also Getting Hourly Statistics Using SQL.
另请参阅使用 SQL 获取每小时统计信息。
采纳答案by Lasse V. Karlsen
You need to have a pre-populated table (or a function returning a table result set) to join with, that contains all the 1-hour slots you want in your result.
您需要有一个预先填充的表（或一个返回表结果集的函数）来加入，其中包含您想要的结果中的所有 1 小时槽。
Then you do a OUTER JOIN with that, and you should get them all.
然后你用它做一个 OUTER JOIN，你应该得到它们。
Something like this:
像这样的东西：
SELECT SLOT_HOUR, SUM(order_id)
FROM
ONEHOURSLOTS
LEFT JOIN ORDERS ON DATEPART(hh, order_date) = SLOT_HOUR
GROUP BY SLOT_HOUR
回答by Ken Keenan
Some of the previous answers recommend using a table of hours and populating it using a UNION query; this can be better done with a Common Table Expression:
之前的一些答案建议使用小时表并使用 UNION 查询填充它；使用公共表表达式可以更好地完成此操作：
; WITH [Hours] ([Hour]) AS
(
SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY [object_id]) AS [Hour]
FROM sys.objects
ORDER BY [object_id]
)
SELECT h.[Hour], o.[Sum]
FROM [Hours] h
LEFT OUTER JOIN (
SELECT datepart(hh, order_date) as [Hour], SUM(order_id) as [Sum]
FROM Orders
GROUP BY datepart(hh, order_date)
) o
ON h.[Hour] = o.[Hour]
回答by Remus Rusanu
Create a table of hours, either persisted or even synthesized 'on the fly':
创建一个小时表，可以是持久化的，也可以是“动态”合成的：
SELECT h.hour, s.sum
FROM (
SELECT 1 as hour
UNION ALL SELECT 2
UNION ALL SELECT 3
...
UNION ALL SELECT 24) as h
LEFT OUTER JOIN (
SELECT datepart(hh, order_date) as hour, SUM(order_id) as sum
FROM ORDERS
GROUP BY datepart(hh, order_date) ) as s
ON h.hour = s.hour;
