如何将最近24小时SQL查询结果按三个时段拆分

针对需求，我们可以通过CASE语句结合时间函数，给每条数据标记所属时间段，再根据需要选择保留明细或进行聚合统计。以下是两种常用方案：

方案1：保留明细并标记时间段 #

该方案在原查询基础上新增时间段标签，方便查看每条数据所属的时段，同时可按时段排序：

SELECT 
    "inmain"."calltime", 
    "inmain"."business", 
    "inmain"."agency", 
    "inmain"."case_id", 
    "inmain"."nature", 
    "inmain"."street", 
    "inmain"."inci_id",
    -- 根据小时判断所属时间段
    CASE
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 7 AND 14 THEN '7:00-15:00'
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 15 AND 22 THEN '15:00-23:00'
        ELSE '23:00-7:00'
    END AS time_period
FROM   "cad"."dbo"."inmain" "inmain"
WHERE  "inmain"."agency" = 'YUPD' 
AND "inmain"."calltime" > GETDATE() - 1
ORDER BY time_period, "inmain"."nature"

方案2：按时间段聚合统计 #

如果需要统计各时间段的案件数量（或其他指标），可以用GROUP BY结合CASE语句实现：

按时间段+案件类型统计 #

SELECT 
    CASE
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 7 AND 14 THEN '7:00-15:00'
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 15 AND 22 THEN '15:00-23:00'
        ELSE '23:00-7:00'
    END AS time_period,
    "inmain"."nature",
    COUNT(*) AS case_count -- 统计每个时段下各案件类型的数量
FROM   "cad"."dbo"."inmain" "inmain"
WHERE  "inmain"."agency" = 'YUPD' 
AND "inmain"."calltime" > GETDATE() - 1
GROUP BY 
    CASE
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 7 AND 14 THEN '7:00-15:00'
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 15 AND 22 THEN '15:00-23:00'
        ELSE '23:00-7:00'
    END,
    "inmain"."nature"
ORDER BY time_period, "inmain"."nature"

仅按时间段统计总数量 #

如果只需各时段的总案件数，去掉nature相关的分组和字段即可：

SELECT 
    CASE
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 7 AND 14 THEN '7:00-15:00'
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 15 AND 22 THEN '15:00-23:00'
        ELSE '23:00-7:00'
    END AS time_period,
    COUNT(*) AS total_cases
FROM   "cad"."dbo"."inmain" "inmain"
WHERE  "inmain"."agency" = 'YUPD' 
AND "inmain"."calltime" > GETDATE() - 1
GROUP BY 
    CASE
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 7 AND 14 THEN '7:00-15:00'
        WHEN DATEPART(HOUR, "inmain"."calltime") BETWEEN 15 AND 22 THEN '15:00-23:00'
        ELSE '23:00-7:00'
    END
ORDER BY time_period

说明 #

• 用DATEPART(HOUR, calltime)提取呼叫时间的小时数，通过CASE语句匹配三个时段：
  • 7:00-15:00对应小时7到14（包含7:00，不包含15:00）
  • 15:00-23:00对应小时15到22（包含15:00，不包含23:00）
  • 剩余小时（23到6）自动归为23:00-7:00
• 原查询的GETDATE()-1逻辑保持不变，确保只取最近24小时的数据

内容的提问来源于stack exchange，提问作者Macheen