数据分析师必备：如何掌握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) *
3.      from ( select
5.                          OrderId
7.                      ,    OrderMonth
9.                       ,    OrderAmount
11.                       ,    Row_Number() Over(
13.                                  OrderBy OrderAmount DESC)
15.                              AS Amt_Order
17.                   from FctSales) tmp
19.       Where Amt_Order between 2000 and 3000

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

1. Select    OrderId
3.             ,    OrderMonth
5.             ,    OrderAmount
7.    From FctSales
9.   Order by OrderAmount Desc
13.   OffSet 2000 ROWS
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(
3.     [ <window partition clause>]
5.     [ <window Order clause>
7.             [ <window frame clause>]
9.     ])

Over::

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

::窗口中的窗口

1. ROWS | RANGE
3.     BETWEEN
5.         UNBOUNDED PRECDEDING  |
7.          <N>  PRECEDING     |
9.           <N>  FOLLOWING   |
11.             CURRENT ROW
13.      AND
15.             UNBOUNDED FOLLOWING  |
17.              <N> PRECEDING  |
19.              <N> FOLLOWING  |
21.              CURRENT ROW

举一个例子：

1. select custid
3.    , ordermonth
5.    , ordervolume
7.     , sum(ordervolume)
9.           over(    partition by custid
11.                 order by ordermonth asc
13.                 rows between
15.                          unbounded preceding
17.                 and    current row)
19.         as cumulatedVolume
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()

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