如何编写Hive SQL从多分区各取100个不重复user_id？

这题我熟，刚好处理过类似的分区抽样需求！针对你的场景，我们可以通过去重+窗口函数的组合来实现每个分区取100个互不重复的user_id，具体方案如下：

核心思路 #

1. 先在指定分区范围内，对每个分区的user_id进行去重，得到每个分区的唯一用户集合；
2. 利用窗口函数给每个分区内的唯一user_id分配行号，筛选出行号≤100的记录，即可得到目标结果。

基础版SQL（按user_id排序取前100） #

如果不需要随机抽取，只是按user_id的字典序取前100个不重复值，可以用这个写法：

SELECT etl_dt, user_id
FROM (
    SELECT 
        etl_dt,
        user_id,
        -- 按分区分配行号，排序依据可按需调整
        ROW_NUMBER() OVER(PARTITION BY etl_dt ORDER BY user_id) AS rn
    FROM (
        -- 第一步：在目标分区内去重user_id
        SELECT DISTINCT etl_dt, user_id
        FROM user_log
        WHERE etl_dt BETWEEN '2018-01-01' AND '2018-01-30'
    ) deduplicated_data
) numbered_data
WHERE rn <= 100;

随机抽样版SQL（随机取100个不重复user_id） #

如果需要随机抽取每个分区的100个用户，只需要把窗口函数的排序条件改成随机函数RAND()即可：

SELECT etl_dt, user_id
FROM (
    SELECT 
        etl_dt,
        user_id,
        -- 按分区随机分配行号，实现随机抽样
        ROW_NUMBER() OVER(PARTITION BY etl_dt ORDER BY RAND()) AS rn
    FROM (
        SELECT DISTINCT etl_dt, user_id
        FROM user_log
        WHERE etl_dt BETWEEN '2018-01-01' AND '2018-01-30'
    ) deduplicated_data
) numbered_data
WHERE rn <= 100;

代码解释 #

• 最内层子查询deduplicated_data：通过DISTINCT关键字在每个etl_dt分区内去重user_id，确保后续处理的都是唯一用户；
• 中间层子查询numbered_data：使用ROW_NUMBER()窗口函数，以etl_dt为分区键，给每个分区内的唯一用户分配连续行号；排序条件决定了选取用户的规则（字典序或随机）；
• 最外层筛选rn <= 100：保留每个分区的前100个用户，得到最终结果。

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