给定绝对样本量的SQL分层抽样实现方案

问题场景 #

现有总体数据如下：

a
b
b
c
c
c
c

需要编写SQL语句生成任意规模的分层样本，当指定样本量为4时，期望输出结果为：

a
b
c
c

解决方案 #

假设数据存储在表sample_data中，目标字段为category，可以通过窗口函数结合样本量分配逻辑实现分层抽样：

WITH grouped_data AS (
    -- 统计每个分组的行数
    SELECT 
        category,
        COUNT(*) AS group_count
    FROM sample_data
    GROUP BY category
),
total_info AS (
    -- 获取总数据行数和分组总数
    SELECT
        SUM(group_count) AS total_rows,
        COUNT(*) AS group_num
    FROM grouped_data
),
initial_allocation AS (
    -- 按占比计算初始样本量，保证每个分组至少1条（样本量≥分组数时）
    SELECT
        gd.category,
        gd.group_count,
        CASE
            WHEN ti.total_rows = 0 THEN 0
            -- 样本量≥分组数：每个分组先分1条，剩余样本按剩余行数占比分配
            WHEN ti.sample_size >= ti.group_num THEN
                1 + FLOOR((gd.group_count - 1) / (ti.total_rows - ti.group_num) * (ti.sample_size - ti.group_num))
            -- 样本量<分组数：随机选对应数量的分组各取1条
            ELSE
                IF(ROW_NUMBER() OVER (ORDER BY RAND()) <= ti.sample_size, 1, 0)
        END AS sample_count
    FROM grouped_data gd, total_info ti
    CROSS JOIN (SELECT 4 AS sample_size) AS target -- 替换这里的数字为指定样本量
),
adjusted_allocation AS (
    -- 调整样本量，确保总和等于目标值
    SELECT
        category,
        sample_count + 
            CASE
                WHEN SUM(sample_count) OVER () > (SELECT sample_size FROM target) THEN
                    -1 * ROW_NUMBER() OVER (ORDER BY sample_count DESC)
                WHEN SUM(sample_count) OVER () < (SELECT sample_size FROM target) THEN
                    ROW_NUMBER() OVER (ORDER BY sample_count DESC)
                ELSE 0
            END AS final_sample_count
    FROM initial_allocation
),
randomized_rows AS (
    -- 给每个分组内的行随机排序
    SELECT
        category,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY RAND()) AS rn
    FROM sample_data
)
SELECT rr.category
FROM randomized_rows rr
JOIN adjusted_allocation aa ON rr.category = aa.category
WHERE rr.rn <= aa.final_sample_count
ORDER BY rr.category;

逻辑说明 #

1. 分组统计：先计算每个分组的行数和总数据量，为样本分配做基础准备
2. 初始样本分配：
   • 若目标样本量≥分组数，每个分组先分配1条，剩余样本按各分组的剩余行数占比分配
   • 若目标样本量<分组数，随机挑选对应数量的分组各取1条
3. 样本量修正：调整初始分配的误差，确保最终抽取的样本总数严格等于指定值
4. 随机抽样：给每个分组内的行随机排序，抽取对应数量的行

针对示例场景，总数据7行、样本量4：

• 分组a有1行，分配1条；分组b有2行，分配1条；分组c有4行，分配2条，总和刚好4，最终抽取结果符合预期。

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