数据分析系列（5）：窗口函数

这篇文章梳理了几个关于分组排序的SQL语句，在查询的时候常会遇到分组排序的场景，比如找出每个群体中中排名前十的用户，找出某一类商品销量最高的几个商品等等。它们分别是：

row_number() over()
rank( ) over( )
dense_rank( ) over( )

由此引申开来，讲述一下窗口函数的功用。

一、row_number() over()排序功能：

1.1 row_number( ) over( )无分组排序

若不对其进行分组，则此时row_number() over()的功能与rownum一致，

SELECT 
    empno,
    WORKDEPT,
    SALARY, 
    Row_Number() OVER (ORDER BY salary desc) rank 
FROM 
    employee   
--------------------------------------  
000010  A00 152750  1  
000030  C01 98250   2  
000070  D21 96170   3  
000020  B01 94250   4  
000090  E11 89750   5  
000100  E21 86150   6  
000050  E01 80175   7  
000130  C01 73800   8  
000060  D11 72250   9

但row_number() over()更强大的地方在于可以进行分组排序。

1.2 row_number( ) over( )分组排序

partition by ：根据字段进行分组
order by desc：在分组后的各组内根据字段进行降序排序

SELECT 
    empno,
    WORKDEPT,
    SALARY, 
    Row_Number() OVER (partition by workdept ORDER BY salary desc) rank 
FROM
    employee   
--------------------------------------  
000010  A00 152750  1  
000110  A00 66500   2  
000120  A00 49250   3  
200010  A00 46500   4  
200120  A00 39250   5  
000020  B01 94250   1  
000030  C01 98250   1  
000130  C01 73800   2

注意：使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where group by order by 的执行
如果没有指定partition by，那么它把整个结果集作为一个分组，与聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录
row_number() over()返回的排名是没有重复值的，也就是即使遇到相同的值其排名也有先后，但具体哪个在前，具有不确定性，详细可见该链接1和链接2，具体的例子可以看文末的对比。

1.3 使用row_number( ) over( )去重

假设表TAB中有a,b,c三列，可以使用下列语句删除a,b,c都相同的重复行。

select 
    year,
    QUARTER,
    RESULTS,
    row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO 
FROM 
    SALE 
WHERE 
    ROW_NO=1

所以除了使用distinct进行去重，还可以用row_number( ) over( )去重。需要注意的有以下几点：

使用关键字 distinct 去重，其作用于单个字段和多个字段的时候是不同的，作用于单个字段时，其“去重”的是表中所有该字段值重复的数据；作用于多个字段的时候，其“去重”的表中所有字段值都相同的数据。
在使用函数 row_number() over() 的时候，其是按先分组排序后，再取出每组的第一条记录来进行“去重”的

二、rank( ) over( )

这是跳跃排序，在同一个分组内，若有两个第一名时接下来就是第三名

select 
    workdept,
    salary,
    rank() over(partition by workdept order by salary) as dense_rank_order 
from 
    emp 
order by 
    workdept
------------------  
A00 39250   1  
A00 46500   2  
A00 49250   3  
A00 66500   4  
A00 152750  5  
B01 94250   1  
C01 68420   1  
C01 68420   1  
C01 73800   3

三、dense_rank( ) over( )

这是连续排序，在同一个分组内，有两个第一名时仍然跟着第二名。


select 
    workdept,
    salary,
    dense_rank() over(partition by workdept order by salary) as dense_rank_order 
from 
    emp 
order by 
    workdept
------------------  
A00 39250   1  
A00 46500   2  
A00 49250   3  
A00 66500   4  
A00 152750  5  
B01 94250   1  
C01 68420   1  
C01 68420   1  
C01 73800   2  
C01 98250   3

四、三者对比

这个例子可以直观地看到，rank() over()是跳跃排序，dense_rank() over()是连续排序，row_number() over()是非重复排序。

select 
    region_id, 
    customer_id, 
    sum(customer_sales) total,
    rank() over(order by sum(customer_sales) desc) rank,
    dense_rank() over(order by sum(customer_sales) desc) dense_rank,
    row_number() over(order by sum(customer_sales) desc) row_number
from 
    user_order
group by 
    region_id, customer_id;
 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
            
8          18           1253840         11         11         11
5          2            1224992         12         12         12
9          23           1224992         12         12         13
9          24           1224992         12         12         14
10         30           1216858         15         13         15

在这个例子中也可以看到order by 的关键字可以是group by之后的聚合函数。

五、窗口函数

以上我们介绍了RANK、DENSE_RANK、ROW_NUMBER这几个函数，其实在数据库中，它们被称为窗口函数，窗口函数可以进行排序、生成序列号等一般的聚合函数无法完成的操作。它也称为OLAP函数。OLAP是OnLine Analytical Processing的简称，意思是对数据库进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

它的基本语法是:

1 2	<窗口函数> OVER ( [PARTITION BY <列清单>] ORDER BY <排序用列清单>)

其中重要的关键字是PARTITON BY 和ORDER BY，理解这两个关键字的作用是帮助我们理解窗口函数的关键。

窗口函数大致可以分为两种：

RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数
能够作为窗口函数的聚合函数（SUM, AVG,COUNT,MAX,MIN）

我们上面已经介绍过RANK、DENSE_RANK、ROW_NUMBER，可以看到它们兼具了GROUP BY子句的分组功能以及ORDER BY子句的排序功能。但是PARTITION BY不具备GROUP BY子句的汇总功能。所以使用RANK函数不会减少原表中记录的行数。

