T-SQL如何按指定比例为每个唯一ID随机抽取对应年份处方日期

前置说明 #

120万唯一用户ID中仅30万存在2020年取药记录，若严格要求2020年占比40%，最大可支持的总抽样量为75万（300000/0.4）。若需覆盖全部120万用户，2020年最高占比仅能达到25%，可按需调整代码中的配额参数。

实现逻辑 #

1. 预处理原始数据，为每条记录标注取药年份，同时标记每个用户是否有2020年取药记录
2. 按占比要求计算各年份抽样配额
3. 为每个用户每年的记录去重，仅保留1条候选
4. 分层随机抽样，优先完成2020年配额，其余年份抽样排除已被抽取的用户，确保每个用户仅输出1条记录

示例代码 #

-- 预处理数据：标注年份、用户2020年记录标识
WITH preprocessed_data AS (
    SELECT
        ID,
        取药日期,
        YEAR(取药日期) AS 取药年份,
        MAX(CASE WHEN YEAR(取药日期) = 2020 THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_2020_record
    FROM 你的处方表名
),
-- 定义各年份抽样配额，此处按总样本75万计算
year_quota AS (
    SELECT 2015 AS target_year, CAST(750000 * 0.05 AS INT) AS quota UNION ALL
    SELECT 2016 AS target_year, CAST(750000 * 0.1 AS INT) AS quota UNION ALL
    SELECT 2017 AS target_year, CAST(750000 * 0.1 AS INT) AS quota UNION ALL
    SELECT 2018 AS target_year, CAST(750000 * 0.15 AS INT) AS quota UNION ALL
    SELECT 2019 AS target_year, CAST(750000 * 0.2 AS INT) AS quota UNION ALL
    SELECT 2020 AS target_year, 300000 AS quota -- 受限2020年可用用户量
),
-- 每个用户每年仅保留1条随机候选记录
user_year_candidates AS (
    SELECT
        ID,
        取药日期,
        取药年份,
        has_2020_record
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY ID, 取药年份 ORDER BY NEWID()) AS rn
        FROM preprocessed_data
    ) t
    WHERE rn = 1
),
-- 2020年优先抽样
sample_2020 AS (
    SELECT TOP (SELECT quota FROM year_quota WHERE target_year = 2020)
        ID,
        取药日期,
        取药年份
    FROM user_year_candidates
    WHERE 取药年份 = 2020
    ORDER BY NEWID()
),
-- 剩余年份抽样，排除已被2020年抽取的用户
sample_other_years AS (
    SELECT
        ID,
        取药日期,
        取药年份,
        ROW_NUMBER() OVER (PARTITION BY 取药年份 ORDER BY NEWID()) AS rn
    FROM user_year_candidates
    WHERE 取药年份 != 2020
    AND ID NOT IN (SELECT ID FROM sample_2020)
)
-- 合并结果输出
SELECT ID, 取药日期, 取药年份 FROM sample_2020
UNION ALL
SELECT so.ID, so.取药日期, so.取药年份
FROM sample_other_years so
JOIN year_quota q ON so.取药年份 = q.target_year
WHERE so.rn <= q.quota

调整说明 #

• 将代码中你的处方表名替换为实际业务表名即可运行
• 若需覆盖全部120万用户，可将2020年配额设为300000，其余年份配额按120万为基数乘以对应占比计算即可
• NEWID()是SQL Server中实现随机排序的标准函数，无需额外依赖

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