[[416110]]

本文轉載自微信公眾號「有關SQL」，作者Lenis。轉載本文請聯系有關SQL公眾號。

常有朋友問，數據分析師的SQL功底該學到什么程度。今天就先談談 T-SQL 中的 Window Function.

Window Function 包含了 4 個大類。分別是：

• 1 - Rank Function
• 2 - Aggregate Function
• 3 - Offset Function
• 4 - Distribution Function.

1 - Rank Function 平常用到最多

• 1.1 Rank() Over()
• 1.2 Row_Number() Over()
• 1.3 Dense_Rank() Over()
• 1.4 NTILE(N) Over()

這四個函數，要注意的地方有兩點：

a. Rank() Over() 與 Row_Number() Over() :

兩者唯一的區別，就在于Row_Number() Over() 真正實現了相同條件的兩條或者多條記錄是用唯一值來區別的

b. Rank() Over() 與 Dense_Rank() Over() :

這兩者的區別，在于他們對位于相同排名之后的名次，是接著相同排名的連續數(Rank) 還是相隔 N 個相同記錄個數之后的連續數(Dense_Rank)。

所以 Rank 出來的結果都是連續數字，而 Dense_Rank 出來的結果有可能有跳格數。

c. 除了有用法上的區別外，順帶說說分頁的實現：

第一種，我們平常用 Row_Number() 加 Top (N) 來實現 ：

select top(100) * 
 
     from ( select  
 
                         OrderId 
 
                     ,    OrderMonth 
 
                      ,    OrderAmount 
 
                      ,    Row_Number() Over(                                    
 
                                 OrderBy OrderAmount DESC) 
 
                             AS Amt_Order 
 
                  from FctSales) tmp 
 
      Where Amt_Order between 2000 and 3000 

第二種，SQL Server 2012 之后的新功能：

Select    OrderId 
 
            ,    OrderMonth 
 
            ,    OrderAmount 
 
   From FctSales 
 
  Order by OrderAmount Desc 
 
 
 
  OffSet 2000 ROWS 
 
  Fetch Next 100 ROWS Only  

按照量的大小倒序排，取第 2000 條后的記錄中前 100 條。

2 - Aggregate Function. 聚合數據

• 2.1 - Sum() Over()
• 2.2 - Count() Over()
• 2.3 - AVG() Over()
• 2.4 - MIN() Over()
• 2.5 - MAX() Over()

在使用 Aggregation 函數的時候，唯一要注意的地方就是 Order 子句。

function_name(<arguments>) Over( 
 
    [ <window partition clause>] 
 
    [ <window Order clause> 
 
            [ <window frame clause>] 
 
    ]) 

Over::

Over( 
 
            [    <PARTITION BY clause>    ] 
 
            [    <ORDER BY clause>          ] 
 
            [    <ROW or RANGE clause>  ] 
 
    ) 

::窗口中的窗口

ROWS | RANGE 
 
    BETWEEN  
 
        UNBOUNDED PRECDEDING  | 
 
         <N>  PRECEDING     | 
 
          <N>  FOLLOWING   | 
 
            CURRENT ROW 
 
     AND 
 
            UNBOUNDED FOLLOWING  | 
 
             <N> PRECEDING  | 
 
             <N> FOLLOWING  | 
 
             CURRENT ROW 

舉一個例子：

select custid 
 
   , ordermonth 
 
   , ordervolume 
 
    , sum(ordervolume)  
 
          over(    partition by custid 
 
                order by ordermonth asc 
 
                rows between     
 
                         unbounded preceding 
 
                and    current row) 
 
        as cumulatedVolume 
 
 from FctSales 

統計了截止到目前為止，每一天的累計總量。

3 - Offset Function:定位記錄

• 3.1 Lead()
• 3.2 LAG()
• 3.3 First_Value()
• 3.4 Last_Value()
• 3.5 Nth_Value()

這一類比較好理解，根據當前的記錄，獲取前后 N 條數據。

4 - Distribution Function: 分布函數

• 4.1- PERCENT_RANK()
• 4.2 - CUME_DIST()
• 4.3 - PERCENT_COUNT()-
• 4.4 - PERCENT_DISC()

這一類應用，到目前為止，未用過。適用于財會類的統計。