如何使用TSQL按指定变量比例抽取约8000条随机样本？

要从38万条记录的表中抽取8000条同时满足**性别4:1（Male/Female）和四分类4:3:2:1（Heavy/Moderate/Light/Very Light）**的随机样本，分层抽样是最靠谱的方案——先按性别+四分类的交叉维度分组，再给每个组分配对应比例的样本量，最后从各组随机抽取。

第一步：计算各交叉组的目标样本量 #

先把总样本拆解到每个细分组：

• 总样本8000，性别比例4:1 → Male需6400条，Female需1600条
• 四分类比例4:3:2:1（总比例和为10），所以每个性别下的分类样本量为：
  • Male组：Heavy=2560，Moderate=1920，Light=1280，Very Light=640
  • Female组：Heavy=640，Moderate=480，Light=320，Very Light=160

第二步：TSQL实现代码 #

假设你的表名为YourTableName，性别字段为Gender，四分类字段为UsageCategory，可以用以下代码：

WITH RankedRecords AS (
    SELECT 
        *,
        -- 按性别+分类分组，给每条记录生成随机排名
        ROW_NUMBER() OVER (
            PARTITION BY Gender, UsageCategory 
            ORDER BY NEWID()
        ) AS RandomRank
    FROM YourTableName
)
SELECT *
FROM RankedRecords
WHERE
    -- 按各分组的目标数量筛选
    (Gender = 'Male' AND UsageCategory = 'Heavy' AND RandomRank <= 2560)
    OR (Gender = 'Male' AND UsageCategory = 'Moderate' AND RandomRank <= 1920)
    OR (Gender = 'Male' AND UsageCategory = 'Light' AND RandomRank <= 1280)
    OR (Gender = 'Male' AND UsageCategory = 'Very Light' AND RandomRank <= 640)
    OR (Gender = 'Female' AND UsageCategory = 'Heavy' AND RandomRank <= 640)
    OR (Gender = 'Female' AND UsageCategory = 'Moderate' AND RandomRank <= 480)
    OR (Gender = 'Female' AND UsageCategory = 'Light' AND RandomRank <= 320)
    OR (Gender = 'Female' AND UsageCategory = 'Very Light' AND RandomRank <= 160);

关键细节说明 #

• NEWID()：SQL Server中用它生成随机排序，比RAND()更适合分组随机，因为RAND()在同一查询中只会生成一次固定值。
• 处理样本量不足的情况：如果某个交叉组的实际记录数小于目标数量（比如Female+Very Light只有100条，但我们要160条），上面的代码会自动取该组的全部记录，最终总样本量会略小于8000。如果需要严格凑够8000，可以在抽取后从其他有剩余记录的组里补充随机样本，不过会轻微偏离比例，示例代码如下（可选）：

-- 先抽取基础样本
WITH BaseSample AS (
    SELECT *
    FROM RankedRecords
    WHERE 
        (Gender = 'Male' AND UsageCategory = 'Heavy' AND RandomRank <= 2560)
        OR (Gender = 'Male' AND UsageCategory = 'Moderate' AND RandomRank <= 1920)
        OR (Gender = 'Male' AND UsageCategory = 'Light' AND RandomRank <= 1280)
        OR (Gender = 'Male' AND UsageCategory = 'Very Light' AND RandomRank <= 640)
        OR (Gender = 'Female' AND UsageCategory = 'Heavy' AND RandomRank <= 640)
        OR (Gender = 'Female' AND UsageCategory = 'Moderate' AND RandomRank <= 480)
        OR (Gender = 'Female' AND UsageCategory = 'Light' AND RandomRank <= 320)
        OR (Gender = 'Female' AND UsageCategory = 'Very Light' AND RandomRank <= 160)
),
-- 收集所有未被选中的记录
RemainingRecords AS (
    SELECT *
    FROM YourTableName
    WHERE NOT EXISTS (
        SELECT 1 FROM BaseSample b WHERE b.PrimaryKey = YourTableName.PrimaryKey
    )
    ORDER BY NEWID()
)
-- 合并基础样本和补充样本
SELECT * FROM BaseSample
UNION ALL
SELECT TOP (8000 - (SELECT COUNT(*) FROM BaseSample)) * FROM RemainingRecords;

验证比例 #

抽取完成后，可以用以下SQL验证样本是否符合预期比例：

-- 验证性别比例
SELECT Gender, COUNT(*) AS Count, 
       CAST(COUNT(*) AS FLOAT)/8000 AS Proportion
FROM YourSampleTable
GROUP BY Gender;

-- 验证四分类比例
SELECT UsageCategory, COUNT(*) AS Count, 
       CAST(COUNT(*) AS FLOAT)/8000 AS Proportion
FROM YourSampleTable
GROUP BY UsageCategory;

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