Hive如何查询获取每个用户各套餐对应的首次订阅记录

原有SQL问题说明 #

你编写的SQL存在几处问题：

1. 字段名不匹配：子查询中使用的subscription_purchase_date与实际表结构中的日期字段start_date不符
2. 关联条件缺失：JOIN逻辑仅关联了用户ID和日期，缺少plan字段关联，会导致同用户同日期不同套餐的记录出现错误匹配
3. 字段返回不全：SELECT子句仅返回了套餐和用户ID，缺少需求要求的订阅ID、起止日期等字段
4. 极端场景兼容差：如果存在同一用户同一套餐同一订阅日有多条记录的情况，会返回多条不符合要求的重复数据

方案1：修正原有JOIN写法 #

适合低版本Hive环境，修正后代码如下：

SELECT 
    H.user_id,
    H.subscription_id,
    H.start_date,
    H.end_date,
    H.plan
FROM my_table H
INNER JOIN (
    SELECT 
        user_id, 
        plan, 
        MIN(start_date) AS first_sub_date
    FROM my_table
    GROUP BY user_id, plan
) X
ON H.user_id = X.user_id 
AND H.plan = X.plan 
AND H.start_date = X.first_sub_date;

如果存在同用户同套餐同起始日多笔订阅的场景，可额外加去重逻辑，Hive 2.1.0及以上版本可直接加QUALIFY ROW_NUMBER() OVER (PARTITION BY H.user_id, H.plan ORDER BY H.subscription_id) = 1即可。

方案2：窗口函数实现（Hive环境更推荐） #

仅需扫描一次表，性能更好，逻辑更简洁，代码如下：

SELECT 
    user_id,
    subscription_id,
    start_date,
    end_date,
    plan
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, plan 
            ORDER BY start_date ASC, subscription_id ASC
        ) AS rn
    FROM my_table
) temp
WHERE rn = 1;

逻辑说明：

• PARTITION BY user_id, plan 按用户+套餐维度独立分组
• ORDER BY start_date ASC 将同组内订阅开始时间最早的记录排在最前，添加subscription_id ASC是为了同日期多笔订阅时取最小订阅ID保证唯一
• 外层筛选rn=1即可得到每个用户每个套餐的最早订阅记录

上述两种方案均能匹配你提供的样例数据，输出符合预期的结果。

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