窗口函数/开窗函数基本语法

一、<专用窗口函数> over (partition by <分组的列名>

                                           order by <排序的列名> asc/desc)

1、专用窗口函数有：rank()over , dense_rank()over , row_number()over。此处（）内不可加字段

2、三者的区别：

成绩	rank	dense_rank	row_number
95	1	1	1
92	2	2	2
88	3	3	3
86	4	4	4
86	4	4	5
83	6	5	6

当排名时出现并列名次时，

rank中并列名次的行会占用下一名次的位置，

dense_rank中并列名次的行不占用下一名次的位置，例：查询薪水第二多的员工的emp_no以及其对应的薪水salary，若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，

select emp_no,salary

from

(select emp_no,salary,dense_rank() over(order by salary desc) t

from salaries)

where t=2

row_number中不考虑并列名次，例：查询薪水第二多的员工的emp_no以及其对应的薪水salary，若有多个员工的薪水为第二多的薪水，则对应的员工的emp_no和salary只输出一个(注：此时也可用order by+limit)

select emp_no,salary

from

(select emp_no,salary,row_number() over(order by salary desc) t

from salaries)

where t=2

3、运行顺序

over后相当于创建一个新表格，先执行over指令，再运行rank等窗口函数，运行结果为一列排名

二、<聚合函数> over (partition by <分组的列名>

                                     order by <排序的列名>)

1、常见聚合函数：sum(),max(),min(),count(),avg()。此处（）内必须加字段

2、count()over()对于查询返回的每一行，它返回了表中所有行的计数。用法示例

例：有一个部门表departments简况如下:

有一个，部门员工关系表dept_emp简况如下:

有一个职称表titles简况如下:

汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序，dept_no一样的再按title升序排序

SELECT
 distinct de.dept_no, dp.dept_name, t.title, 
 count(t.title) over(partition by de.dept_no,t.title order by t.title )
 FROM departments dp,dept_emp de,titles t
 where  dp.dept_no = de.dept_no
 and  de.emp_no = t.emp_no
 order by  de.dept_no 

（还不理解为什么要使用distinct？删去也能正常运行）