SQL 窗口函数 | ||
什么是 SQL 的窗口函数?在一般使用聚合函数的时候，每一组的资讯会被统合为一笔资料。但是在有些情况下，我们会需要保留住每一笔原始资料的资讯，而不是只看到每一组资料统合后的结果。在这类情况下，我们就会用到窗口函数(又称窗函数，英文为window function)。 窗口函数的名称由来是因为它被应用在一个窗口之内的每一笔资料。我们用OVER()来设定视窗的定义。OVER()包含以下两个主要的元素：
有好几个函数可以被用来在窗口函数上。以下是一些常见的函数： 一般聚合函数: SUM, AVG, COUNT我们用以下这个表格来当例子： Store_Sales表格
我们的需求是：要在以上的表格加上一个栏位来显现出每间店(Store_ID)中所有销售员Sales的平均值。要达到这个目标，我们用以下的窗口函数： SELECT Store_ID, Salesperson, Sales, AVG(Sales) OVER (PARTITION BY Store_ID) Avg_Store
FROM Store_Sales; 结果：
Avg_Store 栏位的值代表每间店中所有销售员Sales的平均值。因为它的单位是店，所以在同一个店中的所有销售员在这个栏位的值都是一样的，虽然每个销售员本身卖出去的收入(Sales栏位)通常是不同的。 排名函数: RANK, DENSE_RANK, ROW_NUMBERROW_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; 结果：
若我们将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; 结果：
若我们将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 = 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; 结果：
这里我们要将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; 会让我们得到如下的结果：
我们来看 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; 结果：
|