SQL子查询聚合缺失分类数据：需保留无及时流程的位置日期记录

现有基础数据表包含Process ID、Location、Date、Timeliness字段，需聚合后用于可视化工具。当前SQL查询使用ROLLUP分组与INNER JOIN，会缺失某位置某日期下无in time流程的记录（如Ohio June 24、Chicago May 24等），要求这类记录显示intime=0、total=对应流程总数、percentage=0。

原查询语句 #

SELECT
    gesamt2.Location,
    gesamt2.date,
    intime2.anzahl AS intime,
    gesamt2.anzahl AS total,

    CAST(intime2.anzahl AS float) / CAST(gesamt2.anzahl AS float) * 100 AS percentage

FROM
    (
        SELECT
            location,
            anzahl,
            date
        FROM
            (
                SELECT
                    Location,
                    Date,
                    timeliness,
                    COUNT(1) AS anzahl
                FROM
                    basedata
                GROUP BY ROLLUP (Location, Date, Timeliness)
            ) AS gesamt
        WHERE
            (timeliness IS NULL)
    ) AS gesamt2
    INNER JOIN
    (
        SELECT
            Location, anzahl, date
        FROM
            (
                SELECT
                    Location,
                    Date,
                    timeliness,
                    COUNT(1) AS anzahl
                FROM
                    basedata
                WHERE
                    (BASE_FINAL_APPROVAL > GETDATE() - 366)
                GROUP BY ROLLUP (Location, Date, timeliness)
            ) AS intime
        WHERE
            (timeliness = 'in time')
    ) AS intime2 ON
        gesamt2.Location = intime2.Location
        AND
        gesamt2.date = intime2.date

修改后查询语句 #

SELECT
    gesamt2.Location,
    gesamt2.date,
    -- 无匹配时intime显示0
    ISNULL(intime2.anzahl, 0) AS intime,
    gesamt2.anzahl AS total,
    -- 无及时流程时百分比直接为0，避免空值计算错误
    CASE
        WHEN ISNULL(intime2.anzahl, 0) = 0 THEN 0
        ELSE CAST(intime2.anzahl AS float) / CAST(gesamt2.anzahl AS float) * 100
    END AS percentage

FROM
    (
        SELECT
            location,
            anzahl,
            date
        FROM
            (
                SELECT
                    Location,
                    Date,
                    timeliness,
                    COUNT(1) AS anzahl
                FROM
                    basedata
                GROUP BY ROLLUP (Location, Date, Timeliness)
            ) AS gesamt
        WHERE
            (timeliness IS NULL)
    ) AS gesamt2
    -- 改用LEFT JOIN保留所有位置-日期的总流程记录
    LEFT JOIN
    (
        SELECT
            Location, anzahl, date
        FROM
            (
                SELECT
                    Location,
                    Date,
                    timeliness,
                    COUNT(1) AS anzahl
                FROM
                    basedata
                WHERE
                    (BASE_FINAL_APPROVAL > GETDATE() - 366)
                GROUP BY ROLLUP (Location, Date, timeliness)
            ) AS intime
        WHERE
            (timeliness = 'in time')
    ) AS intime2 ON
        gesamt2.Location = intime2.Location
        AND
        gesamt2.date = intime2.date

修改说明 #

• 将INNER JOIN替换为LEFT JOIN：确保gesamt2中所有的位置-日期记录都被保留，即使没有对应的in time流程数据
• 用ISNULL(intime2.anzahl, 0)处理空值：当没有匹配的in time记录时，将intime字段设为0
• 新增CASE分支计算percentage：避免因intime为空导致的计算错误，直接将无及时流程的百分比设为0

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