使用SQL按不同时间段进行分组，包括按天分组

Question

我需要查询一个销售表，并在给定日期内显示不同时间段的细分结果。

例如，表格具有以下字段：

Id（整数），EntryDate（日期时间），SaleReference（varchar）

我想生成一个类似于此的表格：

日期 9:00-12:00 销售额 12:00-15:00 销售额 18:00-21:00 销售额 ---------- -------------- -------------- ------------- 01-01-2010 10 20 6 02-01-2010 12 16 3 03-01-2010 43 11 2

如有帮助，将不胜感激。谢谢。

- StephenLewes

---

Stephen - 你使用的是哪个关系型数据库管理系统和版本？ - Martin Smith

Answer 1

假设是SQL Server。如果不是的话，可能会有类似的逻辑适用于您的关系型数据库管理系统，但获取日期时间中的小时部分的函数可能不同，并且BETWEEN的行为也可能不同（在SQL Server中，它是一个包含范围）。

SELECT CAST([Date] AS Date) AS [Date],
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 9 AND 11 THEN 1 ELSE NULL END)
    AS [Sales 9am-12pm],
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 14 THEN 1 ELSE NULL END) 
    AS [Sales 12pm-3pm],
COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 18 AND 20 THEN 1 ELSE NULL END) 
    AS [Sales 6pm-9pm]
FROM Table
GROUP BY CAST([Date] AS Date) /*For SQL2008*/

注意：旧版本的SQL Server需要更多的步骤才能仅从日期时间中获取日期部分，例如：CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME（来自此处）。

- Martin Smith

---

你正在按日期分组，这将是一个日期时间。不幸的是，它无法正确分组。将日期转换为日期，并在选择中放置相同的转换，它就会被修复。现在+1 :) - Mike M.

@Mike - 很好的发现。现在已经修复了。 - Martin Smith

那真是太完美了。谢谢Martin和Mike。 :-) - StephenLewes

抱歉...又回到这个问题了 ;-)如果表格中包含一个包含销售价值的列，我该如何获取每个时间段的总销售价值？ - StephenLewes

添加 SUM(CASE WHEN DATEPART(hour, [Date]) BETWEEN 9 AND 11 THEN salesVal ELSE 0 END) AS [Sales Value 9am-12pm] 等。 - Martin Smith

Answer 2

假设您的数据库支持这种日期计算，您可以这样说：

CASE WHEN EntryDate - date(EntryDate) >= INTERVAL '9 hours'
      AND EntryDate - date(EntryDate) < INTERVAL '12 hours'
     THEN ...

（顺便说一下，那是PostgreSQL的间隔语法...可能不是标准的。）但是可能有更优雅的方法来实现它。

- Justin K