基于HiveQL按列子串分组并识别缺失格式分组的技术问询

我来帮你搞定这个需求！核心思路是先提取每个记录的分组名称（去掉末尾的_OLDDAT或_NEWFORMAT后缀），再统计每个分组下两种后缀的存在情况，最后根据统计结果生成对应的Reason。

步骤拆解 #

1. 提取分组名称：用正则表达式去掉每个DATALIST值末尾的_OLDDAT或_NEWFORMAT，得到统一的分组标识。
2. 统计后缀存在情况：对每个分组，分别标记是否存在_NEWFORMAT和_OLDDAT的记录。
3. 生成Reason：根据两个标记的组合，判断分组属于哪种情况（同时包含、缺NEW、缺OLD）。

完整HiveQL代码 #

WITH grouped_data AS (
    -- 第一步：提取分组名称，并标记每个记录的后缀类型
    SELECT
        regexp_replace(DATALIST, '_(NEWFORMAT|OLDDAT)$', '') AS Value,
        CASE WHEN DATALIST LIKE '%_NEWFORMAT' THEN 1 ELSE 0 END AS has_new,
        CASE WHEN DATALIST LIKE '%_OLDDAT' THEN 1 ELSE 0 END AS has_old
    FROM your_table_name -- 替换成你的实际表名
),
aggregated_groups AS (
    -- 第二步：对每个分组聚合，统计是否存在两种后缀
    SELECT
        Value,
        MAX(has_new) AS has_new,
        MAX(has_old) AS has_old
    FROM grouped_data
    GROUP BY Value
)
-- 第三步：根据聚合结果生成Reason
SELECT
    Value,
    CASE
        WHEN has_new = 1 AND has_old = 1 THEN 'Contains Both NEW and OLD'
        WHEN has_new = 0 AND has_old = 1 THEN 'Missing NEW'
        WHEN has_new = 1 AND has_old = 0 THEN 'Missing OLD'
        ELSE 'Unknown' -- 处理意外情况，比如没有后缀的记录
    END AS Reason
FROM aggregated_groups
ORDER BY Value;

代码解释 #

• 正则提取分组名：regexp_replace(DATALIST, '_(NEWFORMAT|OLDDAT)$', '') 精准匹配字符串末尾的两种后缀，替换为空，得到干净的分组名称。
• 后缀标记：用CASE语句给每条记录打标记，1表示对应后缀存在，0表示不存在。
• 聚合统计：用MAX()函数对分组内的标记取最大值，只要分组内有一条记录对应后缀存在，结果就是1，否则0。
• Reason判断：通过CASE分支处理三种核心情况，同时保留Unknown分支应对意外数据（比如没有后缀的记录）。

验证结果 #

把你提供的测试数据代入，运行后会得到完全符合你示例的输出：

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