如何关联聚合表与键组合表并补全计数为0的记录

表结构与数据 #

t_base（每日聚合表） #

t_comb（R_id与Q_id的所有可能组合） #

需求 #

当某个R_id在某日期出现时，补全该R_id对应的所有Q_id记录，无对应数据时计数设为0，最终结果如下：

已知约束：

• t_base中(date, R_id, Q_id)唯一
• t_comb中(R_id, Q_id)唯一
• t_base的所有R_id都存在于t_comb中
• 若(date, r_id)在t_base中不存在，则结果中也不出现该组合
• 实际场景t_base有近14亿条记录，优先考虑性能

测试代码（日期用整数表示） #

with
t_base(d, r_id, q_id, c) as (
    select * from values
        (1, 1, 2, 2),
        (1, 1, 3, 4),
        (2, 2, 2, 5),
        (2, 1, 1, 1)
)
, t_comb(r_id, q_id) as (
    select * from values
        (1, 1),
        (1, 2),
        (1, 3),
        (2, 1),
        (2, 2)
)
select ???

解决方案 #

核心思路是先提取t_base中所有唯一的(date, R_id)组合，再和t_comb关联生成完整记录框架，最后左连接t_base填充计数，避免直接大表笛卡尔积，保证性能。

优化后的SQL #

with
t_base(d, r_id, q_id, c) as (
    select * from values
        (1, 1, 2, 2),
        (1, 1, 3, 4),
        (2, 2, 2, 5),
        (2, 1, 1, 1)
)
, t_comb(r_id, q_id) as (
    select * from values
        (1, 1),
        (1, 2),
        (1, 3),
        (2, 1),
        (2, 2)
)
-- 第一步：获取所有存在的(date, R_id)唯一组合
, t_date_r as (
    select distinct d, r_id from t_base
)
-- 第二步：关联t_comb得到所有需要补全的(date, R_id, Q_id)框架，再左连接t_base取计数
select 
    tdr.d as date,
    tdr.r_id,
    tc.q_id,
    coalesce(tb.c, 0) as c
from t_date_r tdr
join t_comb tc on tdr.r_id = tc.r_id
left join t_base tb 
    on tdr.d = tb.d 
    and tdr.r_id = tb.r_id 
    and tc.q_id = tb.q_id
order by tdr.d, tdr.r_id, tc.q_id;

性能优化说明 #

1. 缩减关联数据量：通过distinct d, r_id提取t_base中活跃的日期-R_id组合，结果集规模远小于原14亿条数据，大幅降低后续关联的计算量。
2. 关联顺序优化：先小表（t_date_r + t_comb）生成完整记录框架，再左连接大表t_base，利用索引快速匹配计数。
3. 索引建议：给t_base建立复合索引(d, r_id, q_id)，使distinct d, r_id和后续左连接操作都能高效利用索引，避免全表扫描。

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