SQL 窗口函数



什么是 SQL 的窗口函数?

在一般使用聚合函数的时候,每一组的资讯会被统合为一笔资料。但是在有些情况下,我们会需要保留住每一笔原始资料的资讯,而不是只看到每一组资料统合后的结果。在这类情况下,我们就会用到窗口函数(又称窗函数,英文为window function)。

窗口函数的名称由来是因为它被应用在一个窗口之内的每一笔资料。我们用OVER()来设定视窗的定义。OVER()包含以下两个主要的元素:

  • PARTITION BY: 设定每个窗口内的资料是如何分割的。这跟GROUP BY很类似。这个元素不是一定需要的。当我们的窗口是包含所有的资料时,我们就不需要用到PARTITION BY
  • ORDER BY:设定在每个窗口内的资料是如何排列的。虽然这个元素并非必须,不过在大部分的情况下,我们会需要设定结果是如何排列的。

有好几个函数可以被用来在窗口函数上。以下是一些常见的函数:

以下我们介绍每一类的函数:

一般聚合函数: SUM, AVG, COUNT

我们用以下这个表格来当例子: Store_Sales表格

 Store_ID  Salesperson  Sales 
1 Aaron 374 
1 Beatrice 492 
1 Cathy 430 
2 Dan 462 
2 Elmo 747 
2 Frank 1332 
2 Gina 898 
2 Harry 603 
3 Isabel 247 
3 Jimmy 1030 
3 Kara 1030 
3 Lamar 1314 
3 Mary 462 

我们的需求是:要在以上的表格加上一个栏位来显现出每间店(Store_ID)中所有销售员Sales的平均值。要达到这个目标,我们用以下的窗口函数:

SELECT Store_ID, Salesperson, Sales, AVG(Sales) OVER (PARTITION BY Store_ID) Avg_Store
FROM Store_Sales;

结果:

 
Store_ID Salesperson SalesAvg_Store
1 Aaron 374 432
1 Beatrice 492 432
1 Cathy 430 432
2 Dan 462 808.4
2 Elmo 747 808.4
2 Frank 1332 808.4
2 Gina 898 808.4
2 Harry 603 808.4
3 Isabel 247 816.6
3 Jimmy 1030 816.6
3 Kara 1030 816.6
3 Lamar 1314 816.6
3 Mary 462 816.6

Avg_Store 栏位的值代表每间店中所有销售员Sales的平均值。因为它的单位是店,所以在同一个店中的所有销售员在这个栏位的值都是一样的,虽然每个销售员本身卖出去的收入(Sales栏位)通常是不同的。

排名函数: RANK, DENSE_RANK, ROW_NUMBER

ROW_NUMBER(), RANK(), and DENSE_RANK() 都是用来计算每一笔资料在窗口之内的排名。这三个函数的不同处在于它们如何处理平手的情况。

RANK(): 有平手的情况时,这个函数给平手的资料相同的名次。下面的排名则会照有多少笔资料平手而决定。举例来说,如果有两笔资料都是并列第一,那下一笔资料就是第三。

DENSE_RANK(): 有平手的情况时,这个函数给平手的资料相同的名次。下面的排名则是直接照排名次序排列。举例来说,如果有两笔资料都是并列第一,那下一笔资料就是第二。

ROW_NUMBER(): 每一笔资料都会有不同的名次,包括平手的资料。在平手的状况下,系统随机决定哪一笔平手的资料获得到较高的排名。举例来说,如果有两笔资料都是最大值,那其中一笔会被排为第一,另一笔则会被排为第二。

如果没有平手的情况发生,那这三个函数所产生的排名就都一样。

接下来我们继续用以上的Store_Sales表格来当例子。若我们要对销售员依照她们在每间店里的销售成绩排名,我们会用以下的窗口函数:

SELECT Store_ID, Salesperson, Sales, RANK() OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Rank
FROM Store_Sales
ORDER BY Store_ID, Sales_Rank;

结果:

Store_ID Salesperson Sales Sales_Rank
1 Beatrice 492 1
1 Cathy 430 2
1 Aaron 374 3
2 Frank 1332 1
2 Gina 898 2
2 Elmo 747 3
2 Harry 603 4
2 Dan 462 5
3 Lamar 1314 1
3 Jimmy 1030 2
3 Kara 1030 2
3 Mary 462 4
3 Isabel 247 5

若我们将RANK()换为DENSE_RANK(),我们的SQL将会成为:

SELECT Store_ID, Salesperson, Sales, DENSE_RANK() OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Dense_Rank
FROM Store_Sales
ORDER BY Store_ID, Sales_Rank;

结果:

Store_ID Salesperson Sales Sales_Dense_Rank
1 Beatrice 492 1
1 Cathy 430 2
1 Aaron 374 3
2 Frank 1332 1
2 Gina 898 2
2 Elmo 747 3
2 Harry 603 4
2 Dan 462 5
3 Lamar 1314 1
3 Jimmy 1030 2
3 Kara 1030 2
3 Mary 462 3
3 Isabel 247 4

若我们将RANK()换为ROW_NUMBER(),我们的SQL将会成为:

SELECT Store_ID, Salesperson, Sales, ROW_NUMBER() OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Row_Number
FROM Store_Sales
ORDER BY Store_ID, Sales_Rank;

结果:

