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;
(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.)
maio 20th, 2012 → 2:23 pm
[...] Funções Analíticas no SQL Server 2012 [...]