SQL多对多关联去重问题：按优惠有效期统计客户渠道触达标识

业务需求 #

现有两张业务表：

• offers表：存储不同客户的优惠信息，每条记录对应一个客户的某类优惠及有效期
• attentions表：记录不同渠道的客户触达记录，每条记录对应一次渠道触达

需求：按优惠的有效期时间段分组，统计各渠道是否对该客户有触达（多次触达仅标记为1）。当前使用LEFT JOIN关联两表时因多对多关系产生重复行，需优化实现需求。

表数据展示 #

offers表数据 #

PERIOD |CELLPHONE   | IDENTIFICATION | FIRST_DATE   | LAST_DATE  | UPSELLING | IPHONE 
202208  56961424344     152783337       09/08/2022    23/08/2022       1         0
202208  56961424344     152783337       09/08/2022    23/08/2022       0         1
202208  56961424344     152783337       24/08/2022    27/09/2022       1         0

attentions表数据 #

PERIOD |   DATE    | IDENTIFICATION | CELLPHONE  | CALL_CENTER | DIGITAL | PUBLIC
202208   09/08/2022   NULL           56961424344        1          0         0
202208   11/08/2022   152783337      56961424344        1          0         0
202208   26/08/2022   152783337      56961424344        0          1         0

期望输出 #

PERIOD | FIRST_DATE | LAST_DATE | CELLPHONE | IDENTIFICATION | UPSELLING | IPHONE | CALL_CENTER | DIGITAL
202208   09/08/2022   23/08/2022 56961424344    152783337         1          1           1          0
202208   24/08/2022   27/09/2022 56961424344    152783337         1          0           0          1

当前已编写的CTE代码 #

WITH attentions_since_to AS
( 
 SELECT DISTINCT period,
                 identification,
                 cellphone,
                 call_center,
                 public,
                 digital,
                 first_date,
                 last_date
    FROM (SELECT a.*, o.first_date, o.last_date
            FROM attentions a
            LEFT JOIN offers o
              ON a.cellphone = o.cellphone
             AND a.date BETWEEN o.first_date AND o.last_date)
)

优化解决方案 #

核心思路：先对触达数据按优惠有效期+客户维度做聚合，提前标记各渠道是否有触达（用MAX函数取1表示有触达），再与优惠表关联，避免多对多关联产生重复行。

完整SQL代码：

WITH processed_attentions AS (
    -- 关联触达记录与优惠有效期，按核心维度聚合渠道标记
    SELECT 
        o.period,
        o.cellphone,
        o.identification,
        o.first_date,
        o.last_date,
        MAX(a.call_center) AS call_center,
        MAX(a.digital) AS digital,
        MAX(a.public) AS public
    FROM offers o
    LEFT JOIN attentions a 
        ON o.cellphone = a.cellphone
        AND COALESCE(o.identification, a.identification) = COALESCE(a.identification, o.identification)
        AND a.date BETWEEN o.first_date AND o.last_date
    GROUP BY 
        o.period, o.cellphone, o.identification, o.first_date, o.last_date
),
processed_offers AS (
    -- 聚合同一有效期内的优惠标识
    SELECT 
        period,
        cellphone,
        identification,
        first_date,
        last_date,
        MAX(upselling) AS upselling,
        MAX(iphone) AS iphone
    FROM offers
    GROUP BY 
        period, cellphone, identification, first_date, last_date
)
-- 关联处理后的两张表得到最终结果
SELECT 
    po.period,
    po.first_date,
    po.last_date,
    po.cellphone,
    po.identification,
    po.upselling,
    po.iphone,
    COALESCE(pa.call_center, 0) AS call_center,
    COALESCE(pa.digital, 0) AS digital,
    COALESCE(pa.public, 0) AS public
FROM processed_offers po
LEFT JOIN processed_attentions pa 
    ON po.period = pa.period
    AND po.cellphone = pa.cellphone
    AND po.identification = pa.identification
    AND po.first_date = pa.first_date
    AND po.last_date = pa.last_date
ORDER BY po.first_date;

代码说明 #

1. processed_attentions：筛选落在优惠有效期内的触达记录，按优惠的核心维度聚合，用MAX函数标记渠道是否有触达（只要有一次触达就返回1）。
2. processed_offers：对优惠表按有效期维度聚合，合并同一时间段内的多个优惠类型记录，保留所有优惠标识。
3. 最后关联两个处理后的表，用COALESCE将NULL转为0，确保渠道标记为0/1格式。

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