Window (ou Windowing) functions are a great way to get different perspectives on a set of data without having to make repeat calls to the server for that data. Por exemplo, podemos reunir a soma de uma coluna e exibi-la lado a lado com os dados de nível de detalhe, de modo que “SalesAmount” e “SUM(SalesAmount)” possam aparecer na mesma linha. Também podemos fazer funções analíticas como PERCENT_RANK e funções de ranking como ROW_NUMBER, tudo sem alterar a granularidade do conjunto de resultados ou fazer viagens adicionais para obter os mesmos dados de origem novamente e novamente.
“Observe como eu equilibro sem esforço duas pedras nos rins do Val Kilmer. Bastante bully!”
Funções de janela todas usam a cláusula OVER(), que é usada para definir como a função é avaliada. A cláusula OVER() aceita três argumentos diferentes:
- PARTITION BY: Reinicia seu contador toda vez que a(s) coluna(s) indicada(s) muda(m) valores.
- ORDER BY: Ordena as linhas que a função irá avaliar. Isto não ordena todo o resultado definido, apenas a forma como a função procede através das linhas.
- ROWS BETWEEN: Especifica como limitar ainda mais as linhas avaliadas pela função.
Vamos fingir que estamos olhando para dados simplificados de uma competição de levantamento de peso. Aqui estão alguns exemplos de código (vamos fazê-los todos em uma declaração SELECT porque adicionar/remover funções da janela de forma alguma altera o número de linhas que recebemos de volta):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SELECT
LiftID
, LiftDate
, LiftPersonID
, LiftWeight
/* ROW_NUMBER irá listar o número da linha, ordenado por LiftID.
O contador irá reiniciar com cada nova combinação de LiftDate e LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM irá somar os pesos levantados.
O primeiro SUM mostrará o total geral para todo o resultado definido.
O segundo SUM mostrará o peso total de levantamento para a data de levantamento daquela linha.
O terceiro SUM mostrará o peso total do elevador para a data de elevação daquela linha e pessoa. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* O AVG mostrará o peso médio levantado.
O primeiro AVG mostrará o peso médio de elevação para a data de elevação daquela linha.
O segundo AVG mostrará o peso médio de elevação para a data de elevação daquela fila e pessoa. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG e LEAD permitem que a linha atual informe os dados nas linhas atrás ou à frente dela.
Esta função LAG irá retornar o LiftWeight de 1 linha atrás dele (na ordem de LiftID) e se nenhum valor for encontrado, ele irá retornar 0 em vez de NULL.
A função LEAD irá retornar o LiftWeight de 3 linhas à frente. Como não especificamos o valor padrão opcional (como o “0” que demos a função LAG, ela retornará NULL se não houver linha 3 linhas à frente. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE AND LAST_VALUE will return the specified column’s first and last column’s last value in the result set.
Esta função PRIMEIRO_VALOR retornará o primeiro LiftWeight no conjunto de resultados.
Esta função ÚLTIMO_VALOR retornará o último LiftWeight no conjunto de resultados.
***AVISO: sem a função ROWS BETWEEN no ÚLTIMO_VALOR, você pode obter resultados inesperados.***
*/
, FIRST_VALUE(LiftWeight) OVER (ORDER BY LiftDate) AS FirstLift
, LAST_VALUE(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastLift
/* SUM usando ROWS BETWEEN reduzirá o escopo avaliado pela função de janela.
A função começará e terminará onde o ROWS BETWEEN especificar.
A primeira SUM adicionará todos os valores de LiftWeight nas linhas até e incluindo a linha atual.
A segunda SUM adicionará todos os valores de LiftWeight nas linhas entre a linha atual e as 3 linhas antes dela.
*/
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WeightRunningTotal
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS WeightSumLast4
FROM dbo.Lifts
|
Considerations for Window Functions
Se você não tiver o SQL Server 2012 ou posterior, o seu armário de funções window é bastante vazio; SQL Server 2005 até 2008 R2 só permitiu PARTITION BY na cláusula OVER de uma função agregada, e você tem RANK() e ROW_NUMBER(). Isso foi mais ou menos isso. Se você é um desenvolvedor ainda em uma dessas versões anteriores, este é um caso convincente para mudar para 2012 ou mais tarde. Pense em quanto tempo você poderia estar economizando não escrevendo múltiplos CTEs e quanto mais rápido suas consultas irão.
Falando de rápido…
Evite viagens de ida e volta ao servidor para os mesmos dados, nós reduzimos I/O nessas tabelas. Se estivermos acertando os índices, podemos realmente diminuir as leituras envolvidas. Há um trade-off, mas normalmente é muito favorável. Funções de janela requerem o SQL Server para construir a janela e calcular a função (mostrada como tarefas como Window Spool, Segment, Sequence Project, e Compute Scalar). Ao fazer isso, ele adiciona leituras à mesa de trabalho. Ainda assim, isto é geralmente menos caro do que voltar atrás para obter os dados originais várias vezes, agregando se necessário, e juntando tudo isso. Além disso, a Worktable existe em tempdb, que – idealmente – está no seu nível de armazenamento mais rápido.
Finalmente, lembre-se que as limitações que você coloca em uma função window – PARTITION BY, ORDER BY, ou ROWS BETWEEN – estão lá para aplicar contexto à função window e de forma alguma se aplicam ao conjunto de resultados como um todo. Em outras palavras, a sua instrução SELECT não será afetada por nada que você diga a uma função de janela para fazer.