Oracle数据库SQL复杂查询、数据处理（二、sql窗口函数）

详细可以参考，介绍的很详细：/PostgreSQL’s 文档/

一、简介

1. 窗口函数：
   也称为OLAP（Online Analytical Processing）函数
   对数据库数据就行实时分析处理

      <窗口函数> OVER （  [PARTITION BY  <列清单>]
                                         ODER BY <排序用列清单>   ）                             
   

2. 窗口函数分类：
   窗口函数分为排名函数、分布函数、偏移函数等。

二、用法简介

1. 排名函数：
   ROW_NUMBER、NTILE、RANK和DENSE_RANK 。在标准中，前两者是一类，后两者是另一类。

• ROW_NUMBER()

  ROW_NUMBER函数根据指定的顺序，从1开始计算连续的行号。

  。语法格式：

  语法：ROW_NUMBER() OVER ( PARTITION BY  COL1  ORDER BY COL2 ) 
  功能：表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）  
       row_number() 返回的主要是“行”的信息，并没有排名
  

  它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

1） 例1：按照SALARY排序

      SELECT  ID
              ,NAME
              ,AGE
              ,SALARY
              ,ROW_NUMBER()OVER(ORDER BY SALARY DESC)   RN
      FROM  TEST_ROW_NUMBER_OVER t

结果：

2）例2：按照ID分组 、SALARY排序

     SELECT ID
              ,NAME
              ,AGE
              ,SALARY
              ,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY SALARY DESC)    RN
      FROM  TEST_ROW_NUMBER_OVER 

结果：

3）每组第一

      WHERE RN<2

• RANK和DENSE_RANK

   三个函数都是按照col1分组内从1开始排序
  其中,ROW_NUMBER() 是没有重复值的排序(即使两条记录相同，序号也不重复的)，不会有同名次。
      DENSE_RANK() 是连续的排序，两个第二名仍然跟着第三名。
      RANK()       是跳跃排序，两个第二名下来就是第四名。
  

对比例：

     SELECT s.names
           ,s.salaries
           ,dept
          , ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salaries DESC) rank
     FROM workers s;  

 SELECT s.names
       , dept
       , s.salaries
       , DENSE_RANK() OVER(PARTITION BY dept ORDER BY salaries DESC) rank 
      FROM workers s;  

结果如下，相同salaries时，RANK有重复，但连续。

   SELECT s.names
        , dept
        ，s.salaries
        , RANK() OVER(PARTITION BY dept ORDER BY salaries DESC) rank 
        FROM workers s;  

结果如下，相同salaries时，RANK有重复，同时不连续。

• 关于Parttion by：

Parttion by关键字是Oracle中分析性函数的一部分，用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录（记录数不变），而Group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条）。
TIPS：
使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。
可以这样： rank over(partition by course order by score desc nulls last)

2. 偏移函数
   LAG，LEAD 分别是向前，向后；

语法：

   LEAD ( scalar_expression ,[ offset ] , [ default ] ) OVER ( PARTITION BY  COL1  ORDER BY COL2 ) 

lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset（偏移多少行，如果不写，默认一行），第三个参数是 超出记录窗口时的默认值 （如果不写，默认为null）

例：

 SELECT *,
   LEAD(Value, 1, 666) OVER (ORDER BY Value) AS LEADVALUE,  --提前1行，默认值666
   LAG(Value, 2, 888) OVER (ORDER BY Value) AS LAGVALUE  --滞后2行，默认值888
   FROM #T;

结果：

不写偏移数和默认值，结果就是这样的：

   SELECT *,
   LEAD(Value) OVER (ORDER BY Value) AS LEADVALUE,  --提前1行
   LAG(Value) OVER (ORDER BY Value) AS LAGVALUE  --滞后1行
   FROM #T;

结果：