语法:OVER(PARTITION BY)及开窗函数

OLAP函数语法四个部分:
  1、function本身 用于对窗口中的数据进行操作;
  2、partitioning clause 用于将结果集分区;
  3、order by clause 用于对分区中的数据进行排序;
  4、windowing clause 用于定义function在其上操作的行的集合,即function所影响的范围。

  一、order by对窗口的影响

  不含order by的:

  SQL> select deptno,sal,sum(sal) over() from emp;

  不含order by时,默认的窗口是从结果集的第一行直到末尾。
  含order by的:
  SQL> select deptno,sal,
   2 sum(sal) over(order by deptno) as sumsal
   3 from emp;
  当含有order by时,默认的窗口是从第一行直到当前分组的最后一行。

  二、用于排列的函数
  SQL> select empno, deptno, sal,
   2 rank() over (partition by deptno order by sal desc nulls last) as rank,
   3 dense_rank() over  (partition by deptno order by sal desc nulls last) as dense_rank,
   4 row_number() over (partition by deptno order by sal desc nulls last) as row_number
   5 from emp;


  三、用于合计的函数

  SQL> select deptno,sal,
   2 sum(sal) over (partition by deptno) as sumsal,
   3 avg(sal) over (partition by deptno) as avgsal,
   4 count(*) over (partition by deptno) as count,
   5 max(sal) over (partition by deptno) as maxsal
   6 from emp;

  四、开窗语句

  1、rows窗口: "rows 5 preceding"
   适用于任何类型而且可以order by多列。
   SQL> select deptno,ename,sal,
   2 sum(sal) over (order by deptno rows 2 preceding) sumsal
   3 from emp;
  rows 2 preceding:将当前行和它前面的两行划为一个窗口,因此sum函数就作 用在这三行上面
  SQL> select deptno,ename,sal,
   2 sum(sal) over (partition by deptno order by ename rows 2 preceding) sumsal
   3 from emp
   4 order by deptno,ename;

  加了partiton by分区后之后,rows 2 preceding(窗口)只在当前分区内生效,不会影响分区之外的行。
  SQL> select ename,sal,hiredate,
   2 first_value(ename) over (order by hiredate asc rows 5 preceding) first_ename,
   3 first_value(hiredate) over (order by hiredate asc rows 5 preceding) first_hiredate
   4 from emp order by hiredate asc;

  order by hiredate asc rows 5 preceding: order by之后,取当前行的前5行+当前行作为窗口(共6行)。

  2、"range unbounded preceding"
  range unbounded preceding会把当前行之前的所有行都包含进来,但当partition by时:
  SQL> select deptno,ename,sal,
   2 sum(sal) over
   3 (partition by deptno order by deptno range unbounded preceding) sumsal
   4 from emp;

  SQL> select deptno,ename,sal,
   2 sum(sal) over
   3 (order by deptno range unbounded preceding) sumsal
   4 from emp;

  这SQL句子和下面这SQL是等价的:
  select deptno,ename,sal,
   sum(sal) over (order by deptno) sumsal
   from emp;

  因为order by的默认窗口总是从结果集的第一行开始到它分组的最后一行。
  而partiton by的默认窗口总是从分区的第一行开始。

  3、range窗口: "range 100 preceding"
  这个子句只适用于number和date,而且只能order by一列。
  假如over()里asc排列,意思是[number-100,number]这样一个闭区间是它的窗口。
  假如over()里desc排列,意思是[number,number+100]这样一个闭区间是它的窗口。

  4、窗口总结
  1、unbounded preceding:从当前分区的第一行开始,到当前行结束。
  2、current row:从当前行开始,也结束于当前行。
  3、[numeric eXPression] preceding:对于rows来说从当前行之前的第[numeric expression]行开始,到当前行结束。对range来说从小于数值

  表达式的值开始,到当前行结束。

  4、[numeric expression] following:与[numeric expression] preceding相反。
请使用浏览器的分享功能分享到微信等