网站建设的客户,咸阳网站建设xymokj,怎么申请网站空间域名,网站 建设 函0 需求1 需求分析需求分析#xff1a;需求中需要求出分组中按成绩排名取倒数第二的值作为新字段#xff0c;且分组内没有倒数第二条的时候取当前值。如果本题只是求分组内排序后倒数第二#xff0c;则很简单#xff0c;使用row_number()函数即可求出#xff0c;但是本题问…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 --正序和倒序值不等 则取倒数第二的值 rn12的值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 --也可以采用该函数辅助判断(pr0时候)from data) t2 小结本文通过实际需求中的案例讲解了如何将分组内空值补充完整的技巧通过开窗min()/max() over(partition by 分组字段)来补充注意点max()函数中根据实际情况写case when语句或构造符合实际需求的条件往往数据清晰中会用到这一技巧。
