基于Round Robin与记录限制的Hive SQL查询构建需求

核心逻辑梳理 #

要实现你的需求，关键是分两步处理每个月的记录选取：

• 优先从当月（mod分配的月份）记录中，每个账号选1条唯一邮箱（每个账号仅取第一条）
• 如果上述记录数量不足bracket值，再从当月的其他记录中补充，凑够bracket条数

以下是完整的Hive SQL实现：

WITH customer_preprocessed AS (
    SELECT
        account_id,
        email,
        -- 给每个账号下的邮箱生成唯一序号，确保每个账号的邮箱不重复选取第一条
        ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY email) AS email_unique_rank,
        -- 全局排序后用mod运算分配1-6月，保证记录均匀分配
        (ROW_NUMBER() OVER(ORDER BY account_id, email) - 1) % 6 + 1 AS assigned_month
    FROM customer_table
),
monthly_priority_records AS (
    -- 提取每个月中，每个账号的第一条邮箱，标记为最高优先级
    SELECT
        account_id,
        email,
        assigned_month,
        1 AS selection_priority,
        ROW_NUMBER() OVER(PARTITION BY assigned_month ORDER BY account_id) AS priority_seq
    FROM customer_preprocessed
    WHERE email_unique_rank = 1
),
monthly_supplement_records AS (
    -- 提取每个月中，非账号第一条的邮箱，标记为次优先级
    SELECT
        account_id,
        email,
        assigned_month,
        2 AS selection_priority,
        ROW_NUMBER() OVER(PARTITION BY assigned_month ORDER BY account_id, email) AS supplement_seq
    FROM customer_preprocessed
    WHERE email_unique_rank > 1
),
combined_monthly_records AS (
    SELECT * FROM monthly_priority_records
    UNION ALL
    SELECT * FROM monthly_supplement_records
),
final_monthly_allocation AS (
    -- 每个月按优先级排序，选取前bracket条记录
    SELECT
        account_id,
        email,
        assigned_month,
        selection_priority
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER(PARTITION BY assigned_month ORDER BY selection_priority, priority_seq, supplement_seq) AS final_seq
        FROM combined_monthly_records
    ) t
    -- 替换这里的数字为实际bracket值，比如4或10
    WHERE final_seq <= 4
)
SELECT * FROM final_monthly_allocation ORDER BY assigned_month, final_seq;

代码说明 #

1. customer_preprocessed：

   • 用ROW_NUMBER() OVER(PARTITION BY account_id)给每个账号的邮箱编号，确保每个账号的邮箱唯一可区分
   • 全局排序后用(seq-1)%6+1实现6个月的均匀分配，避免mod运算出现0值

2. monthly_priority_records：

   • 筛选每个账号的第一条邮箱（email_unique_rank=1），作为当月优先选取的记录，保证账号唯一性

3. monthly_supplement_records：

   • 筛选当月其他记录，作为补充来源，当优先记录数量不足bracket时启用

4. final_monthly_allocation：

   • 每个月内先按优先级排序（优先记录在前），再取前bracket条，完美匹配你"先选不同账号唯一邮箱，再补充剩余"的需求

调整说明 #

• 如果你需要动态传入bracket值，可以把代码中的4替换为Hive参数，比如${hiveconf:bracket_value}
• 全局排序的字段ORDER BY account_id, email可以根据你的实际需求调整，比如换成创建时间等

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