[鐵人賽][Denali 新特性探險6]Analytic functions
Denali 這次新增數個相當實用分析函數，下面我將為大家介紹並操作每個Function 。
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 和SQL2005/2008 兩者寫法上的效能差異。
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統計資訊。
擷取部分執行計畫總成本(相當複雜)。
結果比較表
雖然Denali 寫法結果較耗時(大部分都花費在 I/O)，但因為執行計畫比較簡單且單純，
所以成本比較低，而反觀SQL2005/2008寫法結果雖然較省時，但因為執行計畫太複雜，
所以需要花費較久的編譯時間(大部分都花費在 CPU)，
以我個人遇到資料庫瓶頸都發生在I/O層面居多，反倒是CPU很閒，
所以我會選用SQL2005/2008(雖然寫法較複雜且麻煩)來換取效能。
LEAD:以當前列為起始，取後第 N列數值。
LAG:以當前列為起始，取前第 N列數值。
操作示範
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LEAD(SalesQuota, 1,null) OVER (ORDER BY YEAR(QuotaDate)) AS 'Lead',
LAG(SalesQuota, 2,null) OVER (ORDER BY YEAR(QuotaDate)) AS 'Lag'
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) between 2005 and 2009
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
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN (N'Information Services',N'Document Control')
ORDER BY Department, Rate ;
PERCENTILE_CONT: 取得所對應百分率列數紀錄，或依比例計算相差數值(該數值可能不存在資料表中)。
相差數值公式可參考http://www.sqlskills.com/BLOGS/BOBB/post/What-exactly-does-PERCENTILE_CONT-do-anyhow.aspx:
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
ON e.BusinessEntityID = edh.BusinessEntityID
ORDER BY Department, Rate ;
