如何在SQL中合并同日期同班次的多行生产数据？

问题描述 #

现有班次生产数据查询结果中，infeed、outfeed、scrap三类计数分散在不同行，需要将同一日期、同一班次的这三个值合并到单条记录中。

当前数据形态 #

每条记录对应单个标签（infeed/outfeed/scrap），同一日期+班次会生成3条独立记录，包含字段：Shift、tag、count、date、Datetime。

期望数据形态 #

每日每个班次仅保留1条记录，同时包含三类标签的计数：

现有查询代码 #

SELECT shift, tag, "count", TO_CHAR(capturetime, 'MM/DD HH24:MI:SS') AS "Datetime" ,TO_CHAR(capturetime, 'MM/DD') AS "Date"
FROM shift_data.production_counts
WHERE machine = 'SGP3'
and tag like '%Reason:Total%'
and capturetime >= (CURRENT_TIMESTAMP - '8 days'::interval)
and capturetime <= CURRENT_Date 
and (  (cast(TO_CHAR(capturetime, 'HH24') as int)=6 and cast(TO_CHAR(capturetime, 'MI') as int)>45) 
    or (cast(TO_CHAR(capturetime, 'HH24') as int)=7 and cast(TO_CHAR(capturetime, 'MI') as int)<1) 
    or (cast(TO_CHAR(capturetime, 'HH24') as int)=18 and cast(TO_CHAR(capturetime, 'MI') as int)>45) 
    or (cast(TO_CHAR(capturetime, 'HH24') as int)=19 and cast(TO_CHAR(capturetime, 'MI') as int)<1)
) 
order by capturetime DESC

解决方案 #

方法1：条件聚合（简洁高效） #

利用CASE WHEN结合聚合函数，直接将不同标签的计数聚合到同一行：

SELECT 
    shift,
    'scrap' AS tag,
    MAX(CASE WHEN tag LIKE '%scrap%' THEN "count" END) AS count,
    TO_CHAR(capturetime, 'MM/DD') AS "Date",
    'outfeed' AS tag2,
    MAX(CASE WHEN tag LIKE '%outfeed%' THEN "count" END) AS count2,
    'infeed' AS tag3,
    MAX(CASE WHEN tag LIKE '%infeed%' THEN "count" END) AS count3
FROM shift_data.production_counts
WHERE 
    machine = 'SGP3'
    AND tag LIKE '%Reason:Total%'
    AND capturetime >= CURRENT_TIMESTAMP - '8 days'::interval
    AND capturetime <= CURRENT_DATE
    AND (
        (EXTRACT(HOUR FROM capturetime) = 6 AND EXTRACT(MINUTE FROM capturetime) > 45)
        OR (EXTRACT(HOUR FROM capturetime) = 7 AND EXTRACT(MINUTE FROM capturetime) < 1)
        OR (EXTRACT(HOUR FROM capturetime) = 18 AND EXTRACT(MINUTE FROM capturetime) > 45)
        OR (EXTRACT(HOUR FROM capturetime) = 19 AND EXTRACT(MINUTE FROM capturetime) < 1)
    )
GROUP BY shift, TO_CHAR(capturetime, 'MM/DD')
ORDER BY "Date" DESC, shift DESC;

方法2：自连接（满足表关联需求） #

通过三次自连接，分别关联scrap、outfeed、infeed的记录，合并为单条：

SELECT 
    s.shift,
    s.tag,
    s."count",
    s."Date",
    o.tag AS tag2,
    o."count" AS count2,
    i.tag AS tag3,
    i."count" AS count3
FROM (
    -- 提取scrap记录
    SELECT 
        shift, 
        tag, 
        "count", 
        TO_CHAR(capturetime, 'MM/DD') AS "Date"
    FROM shift_data.production_counts
    WHERE 
        machine = 'SGP3'
        AND tag LIKE '%scrap%'
        AND tag LIKE '%Reason:Total%'
        AND capturetime >= CURRENT_TIMESTAMP - '8 days'::interval
        AND capturetime <= CURRENT_DATE
        AND (
            (EXTRACT(HOUR FROM capturetime) = 6 AND EXTRACT(MINUTE FROM capturetime) > 45)
            OR (EXTRACT(HOUR FROM capturetime) = 7 AND EXTRACT(MINUTE FROM capturetime) < 1)
            OR (EXTRACT(HOUR FROM capturetime) = 18 AND EXTRACT(MINUTE FROM capturetime) > 45)
            OR (EXTRACT(HOUR FROM capturetime) = 19 AND EXTRACT(MINUTE FROM capturetime) < 1)
        )
) s
JOIN (
    -- 提取outfeed记录
    SELECT 
        shift, 
        tag, 
        "count", 
        TO_CHAR(capturetime, 'MM/DD') AS "Date"
    FROM shift_data.production_counts
    WHERE 
        machine = 'SGP3'
        AND tag LIKE '%outfeed%'
        AND tag LIKE '%Reason:Total%'
        AND capturetime >= CURRENT_TIMESTAMP - '8 days'::interval
        AND capturetime <= CURRENT_DATE
        AND (
            (EXTRACT(HOUR FROM capturetime) = 6 AND EXTRACT(MINUTE FROM capturetime) > 45)
            OR (EXTRACT(HOUR FROM capturetime) = 7 AND EXTRACT(MINUTE FROM capturetime) < 1)
            OR (EXTRACT(HOUR FROM capturetime) = 18 AND EXTRACT(MINUTE FROM capturetime) > 45)
            OR (EXTRACT(HOUR FROM capturetime) = 19 AND EXTRACT(MINUTE FROM capturetime) < 1)
        )
) o ON s.shift = o.shift AND s."Date" = o."Date"
JOIN (
    -- 提取infeed记录
    SELECT 
        shift, 
        tag, 
        "count", 
        TO_CHAR(capturetime, 'MM/DD') AS "Date"
    FROM shift_data.production_counts
    WHERE 
        machine = 'SGP3'
        AND tag LIKE '%infeed%'
        AND tag LIKE '%Reason:Total%'
        AND capturetime >= CURRENT_TIMESTAMP - '8 days'::interval
        AND capturetime <= CURRENT_DATE
        AND (
            (EXTRACT(HOUR FROM capturetime) = 6 AND EXTRACT(MINUTE FROM capturetime) > 45)
            OR (EXTRACT(HOUR FROM capturetime) = 7 AND EXTRACT(MINUTE FROM capturetime) < 1)
            OR (EXTRACT(HOUR FROM capturetime) = 18 AND EXTRACT(MINUTE FROM capturetime) > 45)
            OR (EXTRACT(HOUR FROM capturetime) = 19 AND EXTRACT(MINUTE FROM capturetime) < 1)
        )
) i ON s.shift = i.shift AND s."Date" = i."Date"
ORDER BY s."Date" DESC, s.shift DESC;

说明 #

• 条件聚合方法性能更优，避免多次扫描表；
• 自连接方法逻辑直观，适合理解表关联原理；
• 优化了原查询中时间判断的写法，用EXTRACT替代字符串转换，提升效率。

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