Hive实现：为每个用户的固定序列生成缺失标记字段

这个需求我刚好处理过类似场景，用Hive的CTE（公共表表达式）就能清晰搞定，我给你拆解下思路和具体代码～

首先咱们先明确核心逻辑：

• 每个用户必须对应0、1、2三个序列的记录，不管原表有没有这条数据
• 如果用户已经拥有全部三个序列，那所有记录的flag统一为Y
• 如果用户缺了某些序列，原表已有的序列标记Y，缺失的序列标记N

具体实现代码&逻辑说明 #

我用CTE拆分步骤，让代码更易读：

-- 第一步：生成所有用户与固定序列的完整组合
with all_user_seq as (
    select u.User-id, s.seq as sequence
    from (select distinct User-id from your_table) u
    -- 生成固定的序列集合0、1、2
    cross join (select 0 as seq union all select 1 union all select 2) s
),
-- 第二步：统计每个用户实际拥有的不同序列数量
user_seq_count as (
    select User-id, count(distinct sequence) as total_seq
    from your_table
    group by User-id
)
-- 第三步：关联数据生成最终flag
select 
    aus.User-id,
    aus.sequence,
    case 
        -- 用户拥有全部3个序列，所有记录直接标Y
        when usc.total_seq = 3 then 'Y'
        -- 原表存在该用户的当前序列，标Y
        when t.sequence is not null then 'Y'
        -- 其余缺失的序列标N
        else 'N'
    end as flag
from all_user_seq aus
-- 关联原表，判断序列是否存在
left join your_table t 
    on aus.User-id = t.User-id and aus.sequence = t.sequence
-- 关联统计结果，判断用户是否集齐全序列
left join user_seq_count usc 
    on aus.User-id = usc.User-id
-- 按用户和序列排序，和期望输出格式对齐
order by aus.User-id, aus.sequence;

关键步骤解释 #

1. all_user_seq：通过cross join把所有用户和固定的三个序列做笛卡尔积，补全原表中用户缺失的序列行，确保每个用户都有0、1、2三条记录。
2. user_seq_count：分组统计每个用户实际拥有的不同序列数量，用来快速判断该用户是否集齐了全部三个序列。
3. 最终关联环节：用case when分支判断生成flag，优先判断用户是否全序列拥有，再判断当前序列是否存在于原表，逻辑清晰且高效。

执行这段SQL后，输出结果就完全匹配你期望的格式啦～

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