mysql

窗口函数一瞥

标签： windows function,oracel

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

开窗函数

函数名(列) OVER(选项) 
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用OVER关键字来区分这两种用法。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

1. 列表项

   over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数  
      over（partition by deptno）按照部门分区

2：
  over（order by salary range between 5 preceding and 5 following）
   每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5

常用的窗口函数

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

窗口函数举例

1. select deptno,row_number() over(partition by deptno order by sal)
   from
   emp order by deptno;
2. select deptno,rank() over (partition by deptno
   order by sal) from emp order by deptno;
   select deptno,dense_rank()
   over(partition by deptno order by sal) from emp order by deptno;
3. select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename)
   from
   emp ord er by deptno;
4. select deptno,ename,sal,lag(ename,2,'example') over(partition by deptno order by ename)
   from em p order by deptno;
5. select deptno, sal,sum(sal) over(partition by deptno) from
   emp;--每行记录后都有总计值  select deptno, sum(sal) from emp group by deptno;
6. 求每个部门的平均工资以及每个人与所在部门的工资差额
   select deptno,ename,sal ,round(avg(sal) over(partition by deptno)) as dept_avg_sal, round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff
   from emp;