Funções Analíticas no SQL Server 2012

Posted on maio 17, 2012

1


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

 

Segue lista:

 

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

 

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;

  •  

image

(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;

image

(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;

image

(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;

image

(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;

image

(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’);

image

(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’);

image

(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’);

 

image

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