如何将两个统计工单小时数据的SQL查询合并为单个查询？

我明白你想把两个单独的统计查询合并成一个，一次性拿到每小时的工单接收量和完成量对吧？咱们可以用条件聚合的方式来实现，既简洁又高效，不用额外的子查询或者联合查询。

合并后的完整查询语句（含全小时维度） #

SELECT
    -- 统计每小时接收的工单数量
    COUNT(CASE WHEN from_unixtime(CreateTime/1000, '%H') = hours THEN ReceiptScanRequestID END) AS ReceiveCount,
    -- 统计每小时完成的工单数量
    COUNT(CASE WHEN from_unixtime(FinishTime/1000, '%H') = hours THEN ReceiptScanRequestID END) AS FinishCount,
    hours AS PST,
    hours + 12.5 AS IST
FROM (
    -- 生成0-23的全小时维度，确保没有工单的小时也能返回记录（数量为0）
    SELECT 0 AS hours UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
    SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
    SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) AS HourDim
LEFT JOIN ReceiptScanRequestTable ON 
    (from_unixtime(CreateTime/1000, '%H') = HourDim.hours OR from_unixtime(FinishTime/1000, '%H') = HourDim.hours)
    AND CreateTime > {hour_filter_value}
GROUP BY hours
ORDER BY hours;

简化版（仅返回有工单活动的小时） #

如果不需要显示没有工单的小时，可以去掉全小时维度表，直接从原表分组：

SELECT
    COUNT(ReceiptScanRequestID) AS ReceiveCount,
    COUNT(CASE WHEN from_unixtime(FinishTime/1000, '%H') = from_unixtime(CreateTime/1000, '%H') THEN ReceiptScanRequestID END) AS FinishCount,
    from_unixtime(CreateTime/1000, '%H') AS PST,
    from_unixtime(CreateTime/1000, '%H') + 12.5 AS IST
FROM ReceiptScanRequestTable
WHERE CreateTime > {hour_filter_value}
GROUP BY from_unixtime(CreateTime/1000, '%H')
ORDER BY PST;

关键说明 #

1. 条件聚合逻辑：通过COUNT(CASE ... END)分别筛选统计：
   • ReceiveCount统计创建时间落在当前小时的工单
   • FinishCount统计完成时间落在当前小时的工单
2. 时区转换：完全保留了你原来的PST/IST转换逻辑
3. 笔误修正：你的原第二个查询里FinisheTime是拼写错误，我已经修正为FinishTime，避免语法报错

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