SQL JOIN后结果行数减少且数据漏报问题求助

核心问题 #

1. 关联字段不匹配：你原语句里用a.account_id = b.id关联，但从示例数据看，两张表的关联字段应该是customer_id，这直接导致大量数据无法匹配到对应的名称，出现漏报或空值分组。
2. 冗余分组+关联重复：子查询里对明细字段（imp、clicks、spend、name）分组完全多余，而且如果table_2存在一对多的记录，关联后会把table_1的一行拆成多行，后续求和时会重复计算，导致统计值异常；如果关联字段错误，还会出现大量无效的空值分组，让结果行数异常。

修复后的SQL写法 #

写法一：先聚合再关联（推荐，避免重复）

先把table_1按customer_id聚合好统计值，再关联table_2获取名称，最后按名称分组（如果同一个名称对应多个客户，会自动合并统计）：

SELECT 
    SUM(t1.record_count) AS total_records,
    t2.customer_name AS name,
    SUM(t1.total_impressions) AS imp,
    SUM(t1.total_clicks) AS clicks,
    SUM(t1.total_spend) AS spend
FROM (
    -- 先统计每个客户的核心指标
    SELECT 
        customer_id,
        COUNT(*) AS record_count,
        SUM(impressions) AS total_impressions,
        SUM(clicks) AS total_clicks,
        SUM(spend) AS total_spend
    FROM table_1
    GROUP BY customer_id
) t1
LEFT JOIN table_2 t2 
    ON t1.customer_id = t2.customer_id  -- 修正关联字段
GROUP BY t2.customer_name;

写法二：直接关联后聚合（适用于一对一关联场景）

如果table_2和table_1的customer_id是严格一对一的关系，可以简化成直接关联后聚合：

SELECT 
    COUNT(*) AS total_records,
    t2.customer_name AS name,
    SUM(t1.impressions) AS imp,
    SUM(t1.clicks) AS clicks,
    SUM(t1.spend) AS spend
FROM table_1 t1
LEFT JOIN table_2 t2 
    ON t1.customer_id = t2.customer_id  -- 修正关联字段
GROUP BY t2.customer_name;

额外注意点 #

如果table_2里同一个customer_id对应多条记录（比如客户有多个名称条目），必须先对table_2去重，比如取唯一名称：

-- 先对table_2去重，取每个customer_id对应的唯一名称
WITH unique_table2 AS (
    SELECT customer_id, customer_name
    FROM table_2
    QUALIFY ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY customer_name) = 1
)
SELECT 
    COUNT(*) AS total_records,
    ut2.customer_name AS name,
    SUM(t1.impressions) AS imp,
    SUM(t1.clicks) AS clicks,
    SUM(t1.spend) AS spend
FROM table_1 t1
LEFT JOIN unique_table2 ut2 
    ON t1.customer_id = ut2.customer_id
GROUP BY ut2.customer_name;

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