PostgreSQL窗口函数实现累计去重计数需求

针对PostgreSQL中无法用窗口函数count(distinct)实现累计季度去重产品统计的问题，推荐以下两种高效的标准SQL方案：

方案一：基于产品首次出现季度的统计（最优性能） #

核心思路是先定位每个区域中每个产品的首次出现季度，再统计每个累计季度内所有首次出现时间≤当前季度的产品数量，确保每个产品仅被计数一次。

WITH product_first_quarter AS (
    -- 提取每个区域-产品的首次出现季度，可添加过滤条件
    SELECT 
        territory_id,
        product_id,
        MIN(quarter_num) AS first_quarter
    FROM some_table
    -- WHERE some_condition -- 若需过滤数据，在此添加条件
    GROUP BY territory_id, product_id
),
territory_quarters AS (
    -- 生成所有区域与1-28季度的完整组合，避免遗漏无数据的季度
    SELECT 
        t.territory_id,
        q.quarter_num
    FROM (SELECT DISTINCT territory_id FROM some_table) t
    CROSS JOIN generate_series(1, 28) q(quarter_num)
)
-- 计算每个区域各累计季度的去重产品数
SELECT 
    tq.territory_id,
    tq.quarter_num,
    COUNT(pfq.product_id) AS cumulative_distinct_products
FROM territory_quarters tq
LEFT JOIN product_first_quarter pfq 
    ON tq.territory_id = pfq.territory_id
    AND pfq.first_quarter <= tq.quarter_num
GROUP BY tq.territory_id, tq.quarter_num
ORDER BY tq.territory_id, tq.quarter_num;

方案优势： #

• 仅需两次轻量聚合，避免大量重复数据计算，性能远优于自连接方案
• 自动补全区域的所有季度（即使某季度无产品数据），完全匹配需求中“仅第1季度、第1+2季度…第1至28季度”的要求

方案二：自连接直接统计（直观但性能一般） #

通过自连接关联当前季度及之前所有季度的产品数据，再用count(distinct)去重统计，适合数据量较小的场景。

SELECT 
    t1.territory_id,
    t1.quarter_num,
    COUNT(DISTINCT t2.product_id) AS cumulative_distinct_products
FROM some_table t1
JOIN some_table t2 
    ON t1.territory_id = t2.territory_id
    AND t2.quarter_num <= t1.quarter_num
-- WHERE t1.some_condition AND t2.some_condition -- 按需添加过滤条件
GROUP BY t1.territory_id, t1.quarter_num
ORDER BY t1.territory_id, t1.quarter_num;

注意事项： #

• 若原表存在重复的(territory_id, quarter_num, product_id)记录，count(distinct)仍能正确去重，但会增加计算量
• 若需补全无数据的季度，需额外关联季度序列表

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