數(shù)據(jù)分析師的SQL功底該學(xué)到什么程度？

常有朋友問(wèn)，數(shù)據(jù)分析師的SQL功底該學(xué)到什么程度。今天就先談?wù)?T-SQL 中的 Window Function.

Window Function 包含了 4 個(gè)大類。分別是：

• 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()

這四個(gè)函數(shù)，要注意的地方有兩點(diǎn)：

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

兩者唯一的區(qū)別，就在于Row_Number() Over() 真正實(shí)現(xiàn)了相同條件的兩條或者多條記錄是用唯一值來(lái)區(qū)別的

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

這兩者的區(qū)別，在于他們對(duì)位于相同排名之后的名次，是接著相同排名的連續(xù)數(shù)（Dense_Rank) 還是相隔 N 個(gè)相同記錄個(gè)數(shù)之后的連續(xù)數(shù)(Rank)。

所以 Dense_Rank 出來(lái)的結(jié)果都是連續(xù)數(shù)字，而 非Dense_Rank 出來(lái)的結(jié)果有可能有跳格數(shù)。

c. 除了有用法上的區(qū)別外，順帶說(shuō)說(shuō)分頁(yè)的實(shí)現(xiàn)：

第一種，我們平常用 Row_Number() 加 Top (N) 來(lái)實(shí)現(xià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. 聚合數(shù)據(jù)

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

在使用 Aggregation 函數(shù)的時(shí)候，唯一要注意的地方就是 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

舉一個(gè)例子：

    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

統(tǒng)計(jì)了截止到目前為止，每一天的累計(jì)總量。

3 - Offset Function:定位記錄

• 3.1 Lead()

• 3.2 LAG()

• 3.3 First_Value()

• 3.4 Last_Value()

• 3.5 Nth_Value()

這一類比較好理解，根據(jù)當(dāng)前的記錄，獲取前后 N 條數(shù)據(jù)。

4 - Distribution Function: 分布函數(shù)

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

這一類應(yīng)用，到目前為止，未用過(guò)。適用于財(cái)會(huì)類的統(tǒng)計(jì)。