数据分析师的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) 来实现 ：

1. select top(100) * 
2.  
3.      from ( select  
4.  
5.                          OrderId 
6.  
7.                      ,    OrderMonth 
8.  
9.                       ,    OrderAmount 
10.  
11.                       ,    Row_Number() Over(                                    
12.  
13.                                  OrderBy OrderAmount DESC) 
14.  
15.                              AS Amt_Order 
16.  
17.                   from FctSales) tmp 
18.  
19.       Where Amt_Order between 2000 and 3000 

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

1. Select    OrderId 
2.  
3.             ,    OrderMonth 
4.  
5.             ,    OrderAmount 
6.  
7.    From FctSales 
8.  
9.   Order by OrderAmount Desc 
10.  
11.  
12.  
13.   OffSet 2000 ROWS 
14.  
15.   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 子句。

1. function_name(<arguments>) Over( 
2.  
3.     [ <window partition clause>] 
4.  
5.     [ <window Order clause> 
6.  
7.             [ <window frame clause>] 
8.  
9.     ]) 

Over::

1. Over( 
2.  
3.             [    <PARTITION BY clause>    ] 
4.  
5.             [    <ORDER BY clause>          ] 
6.  
7.             [    <ROW or RANGE clause>  ] 
8.  
9.     ) 

::窗口中的窗口

1. ROWS | RANGE 
2.  
3.     BETWEEN  
4.  
5.         UNBOUNDED PRECDEDING  | 
6.  
7.          <N>  PRECEDING     | 
8.  
9.           <N>  FOLLOWING   | 
10.  
11.             CURRENT ROW 
12.  
13.      AND 
14.  
15.             UNBOUNDED FOLLOWING  | 
16.  
17.              <N> PRECEDING  | 
18.  
19.              <N> FOLLOWING  | 
20.  
21.              CURRENT ROW 

举一个例子：

1. select custid 
2.  
3.    , ordermonth 
4.  
5.    , ordervolume 
6.  
7.     , sum(ordervolume)  
8.  
9.           over(    partition by custid 
10.  
11.                 order by ordermonth asc 
12.  
13.                 rows between     
14.  
15.                          unbounded preceding 
16.  
17.                 and    current row) 
18.  
19.         as cumulatedVolume 
20.  
21.  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()

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

原文链接：https://mp.weixin.qq.com/s/vnmeSZWEKThfOmHsusDGjA