数据分析师的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() :

这两者的区别，在于他们对位于相同排名之后的名次，是接着相同排名的连续数（Dense_Rank) 还是相隔 N 个相同记录个数之后的连续数(Rank)。

所以 Dense_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()

这一类应用，到目前为止，未用过。适用于财会类的统计。

–完–

往期精彩：

外企一道 SQL 面试题，刷掉 494 名候选人