SQL如何按ColA分组随机采样整组数据而非单条行记录

你之前使用的TABLE_SAMPLE BERNOULI属于行级采样逻辑，会随机抽取单条记录，必然会拆分同一个ColA对应的完整分组，无法满足保留整组事件序列的需求。

基础版：按比例整组采样+ColB分组占比匹配 #

通用SQL实现（支持大部分带窗口函数的SQL引擎，如Spark SQL、Hive SQL、PostgreSQL等）：

WITH distinct_cola_groups AS (
    -- 提取所有唯一的ColA分组及对应ColB属性
    SELECT DISTINCT ColA, ColB
    FROM your_table_name
),
sampling_groups AS (
    SELECT
        ColA,
        -- 按ColB分层后对分组打随机值
        RAND() AS random_score
    FROM distinct_cola_groups
    -- 如需调整采样比例，修改此处0.3即可，示例为采样30%的ColA分组
    QUALIFY random_score <= 0.3
)
-- 关联原表获取选中分组的全部行记录
SELECT t.*
FROM your_table_name t
JOIN sampling_groups s ON t.ColA = s.ColA

进阶版：严格匹配ColB分组总行数占比 #

如果需要最终结果中10个ColB分组的行数占比和原始表完全一致，可以用配额控制的分层采样逻辑：

WITH b_row_stats AS (
    -- 计算各ColB分组的原始行数占比
    SELECT
        ColB,
        COUNT(*) AS b_total_rows,
        SUM(COUNT(*)) OVER() AS global_total_rows
    FROM your_table_name
    GROUP BY ColB
),
cola_group_stats AS (
    -- 统计每个ColA分组的行数及所属ColB
    SELECT
        ColA,
        ColB,
        COUNT(*) AS group_rows
    FROM your_table_name
    GROUP BY ColA, ColB
),
-- 此处示例最终结果总产出为10000行，可根据需求调整数值
target_quota AS (
    SELECT
        ColB,
        ROUND(10000 * b_total_rows / global_total_rows) AS target_rows
    FROM b_row_stats
),
ranked_groups AS (
    SELECT
        c.ColA,
        c.ColB,
        -- 按ColB分层后对分组随机排序并累加行数
        SUM(c.group_rows) OVER(PARTITION BY c.ColB ORDER BY RAND() ROWS UNBOUNDED PRECEDING) AS cumulative_rows
    FROM cola_group_stats c
    JOIN target_quota q ON c.ColB = q.ColB
),
selected_groups AS (
    SELECT DISTINCT ColA
    FROM ranked_groups r
    JOIN target_quota q ON r.ColB = q.ColB
    WHERE r.cumulative_rows <= q.target_rows
)
SELECT t.*
FROM your_table_name t
JOIN selected_groups s ON t.ColA = s.ColA

逻辑说明 #

• 所有采样逻辑都作用在ColA分组维度，保证选中的分组完整保留所有事件行
• 按ColB分层处理的逻辑，确保最终结果中各ColB分组的行数占比和原始分布一致

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