按会员状态比例分配病例-对照组的SQL查询需求

需求说明 #

筛选出从未购买过'FISH'的客户，按MEMBERSHIP_STATUS（会员状态）分层，每个会员组内抽取10%作为对照组，剩余90%作为病例组，且不使用动态SQL实现。

解决方案（以MySQL为例） #

WITH filtered_clients AS (
    -- 第一步：筛选未购买过FISH的客户
    SELECT 
        c.*
    FROM TB_CLIENTS c
    LEFT JOIN TB_ORDERS o 
        ON c.CLIENT_ID = o.CLIENT_ID 
        AND o.PRODUCT_NAME = 'FISH'
    WHERE o.CLIENT_ID IS NULL
),
client_groups AS (
    -- 第二步：按会员状态分组，给组内客户随机编号并统计组总人数
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY MEMBERSHIP_STATUS ORDER BY RAND()) AS rn,
        COUNT(*) OVER(PARTITION BY MEMBERSHIP_STATUS) AS group_total
    FROM filtered_clients
)
-- 第三步：按组内比例分配对照组/病例组
SELECT 
    *,
    CASE 
        WHEN rn <= CEIL(group_total * 0.1) THEN '对照组'
        ELSE '病例组'
    END AS group_type
FROM client_groups;

关键逻辑说明 #

1. 筛选未购客户：通过左关联订单表，判断关联结果为空，确保只保留从未购买过'FISH'的客户。
2. 分层编号与计数：利用窗口函数PARTITION BY MEMBERSHIP_STATUS按会员状态分组，ROW_NUMBER()结合随机排序给组内客户分配唯一序号，同时用COUNT(*) OVER()动态计算每个组的总人数。
3. 比例分配：用CEIL()函数对组内人数的10%向上取整（避免小数无法取整的问题），序号小于等于该值的标记为对照组，其余为病例组，保证每个会员组的对照组占比精准贴近10%。

适配Oracle数据库的版本 #

仅需替换随机排序函数：

WITH filtered_clients AS (
    SELECT 
        c.*
    FROM TB_CLIENTS c
    LEFT JOIN TB_ORDERS o 
        ON c.CLIENT_ID = o.CLIENT_ID 
        AND o.PRODUCT_NAME = 'FISH'
    WHERE o.CLIENT_ID IS NULL
),
client_groups AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY MEMBERSHIP_STATUS ORDER BY DBMS_RANDOM.VALUE) AS rn,
        COUNT(*) OVER(PARTITION BY MEMBERSHIP_STATUS) AS group_total
    FROM filtered_clients
)
SELECT 
    *,
    CASE 
        WHEN rn <= CEIL(group_total * 0.1) THEN '对照组'
        ELSE '病例组'
    END AS group_type
FROM client_groups;

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