---

title: SQLServer · 特性分析 · SQL Server 2012的分析函式未必都理解透了

author: 石沫

1. 背景

最近有使用者在做一些專案，使用到SQL SERVER 2012的一些新特性，比如SQL SERVER 提供的8個非常有用的分析函式，一開始我看了相關的文件，感覺內容很多，理解不清楚，不透徹。而我現在想來，其實不需要那麼清楚，我覺得值要理解他的基本用法就足以應對工作，下面根據我的理解，以最簡單的方式解析這些分析函式。

1. 分析函式CUME_DIST

微軟的定義：
計算某個值在 SQL Server 2012 中的一組值內的累積分佈。CUME_DIST 計算某指定值在一組值中的相對位置。 對於行 r，假定採用升序，r 的 CUME_DIST 是值低於或等於 r 的值的行數除以在分割槽或查詢結果集中求出的行數。
其實，我看了也不是很懂，我們看一個例項，就很清楚了。
首先，我們構造一組資料：

DECLARE
    @analytic TABLE( 
        name varchar(35) ,
        dept varchar(35),
        salary money
    )
INSERT INTO @analytic 
        VALUES
--bd
(`andy01`,`bd`,15000),
(`andy02`,`bd`,12000),
(`andy03`,`bd`,12000),
(`andy04`,`bd`,10000),
(`andy05`,`bd`,8000),
--ca
(`andy06`,`ca`,20000),
(`andy07`,`ca`,18000),
(`andy08`,`ca`,18000),
(`andy09`,`ca`,15000),
(`andy10`,`ca`,12000),
(`andy11`,`ca`,12000),
(`andy12`,`ca`,10000),
(`andy13`,`ca`,8000),
(`andy14`,`ca`,8000),
(`andy15`,`ca`,8000)

SELECT 
    dept,name ,salary,
    CUME_DIST() OVER(PARTITION BY dept ORDER BY salary)  AS cume_dist_ 
FROM @analytic
ORDER BY dept,salary DESC

然後我們再看看結果：

這是個什麼意思呢？ 按照dept分組，根據salary邏輯排序，針對每一個分組裡的每一個值，在該分組下等於或者小於自己的salary的分佈百分比。舉個例子，bd部門的andy02，salary為12000，那麼等於或者小於這個12000的有4條，總共5條記錄，因此那麼CUME_DIST()=4/5 = 0.8。 同理，其他也是如此計算，這下就明白了吧。

2. 分析函式LAST_VALUE

微軟的定義：
返回 SQL Server 2012 中有序值集中的最後一個值。
好像我還是不太懂。那麼還是看看一個示例吧：

DECLARE
    @analytic TABLE( 
        name varchar(35) ,
        dept varchar(35),
        salary money ,
        hiredate date
    )
INSERT INTO @analytic 
        VALUES
--bd
(`andy01`,`bd`,15000,`2002-01-09`),
(`andy02`,`bd`,12000,`2003-01-09`),
(`andy03`,`bd`,12000,`2003-02-09`),
(`andy04`,`bd`,10000,`2005-05-09`),
(`andy05`,`bd`,8000,`2003-06-09`),
--ca
(`andy06`,`ca`,20000,`2003-01-09`),
(`andy07`,`ca`,18000,`2005-02-09`),
(`andy08`,`ca`,18000,`2005-03-09`),
(`andy09`,`ca`,15000,`2004-01-09`),
(`andy10`,`ca`,12000,`2003-06-09`),
(`andy11`,`ca`,12000,`2002-09-09`),
(`andy12`,`ca`,10000,`2003-07-09`),
(`andy13`,`ca`,8000,`2003-08-09`),
(`andy14`,`ca`,8000,`2003-11-09`),
(`andy15`,`ca`,8000,`2003-01-09`)

SELECT 
    dept,name ,salary,hiredate,
    LAST_VALUE(hiredate) OVER(PARTITION BY dept ORDER BY salary)  AS last_value_ 
FROM @analytic

我們再看看這個執行結果：

這個意思是按照OVER子句中ORDER BY 根據salary排序，取salary最後行的hiredate值作為最後的LAST VALUE，重點在於salary有相同時需要取salary排序後的最後一條作為其他的LAST VALUE。

