mysql - HiveQL 按列值的子字符串分组并识别缺失的组

Question

我有一个配置单元表，其中有一个名为 DATALIST 的列。它可以有以下值

XYZ_EF1_GHD_OLDDAT
XYZ_EF1_GHD_NEWFORMAT
ABC_FG1_JJJ_ASD_OLDDAT
TTT_LMN_IJK_YHVS_TXY_OLDDAT
TTT_LMN_IJK_YHVS_TXY_NEWFORMAT
PQR_GHSS_TYHT_NEWFORMAT

我需要创建一个输出来识别列中没有同时包含 _NEWFORMAT 和 _OLDDAT 的所有名称。在这些情况下，它应该输出以下内容

Value                          Reason
ABC_FG1_JJJ_ASD               Missing NEW
PQR_GHSS_TYHT                 Missing OLD
XYZ_EF1_GHD                   Contains Both NEW and OLD
TTT_LMN_IJK_YHVS_TXY          Contains both NEW and OLD

任何有关 SQL/HIVEQL 逻辑的建议/帮助都将不胜感激。

Answer 1

在子查询中，您可以使用 regexp functions regexp_replace() and regexp_extract()将值与后缀分开。然后，您可以在外部查询中聚合和应用逻辑:

select 
    value,
    case 
        when min(suffix) = '_NEWFORMAT' and max(suffix) = '_OLDDAT' 
            then 'Contains both NEW and OLD'
        when max(suffix) = '_NEWFORMAT'
            then 'Missing OLD'
        else 'Missing NEW'
    end reason
from (
    select 
        regexp_replace(datalist, '_OLDDAT|_NEWFORMAT$', '') value,
        regexp_extract(datalist, '(_OLDDAT|_NEWFORMAT)$', 1) suffix
    from mytable
) x
group by value