SQL查询两张表求和后关联结果时停机时间计算翻倍问题求助

问题核心原因 #

你遇到的停机时间统计翻倍问题，本质是两表直接JOIN产生了笛卡尔积重复行：当同一个REPORTCODE在生产表WORK_AT_JOB有N条记录、在停机表WAITING_AT_SITE有M条记录时，直接JOIN会生成N*M条关联结果，聚合求和时停机时间会被重复计算N次，生产时间会被重复计算M次，导致结果偏大。

解决方案 #

正确的逻辑是先分别对两张表按REPORTCODE分组聚合得到单条统计值，再做关联，避免JOIN阶段的行膨胀。
修正后的查询语句如下：

SELECT 
    COALESCE(WAJ_AGG.REPORTCODE, WAS_AGG.REPORTCODE) AS REPORTCODE,
    ISNULL(WAJ_AGG.PRODUCTION_TIME, 0) AS PRODUCTION_TIME,
    ISNULL(WAS_AGG.DOWNTIME, 0) AS DOWNTIME
FROM 
    -- 先聚合生产时间
    (SELECT 
        REPORTCODE,
        SUM(CASE 
            WHEN DATEDIFF(SECOND, TIMESTARTED, TIMEFINISHED) < 0 
            THEN (86400 + DATEDIFF(SECOND, TIMESTARTED, TIMEFINISHED)) / 3600.0
            ELSE DATEDIFF(SECOND, TIMESTARTED, TIMEFINISHED) / 3600.0
        END) AS PRODUCTION_TIME
    FROM WORK_AT_JOB
    WHERE REPORTCODE LIKE '04102021%'
    GROUP BY REPORTCODE) WAJ_AGG
-- 全关联保证只有生产时间/只有停机时间的记录也能出结果
FULL OUTER JOIN 
    -- 先聚合停机时间
    (SELECT 
        REPORTCODE,
        SUM(CASE 
            WHEN DATEDIFF(SECOND, TIMESTARTED, TIMEFINISHED) < 0 
            THEN (86400 + DATEDIFF(SECOND, TIMESTARTED, TIMEFINISHED)) / 3600.0
            ELSE DATEDIFF(SECOND, TIMESTARTED, TIMEFINISHED) / 3600.0
        END) AS DOWNTIME
    FROM WAITING_AT_SITE
    WHERE REPORTCODE LIKE '04102021%'
    GROUP BY REPORTCODE) WAS_AGG
ON WAJ_AGG.REPORTCODE = WAS_AGG.REPORTCODE

补充说明 #

如果你的业务场景中所有REPORTCODE都一定同时存在于两张表，也可以把FULL OUTER JOIN换成INNER JOIN，用COALESCE和ISNULL是为了兼容单边存在记录的场景，避免统计值为NULL。

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