按优先规则匹配位置后按月统计去重客户数的高效SQL实现

基础表与测试数据 #

现有数据表结构及初始化数据如下：

CREATE TABLE tbl (
  id          int NOT NULL
, date        date NOT NULL
, cid         int NOT NULL
, birth_place text NOT NULL
, location    text NOT NULL
);
 
INSERT INTO tbl VALUES
  (1 , '2022-01-01', 1, 'France' , 'Germany')
, (2 , '2022-01-30', 1, 'France' , 'France')
, (3 , '2022-01-25', 2, 'Spain'  , 'Spain')
, (4 , '2022-01-12', 3, 'France' , 'France')
, (5 , '2022-02-01', 4, 'England', 'Italy')
, (6 , '2022-02-12', 1, 'France' , 'France')
, (7 , '2022-03-05', 5, 'Spain'  , 'England')
, (8 , '2022-03-08', 2, 'Spain'  , 'Spain')
, (9 , '2022-03-15', 2, 'Spain'  , 'Spain')
, (10, '2022-03-30', 5, 'Spain'  , 'Italy')
, (11, '2022-03-22', 4, 'England', 'England')
, (12, '2022-03-22', 3, 'France' , 'England');

统计规则 #

需按月、位置统计去重客户数（统计字段为cid），客户归属位置遵循以下优先级规则：

1. 若客户在指定月份内存在location = birth_place（返回出生地）的记录，优先将该客户归属到出生地位置
2. 若客户当月从未返回出生地，可任选该客户当月出现过的任意一个位置归属
   期望输出结果如下：

date         location   count
2022-01-01   France     2
2022-01-01   Spain      1
2022-02-01   Italy      1
2022-02-01   France     1
2022-03-01   Spain      1
2022-03-01   England    3

示例说明：2022年1月cid=1的客户存在返回出生地France的记录，因此该客户当月归属于France，不会统计到Germany维度下，最终结果无Germany统计项。

原有实现问题 #

原有CTE写法可以返回正确结果，但在1亿行数据量级下执行效率极低，存在以下性能瓶颈：

• 多次扫描原表：3个CTE分别扫描全表，IO开销是单表扫描的3倍
• 窗口函数排序开销：row_number()需要按(月份, cid)分区排序，大数据量下排序成本极高
• 多表关联开销：两次左连接会产生大量中间结果，占用内存和计算资源
• 重复去重开销：最终聚合使用count(distinct cid)，存在不必要的去重计算

优化后实现 #

核心思路：单遍扫描原表，直接按「月份+客户」分组，在分组内按优先级规则计算归属位置，避免多表关联、窗口排序和重复扫描。

WITH customer_month_assign AS (
    SELECT
        date_trunc('month', date)::date AS month_date,
        cid,
        COALESCE(
            -- 只要当月存在返回出生地的记录，直接取出生地作为归属位置
            MAX(CASE WHEN location = birth_place THEN birth_place END),
            -- 无返回出生地记录时，任选一个当月出现的位置即可，MAX/MIN均可满足要求
            MAX(location)
        ) AS assign_location
    FROM tbl
    GROUP BY month_date, cid
)
SELECT
    month_date AS date,
    assign_location AS location,
    COUNT(*) AS count
FROM customer_month_assign
GROUP BY month_date, assign_location
ORDER BY month_date, assign_location;

优化收益 #

• 仅需1次全表扫描，IO开销降低60%以上
• 移除窗口函数排序操作，避免大内存排序开销
• 移除所有join操作，减少中间结果计算
• 每个「月份+客户」分组仅输出1行，最终聚合直接用COUNT(*)，无需额外去重
• 执行计划更简洁，在分布式数仓/大表场景下性能可提升5~10倍

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