PostgreSQL使用窗口函数与GROUP BY时如何避免重复行

问题根源 #

你的查询核心错误是将原始表的total列加入了GROUP BY子句，同时窗口函数引用的是原始total而非聚合后的sum(total)：

• 当同一day、study_id、status下存在多个不同的原始total值时，会生成多个分组，其中部分分组的sum(current)和sum(total)可能为0，导致出现重复的0值行。
• 窗口函数计算差值时用的是未聚合的原始total，逻辑完全不符合“当日总数量与前日总数量差值”的需求。

修正方案 #

先通过CTE完成每日聚合计算，再在外层应用窗口函数计算差值，同时过滤掉全0的行：

WITH daily_candidate_stats AS (
    SELECT
        "day",
        "study_id" AS "studyId",
        "status",
        sum("current") AS "current",
        sum("total") AS "total"
    FROM "candidates"
    WHERE
        "study_id" = 'CY'
        AND "status" = 'PENDING_CALLCENTER'
    GROUP BY "day", "study_id", "status"
)
SELECT
    "day",
    "studyId",
    "status",
    "current",
    "total",
    -- 若要计算与前一日的差值，LAG的第二个参数应为1，你原查询写的是2，可根据实际需求调整
    COALESCE("total" - LAG("total", 1) OVER (ORDER BY "day"), 0) AS "difference"
FROM daily_candidate_stats
-- 过滤掉current、total、difference全为0的行
WHERE "current" != 0 OR "total" != 0 OR "difference" != 0
ORDER BY "day" ASC, "studyId" ASC, "status" ASC;

关键说明 #

1. 聚合与窗口函数分离：先聚合得到每日的统计值，再基于聚合结果计算窗口函数，避免GROUP BY对原始列的错误分组。
2. 过滤全0行：通过外层WHERE条件直接排除不需要的0值行。
3. LAG参数调整：原查询中LAG(total, 2)是取前2行的值，若需求是“当日与前日（前1行）”的差值，需将参数改为1。

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