窗口函数是什么

窗口函数，又叫OLAP函数(Online Anallytical Processing,联机分析处理)，常用于解决组内排名问题，如求每个部门中薪水第二高的职员。

窗口函数基本格式如下：

<窗口函数> over (partition by (用于分组的列名) order by (用于排序列名))

窗口函数通常对经过where或group by处理后的结果进行进一步分析，所以窗口函数原则上只用于select子语句中。

示例数据 Employees：

emp_id	department_id	salary
1	1	2000
2	1	1000
3	2	2000
4	2	2000
5	2	1000

专用窗口函数

sql语句：

select *, 
        rank() over (partition by department_id order by salary desc) as rank_col,
        dense_rank() over (partition by department_id order by salary desc) as dense_rank_col,
        row_number() over (partition by department_id order by salary desc) as row_number_col
from Employees

执行结果：

emp_id	department_id	salary	rank_col	dense_rank_col	row_number_col
1	1	2000	1	1	1
2	1	1000	2	2	2
3	2	2000	1	1	1
4	2	2000	1	1	2
5	2	1000	3	2	3

rank() 按照分组，在组内进行排序，并按顺序赋值，薪水相同时值相同，并占用下一位次。

dense_rank() 按照分组，在组内进行排序，并按顺序赋值，薪水相同时值相同，不占用下一位。

row_number()按照分组，在组内进行排序，并按顺序赋值，不考虑薪水相同，按顺序赋值。

聚合函数用作窗口函数

sql语句：

  select *, 
            sum(salary) over (partition by department_id order by salary desc) as sum_col,
            avg(salary) over (partition by department_id order by salary desc) as avg_col,
            count(salary) over (partition by department_id order by salary desc) as count_col,
            max(salary) over (partition by department_id order by salary desc) as max_col,
            min(salary) over (partition by department_id order by salary desc) as min_col
    from Employees

执行结果：

emp_id	department_id	salary	sum_col	avg_col	count_col	max_col	min_col
1	1	2000	2000	2000	1	2000	2000
2	1	1000	3000	1500	2	2000	1000
3	2	2000	2000	2000	1	2000	2000
4	2	2000	4000	2000	2	2000	2000
5	2	1000	5000	1666.6667	3	2000	1000

聚合函数用作窗口函数，是对自身数据以及自身数据以上的所有数据进行