Introdução

Foram implementadas novas funções analíticas no SQL Server 2012.Segue lista:

• CUME_DIST
• PERCENTILE_DISC
• PERCENTILE_CONT 
• PERCENT_RANK 
• FIRST_VALUE 
• LAST_VALUE
• LEAD
• LAG

 

Segue exemplo de cada um e como utilizá-los:

 

Funções

CUME_DIST

Calcula a distribuição de um valor em um determinado grupo. Essa função calcula a posição relativa de um valor em um grupo específico.

 

SELECT Department,

        LastName,

        Rate,

       CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist

FROM HumanResources.vEmployeeDepartmentHistory AS edh

INNER JOIN HumanResources.EmployeePayHistory AS e 

ON e.BusinessEntityID = edh.BusinessEntityID

ORDER BY Department, Rate DESC;

 

(Figura 1 – Porcentagem de Salário para cada empregado em um departamento – Função – CUME_DIST.)

PERCENTILE_DISC 

 
Realiza o sort de valores em um conjunto dividido por partições.

 

SELECT DISTINCT

        Name AS DepartmentName,

        PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ph.Rate) OVER (PARTITION BY Name)AS PercentileDisc

FROM HumanResources.Department AS d

INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh

    ON dh.DepartmentID = d.DepartmentID

INNER JOIN HumanResources.EmployeePayHistory AS ph

    ON ph.BusinessEntityID = dh.BusinessEntityID

WHERE dh.EndDate IS NULL

ORDER BY DepartmentName;

(Figura 2 – Média dos salários dos empregados de acordo com departamento – PERCENTILE_DISC.)

PERCENTILE_CONT 

Calcula a porcentagem baseada na distribuição de uma coluna.

 

SELECT DISTINCT

        Name AS DepartmentName,

        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate) OVER (PARTITION BY Name)AS PercentileCont

FROM HumanResources.Department AS d

INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh

    ON dh.DepartmentID = d.DepartmentID

INNER JOIN HumanResources.EmployeePayHistory AS ph

    ON ph.BusinessEntityID = dh.BusinessEntityID

WHERE dh.EndDate IS NULL

ORDER BY DepartmentName;

(Figura 3 – Média dos salários dos empregados de acordo com departamento – PERCENTILE_CONT.)

PERCENT_RANK  

Realiza o um Rank relativo de linhas de acordo com um grupo específico.

SELECT Department,

        LastName,

        Rate,

       PERCENT_RANK () OVER (PARTITION BY Department ORDER BY Rate) AS PercentRank

FROM HumanResources.vEmployeeDepartmentHistory AS edh

INNER JOIN HumanResources.EmployeePayHistory AS e 

ON e.BusinessEntityID = edh.BusinessEntityID

ORDER BY Department, Rate DESC;

(Figura 4 – Realiza o RANK e computa o cálculo de porcentagem dos salários em um determinado departamento.)

FIRST_VALUE 

Retorna o primeiro valor ordenado.

 

SELECT Name,

        ListPrice,

       FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive

FROM Production.Product

WHERE ProductSubcategoryID = 28;

(Figura 5 – Retorna o primeiro valor ordernado pelo preço.)

 
LAST_VALUE

Retorna o último valor ordenado.

SELECT Department, LastName, Rate, HireDate,

    LAST_VALUE(HireDate) OVER (PARTITION BY Department ORDER BY Rate) AS LastValue

FROM HumanResources.vEmployeeDepartmentHistory AS edh

INNER JOIN HumanResources.EmployeePayHistory AS eph 

    ON eph.BusinessEntityID = edh.BusinessEntityID

INNER JOIN HumanResources.Employee AS e

    ON e.BusinessEntityID = edh.BusinessEntityID

WHERE Department IN (N’Engineering’,N’Finance’);

(Figura 6 – Retorna a data de contratação de acordo com o último empregado em um determinado departamento.)

 

LEAD

Realiza a comparação de valores em uma determinada linha.

 

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN (’2005′,’2006′);

(Figura 7 – Retorna a diferença de quotas para um específico empregado em relação a anos.)

 

LAG

 
Realiza o acesso de dados posteriores de um result set

 

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN (’2005′,’2006′);

 

(Figura 8 – Retorna a diferença de quotas para um específico empregado em relação a anos anteriores.)