hive中举行复杂数据处理时必备函数--窗口函数、行转列列转行、groupingsets
本文总结了数据分析工作中举行复杂数据处理时必要用到的函数，掌握这些函数可以大概大大提效，事半功倍：row_number排序窗口、lag/lead前后窗口、preceding滑动窗口、grouping sets、行转列collect_set/concat_ws、列转行explod/lateral view 。假设有一张学生结果表，字段有【月份、班级、学科、学生id、学生结果】 ，这几个字段，我们必要举行如下分析：
1、学生结果排名：按班级、按学科对学生这个月的测验结果举行排序
2、学生结果较上个月提升：计算学生各科结果较上个月的变革
3、学生结果较上三月提升：计算每个月学生各科结果较上三个月均值的变革
4、学科结果汇总：汇总每个学科的年均匀结果、月均匀结果、各班级年均匀结果、各班级月均匀结果、各个学生的年均匀结果
5、行转列1—学生各科结果展示到一列上：将数据布局变为这样【月份、班级、学生id、学科聚集(语文|数学)、各科结果聚集(90|88)】
6、行专列2—学生各科结果展示到多列上：将数据布局变为这样【月份、班级、学生id、语文结果、数学结果】
7、列转行—学生各科结果在1列上转为多行展示，即将数据布局【月份、班级、学生id、学科聚集(语文|数学)、各科结果聚集(90|88)】转回到【月份、班级、学科、学生id、学生结果】
学生结果表tmp.tmp_student_20250105的建表语句如下：
create table tmp.tmp_student_20250105
(
inc_month string,
class_name string,
subject_name string,
student_id string,
score int
)
insert into tmp.tmp_student_20250105 values
('2024-01','一年级1班', '语文', '001', 80)
, ('2024-01','一年级1班', '语文', '002', 90)
, ('2024-01','一年级1班', '数学', '001', 79)
, ('2024-01','一年级1班', '数学', '002', 99)
, ('2024-01','一年级2班', '语文', '001', 80)
, ('2024-01','一年级2班', '语文', '002', 90)
, ('2024-01','一年级2班', '数学', '001', 79)
, ('2024-01','一年级2班', '数学', '002', 99)
;
1、窗口函数
1.1 排序：row_number
学生结果排名：按班级、按学科对学生这个月的测验结果举行排序
select inc_month, class_name ,subject_name, student_id, score
, row_number() over(partition by inc_month, class_name ,subject_name order by score desc) rn -- 排序
from tmp.tmp_student_20250105 1.2 前后：lag/lead
学生结果较上个月提升：计算学生各科结果较上个月的变革
select inc_month, class_name ,subject_name, student_id, score
, lag(score) over(partition by class_name ,subject_name, student_id order by inc_month ) lt1_subject_score -- 上个月的成绩
from tmp.tmp_student_20250105 1.3 滑动窗口：preceding
学生结果较上三月提升：计算每个月学生各科结果较上三个月均值的变革
select inc_month, class_name ,subject_name, student_id, score
, avg(score) over(partition by class_name ,subject_name, student_id order by inc_month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) lt3_avg_subject_score -- 平均成绩
from tmp.tmp_student_20250105 2、多粒度汇总
学科结果汇总：汇总每个学科的年均匀结果、月均匀结果、各班级年均匀结果、各班级月均匀结果、各个学生的年均匀结果，除了多次union all之外，还可以用grouping sets函数一次性解决
2.1 grouping sets
select grouping__id
, inc_month
, class_name
, subject_name
, student_id
, avg(score) as avg_score
from tmp.tmp_student_20250105
group by inc_month
, class_name
, subject_name
, student_id
grouping sets(
(class_name, subject_name, inc_month)
, (class_name, subject_name)
, (subject_name, inc_month)
, (subject_name)
, (class_name, student_id, subject_name)
) grouping sets的参数是必要汇总的粒度，差别的汇总粒度用括号区分，用逗号分隔。区分差别的汇总粒度可以用grouping_id区分，grouping__id的计算方法如下：
[*]先对 group by 的所有字段 举行 倒序排序，
[*]与grouping sets 里的聚合粒度 举行比对，假如字段出现在该聚合粒度中，则将该位置的值 赋值为1，否则记为0
[*]这样就形成了一个二进制数, 将该二进制数字转化为十进制, 这个十进制数字就是grouping_id
假如着实记不住grouping__id的计算方法，也可以用汇总粒度的值是否为null区分。
3、行列互转
3.1 行转1列：collect_set
学生各科结果展示到一列上：将数据布局变为这样【月份、班级、学生id、学科聚集(语文|数学)、各科结果聚集(90|88)】
select inc_month
, class_name
, student_id
, concat_ws("|", collect_set(subject_name)) subject_name_arr
, concat_ws("|", collect_set(score)) subject_score_arr
from tmp.tmp_student_20250105
group by inc_month
, class_name
, student_id collect_set 将字段转化为数组，并举行去重处理，concat_ws则是将数组用 | 分隔符拼接成字符串，collect_set已经去重，其他字段也必要跟着去重，所以其他字段必要举行group by
3.2 行转多列：case when + max
学生各科结果展示到多列上：将数据布局变为这样【月份、班级、学生id、语文结果、数学结果】
select inc_month
, class_name
, student_id
, max(case when subject_name = "语文" then score else null end) yw_score
, max(case when subject_name = "数学" then score else null end) sx_score
from tmp.tmp_student_20250105
group by inc_month
, class_name
, student_id case when 可以大概之展示特定条件的数据，但是还必要共同聚合函数才气实现真正的去重，所以在前边必要加max/min这样的函数。
3.3 列转行: explode/posexplode
列转行—学生各科结果在1列上转为多行展示，即将数据布局【月份、班级、学生id、学科聚集(语文|数学)、各科结果聚集(90|88)】转回到【月份、班级、学生id、学科、学生结果】
[*]explode + lateral view 能将学科这一列转为多行
-- 把 学科 转为列
select inc_month
, class_name
, student_id
, subject_name_t.subject_name
from (
select inc_month
, class_name
, student_id
, concat_ws("|", collect_set(subject_name)) subject_name_arr
, concat_ws("|", collect_set(score)) subject_score_arr
from tmp.tmp_student_20250105
group by inc_month
, class_name
, student_id
) t
lateral view explode(split(subject_name_arr, ',') subject_name_t as subject_name explode（爆炸
页:
[1]