Windows Function + CTE
Introdução às funções da janela
Windows function, ou função de janela em português, é um recurso da linguagem SQL que permite aplicar funções analíticas em um conjunto de registros selecionados em uma consulta.
As funções de janela permitem calcular valores agregados com base em uma janela de registros, que pode ser definida com base em uma ordenação específica, por exemplo. Isso pode ser útil em uma variedade de cenários, como cálculo de médias móveis, classificação de dados em uma tabela ou a identificação de valores máximos e mínimos em um conjunto de registros.
As funções de janela têm uma sintaxe específica em SQL e geralmente são escritas junto com a cláusula "OVER", que define a janela de registros a serem considerados na aplicação da função. Existem muitas funções de janela disponíveis em SQL, incluindo funções de agregação como SUM e COUNT, funções de ranking como RANK e DENSE_RANK, e funções analíticas como LAG e LEAD.
A principal vantagem de usar funções de janela sobre funções de agregação regulares é: as funções de janela não fazem com que as linhas sejam agrupadas em uma única linha de saída, as linhas retêm suas identidades separadas e um valor agregado será adicionado a cada linha.
Em resumo, as funções de janela são uma ferramenta poderosa em SQL que podem ajudar a realizar cálculos e análises mais complexas em um conjunto de registros.
Sintaxe
SELECT column1, column2, column3,
SUM(column3) OVER(PARTITION BY column1 ORDER BY column2) AS sum_column3
FROM table_name
Tipos de funções do Windows
Uma função de janela agregada é semelhante a uma função agregada comum, exceto que adicioná-la a uma consulta não altera o número de linhas retornadas. Em vez disso, para cada linha, o resultado da função de janela agregada é como se a agregação correspondente fosse executada em todas as linhas na "moldura da janela" especificada pela cláusula OVER.
Ao contrário das funções comuns, as funções de janela não podem usar a palavra-chave DISTINCT. Além disso, as funções de janela podem aparecer apenas no conjunto de resultados e na cláusula ORDER BY de uma instrução SELECT.
1. Funções de Janela Agregadas
Exemplos:
SELECT order_id, order_date, customer_name, city, order_amount
,SUM(order_amount) OVER(PARTITION BY city) as sum_total
FROM [dbo].[Orders]
No exemplo acima, a função SUM() somou os valores de cada pedido, particionou por cidade e retornou com a soma do valor total. Contudo, a consulta trouxe outra tabela “sum_total” com o valor total para cada linha retendo sua identidade.
SELECT order_id, order_date, customer_name, city, order_amount
,AVG(order_amount) OVER(PARTITION BY city) as media
FROM [dbo].[Orders]
A função Average ou AVG executa igualmente com uma função Window, soma-se o valor total do pedido e divide pela quantidade total de pedidos. O resulta acima demonstra o valor médio dos pedidos para cada cidade.
2. Funções da janela de classificação
As funções da janela de classificação são usadas para realizar operações em conjuntos de linhas dentro dessas janelas, as funções RANKING classificarão os valores de um campo especificado e os categorizarão de acordo com sua classificação.
Exemplos:
SELECT order_id, order_date, customer_name, city, order_amount
,RANK() OVER(ORDER BY order_amount DESC) [Rank]
FROM [dbo].[Orders]
Na demonstração acima, verifica que a função RANK() em SQL atribui o mesmo ranking para dois registros que possuem o mesmo valor na coluna utilizada para a classificação, o que faz com que a próxima classificação seja ignorada. Dessa forma, se houver dois valores idênticos na classificação 3, ambos receberão a mesma classificação e a próxima classificação será pulada, atribuindo a classificação 5 ao próximo registro.
SELECT order_id, order_date, customer_name, city, order_amount
,DENSE_RANK() OVER(ORDER BY order_amount DESC) [Rank]
FROM [dbo].[Orders]
A diferença entre a função DENSE_RANK() e a função RANK() é que a primeira não deixa lacunas entre as classificações subsequentes. Em outras palavras, a função DENSE_RANK() atribui um rank sequencial para cada linha na tabela, e valores repetidos recebem a mesma classificação.
3. Funções da Janela de Valor
As funções da janela de valor em SQL permitem que você trabalhe com valores que aparecem em outras linhas dentro da mesma consulta. Isso é especialmente útil em casos em que você precisa comparar valores em linhas diferentes ou usar um valor de linha anterior ou posterior na análise.
As funções que podem ser usadas são LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
Exemplos:
SELECT order_id,customer_name,city, order_amount,order_date,
--Na linha abaixo, 1 indica verificar a linha anterior à linha atual.
LAG(order_date,1) OVER(ORDER BY order_date) prev_order_date
FROM [dbo].[Orders]
A função LAG permite que você acesse dados da linha anterior dentro do mesmo conjunto de resultados, sem a necessidade de usar junções SQL. Como demonstrado no exemplo abaixo, ao utilizar a função LAG, é possível obter a data do pedido imediatamente anterior.
SELECT order_id,customer_name,city, order_amount,order_date,
--Na linha abaixo, 1 indica verificar a linha seguinte à linha atual.
LEAD(order_date,1) OVER(ORDER BY order_date) next_order_date
FROM [dbo].[Orders]
A função LEAD permite que você acesse os dados da linha subsequente dentro do mesmo conjunto de resultados, sem a necessidade de usar junções SQL. Como mostrado no exemplo abaixo, ao utilizar a função LEAD, é possível obter a data do próximo pedido.
A função FIRST_VALUE() retorna o primeiro valor na janela especificada, enquanto a função LAST_VALUE() retorna o último valor na janela. Ambas as funções requerem uma expressão de entrada, que é o valor usado para determinar o primeiro e o último valor na janela. Além disso, essas funções também exigem uma cláusula OVER que especifica a janela que deve ser usada para a análise.
SELECT order_id,order_date,customer_name,city, order_amount,
FIRST_VALUE(order_date) OVER(PARTITION BY city ORDERBY order_date) first_order_date,
LAST_VALUE(order_date) OVER(PARTITION BY city ORDERBY city) last_order_date
FROM [dbo].[Orders]
Na imagem acima, podemos ver claramente que o primeiro pedido recebido em 02/04/2017 e o último pedido recebido em 11/04/2017 para a cidade de Arlington e funciona da mesma forma para outras cidades.
CTE (Common Table Expressions)
Uma Expressão de Tabela Comum (Common Table Expression - CTE) é um conjunto temporário de resultados nomeado que pode ser referenciado dentro de uma instrução SELECT, INSERT, UPDATE ou DELETE em SQL. As CTEs são frequentemente usadas para simplificar consultas complexas, dividindo-as em partes menores e mais gerenciáveis.
A sintaxe para criar uma CTE é a seguinte:
WITH cte_nome (coluna1, coluna2, ...) AS (
SELECT coluna1, coluna2, ...
FROM nome_da_tabela
WHERE condição
)
SELECT *
FROM cte_nome;
Aqui, ‘cte_nome’ é o nome dado à CTE e ‘(coluna1, coluna2, ...)’ é uma lista opcional de nomes de colunas que definem a estrutura da CTE. A instrução SELECT dentro dos parênteses define os dados a serem incluídos na CTE.
Depois que a CTE for definida, ela poderá ser referenciada em declarações SQL subsequentes usando seu nome. Por exemplo:
WITH sales_cte
AS
(
SELECT order_id, sum(order_amount) as total_sales
FROM Orders
GROUP BY order_id
)
SELECT *
FROM sales_cte
WHERE total_sales > 1000;
Neste exemplo, a CTE ‘sales_cte’ calcula as vendas totais para cada cliente e, em seguida, a instrução SELECT fora da CTE seleciona apenas os clientes cujas vendas totais são maiores que $1000.
Vantagens de usar CTE
A seguir estão algumas vantagens de usar expressões de tabela comuns ou CTEs:
§ Melhore a legibilidade de consultas complexas. Você usa CTEs para organizar consultas complexas de maneira mais organizada e legível.
§ Capacidade de criar consultas recursivas. Consultas recursivas são consultas que fazem referência a si mesmas. As consultas recursivas são úteis quando você deseja consultar dados hierárquicos.
§ Use em conjunto com funções. Você pode usar CTEs em conjunto com funções para criar um conjunto de resultados inicial e usar outra instrução select para processar ainda mais esse conjunto de resultados.
Autor: Leandro Lourenço Malaquias Área: Analytics Função: Engenheiro de Dados
Data: 28/02/2023
Referencias
https://learn.microsoft.com/en-us/sql/t-sql/queries/select-window-transact-sql?view=sql-server-ver16
https://mode.com/sql-tutorial/sql-window-functions/#the-usual-suspects-sum-count-and-avg
https://www.sqlite.org/windowfunctions.html#aggwinfunc
https://www.sqlshack.com/use-window-functions-sql-server/
https://www.macoratti.net/13/05/sql_cte1.htm