HiveSQL一天一个小技巧：如何将分组内数据填充完整？

0 需求

1 需求分析

需求分析：需求中需要求出分组中按成绩排名取倒数第二的值作为新字段，且分组内没有倒数第二条的时候取当前值。

如果本题只是求分组内排序后倒数第二，则很简单，使用row_number()函数即可求出，但是本题问题点在于没有倒数第二时候需要保留当前值，如何优雅求出呢？

使用row_number()函数得到如下结果

with data as(select 111 as stu_id, 'class1' as class_name, 69 as scoreunion allselect 113 as stu_id, 'class1' as class_name, 74 as scoreunion allselect 112 as stu_id, 'class1' as class_name, 80 as scoreunion allselect 115 as stu_id, 'class1' as class_name, 93 as scoreunion allselect 114 as stu_id, 'class1' as class_name, 94 as scoreunion allselect 124 as stu_id, 'class2' as class_name, 70 as scoreunion allselect 121 as stu_id, 'class2' as class_name, 74 as scoreunion allselect 123 as stu_id, 'class2' as class_name, 78 as scoreunion allselect 122 as stu_id, 'class2' as class_name, 86 as scoreunion allselect 9999 as stu_id, 'class3' as class_name, 99 as score)
select stu_id, class_name, score, row_number() over (partition by class_name order by score desc ) rn1from data

根据上述结果，如何取出倒数第二值？上层使用case when rn = 2 then score end ,看看效果

with data as(select 111 as stu_id, 'class1' as class_name, 69 as scoreunion allselect 113 as stu_id, 'class1' as class_name, 74 as scoreunion allselect 112 as stu_id, 'class1' as class_name, 80 as scoreunion allselect 115 as stu_id, 'class1' as class_name, 93 as scoreunion allselect 114 as stu_id, 'class1' as class_name, 94 as scoreunion allselect 124 as stu_id, 'class2' as class_name, 70 as scoreunion allselect 121 as stu_id, 'class2' as class_name, 74 as scoreunion allselect 123 as stu_id, 'class2' as class_name, 78 as scoreunion allselect 122 as stu_id, 'class2' as class_name, 86 as scoreunion allselect 9999 as stu_id, 'class3' as class_name, 99 as score)
select stu_id, class_name, score, case when  rn1 = 2 then score end as  res
from (select stu_id, class_name, score, row_number() over (partition by class_name order by score desc ) rn1--, row_number() over (partition by class_name order by score  ) rn2from data) t

倒数第二值是取出来了，但是还不符合要求，需求中要求该分组内生成的字段每一行全部为该值，如何做呢？这里有个小技巧，也是数据清洗的手段，如何将分组内空值用该分组内有值的值填充完整？我们采用max()函数开窗的技巧：max() over(partition by 分组字段)，这样同一个组内的所有空值都会被赋值为同一个字段。SQL如下：

with data as(select 111 as stu_id, 'class1' as class_name, 69 as scoreunion allselect 113 as stu_id, 'class1' as class_name, 74 as scoreunion allselect 112 as stu_id, 'class1' as class_name, 80 as scoreunion allselect 115 as stu_id, 'class1' as class_name, 93 as scoreunion allselect 114 as stu_id, 'class1' as class_name, 94 as scoreunion allselect 124 as stu_id, 'class2' as class_name, 70 as scoreunion allselect 121 as stu_id, 'class2' as class_name, 74 as scoreunion allselect 123 as stu_id, 'class2' as class_name, 78 as scoreunion allselect 122 as stu_id, 'class2' as class_name, 86 as scoreunion allselect 9999 as stu_id, 'class3' as class_name, 99 as score)
select stu_id, class_name, score, max(case when  rn1 = 2 then score end ) over(partition by class_name)   as  res
from (select stu_id, class_name, score, row_number() over (partition by class_name order by score desc ) rn1--, row_number() over (partition by class_name order by score  ) rn2from data) t

我们看到其结果值越来越符合预期，但是对于分组内只有一个值的如何处理呢？这里我们需要辅助判断，我们可以采用采用min() =max()判断，也可以采用percent_rank()=0判断等等，这里我们采用min() =max()判断，只要最大值等于最小值说明就分组内值唯一，最终SQL如下：

with data as(select 111 as stu_id, 'class1' as class_name, 69 as scoreunion allselect 113 as stu_id, 'class1' as class_name, 74 as scoreunion allselect 112 as stu_id, 'class1' as class_name, 80 as scoreunion allselect 115 as stu_id, 'class1' as class_name, 93 as scoreunion allselect 114 as stu_id, 'class1' as class_name, 94 as scoreunion allselect 124 as stu_id, 'class2' as class_name, 70 as scoreunion allselect 121 as stu_id, 'class2' as class_name, 74 as scoreunion allselect 123 as stu_id, 'class2' as class_name, 78 as scoreunion allselect 122 as stu_id, 'class2' as class_name, 86 as scoreunion allselect 9999 as stu_id, 'class3' as class_name, 99 as score)
select stu_id, class_name, score, max(casewhen rn1 != rn2 and rn1 = 2  --正序和倒序值不等 则取倒数第二的值 （rn1=2的值）then scorewhen rn1 = rn2 then score   --正序和倒序值相等 则取当前值end) over (partition by class_name) res
from (select stu_id, class_name, score, dense_rank()  over (partition by class_name order by score desc ) rn1, dense_rank() over (partition by class_name order by score) rn2 --用来辅助判断-- , percent_rank() over (partition by class_name order by score) pr --也可以采用该函数辅助判断(pr=0时候)from data) t

2 小结

本文通过实际需求中的案例，讲解了如何将分组内空值补充完整的技巧，通过开窗，min()/max() over(partition by 分组字段)来补充，注意点max()函数中根据实际情况写case when语句，或构造符合实际需求的条件，往往数据清晰中会用到这一技巧。