SQL今日一题（11）：窗口函数

原创虾壳可乐可乐的数据分析之路

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

题目描述

获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

这题用到dept_emp表和salaries表，以emp_no为公共字段

方法1

select d.dept_no      ,d.emp_no      ,max(s.salary) as salary from  dept_emp as d,salaries as swhere d.emp_no = s.emp_no and d.to_date = '9999-01-01' and s.to_date = '9999-01-01'group by d.dept_no,d.emp_no

通常的想法就是这样写了，max函数取最大值再对d.dept_no和d.emp_no分组，结果如下图所示，但其实这样取出来是不符合结果的，因为题目要求一个部门只有一个薪水的最大值，但因为我们对emp_no也分组了，所以会取出来一个部门下不同员工的salary。

方法2

select r.dept_no      ,r.emp_no      ,max(r.salary) from (      select d.dept_no            ,d.emp_no            ,s.salary       from dept_emp d,salaries s      where d.emp_no=s.emp_no      and d.to_date='9999-01-01'       and s.to_date='9999-01-01'      order by s.salary desc)as rgroup by r.dept_noorder by r.dept_no asc

这道题网上有很多种解答方法，这种解法是牛客网上讨论最多的一种。

1、首先将两个表连接起来按salary降序，where子句给出限定条件“当前”日期和公共字段的连接，将连接后的表命名为r表

2、再将r表按照dept_no分组，并从中取出最大的salary

这是只对dept_no分组，但细琢磨这是不对的，因为按照语法，group by 后要跟select以后聚合函数前的所有字段，也就是说select dept_no,emp_no了以后，group by 就必须包含dept_no和emp_no。

有的数据库可以只对dept_no分组，但返回的emp_no是默认取第一条，这里第一条不一定是最大的salary对应的那条，因此要先对salary降序排列，而根据语法，order by 要写在group by的后面，所以用了子查询。

而有的数据库group by会很严格的报错，所以要如何绕过emp_no分组呢？

方法3

select rank.dept_no      ,rank.emp_no      ,rank.salary from(      select d.dept_no            ,d.emp_no            ,s.salary            ,ROW_NUMBER() over(                  partition by d.dept_no                   order by s.salary desc                  ) as nums       from dept_emp d,salaries s       where d.emp_no=s.emp_no       and d.to_date='9999-01-01'       and s.to_date='9999-01-01') rankwhere rank.nums = 1

其实对方法1中的结果我们只要再增加一列排序就可以了啊，对每一个分组降序排列，取每个排序为1的不就符合题目要求了吗，实现的方法就要用到窗口函数里的row_number() over()了。

1、首先将dept_emp和salaries两个表以公共字段emp_no连接起来，并用where子句限制“当前”条件

2、同时加入row_number() over(partition by d.dept_no order by s.salary)语句，意思是按照dept_no分组，在每一个dept_no的分组内按照salary降序排列，排序之后，对每一个分组内的多行数据，标记上序号，序号从1开始，依次递增，同时也给序号赋值为nums

3、进行开窗操作后，再进行一次select+where的操作，来选出需要的数据，即nums = 1的记录。

以上3种方法均能通过牛客网的练习，但能通过不代表就是对的，这道题目很值得思考，尤其是对group by和窗口函数掌握不深入的小伙伴来说，看一眼就过了，以为自己明白了，但其实还差得多，希望大家好好思考。

知识点

max函数

返回指定列中的最大值
要指定列名，如max(column)

row_number

语法：row_number() over(partition by column1,column2 order by column3) as column4
按照column1,column2分组，对每个组内按照column3排序，并将排序的序号命名为column4
赋予唯一的连续位次，如有相同的3条记录排名为1时，结果记为1,2,3

猜你喜欢:

SQL今日一题（10）

数学之美：数学究竟是如何被运用到生活中的？