Store_ID Salesperson Sales Sales_Row_Number
1 Beatrice 492 1
1 Cathy 430 2
1 Aaron 374 3
2 Frank 1332 1
2 Gina 898 2
2 Elmo 747 3
2 Harry 603 4
2 Dan 462 5
3 Lamar 1314 1
3 Jimmy 1030 2
3 Kara 1030 3
3 Mary 462 4
3 Isabel 247 5

这三个不同的函数在 Store_ID = 3 的资料排名有所不同。在这间店中,有两个销售员的成绩是并列第二。当我们用RANK()时,排名顺序是 1,2,2,4,5;用 1,2,2,4,5;用DENSE_RANK()时,排名顺序是1,2,2,3,4;而用ROW_NUMBER()时,排名顺序则是1,2,3,4,5。

分割函数: NTILE

我们在用NTILE()函数时,会加入一个整数的参数。这个参数会代表在视窗内所有的资料要分成多少组,而NTILE()会自动将窗口内所有的资料分割成为这么多组,而每组的资料笔数会是相同或是几乎相同。最前面的一组会被称为第一组,第二前面的组会被称为第二组,等等。

举例来说,如果窗口内有100笔资料,而我们用NTILE(5),那每一个NTILE就会包含20笔资料。

如果总共资料笔数不能被组的数量整除的话,那不同组的资料笔数就可能会差一。比较前面的组里面的资料笔数会比较多。

举例来说,如果窗口内有99笔资料,而我们用NTILE(5),那前面四组就会包含20笔资料,而最后一组则有19笔资料。

我们来看个例子。如果我们要将所有的销售员依照他们的销售量来分为四组的话 (无论她们是属于哪一间店),我们就用以下的SQL:

SELECT Store_ID, Salesperson, Sales, NTILE(4) OVER (ORDER BY Sales DESC) Sales_Group
FROM Store_Sales
ORDER BY Sales DESC;

结果:

Store_ID Salesperson Sales Sales_Group
2 Frank 1332 1
3 Lamar 1314 1
3 Jimmy 1030 1
3 Kara 1030 1
2 Gina 898 2
2 Harry 603 2
2 Elmo 747 2
1 Beatrice 492 3
2 Dan 462 3
3 Mary 462 3
1 Cathy 430 4
1 Aaron 374 4
3 Isabel 247 4

这里我们要将13笔资料分为4组,所以第一组有4笔资料,而其他三组则各有3笔资料。

之前和之后函数: LEAD, LAG

当我们的运算需要用到在同一窗口内排于该笔资料之前或之后的资料的话,我们就可以用LEAD()LAG()这两个函数。以下我们分别介绍:

LAG()函数让我们运用排在该笔资料前面的资料。LAG()函数的语法为下:

LAG(expression [, interval [, default_value]]) OVER ()

interval参数代表要跳过几笔资料。如果这个参数没有被设定,那代表从前一笔资料开始。default_value参数的用处是如果LAG()函数造成我们离开了视窗之外时,我们可以传回default_value的值,而不是传回NULL。

LEAD()函數讓我們運用排在該筆資料後面的資料。LEAD()函數的語法為下:

LEAD(expression [, interval [, default_value]]) OVER ()

interval参数代表要跳过几笔资料。如果这个参数没有被设定,那代表从后一笔资料开始。default_value参数的用处是如果LEAD()函数造成我们离开了视窗之外时,我们可以传回default_value的值,而不是传回NULL。

我们来看个例子。以下的SQL,

SELECT Store_ID, Salesperson, Sales, LEAD(Sales) OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Lead, LAG(Sales) OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Lag
FROM Store_Sales
ORDER BY Store_ID, Sales DESC;

会让我们得到如下的结果:

Store_ID Salesperson Sales Sales_Lead Sales_Lag
1Beatrice 492 430 NULL
1Cathy 430 374 492
1Aaron 374 NULL 430
2Frank 1332 898 NULL
2Gina 898 747 1332
2Elmo 747 603 898
2Harry 603 462 747
2Dan 462 NULL 603
3Lamar 1314 1030 NULL
3Jimmy 1030 1030 1314
3Kara 1030 462 1030
3Mary 462 247 1030
3Isabel 247 NULL 462

我们来看 Cathy。Cathy这一笔资料在Sales_Lead栏位的值是374,而这就是排在Cathy下一个Aaron的Sales值。同一笔资料在Sales_Lag栏位的值是492,而这就是排在Cathy上一个Beatrice的Sales值。

另一个比较常见的例子是当我们需要知道每笔资料和排名在它前面那笔资料的差距有多大。以下的SQL可以获得答案:

SELECT Store_ID, Salesperson, Sales, LAG(Sales,1,Sales) OVER (PARTITION BY Store_ID ORDER BY Sales DESC) - Sales Sales_Difference FROM Store_Sales
ORDER BY Store_ID, Sales DESC;

结果:

Store_ID Salesperson Sales Sales_Difference
1Beatrice 492 0
1Cathy 430 62
1Aaron 374 56
2Frank 1332 0
2Gina 898 434
2Elmo 747 151
2Harry 603 144
2Dan 462 141
3Lamar 1314 0
3Jimmy 1030 284
3Kara 1030 0
3Mary 462 568
3Isabel 247 215

SQL Concatenate 函数 >>

本页最近于 2022年6月21日更新



Copyright © 2022   1keydata.com   All Rights Reserved.