hive行转列

原创于 2020-01-15 15:13:04 发布 · 292 阅读

CC 4.0 BY-SA版权

本文介绍了一种使用SQL进行复杂聚合查询的方法，通过案例展示了如何根据不同字段级别进行计数，并使用case when语句来筛选特定条件的数据。适用于需要处理大量数据并进行多维度统计的场景。

select field_name,
max(case when f_level='F0' THEN count else 0 end) as F0,
max(case when f_level='F1' THEN count else 0 end) as F1,
max(case when f_level='F2' THEN count else 0 end) as F2,
max(case when f_level='F3' THEN count else 0 end) as F3,
max(case when f_level='F4' THEN count else 0 end) as F4,
max(case when f_level='null' THEN count else 0 end) as e
from (
select field_name,
f_level,
count(1) as count
from tmp_weibo_plat_high_quality_user_detail
where dt='20200107' and cast(score as double)>=23 and cast(filtered_fans_num as integer)>1000
group by field_name,f_level) a
group by field_name order by field_name;