如何在Impala/HiveQL中按周重采样类别分布并保留最大数据量

此前我提问过如何将服从随机分布的数据集重采样为指定类别分布，同时获取各类别最大可用数据点，该问题已有解决方案，但仅提供了Python实现。当前业务场景需要我使用兼容Impala或HiveQL的纯SQL实现该能力。

为便于理解问题，以下是按自然周统计的三类别分布模拟数据（含实际分布和目标分布）：

| Week | Class | Count | Distribution | Desired Distribution |
|------|-------|-------|--------------|----------------------|
| 01   | A     | 954   |     0.36     |         0.55         |
| 01   | B     | 554   |     0.21     |         0.29         |
| 01   | C     | 1145  |     0.43     |         0.16         |
| 02   | A     | 454   |     0.21     |         0.55         |
| 02   | B     | 944   |     0.44     |         0.29         |
| 02   | C     | 748   |     0.35     |         0.16         |

可见实际分布为随机值，与基准*目标分布（Desired Distribution）*差异较大。

我需要保留每个自然周各分类下的最大可用数据记录，因此不能直接使用sum(count_per_week) * desired_distribution的方式计算各分类应保留数据量：更复杂的场景是，目标占比最高的类别（如Class="A"）实际存量往往最少，该前提是方案必须满足的约束。最终计算得到的数值将用于筛选数据记录到指定量级。

因此需要实现SQL方案，按自然周分组，计算各周可保留的最大数据量，并据此筛选对应数据记录。

如何使用SQL实现按自然周将类别计数分布重采样为指定目标分布，同时保留各分类下尽可能多的有效数据？

实现逻辑 #

核心思路是找到每周的采样瓶颈：计算每周每个类别「实际存量/目标占比」的最小值，该值就是本周可采样的最大总数据量，此时瓶颈类别会被全量保留，其余类别按目标占比乘以总采样量得到应采样数量，均不会超过该类别的实际存量，保证整体数据量最大化。

兼容Impala/Hive的SQL实现 #

假设你有两张基础表：

• 原始数据表source_data：包含week（自然周标识）、class（类别标识）、以及其他业务字段
• 目标分布配置表desired_dist：包含class（类别标识）、desired_ratio（对应类别的目标占比）

完整SQL如下：

WITH 
-- 统计每周每个类别的实际存量
week_class_count AS (
    SELECT 
        week,
        class,
        COUNT(1) AS actual_cnt
    FROM source_data
    GROUP BY week, class
),
-- 关联目标占比，计算每个类别可支撑的理论最大总采样量
week_class_limit AS (
    SELECT 
        wc.week,
        wc.class,
        wc.actual_cnt,
        dd.desired_ratio,
        wc.actual_cnt / dd.desired_ratio AS support_total
    FROM week_class_count wc
    JOIN desired_dist dd ON wc.class = dd.class
),
-- 取每周最小的支撑总采样量，作为本周实际总采样上限
week_total AS (
    SELECT 
        week,
        MIN(support_total) AS sample_total
    FROM week_class_limit
    GROUP BY week
),
-- 计算每周每个类别的实际采样数
week_class_sample AS (
    SELECT 
        wl.week,
        wl.class,
        -- 取最小值避免浮点误差导致采样数超过实际存量
        LEAST(wl.actual_cnt, FLOOR(wt.sample_total * wl.desired_ratio)) AS sample_cnt
    FROM week_class_limit wl
    JOIN week_total wt ON wl.week = wt.week
),
-- 给每周每个类别的原始数据打序号，支持随机采样或按业务规则排序
ranked_data AS (
    SELECT 
        sd.*,
        -- 随机采样用RAND()，需按业务规则采样可替换为对应排序逻辑
        ROW_NUMBER() OVER (PARTITION BY sd.week, sd.class ORDER BY RAND()) AS rn
    FROM source_data sd
    JOIN week_class_sample ws 
        ON sd.week = ws.week 
        AND sd.class = ws.class
)
-- 筛选得到最终采样结果
SELECT *
FROM ranked_data
WHERE rn <= sample_cnt

说明 #

• 所有语法均兼容Impala和HiveQL，无引擎专属特性
• 若不需要随机采样，可将ORDER BY RAND()替换为业务排序规则，比如按时间取最新数据可改成ORDER BY create_time DESC
• 若目标分布是固定值不需要配置表，可直接在week_class_limit中用CASE语句硬编码对应类别的desired_ratio即可

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