窗口函数实现同their_ids仅取最新值累加及SUM DISTINCT报错解决

核心思路 #

• 先做行级标记：给每个dude_id分组下的相同their_ids标记最新记录，用ROW_NUMBER()按dude_id + their_ids分区，main_date倒序排序，标记为1的就是对应their_ids的最新记录
• 再做窗口累加：仅将标记为最新记录的how_many、how_much值计入统计，旧记录对应字段取值为0，避免重复统计

实现代码 #

WITH marked_records AS (
    SELECT
        *,
        -- 标记每个dude_id下相同their_ids的最新记录，is_latest=1即为最新
        ROW_NUMBER() OVER (PARTITION BY dude_id, their_ids ORDER BY main_date DESC) AS is_latest
    FROM some_table
)
SELECT
    dude_id,
    main_date,
    how_many,
    how_much,
    their_ids,
    SUM(CASE WHEN is_latest = 1 THEN how_many ELSE 0 END) OVER (
        PARTITION BY dude_id 
        ORDER BY main_date 
    ) AS count_stuff_EXPECTED,
    SUM(CASE WHEN is_latest = 1 THEN how_much ELSE 0 END) OVER (
        PARTITION BY dude_id 
        ORDER BY main_date 
    ) AS cumulative_sum_EXPECTED
FROM marked_records
ORDER BY dude_id, main_date;

逻辑说明 #

你之前尝试的SUM(DISTINCT)不可行，一是因为PostgreSQL当前不支持窗口函数中搭配ORDER BY使用DISTINCT参数，二是DISTINCT是对聚合值去重，不是按their_ids维度去重，本身也不符合业务逻辑。
上述方案的核心是通过预标记过滤掉旧的重复their_ids记录的统计贡献：比如同一个dude_id下their_ids为373的记录共有2条，main_date更大的那条is_latest标记为1，更早的那条标记为2。累加时更早的那条贡献值为0，只有最新的那条会被计入总和，刚好符合「相同their_ids仅保留最新值参与统计」的需求。
如果你的业务允许同一个their_ids的旧记录完全不展示，可以在CTE里直接过滤掉is_latest != 1的记录，再做累加会更高效。 #

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