PostgreSQL如何按班次统计单日总生产数量并合并结果

方案一：条件聚合（推荐，单次扫描表更高效） #

直接在一次GROUP BY DATE中，用CASE WHEN区分白班/夜班数据，分别计算各指标最大值，同时算出单日总和：

SELECT
    DATE(t_stamp) AS 日期,
    -- 白班指标（7:00-19:00，对应小时7-18）
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Units" END) AS "白班合格件数",
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Downgrade_Units" END) AS "白班降级件数",
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Panels" END) AS "白班合格板数",
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Downgrade_Panels" END) AS "白班降级板数",
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Panels" + "Stacker_8ft_Downgrade_Panels" END) AS "白班总板数",
    -- 夜班指标（19:00-次日7:00，对应小时19-23或0-6）
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Units" END) AS "夜班合格件数",
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Downgrade_Units" END) AS "夜班降级件数",
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Panels" END) AS "夜班合格板数",
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Downgrade_Panels" END) AS "夜班降级板数",
    MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Panels" + "Stacker_8ft_Downgrade_Panels" END) AS "夜班总板数",
    -- 单日总和计算
    COALESCE(MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Panels" END), 0) +
    COALESCE(MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Downgrade_Panels" END), 0) +
    COALESCE(MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Panels" END), 0) +
    COALESCE(MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Downgrade_Panels" END), 0) AS "单日总板数",
    COALESCE(MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Units" END), 0) +
    COALESCE(MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18 THEN "Stacker_8ft_Downgrade_Units" END), 0) +
    COALESCE(MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Ongrade_Units" END), 0) +
    COALESCE(MAX(CASE WHEN EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18 THEN "Stacker_8ft_Downgrade_Units" END), 0) AS "单日总件数"
FROM eight_ft_stacker_counts
WHERE "Stacker_8ft_Ongrade_Panels" > 0
  AND t_stamp > '2023-07-11 00:00:00'
GROUP BY DATE(t_stamp)
ORDER BY DATE(t_stamp);

关键点说明：

• 用CASE WHEN筛选对应班次的数据，再用MAX取该班次的最大计数（匹配你原逻辑）
• COALESCE处理某天只有一个班次数据的情况，避免NULL导致总和计算错误
• 一次扫描表完成所有计算，比多次查询+合并效率更高

方案二：CTE+JOIN（逻辑更直观，适合复杂场景） #

先分别计算白班、夜班的每日统计数据，再通过日期关联合并成一行：

WITH day_shift AS (
    SELECT
        DATE(t_stamp) AS 日期,
        MAX("Stacker_8ft_Ongrade_Units") AS "白班合格件数",
        MAX("Stacker_8ft_Downgrade_Units") AS "白班降级件数",
        MAX("Stacker_8ft_Ongrade_Panels") AS "白班合格板数",
        MAX("Stacker_8ft_Downgrade_Panels") AS "白班降级板数",
        MAX("Stacker_8ft_Ongrade_Panels" + "Stacker_8ft_Downgrade_Panels") AS "白班总板数"
    FROM eight_ft_stacker_counts
    WHERE "Stacker_8ft_Ongrade_Panels" > 0
      AND EXTRACT(HOUR FROM t_stamp) BETWEEN 7 AND 18
      AND t_stamp > '2023-07-11 00:00:00'
    GROUP BY DATE(t_stamp)
),
night_shift AS (
    SELECT
        DATE(t_stamp) AS 日期,
        MAX("Stacker_8ft_Ongrade_Units") AS "夜班合格件数",
        MAX("Stacker_8ft_Downgrade_Units") AS "夜班降级件数",
        MAX("Stacker_8ft_Ongrade_Panels") AS "夜班合格板数",
        MAX("Stacker_8ft_Downgrade_Panels") AS "夜班降级板数",
        MAX("Stacker_8ft_Ongrade_Panels" + "Stacker_8ft_Downgrade_Panels") AS "夜班总板数"
    FROM eight_ft_stacker_counts
    WHERE "Stacker_8ft_Ongrade_Panels" > 0
      AND EXTRACT(HOUR FROM t_stamp) NOT BETWEEN 7 AND 18
      AND t_stamp > '2023-07-11 00:00:00'
    GROUP BY DATE(t_stamp)
)
SELECT
    COALESCE(d.日期, n.日期) AS 日期,
    COALESCE(d."白班合格件数", 0) AS "白班合格件数",
    COALESCE(d."白班降级件数", 0) AS "白班降级件数",
    COALESCE(d."白班合格板数", 0) AS "白班合格板数",
    COALESCE(d."白班降级板数", 0) AS "白班降级板数",
    COALESCE(d."白班总板数", 0) AS "白班总板数",
    COALESCE(n."夜班合格件数", 0) AS "夜班合格件数",
    COALESCE(n."夜班降级件数", 0) AS "夜班降级件数",
    COALESCE(n."夜班合格板数", 0) AS "夜班合格板数",
    COALESCE(n."夜班降级板数", 0) AS "夜班降级板数",
    COALESCE(n."夜班总板数", 0) AS "夜班总板数",
    COALESCE(d."白班总板数", 0) + COALESCE(n."夜班总板数", 0) AS "单日总板数",
    COALESCE(d."白班合格件数", 0) + COALESCE(d."白班降级件数", 0) + COALESCE(n."夜班合格件数", 0) + COALESCE(n."夜班降级件数", 0) AS "单日总件数"
FROM day_shift d
FULL OUTER JOIN night_shift n ON d.日期 = n.日期
ORDER BY COALESCE(d.日期, n.日期);

关键点说明：

• 用CTE（公共表表达式）拆分白班、夜班的统计逻辑，代码可读性更强
• FULL OUTER JOIN确保即使某天只有一个班次有数据，也能正常显示该行
• 同样用COALESCE处理NULL值，保证总和计算准确

原问题原因说明 #

你原代码用UNION合并两个查询时，因为两个查询的字段名（如Day Ongrade Units和Night Ongrade Units）不一致，UNION会将它们视为不同结果集合并，导致每天返回两条记录（一条白班、一条夜班）。而上述两种方案都是将两个班次的数据整合到同一行，同时计算出单日总和。

内容的提问来源于stack exchange，提问作者Zach A.