rollup
rollup 用于hive在group by的顺序分组聚合,举例如下:
select mac, stat_date,
avg(cast(cpuOccupancyRate as int)) cpu_avg
from bidata.t_ods_pp_uplink_status_id
where stat_date>=20220119 and stat_date<=20220119
group by rollup(mac,stat_date)
输出结果:
mac | stat_date | cpu_avg |
---|---|---|
NULL | NULL | 3.8 |
30A176EDF550 | 20220119 | 5.645833333333333 |
30A176EDAB70 | 20220119 | 1.9148936170212767 |
30A176EDF550 | NULL | 5.645833333333333 |
30A176EDAB70 | NULL | 1.9148936170212767 |
以上内容可以看出,mac和stat_date会分别进行聚合,输出值为NULL,有时候不希望输出mac为NULL的结果,自然想到通过where mac is not null 进行过滤,实际验证
select * from
(
select mac, stat_date,
avg(cast(cpuOccupancyRate as int)) cpu_avg
from bidata.t_ods_pp_uplink_status_id
where stat_date>=20220119 and stat_date<=20220119
group by rollup(mac,stat_date)
) as t1
where mac is not null
结果如下
mac | stat_date | cpu_avg |
---|---|---|
NULL | NULL | 3.8 |
30A176EDF550 | 20220119 | 5.645833333333333 |
30A176EDAB70 | 20220119 | 1.9148936170212767 |
30A176EDF550 | NULL | 5.645833333333333 |
30A176EDAB70 | NULL | 1.9148936170212767 |
为什么没有完成过滤呢?难道是NULL 不是空?是真的字符串值为NULL吗?经过验证,where mac!=‘NULL’,结果依然如上,满脸问号????
经过反复验证,解决方案如下:
select * from
(
select NVL(mac,'mac') as mac, NVL(stat_date,'stat') as stat_date,
avg(cast(cpuOccupancyRate as int)) cpu_avg
from bidata.t_ods_pp_uplink_status_id
where stat_date>=20220119 and stat_date<=20220119
group by rollup(mac,stat_date)
) as t1
where mac != 'mac'
结果如下
mac | stat_date | cpu_avg |
---|---|---|
30A176EDF550 | 20220119 | 5.645833333333333 |
30A176EDAB70 | 20220119 | 1.9148936170212767 |
30A176EDF550 | stat | 5.645833333333333 |
30A176EDAB70 | stat | 1.9148936170212767 |
为什么????还没找到原因,知道的同学求解答!!