case-when-like-regexp

i7uaboj4  于 2021-05-29  发布在  Hadoop
关注(0)|答案(1)|浏览(436)

我想在配置单元表中使用case-when,like和正则表达式编写一个查询。我用过 regexp 以及 rlike ,但我没有得到想要的结果。我目前的尝试如下

select distinct ending from
(select date, ending, name, count(distinct id) 
from (select CONCAT_WS("/",year,month,day,hour) as date, id, name,
case when type = 'TRAN' then 'tran'
when events regexp '%[:]no_reply[:]%[^o][^n][:]incomplete[:]%' and type rlike '%HUP' then 'con'
when events not regexp '%[:]no_reply[:]%[^o][^n][:]incomplete[:]%' and type rlike '%HUP'  then 'aban'
else 'other'
end as ending
from data_struct1) tmp
group by date, ending, name) tmp2;

还有

select distinct ending from
    (select date, ending, name, count(distinct id) 
    from (select CONCAT_WS("/",year,month,day,hour) as date, id, name,
    case when type = 'TRAN' then 'tran'
    when events rlike '%[:]no_reply[:]%[^o][^n][:]incomplete[:]%' and type rlike '%HUP' then 'con'
    when events not rlike '%[:]no_reply[:]%[^o][^n][:]incomplete[:]%' and type rlike '%HUP'  then 'aban'
    else 'other'
    end as ending
    from data_struct1) tmp
    group by date, ending, name) tmp2;

两个查询都返回不正确的结果(语法不错,只是结果不正确)。

xcitsw88

xcitsw881#

regex量词上有很多文档,例如:https://docs.microsoft.com/en-us/dotnet/standard/base-types/quantifiers-in-regular-expressions

select 'opencase_2,initial_state:inquiry,inquiry:no_reply:initial_state:incomplete::,inquiry:reask:secondary_state:complete::' regexp 'no_reply:[^:]+:incomplete';

OK
true

这也是错误的: rlike '%HUP' . 应该是这样的 '.*HUP$' (在字符串的末尾)或简单的“hup”,如果hup位于何处并不重要:在字符串的中间、结尾或开头 rlike 以及 regexp 在同样的查询工作中,最好使用相同的运算符:regexp或rlike only。这两个是同义词。
测试:https://regex101.com/r/ksg67v/1

相关问题