数据分析师的SQL功底该学到什么程度？

本文转载自微信公众号「有关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() Over( 
 
    [ ] 
 
    [ Order clause> 
 
            [ ] 
 
    ]) 

Over::

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

::窗口中的窗口

ROWS | RANGE 
 
    BETWEEN  
 
        UNBOUNDED PRECDEDING  | 
 
           PRECEDING     | 
 
            FOLLOWING   | 
 
            CURRENT ROW 
 
     AND 
 
            UNBOUNDED FOLLOWING  | 
 
              PRECEDING  | 
 
              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()

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