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的建表语句如下：

1. create table tmp.tmp_student_20250105
2. (
3.    inc_month string,
4.    class_name string,
5.    subject_name string,
6.    student_id string,
7.    score int
8. )
10. insert into tmp.tmp_student_20250105 values
11.     ('2024-01','一年级1班', '语文', '001', 80)
12.     , ('2024-01','一年级1班', '语文', '002', 90)
13.     , ('2024-01','一年级1班', '数学', '001', 79)
14.     , ('2024-01','一年级1班', '数学', '002', 99)
16.     , ('2024-01','一年级2班', '语文', '001', 80)
17.     , ('2024-01','一年级2班', '语文', '002', 90)
18.     , ('2024-01','一年级2班', '数学', '001', 79)
19.     , ('2024-01','一年级2班', '数学', '002', 99)
20.     ;

复制代码

1、窗口函数

1.1 排序：row_number

学生结果排名：按班级、按学科对学生这个月的测验结果举行排序

1. select inc_month, class_name ,subject_name, student_id, score
2. , row_number() over(partition by inc_month, class_name ,subject_name order by score desc) rn -- 排序
3. from tmp.tmp_student_20250105

复制代码

1.2 前后：lag/lead

学生结果较上个月提升：计算学生各科结果较上个月的变革

1. select inc_month, class_name ,subject_name, student_id, score
2. , lag(score) over(partition by class_name ,subject_name, student_id order by inc_month ) lt1_subject_score -- 上个月的成绩
3. from tmp.tmp_student_20250105

复制代码

1.3 滑动窗口：preceding

学生结果较上三月提升：计算每个月学生各科结果较上三个月均值的变革

1. select inc_month, class_name ,subject_name, student_id, score
2. , 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 -- 平均成绩
3. from tmp.tmp_student_20250105

复制代码

2、多粒度汇总

学科结果汇总：汇总每个学科的年均匀结果、月均匀结果、各班级年均匀结果、各班级月均匀结果、各个学生的年均匀结果，除了多次union all之外，还可以用grouping sets函数一次性解决
2.1 grouping sets

1. select grouping__id
2. , inc_month
3. , class_name
4. , subject_name
5. , student_id
6. , avg(score) as avg_score
7. from tmp.tmp_student_20250105
8. group by inc_month
9. , class_name
10. , subject_name
11. , student_id
12. grouping sets(
13.     (class_name, subject_name, inc_month)
14.     , (class_name, subject_name)
15.     , (subject_name, inc_month)
16.     , (subject_name)
17.     , (class_name, student_id, subject_name)
18. )

复制代码

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)】

1. select inc_month
2. , class_name
3. , student_id
4. , concat_ws("|", collect_set(subject_name)) subject_name_arr
5. , concat_ws("|", collect_set(score)) subject_score_arr
6. from tmp.tmp_student_20250105
7. group by inc_month
8. , class_name
9. , student_id

复制代码

collect_set 将字段转化为数组，并举行去重处理，concat_ws则是将数组用 | 分隔符拼接成字符串，collect_set已经去重，其他字段也必要跟着去重，所以其他字段必要举行group by

3.2 行转多列：case when + max

学生各科结果展示到多列上：将数据布局变为这样【月份、班级、学生id、语文结果、数学结果】

1. select inc_month
2. , class_name
3. , student_id
4. , max(case when subject_name = "语文" then score else null end) yw_score
5. , max(case when subject_name = "数学" then score else null end) sx_score
6. from tmp.tmp_student_20250105
7. group by inc_month
8. , class_name
9. , student_id

复制代码

case when 可以大概之展示特定条件的数据，但是还必要共同聚合函数才气实现真正的去重，所以在前边必要加max/min这样的函数。

3.3 列转行: explode/posexplode

列转行—学生各科结果在1列上转为多行展示，即将数据布局【月份、班级、学生id、学科聚集(语文|数学)、各科结果聚集(90|88)】转回到【月份、班级、学生id、学科、学生结果】

• explode + lateral view 能将学科这一列转为多行
  

1. -- 把 学科 转为列
2. select inc_month
3. , class_name
4. , student_id
5. , subject_name_t.subject_name
6. from (
7.     select inc_month
8.         , class_name
9.         , student_id
10.         , concat_ws("|", collect_set(subject_name)) subject_name_arr
11.         , concat_ws("|", collect_set(score)) subject_score_arr
12.     from tmp.tmp_student_20250105
13.     group by inc_month
14.         , class_name
15.         , student_id
16. ) t
17. lateral view explode(split(subject_name_arr, ',') subject_name_t as subject_name

复制代码

explode（爆炸