记录下 hivesql 的 case when 的注意事项:
1. sum ( case when ... then ... else ... end ),不要漏了else里面的情况,这个可能会导致计算错误。
--近一个月,不同事件人均发生次数
select
t2.type,
sum(tot) as total,
sum(user_num) as total_user_num
--近一个月,不同事件类型,每天每个用户的发生次数
from (
select
a.type,
dt,
sum(case when nvl(use_times,'')<>'' then use_times end) as tot --发生次数
from tb1
where dt between '2099-01-01' and '2099-01-31'
group by a.type, dt
) t1
--近一个月,每天不同事件类型的用户总数,左关联剔除没有数据上报的类型
left join (
select type, user_num, dt
from tb2
where dt between '2099-01-01' and '2099-01-31'
) t2
on t1.type = t2.type and t1.dt = t2.dt
group by t2.type
这边 sum(case when nvl(use_times,'')<>'' then use_times end) as tot,想统计次数不为null和空串的总和,但是其实没有什么意义,漏掉else的情况,默认还是为NULL。这样导致后面关联总人数的时候,把type对应次数为NULL的也统计进去了,分母偏大。应该在最后把NULL的情况过滤掉。
2. case when 逻辑错误。
case
when x>0 and x≤10 then (0, 10]
when x≤20 then (10, 20]
when x≤30 then (20, 30]
when x≤40 then (30, 40]
when x≤50 then (40, 50]
else (50,+∞)
end as range
这边犯了一个逻辑错误。case when是从上往下读条件的,但是当x=0的时候,第一种情况不满足,会走到第二种情况。
3. 当要判断的条件在同一行数据的时候,注意不能用case when去判断了,要分开统计。
--假设map类型的字段info有一条数据为:
>>>{"sw_1":"1", "sw_2":"1", "sw_3":"0"}
select
case
when info['sw_1'] = 1 then '开关1打开'
when info['sw_2'] = 1 then '开关2打开'
when info['sw_3'] = 1 then '开关3打开'
end as type
,count(id) as user_number
from xxxx
group by
case
when info['sw_1'] = 1 then '开关1打开'
when info['sw_2'] = 1 then '开关2打开'
when info['sw_3'] = 1 then '开关3打开'
end
这样统计3个开关打开的用户数是不行的,当开关1=1的时候,就不会往下走了,导致数据统计会有误。需要分别统计。