數(shù)據(jù)分析師的SQL功底該學(xué)到什么程度？
本文轉(zhuǎn)載自微信公眾號(hào)「有關(guān)SQL」，作者Lenis。轉(zhuǎn)載本文請(qǐng)聯(lián)系有關(guān)SQL公眾號(hào)。
常有朋友問，數(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)了相同條件的兩條或者多條記錄是用唯一值來區(qū)別的
b. Rank() Over() 與 Dense_Rank() Over() :
這兩者的區(qū)別，在于他們對(duì)位于相同排名之后的名次，是接著相同排名的連續(xù)數(shù)(Rank) 還是相隔 N 個(gè)相同記錄個(gè)數(shù)之后的連續(xù)數(shù)(Dense_Rank)。
所以 Rank 出來的結(jié)果都是連續(xù)數(shù)字，而 Dense_Rank 出來的結(jié)果有可能有跳格數(shù)。
c. 除了有用法上的區(qū)別外，順帶說說分頁的實(shí)現(xiàn)：
第一種，我們平常用 Row_Number() 加 Top (N) 來實(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)用，到目前為止，未用過。適用于財(cái)會(huì)類的統(tǒng)計(jì)。
