SQL如何保留GROUP BY分组输出的同时获取去重后全局PERSON_ID计数

解决方案 #

方案1：窗口函数实现（最优，性能最好） #

核心逻辑是用窗口函数在不破坏原有GROUP BY分组结构的前提下，直接计算每日全局去重的总人数，所有分组行都会携带正确的总分母值：

SELECT 
    dt,
    GROUP_P,
    Numerator,
    Denominator AS group_denominator,
    total_denominator
FROM (
    SELECT      
        TO_DATE(ORDER_TIME AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific','YYYY-MM-DD') AS dt,
        GROUP_P,
        COUNT(CASE WHEN ORDER_TIME IS NOT null THEN ORDER_ID END) AS Numerator,
        COUNT(DISTINCT CASE WHEN ORDER_TIME IS NOT null THEN PERSON_ID END) AS Denominator,
        -- 按日期分区计算当日全局去重总人数
        COUNT(DISTINCT CASE WHEN ORDER_TIME IS NOT null THEN PERSON_ID END) OVER(PARTITION BY TO_DATE(ORDER_TIME AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific','YYYY-MM-DD')) AS total_denominator
    FROM ORDER_TAB 
    WHERE TO_DATE(ORDER_TIME AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific','YYYY-MM-DD')
    BETWEEN DATEADD(day, -1, current_Date) AND current_Date
    GROUP BY 1, 2
) t

输出结果会保留原有4行分组结构，每行新增total_denominator字段，值固定为1699，汇总时直接使用该字段作为总分母即可。

方案2：CTE关联实现（兼容性最好） #

如果使用的数据库不支持COUNT(DISTINCT)作为窗口函数，可以用CTE先计算全局总分母再和分组结果关联：

WITH daily_total AS (
    -- 计算每日全局去重总人数
    SELECT      
        TO_DATE(ORDER_TIME AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific','YYYY-MM-DD') AS dt,
        COUNT(DISTINCT CASE WHEN ORDER_TIME IS NOT null THEN PERSON_ID END) AS total_denominator
    FROM ORDER_TAB 
    WHERE TO_DATE(ORDER_TIME AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific','YYYY-MM-DD')
    BETWEEN DATEADD(day, -1, current_Date) AND current_Date
    GROUP BY 1
),
group_detail AS (
    -- 原有分组统计逻辑
    SELECT      
        TO_DATE(ORDER_TIME AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific','YYYY-MM-DD') AS dt,
        GROUP_P,
        COUNT(CASE WHEN ORDER_TIME IS NOT null THEN ORDER_ID END) AS Numerator,
        COUNT(DISTINCT CASE WHEN ORDER_TIME IS NOT null THEN PERSON_ID END) AS Denominator
    FROM ORDER_TAB 
    WHERE TO_DATE(ORDER_TIME AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific','YYYY-MM-DD')
    BETWEEN DATEADD(day, -1, current_Date) AND current_Date
    GROUP BY 1, 2
)
SELECT 
    g.dt,
    g.GROUP_P,
    g.Numerator,
    g.Denominator AS group_denominator,
    d.total_denominator
FROM group_detail g
JOIN daily_total d ON g.dt = d.dt

该方案兼容所有支持CTE语法的数据库，性能和方案1基本一致，数据库会自动优化同一张表的两次过滤逻辑，不会重复扫描数据。

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