DAY 6
5

## [Denali 新特性探險6]Analytic Functions

Analytic Functions : LAST_VALUE、 FIRST_VALUE、PERCENT_RANK 、PERCENTILE_DISC、PERCENTILE_CONT、CUME_DIST 、LEAD 、LAG
Denali 這次新增數個相當實用分析函數，

FIRST_VALUE:返回排序資料集中第一個值。
LAST_VALUE:返回排序資料集中最後一個值。

``````select top(200) t1.NationalIDNumber,t1.JobTitle,t1.Gender,
first_value(t1.JobTitle) over (order by t1.SickLeaveHours desc) as 'First JobTitle_病假時數',
first_value(t1.JobTitle) over (order by t1.SickLeaveHours ) as 'Last JobTitle_病假時數'
from HumanResources.Employee t1
``````

(擷取部分)。

Denali 寫法

``````select t1.NationalIDNumber,t1.JobTitle,
first_value(t1.JobTitle) over (order by t1.NationalIDNumber  ) as 'First JobTitle',
first_value(t1.JobTitle) over (order by t1.NationalIDNumber desc) as 'Last JobTitle'
from HumanResources.Employee t1
order by t1.NationalIDNumber
``````

I/O and Time 統計資訊。

SQL2005/SQL2008 寫法(還有另一種寫法是利用Max and Min Function)

``````;with cte
as
(
select t1.NationalIDNumber,t1.JobTitle,
row_number() over (order by NationalIDNumber) as 'rows'
from HumanResources.Employee t1
)
select NationalIDNumber,JobTitle,
(select JobTitle from cte where rows=1 ) as 'First JobTitle',
(select JobTitle from cte where rows=290 ) as 'Last JobTitle'
from cte
order by NationalIDNumber
``````

I/O and Time統計資訊。

LAG:以當前列為起始，取前第 N列數值。

``````SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG(SalesQuota, 2,null) OVER (ORDER BY YEAR(QuotaDate)) AS 'Lag'
FROM Sales.SalesPersonQuotaHistory
``````

PERCENT_RANK:計算同一群組中的資料，每筆資料在群組中依百分率排名(範圍0~1)。
PERCENT_RANK = (R -1)/(T-1)
R:Rank。
T:群組總數。
CUME_DIST:類似PERCENT_RANK function，但每筆資料在群組中採百分比累加分布(範圍0~1)。
CUME_DIST =sum((R)/(T))
R:累加數量。
T:群組總數。

``````SELECT Department, LastName, Rate,
CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist,
PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank,
rank() over(PARTITION BY Department ORDER BY Rate) as 'R for PERCENT_RANK',
rank() over(PARTITION BY Department,Rate ORDER BY Rate) as 'R for CUME_DIST',
count(1) over(PARTITION BY Department) as 'Total'
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS e
WHERE Department IN (N'Information Services',N'Document Control')
ORDER BY Department, Rate ;
``````

PERCENTILE_CONT: 取得所對應百分率列數紀錄，或依比例計算相差數值(該數值可能不存在資料表中)。公式可參考Paul on SQL Server
P= percentile。N=同一群組總筆數。RN = (1+ (P*(N-1))。CRN = CEILING(RN) and FRN = FLOOR(RN)
PERCENTILE_DISC: 取得所對應百分率列數紀錄(無條件進位)。

``````SELECT Department, LastName, Rate,
Cume_Dist() Over(Partition By Department Order By Rate) As'百分率',
PERCENTILE_CONT (.7) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Department) AS 'PERCENTILE_CONT(70%)',
PERCENTILE_CONT (.5) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Department) AS 'PERCENTILE_CONT(50%)',
PERCENTILE_DISC(.6) WITHIN GROUP (ORDER BY Rate)  OVER (PARTITION BY Department ) AS 'PERCENTILE_DISC(60%)',
PERCENTILE_DISC(.9) WITHIN GROUP (ORDER BY Rate)  OVER (PARTITION BY Department ) AS 'PERCENTILE_DISC(90%)'
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS e
ORDER BY Department, Rate ;
``````

(擷取部分)。

MS SQL Server新特性探險:探險代碼 Denali 全系列文章

### 1 則留言

0
SunAllen
iT邦研究生 1 級 ‧ 2011-10-11 00:41:59

rico 大大太強了!

rico iT邦新手 2 級 ‧ 2011-10-11 22:15:21 檢舉