SQL多表关联因缺少公共列致数据重复，求解决方法

问题场景 #

执行多表关联SQL时，adjust表无platform和date字段，仅通过campaign_name关联后，该表数据被重复带出，按campaign维度可视化时重复数据被累加，引发统计错误。

原SQL代码 #

with 
sent as (
    select campaign_name, date(date) as date, platform, count(id) as sent
    from send
    group by 1,2,3
),

bounce as (
    select campaign_name, platform, count(id) as bounce 
    from bounce
    group by 1,2
),
open as (
    select campaign_name, platform, count(id) as clicks
    from open
    group by 1,2
),
adjust as (
    select campaign, sum(purchase_events) as transactions, count(distinct adjust_id) as sessions, sum(sessions) as s2, sum(clicks) as ad_clicks
    from adjust
    group by 1
)
select 

s.campaign_name,
s.date,
s.platform,
s.sent,
(s.sent-b.bounce) as delivered,
b.bounce,
o.clicks,
a.ad_clicks,
a.sessions,
a.s2,
a.transactions

from sent s
join bounce b on s.campaign_name = b.campaign_name and s.platform = b.platform
join open o on s.campaign_name = o.campaign_name and s.platform = o.platform
left join adjust a on s.campaign_name = a.campaign

解决方案 #

重复问题的核心原因是：adjust表按campaign聚合后仅单条数据，但sent表按campaign+date+platform聚合后有多条数据，左关联后adjust的单条数据会被匹配到sent的所有对应行，导致字段重复出现。以下是几种可行的解决办法：

方法1：外层查询对adjust字段用聚合函数去重 #

因为adjust数据已经是按campaign聚合后的结果，在外层用MAX()或SUM()（此处MAX更准确）提取adjust字段，避免重复累加：

with 
sent as (
    select campaign_name, date(date) as date, platform, count(id) as sent
    from send
    group by 1,2,3
),
bounce as (
    select campaign_name, platform, count(id) as bounce 
    from bounce
    group by 1,2
),
open as (
    select campaign_name, platform, count(id) as clicks
    from open
    group by 1,2
),
adjust as (
    select campaign, sum(purchase_events) as transactions, count(distinct adjust_id) as sessions, sum(sessions) as s2, sum(clicks) as ad_clicks
    from adjust
    group by 1
)
select 
    s.campaign_name,
    s.date,
    s.platform,
    s.sent,
    (s.sent-b.bounce) as delivered,
    b.bounce,
    o.clicks,
    MAX(a.ad_clicks) as ad_clicks,
    MAX(a.sessions) as sessions,
    MAX(a.s2) as s2,
    MAX(a.transactions) as transactions
from sent s
join bounce b on s.campaign_name = b.campaign_name and s.platform = b.platform
join open o on s.campaign_name = o.campaign_name and s.platform = o.platform
left join adjust a on s.campaign_name = a.campaign
group by s.campaign_name, s.date, s.platform, s.sent, delivered, b.bounce, o.clicks

方法2：用子查询单独获取adjust字段 #

直接在select语句中通过子查询调取adjust的对应字段，每个campaign仅查询一次，避免关联带来的重复：

with 
sent as (
    select campaign_name, date(date) as date, platform, count(id) as sent
    from send
    group by 1,2,3
),
bounce as (
    select campaign_name, platform, count(id) as bounce 
    from bounce
    group by 1,2
),
open as (
    select campaign_name, platform, count(id) as clicks
    from open
    group by 1,2
),
adjust as (
    select campaign, sum(purchase_events) as transactions, count(distinct adjust_id) as sessions, sum(sessions) as s2, sum(clicks) as ad_clicks
    from adjust
    group by 1
)
select 
    s.campaign_name,
    s.date,
    s.platform,
    s.sent,
    (s.sent-b.bounce) as delivered,
    b.bounce,
    o.clicks,
    (select ad_clicks from adjust where campaign = s.campaign_name) as ad_clicks,
    (select sessions from adjust where campaign = s.campaign_name) as sessions,
    (select s2 from adjust where campaign = s.campaign_name) as s2,
    (select transactions from adjust where campaign = s.campaign_name) as transactions
from sent s
join bounce b on s.campaign_name = b.campaign_name and s.platform = b.platform
join open o on s.campaign_name = o.campaign_name and s.platform = o.platform

方法3：先合并基础维度表，再关联adjust #

先将sent、bounce、open的结果合并为一个按campaign_name+date+platform聚合的临时表，再左关联adjust，最后对adjust字段做聚合：

with 
sent as (
    select campaign_name, date(date) as date, platform, count(id) as sent
    from send
    group by 1,2,3
),
bounce as (
    select campaign_name, platform, count(id) as bounce 
    from bounce
    group by 1,2
),
open as (
    select campaign_name, platform, count(id) as clicks
    from open
    group by 1,2
),
adjust as (
    select campaign, sum(purchase_events) as transactions, count(distinct adjust_id) as sessions, sum(sessions) as s2, sum(clicks) as ad_clicks
    from adjust
    group by 1
),
combined as (
    select 
        s.campaign_name,
        s.date,
        s.platform,
        s.sent,
        (s.sent - b.bounce) as delivered,
        b.bounce,
        o.clicks
    from sent s
    join bounce b on s.campaign_name = b.campaign_name and s.platform = b.platform
    join open o on s.campaign_name = o.campaign_name and s.platform = o.platform
)
select 
    c.campaign_name,
    c.date,
    c.platform,
    c.sent,
    c.delivered,
    c.bounce,
    c.clicks,
    MAX(a.ad_clicks) as ad_clicks,
    MAX(a.sessions) as sessions,
    MAX(a.s2) as s2,
    MAX(a.transactions) as transactions
from combined c
left join adjust a on c.campaign_name = a.campaign
group by c.campaign_name, c.date, c.platform, c.sent, c.delivered, c.bounce, c.clicks

内容的提问来源于stack exchange，提问作者Abhishek K G