SQL今日一题（19）：表复用

这是SQL今日一题的第19篇文章

题目描述

对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

用到的是salaries表

方法1

select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries s1,salaries s2
where s1.salary <= s2.salary 
and s1.to_date = '9999-01-01' 
and s2.to_date = '9999-01-01'
group by s1.emp_no,s1.salary
order by s1.salary desc,s1.emp_no asc

1、这题的难点在于对1-N的排名的理解，在计算排序时，若存在相同位次，不会跳过之后的位次，比如有3条数据排名都是1，那么排序为1,1,1,2。

2、本题的思想在于对salaries表的复用。

3、先从salaries s1和salaries s2表中给定限定条件

• and s1.to_date = '9999-01-01'  and s2.to_date = '9999-01-01'

4、重点在于如何排名。where s1.salary <= s2.salary ，意思是有多少个s2.salary大于等于s1.salary，比如等于等于75508的有94692、9000、88958、88070、75508、75508这6条数据，其中75508重复了2次，用count(distinct s2.salary)去重，得到75508这个薪资的排名为5。

5、用count了就用group by 分组一下

• group by s1.emp_no,s1.salary

6、最后按照s1.salary降序，相同s1.salary的按照emp_no升序排列

• order by s1.salary desc,s1.emp_no asc
  

方法2

select emp_no, salaries, 
dense_rank() over(order by salary desc) as rank
where to_date = '9999-01-01' 
order by salary desc, emp_no

1、用窗口函数来做排序，按照这题的要求，用dense_rank来写，这个窗口函数求的排名会存在重复，意味着总数是减少的

2、dense_rank() over(order by salary desc)，意思是按照salary降序排列，相同位次的不跳过之后的位次，就这一个窗口函数解决了表复用的问题，所以还是要学会窗口函数。

知识点

dense_rank窗口函数

• 窗口函数的格式：<窗口函数> over ( partition by 列1 order by列2 ）

• 对数据排序，当存在相同位次时，不跳过之后的位次

• partition by 列1是按照列1进行分组

• order by列2是按照列2进行排序

猜你喜欢:

强势回归！再说回归分析

100篇原创文章，可乐de数据分析之路

指标又双叒下降了，我到底该怎么给老板分析？

@ 作者：可乐
@ 公众号/知乎专栏/头条/简书：可乐的数据分析之路
@加个人微信：data_cola，备注：进群，拉你入 可乐的数据分析群 和各行各业的小伙伴交流探讨数据分析相关内容