3. 分析函式FIRST_VALUE

微軟的定義：
返回 SQL Server 2012 中有序值集中的第一個值。 從這個地方看起來，似乎跟LAST_VALUE是相反的一個意思，實際上是不是這樣，我們來看看例項：

DECLARE
    @analytic TABLE( 
        name varchar(35) ,
        dept varchar(35),
        salary money ,
        hiredate date
    )
INSERT INTO @analytic 
        VALUES
--bd
(`andy01`,`bd`,15000,`2002-01-09`),
(`andy02`,`bd`,12000,`2003-01-09`),
(`andy03`,`bd`,12000,`2003-02-09`),
(`andy04`,`bd`,10000,`2005-05-09`),
(`andy05`,`bd`,8000,`2003-06-09`),
--ca
(`andy06`,`ca`,20000,`2003-01-09`),
(`andy07`,`ca`,18000,`2005-02-09`),
(`andy08`,`ca`,18000,`2005-03-09`),
(`andy09`,`ca`,15000,`2004-01-09`),
(`andy10`,`ca`,12000,`2003-06-09`),
(`andy11`,`ca`,12000,`2002-09-09`),
(`andy12`,`ca`,10000,`2003-07-09`),
(`andy13`,`ca`,8000,`2003-08-09`),
(`andy14`,`ca`,8000,`2003-11-09`),
(`andy15`,`ca`,8000,`2003-01-09`)

SELECT 
    dept,name ,salary,hiredate,
    FIRST_VALUE(name) OVER(PARTITION BY dept ORDER BY salary)  AS first_value_ 
FROM @analytic

顯然，這個與LAST_VALUE不是同一個意思，OVER子句根據ORDER BY 來排序，按dept分組來確定這個分組的第一個值，而不是根據salary的值來確定的，所以與LAST_VALUE是不一樣的，將FIRST_VALUE(name)修改為FIRST_VALUE(hiredate)後，對比看得更清楚。這個很有矇蔽性。

4. 分析函式LEAD

微軟的定義：
訪問相同結果集的後續行中的資料，而不使用 SQL Server 2012 中的自聯接。 LEAD 以當前行之後的給定物理偏移量來提供對行的訪問。 在 SELECT 語句中使用此分析函式可將當前行中的值與後續行中的值進行比較。
感覺這個好理解多了。不過我們還是看看一個示例來說明：

DECLARE
    @analytic TABLE( 
        name varchar(35) ,
        dept varchar(35),
        salary money ,
        hiredate date
    )
INSERT INTO @analytic 
        VALUES
--bd
(`andy01`,`bd`,15000,`2002-01-09`),
(`andy02`,`bd`,12000,`2003-01-09`),
(`andy03`,`bd`,12000,`2003-02-09`),
(`andy04`,`bd`,10000,`2005-05-09`),
(`andy05`,`bd`,8000,`2003-06-09`),
--ca
(`andy06`,`ca`,20000,`2003-01-09`),
(`andy07`,`ca`,18000,`2005-02-09`),
(`andy08`,`ca`,18000,`2005-03-09`),
(`andy09`,`ca`,15000,`2004-01-09`),
(`andy10`,`ca`,12000,`2003-06-09`),
(`andy11`,`ca`,12000,`2002-09-09`),
(`andy12`,`ca`,10000,`2003-07-09`),
(`andy13`,`ca`,8000,`2003-08-09`),
(`andy14`,`ca`,8000,`2003-11-09`),
(`andy15`,`ca`,8000,`2003-01-09`)

SELECT 
    dept,name,hiredate,salary,
    LEAD(salary,1,0) OVER(PARTITION BY dept ORDER BY salary) AS lead_,
    (LEAD(salary,1,0) OVER(PARTITION BY dept ORDER BY salary)-salary) AS diff_salary 
FROM @analytic

我們再看看結果：

這個還是比較容易理解的。按照dept分割槽，根據salary排序，比較當前記錄和後一條記錄（偏移量為1）的salary值的差值，這個非常的使用

後面還有4個函式，下一篇分析：LAG，PERCENT_RANK ，PERCENTILE_DISC，PERCENTILE_CONT