通过PARTITION BY分组后的记录集合称为窗口。此处的窗口表示范围。

目前为止我们学过的函数大多数都没有使用位置的限制，最多也就是在WHERE子句不能使用聚合函数。但是，使用窗口函数的位置却有很大的限制，确切的说，窗口函数只能在SELECT子句中使用。

所有的聚合函数都能用作窗口函数，且使用语法与专用窗口函数完全相同。

5.1 窗口函数——SUM

--将SUM函数作为窗口函数使用
 SELECT 
    product_id, 
    product_name, 
    sale_price,        
    SUM(sale_price) OVER (ORDER BY product_id) AS current_sum   
FROM 
    Product;

product_id | product_name | sale_price | current_sum------------+--------------+------------+-------------
 0001       | T衫          |       1000 |        1000
 0002       | 打孔器       |        500 |        1500
 0003       | 运动T衫      |       4000 |        5500
 0004       | 菜刀         |       3000 |        8500
 0005       | 高压锅       |       6800 |       15300
 0006       | 叉子         |        500 |       15800
 0007       | 擦菜板       |        880 |       16680
 0008       | 圆珠笔       |        100 |       16780
(8 行记录)

使用聚合函数作为窗口函数时，需要在其括号内指定相应的列。像上例中，使用sale_price(销售单价)作为累加的对象, current_sum的结果为在它之前的销售单价的合计。这种统计方法称为累计。

5.2 窗口函数——AVG

--将AVG函数作为窗口函数使用
SELECT 
    product_id, 
    product_name, 
    sale_price,        
    AVG(sale_price) OVER (ORDER BY product_id) AS current_avg   
FROM 
    Product;

product_id | product_name | sale_price |      current_avg------------+--------------+------------+-----------------------
 0001       | T衫          |       1000 | 1000.0000000000000000
 0002       | 打孔器       |        500 |  750.0000000000000000
 0003       | 运动T衫      |       4000 | 1833.3333333333333333
 0004       | 菜刀         |       3000 | 2125.0000000000000000
 0005       | 高压锅       |       6800 | 3060.0000000000000000
 0006       | 叉子         |        500 | 2633.3333333333333333
 0007       | 擦菜板       |        880 | 2382.8571428571428571
 0008       | 圆珠笔       |        100 | 2097.5000000000000000
(8 行记录)

current_avg的结果为在它之前的销售单价的平均值。像这样以“自身记录”（当前记录）作为基准进行统计，就是将聚合函数作为窗口函数使用时的最大特征。

5.2 移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其中还包含在窗口中指定更详细的汇总范围的备选功能，这种备选功能中的汇总范围称为框架。

例如，指定“最靠近的3行”作为汇总对象：

--指定“最靠近的3行”作为汇总对象
 SELECT 
    product_id, 
    product_name, 
    sale_price,        
    AVG(sale_price) OVER (ORDER BY product_id  ROWS 2 PRECEDING) AS moving_avg   
FROM 
    Product;

product_id | product_name | sale_price |      moving_avg------------+--------------+------------+-----------------------
 0001       | T衫          |       1000 | 1000.0000000000000000
 0002       | 打孔器       |        500 |  750.0000000000000000
 0003       | 运动T衫      |       4000 | 1833.3333333333333333
 0004       | 菜刀         |       3000 | 2500.0000000000000000
 0005       | 高压锅       |       6800 | 4600.0000000000000000
 0006       | 叉子         |        500 | 3433.3333333333333333
 0007       | 擦菜板       |        880 | 2726.6666666666666667
 0008       | 圆珠笔       |        100 |  493.3333333333333333
(8 行记录)

上例中，我们使用了ROWS（行）和PRECEDING(之前)两个关键字，将框架指定为“截止到之前~行”，因此，“ ROWS 2 PRECEDING”意思就是将框架指定为“截止到之前2行”，也就是“最靠近的3行”。如果将条件中的数字改为“ROWS 5 PRECEDING”，就是“截止到之前5行”（最靠近的6行）的意思。这样的统计方法称为移动平均。

使用关键字FOLLOWING(之后)替换PRECEDING，就可以指定“截止到之后~行”作为框架。

如果希望将当前记录的前后行作为汇总对象，可以同时使用PRECEDING(之前)和FOLLOWING（之后）关键字来实现。
例，将当前记录的前后行作为汇总对象：

 --将当前记录的前后行作为汇总对象
SELECT 
    product_id, 
    product_name, 
    sale_price,       
    AVG(sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg   
FROM 
    Product;

product_id | product_name | sale_price |      moving_avg------------+--------------+------------+-----------------------
 0001       | T衫          |       1000 |  750.0000000000000000
 0002       | 打孔器       |        500 | 1833.3333333333333333
 0003       | 运动T衫      |       4000 | 2500.0000000000000000
 0004       | 菜刀         |       3000 | 4600.0000000000000000
 0005       | 高压锅       |       6800 | 3433.3333333333333333
 0006       | 叉子         |        500 | 2726.6666666666666667
 0007       | 擦菜板       |        880 |  493.3333333333333333
 0008       | 圆珠笔       |        100 |  490.0000000000000000
(8 行记录)

当前记录的前后行的具体含义就是：

之前1行的记录
自身（当前记录）
之后1行的记录

如果能够熟练掌握框架功能，就可以称为窗口函数高手了。