如何在SQL中为测试组匹配分布相似的对照组？

核心背景回顾 #

你面临的需求是从5000万用户池中，为5万+测试用户匹配规模相同、数值特征均值一致的对照组：

• 分类特征用inner join关联
• 数值特征通过取整来控制分层粒度，但当前遇到两个问题：
  1. 取整到十位时，仅匹配到3.6万对照组用户，未达5万的目标
  2. 提高取整精度到个位时，查询因spool资源耗尽失败
• 同时希望调整逻辑，让用户可以被纳入多个分层，扩大匹配池

现有SQL的潜在问题 #

咱们先拆解下代码里可能拖后腿的地方：

1. 窗口函数排序的不稳定风险 #

你的CTEpl_subs里用ORDER BY null生成行号RN，不同数据库对这种写法的处理差异很大，可能导致同一分层内的行号分配随机且不可控，后续匹配时会浪费潜在的对照组用户资源。

2. NOT IN的性能与逻辑漏洞 #

用al.cust_id not in (select cust_id from ai_pl_SUBS)排除测试用户，当子查询返回NULL时，整个NOT IN会返回NULL导致过滤失效；而且对于5000万量级的表，这种子查询的性能极差，会进一步加剧spool压力。

3. 分层匹配的严格限制 #

当前逻辑是每个对照组用户只能匹配一个测试分层（通过rn <= rn_pl），取整粒度太粗时，部分测试分层的对照组用户存量不足，直接导致总匹配数达不到5万的目标。

现有代码格式化（方便参考） #

with pl_subs as( 
    -- 生成测试组分层的行号
    select al.* ,
        ROW_NUMBER() OVER(
            PARTITION BY al.device_type ,al.report_mnth ,
            round(al.days_to_LAST_FLASH_DTTM, -1) ,round(al.LT_month, -1) ,
            round(al.REVC, -1) ,round(al.usg_in, -2) ,round(al.usg_AC, -1) 
            ORDER BY null
        ) AS RN 
    from ai_pl_SUBS test_gr 
    inner join ai_SUBS_MONTH_CLR al 
        on al.cust_id = test_gr.cust_id 
        and al.report_mnth = test_gr.REGISTERED_mnth 
    where al.report_mnth = '2017-11' 
        and test_gr.REGISTERED_mnth = '2017-11' 
) 
select count(1) -- 仅统计匹配数量
from ( 
    select al.cust_id, pl_subs.rn rn_pl ,
        ROW_NUMBER() OVER(
            PARTITION BY pl_subs.device_type ,pl_subs.report_mnth ,pl_subs.MCID ,
            round(pl_subs.days_to_LF, -1) ,round(pl_subs.LT_month, -1) ,
            round(pl_subs.REVC, -1) ,round(pl_subs.usg_in, -2) ,round(pl_subs.usg_AC, -1) 
            ORDER BY null
        ) AS RN 
    from pl_subs 
    inner join ai_SUBS_MONTH_CLR al 
        on -- 关联分类特征
        pl_subs.device_type = al.device_type 
        and pl_subs.report_mnth = al.report_mnth 
        -- 关联取整后的数值特征
        and round(pl_subs.days_to_LF, -1) = Round(al.days_to_LF, -1) 
        and round(pl_subs.LT_month, -1) = Round(al.LT_month, -1) 
        and round(pl_subs.REVC, -1) = Round(al.REVC, -1) 
        and round(pl_subs.usg_in, -2) = Round(al.usg_in, -2) 
        and round(pl_subs.usg_AC, -1) = Round(al.usg_AC, -1) 
    -- 排除测试组用户
    where al.cust_id not in (select cust_id from ai_pl_SUBS) 
        and al.report_mnth = '2017-11' 
) _out 
where rn <= rn_pl -- 按测试组分层的数量抽取对照组用户

解决方案：允许用户纳入多个分层+性能优化 #

要实现“用户可被纳入多个分层”，我们需要重构匹配逻辑，让同一个对照组用户可以对应多个测试分层，同时优化spool压力：

优化后的SQL代码 #

