如何获取包含全部指定Column2变量的5万行SQL随机样本？

你原来的查询之所以抽不全那7个Column2的指定值，核心问题在于全局随机抽样的不确定性——就像从混着7种颜色的球堆里抓5万个，运气差的时候，某些颜色的球可能刚好没被抓到，尤其是如果某些颜色的球本身数量偏少的话，这种情况更容易发生。

要解决这个问题，我们需要用分层抽样的思路：先确保每个指定类别都能进入样本，再补充剩下的名额，这样就能保证最终的5万行样本里一定包含所有7个变量。下面给你几个实用的方案：

方案1：按比例分层抽样（适用于每个类别数据量充足的情况） #

如果每个Column2的类别都有至少7143行数据（50000÷7≈7143），可以给每个类别分配差不多的抽样名额，最后合并到5万行：

WITH CategorySamples AS (
    SELECT 
        ceo.column1, 
        ceo.column2, 
        cm.column3, 
        cm.column4,
        -- 给每个类别内的行随机排序
        ROW_NUMBER() OVER (PARTITION BY ceo.column2 ORDER BY NEWID()) AS rn
    FROM table1 ceo
    INNER JOIN table2 cm ON cm.column1 = ceo.column1
    WHERE ISNUMERIC(ceo.column1) != 0 
        AND ceo.column2 IN ('zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg','kkkk','ooo')
)
SELECT TOP 50000 column1, column2, column3, column4
FROM (
    -- 每个类别先抽7143行，总共49991行
    SELECT column1, column2, column3, column4
    FROM CategorySamples
    WHERE rn <= 7143
    UNION ALL
    -- 补充剩下的9行，从符合条件的剩余数据里随机抽
    SELECT 
        ceo.column1, 
        ceo.column2, 
        cm.column3, 
        cm.column4
    FROM table1 ceo
    INNER JOIN table2 cm ON cm.column1 = ceo.column1
    WHERE ISNUMERIC(ceo.column1) != 0 
        AND ceo.column2 IN ('zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg','kkkk','ooo')
        AND NOT EXISTS (
            SELECT 1 FROM CategorySamples cs
            WHERE cs.column1 = ceo.column1 
                AND cs.column2 = ceo.column2 
                AND cs.column3 = cm.column3 
                AND cs.column4 = cm.column4
                AND cs.rn <=7143
        )
    ORDER BY NEWID()
    TOP 9
) CombinedSamples
ORDER BY NEWID(); -- 最后打乱整体顺序，让样本更随机

方案2：兼容小数据量类别（某些类别数据不足时用） #

如果有的Column2类别数据量很少（比如不足7143行），可以先把这类别的所有数据都纳入样本，剩下的名额再分配给其他数据充足的类别：

WITH CategoryCounts AS (
    -- 先统计每个类别的总数据量
    SELECT 
        ceo.column2, 
        COUNT(*) AS total_rows
    FROM table1 ceo
    INNER JOIN table2 cm ON cm.column1 = ceo.column1
    WHERE ISNUMERIC(ceo.column1) != 0 
        AND ceo.column2 IN ('zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg','kkkk','ooo')
    GROUP BY ceo.column2
),
CategorySamples AS (
    SELECT 
        ceo.column1, 
        ceo.column2, 
        cm.column3, 
        cm.column4,
        ROW_NUMBER() OVER (PARTITION BY ceo.column2 ORDER BY NEWID()) AS rn,
        cc.total_rows
    FROM table1 ceo
    INNER JOIN table2 cm ON cm.column1 = ceo.column1
    INNER JOIN CategoryCounts cc ON cc.column2 = ceo.column2
    WHERE ISNUMERIC(ceo.column1) != 0 
        AND ceo.column2 IN ('zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg','kkkk','ooo')
),
AllRequiredSamples AS (
    -- 数据量少的类别全取，数据量多的按基础名额取
    SELECT 
        column1, column2, column3, column4
    FROM CategorySamples
    WHERE rn <= CASE WHEN total_rows <= 7143 THEN total_rows ELSE 7143 END
),
RemainingQuota AS (
    -- 计算还需要多少行才能凑够5万
    SELECT 50000 - COUNT(*) AS remaining
    FROM AllRequiredSamples
)
SELECT TOP 50000 column1, column2, column3, column4
FROM (
    SELECT column1, column2, column3, column4 FROM AllRequiredSamples
    UNION ALL
    -- 从剩余数据里随机抽取补充名额
    SELECT 
        ceo.column1, 
        ceo.column2, 
        cm.column3, 
        cm.column4
    FROM table1 ceo
    INNER JOIN table2 cm ON cm.column1 = ceo.column1
    WHERE ISNUMERIC(ceo.column1) != 0 
        AND ceo.column2 IN ('zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg','kkkk','ooo')
        AND NOT EXISTS (
            SELECT 1 FROM AllRequiredSamples ars
            WHERE ars.column1 = ceo.column1 
                AND ars.column2 = ceo.column2 
                AND ars.column3 = cm.column3 
                AND ars.column4 = cm.column4
        )
    ORDER BY NEWID()
    TOP (SELECT remaining FROM RemainingQuota)
) FinalSamples
ORDER BY NEWID();

方案3：极简版（仅确保每个类别至少1行） #

如果不需要严格的比例，只是想保证7个类别都出现在样本里，剩下的随机抽就行，这个方案更简单：

WITH AllValidData AS (
    SELECT 
        ceo.column1, 
        ceo.column2, 
        cm.column3, 
        cm.column4
    FROM table1 ceo
    INNER JOIN table2 cm ON cm.column1 = ceo.column1
    WHERE ISNUMERIC(ceo.column1) != 0 
        AND ceo.column2 IN ('zzz', 'xxxx', 'yyyy', 'hhhh', 'ggggg','kkkk','ooo')
),
CategoryMinSamples AS (
    -- 每个类别至少取1行
    SELECT column1, column2, column3, column4
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY NEWID()) AS rn
        FROM AllValidData
    ) t
    WHERE rn = 1
),
RemainingSamples AS (
    -- 剩下的49993行从所有数据里随机抽（排除已经取过的7行）
    SELECT TOP 49993 column1, column2, column3, column4
    FROM AllValidData
    WHERE NOT EXISTS (
        SELECT 1 FROM CategoryMinSamples cms
        WHERE cms.column1 = AllValidData.column1 
            AND cms.column2 = AllValidData.column2 
            AND cms.column3 = AllValidData.column3 
            AND cms.column4 = AllValidData.column4
    )
    ORDER BY NEWID()
)
-- 合并结果并打乱顺序
SELECT column1, column2, column3, column4
FROM CategoryMinSamples
UNION ALL
SELECT column1, column2, column3, column4
FROM RemainingSamples
ORDER BY NEWID();

这些方案的核心逻辑都是先锁定每个目标类别的样本，再补充剩余名额，彻底避免了全局随机抽样导致的类别缺失问题。

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