-- 第一步：统计测试组各分层的用户数量
with test_strata as (
    select 
        device_type,
        report_mnth,
        round(days_to_LAST_FLASH_DTTM, -1) as days_to_LF_rnd,
        round(LT_month, -1) as LT_month_rnd,
        round(REVC, -1) as REVC_rnd,
        round(usg_in, -2) as usg_in_rnd,
        round(usg_AC, -1) as usg_AC_rnd,
        count(*) as test_count -- 记录每个分层的测试用户数
    from ai_pl_SUBS test_gr
    join ai_SUBS_MONTH_CLR al 
        on al.cust_id = test_gr.cust_id 
        and al.report_mnth = test_gr.REGISTERED_mnth
    where al.report_mnth = '2017-11'
        and test_gr.REGISTERED_mnth = '2017-11'
    group by 
        device_type, report_mnth,
        round(days_to_LAST_FLASH_DTTM, -1),
        round(LT_month, -1),
        round(REVC, -1),
        round(usg_in, -2),
        round(usg_AC, -1)
),
-- 第二步：为对照组用户生成可循环使用的行号
control_pool as (
    select 
        cust_id,
        device_type,
        report_mnth,
        round(days_to_LF, -1) as days_to_LF_rnd,
        round(LT_month, -1) as LT_month_rnd,
        round(REVC, -1) as REVC_rnd,
        round(usg_in, -2) as usg_in_rnd,
        round(usg_AC, -1) as usg_AC_rnd,
        -- 随机排序生成行号，保证对照组分布均匀
        ROW_NUMBER() OVER(
            PARTITION BY device_type, report_mnth,
            round(days_to_LF, -1), round(LT_month, -1),
            round(REVC, -1), round(usg_in, -2), round(usg_AC, -1)
        ORDER BY DBMS_RANDOM.VALUE) as control_rn
    from ai_SUBS_MONTH_CLR al
    where al.report_mnth = '2017-11'
        -- 用NOT EXISTS替代NOT IN，避免NULL逻辑漏洞，提升性能
        and not exists (
            select 1 from ai_pl_SUBS t 
            where t.cust_id = al.cust_id
        )
)
-- 第三步：按测试分层的需求抽取对照组用户（允许重复使用）
select 
    cp.cust_id as control_cust_id,
    ts.*
from test_strata ts
join control_pool cp 
    on ts.device_type = cp.device_type
    and ts.report_mnth = cp.report_mnth
    and ts.days_to_LF_rnd = cp.days_to_LF_rnd
    and ts.LT_month_rnd = cp.LT_month_rnd
    and ts.REVC_rnd = cp.REVC_rnd
    and ts.usg_in_rnd = cp.usg_in_rnd
    and ts.usg_AC_rnd = cp.usg_AC_rnd
-- 关键：通过倍数控制，允许同一个对照组用户被多个分层匹配
where cp.control_rn <= ts.test_count * CEIL(50000 / (select count(*) from ai_pl_SUBS))
-- 限制总数量为5万
limit 50000;

关键修改点说明 #

• 允许用户纳入多个分层：通过control_rn <= ts.test_count * 倍数的逻辑，让同一个对照组用户可以被多个测试分层匹配（只要行号满足条件），相当于循环使用对照组用户池，解决了部分分层用户不足的问题。
• 性能优化：
  • 用NOT EXISTS替代NOT IN，避免NULL导致的过滤失效，同时提升大表查询性能
  • 先统计测试分层的用户数，减少后续关联的数据量
  • 用DBMS_RANDOM.VALUE随机排序，保证对照组分布更均匀，避免取整带来的偏差
• 灵活调整粒度：如果取整到十位还是不够，可以尝试动态取整（比如对不同特征用不同精度，days_to_LF取十位，REVC取百位），既保证匹配度，又不会过度增加spool压力。

补充建议 #

如果你的数据库支持近似匹配功能，可以考虑用**倾向性得分匹配(PSM)**替代简单的取整分层，这样能更精准地匹配数值特征的分布，同时减少分层数量，进一步缓解spool压力。

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