Por que eu deveria aprender as funções da janela SQL?

A SQL existe há mais de 25 anos. Sua sintaxe básica - como SELECT, WHERE, GROUP BY, HAVING, e ORDER BY - é bem conhecida. Mas existe algo além do SQL tradicional? Você pode ir além do básico e melhorar suas habilidades?

A resposta a esta pergunta é sim. Existe uma versão moderna do SQL. Neste artigo, vamos mergulhar na versão moderna e aprender sobre as funções da janela SQL. Se você é um iniciante avançado que quer analisar dados com a ajuda das funções de janela SQL (também chamadas funções analíticas), este artigo é para você.

Um breve histórico de SQL

História do desenvolvimento SQL - padronização

SQL é uma linguagem famosa, mas muito antiga. Foi introduzida na década de 1970 pela IBM. Em 1986, os grupos de normas ANSI e ISO adotaram oficialmente uma definição padrão "Database Language SQL" (SQL-1986).

Em 1992, foi feita uma grande revisão (SQL- 92) da norma inicial. É a SQL padrão que usamos hoje. Isso mesmo - já se passaram 28 anos desde que a SQL-92 foi oficialmente adotada. Seus conceitos básicos (SELECT, WHERE, etc.) são familiares, não importa se você está usando Oracle, DB2, MySQL, PostgreSQL, ou algum outro banco de dados. Em cada SGBD, você escreverá instruções SQL similares devido a essa mesma padronização.

Os conceitos básicos de SQL são antigos, mas são muito úteis. Eu diria que eles são universais, pois funcionam independentemente da indústria à qual você os está aplicando. Para aprender SQL, ou mesmo para crescer como um analista, você terá que aprender estes conceitos.

Ainda assim, o mundo da computação mudou muito desde 1992. Certamente o SQL evoluiu desde então. Sim, a SQL evoluiu e novas revisões dos padrões foram feitas. Os anos 2000 assistiram ao início da SQL moderna, um conceito que vai além das afirmações básicas. Em 2003, as funções da janela SQL foram introduzidas. Vamos falar sobre os benefícios das funções de janela SQL e por que conhecê-las é uma coisa boa.

Primeiro, porém, vamos rever algo que os novatos muitas vezes confundem com funções de janela: funções agregadas de SQL.

Uma Atualização sobre Funções Agregadas

Se você está familiarizado com SQL tradicional, então provavelmente já usou funções agregadas, que lhe permitem realizar cálculos através de conjuntos de linhas e obter uma única linha de saída ou resultado. Por exemplo, talvez você tenha calculado alguns totais ou médias sobre um conjunto de linhas ou talvez tenha contado o número de linhas por categoria. Nesse caso, você usou pelo menos algumas das funções agregadas do SQL: SUM(), AVG(), MIN(), MAX(), e COUNT(). Elas são freqüentemente usadas com as cláusulas GROUP BY e HAVING dentro das declarações SELECT.

Vejamos um exemplo: como podemos calcular o preço médio por grupo de filas usando GROUP BY.

Vamos utilizar dados Forex sobre taxas de câmbio de moedas. Aqui está a nossa tabela de entrada:

Taxas de câmbio de moedas - tabela CURRENCYTRADE

A tabela contém os preços de fechamento para a troca de moedas GBP-USD e EUR-USD. Neste exemplo, estamos usando seis preços de fechamento para cada par de moedas. A partir desses dados, vamos calcular o preço médio de fechamento para cada par de moedas (GBP-USD, EURUSD) separadamente.

Aqui está a declaração SELECT que calcula o preço médio de fechamento para cada par de moedas:

select ticker,avg(close) as average_price from CURRENCYTRADE group by ticker;

A imagem abaixo mostra o resultado à direita:

O resultado é apresentado como uma única linha de saída para cada par de moedas. Os cálculos (o valor médio para cada par) foram feitos em seis filas para cada par. Este é um exemplo simples de uma função agregada.

Agora, vamos mergulhar nas funções de janela.

O que é uma função de janela SQL?

As funções de janela SQL são similares às funções agregadas de GROUP BY sintaxe? Eu gosto de pensar que sim. Como uma função agregada usada com uma cláusula GROUP BY, uma função de janela também executa cálculos através de um conjunto de linhas. Entretanto, o resultado de uma função de janela não é apresentado como uma única linha de saída por cada grupo; ou seja, as linhas não são colapsadas na tabela resultante. Ao invés disso, cada linha da tabela de entrada é retornada.

A diferença entre uma função agregada e uma função de janela em SQL é simples. Uma função agregada colapsula todas as linhas em um único resultado, o que significa que você perde o acesso às linhas individuais. Uma função de janela permite o acesso a cada linha na janela definida. Isto é mostrado na imagem abaixo:

Diferença entre as funções SQL agregada e de janela

Para aprender as funções da janela SQL, eu recomendo a janela interativa Funções de Janela (Window Functions) em SQL curso em LearnSQL.com.br.

Voltemos ao nosso exemplo. Se você executar esta parte do código ...

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

... o preço médio de fechamento para cada par de moedas será atribuído a cada linha do CURRENCYTRADE mesa.

O resultado será algo parecido com isto:

Como você pode ver, a função de janela não agrupou a saída em uma única linha de saída por grupo de pares de moedas. Ao invés disso, cada linha agora contém informações adicionais: o preço médio de fechamento para o par de moedas apropriado. Isto pode ser muito útil, pois muitas análises exigirão algumas informações adicionais para cada linha, mantendo todas as colunas do conjunto de dados inicial.

Sintaxe da função Janela

Em nosso último exemplo de função de janela, usamos algumas palavras-chave especiais como OVER() e PARTITION BY:

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

Essas são as principais palavras-chave que definem uma função de janela. Aqui está uma breve explicação:

1. OVER denota que esta é uma função de janela. Dentro da cláusula OVER, podemos ter PARTITION, ORDER BY e outras cláusulas de estrutura de janela que nos dizem como a janela é enquadrada (ou seja, os grupos e como eles são ordenados). Embora não tenhamos usado a cláusula de moldura de janela (ROW ou RANGE) em nosso exemplo, tenha em mente que você pode usar tanto dentro da cláusula OVER para especificar molduras dentro das divisórias.
2. PARTITION nos diz como os dados são agrupados ou enquadrados. Ele é colocado dentro da cláusula OVER. Em nosso exemplo, calculamos o preço médio para cada par de moedas; assim, dividimos nossa janela pelo marcador de coluna.
3. O ORDER BY (que não usamos em nosso exemplo) é freqüentemente usado para determinar a ordem das linhas dentro de cada quadro.
4. ROW ou RANGE é usado se quisermos limitar ainda mais as linhas dentro da divisória. Fazemos isso especificando os pontos inicial e final dentro da partição. Não importa qual destas duas cláusulas você emprega, ela deve ser usada com ORDER BY. A sintaxe é a seguinte:

[ROWS | RANGE] BETWEEN  AND 

A <linha_de_arranque> é denotada por uma das seguintes:

• SEM LIMITES PRECEDENTES: A janela começa na primeira linha da partição.
• CORRENTE ROW: A janela começa na linha atual.
• <número inteiro não-assinado literal> PRECEDING ou FOLLOWING.

E a <fila_de_fecho> usa uma das seguintes opções:

• SEM LIMITES: A janela termina na última linha da partição.
• CORRENTE ROW: A janela termina na linha atual.
• <número inteiro não-assinado literal> PRECEDING ou FOLLOWING.

Você pode encontrar mais exemplos de cláusulas de estrutura de janela aqui.

Não se preocupe se você não se sentir confortável com esta sintaxe. A prática ajudará; eu posso recomendar LearnSQL.com.br's Funções de Janela (Window Functions) em SQL curso, que tem muitas boas informações.

Agora que você aprendeu como usar funções analíticas SQL em suas consultas, o próximo passo é dar uma olhada nos tipos de funções de janela disponíveis em SQL. Até agora, mostramos apenas como o AVG() (uma função agregada) pode ser usado como uma função de janela. Vamos ver o que as outras funções podem fazer.

Tipos de funções de janela

Há três tipos principais de funções de janela:

• Funções de janela agregada: AVG(), MIN(), MAX(), COUNT(), SUM(). Estas funções são para calcular valores médios, mínimos ou máximos, o número total de linhas, ou a soma total dentro de cada quadro definido. As funções de janela agregada retornam um único valor para cada linha da consulta subjacente.

• Funções de janela de classificação: RANK(), ROW_NUMBER(), e similares. Funções de janela de classificação são usadas para classificar linhas dentro de cada quadro. Por exemplo, RANK() classificará um valor em um grupo de valores. A expressão ORDER BY na cláusula OVER determina o valor da classificação. Cada valor é classificado dentro de sua partição. Linhas com valores iguais para o critério de classificação recebem a mesma classificação.

  Vamos ter outro exemplo de uma função de janela de classificação. ROW_NUMBER() determina o número ordinal da linha atual dentro de sua partição. Mais uma vez, o ORDER BY na cláusula OVER determina este número. Cada valor é ordenado dentro de sua partição.

• Funções da janela de valores: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Estas funções realmente ajudam a relatar e resumir os dados. As funções da janela LAG() e LEAD() retornam o valor para a linha antes (LEAD()) ou depois (LAG()) da linha atual em uma partição. Se não houver nenhuma linha, um zero é retornado. Da mesma forma, a função da janela FIRST_VALUE() / LAST_VALUE() retorna o valor da expressão especificada para a primeira (ou última) linha na moldura da janela.

Já vimos um exemplo de como usar funções de janela agregadas, para que você possa entender porque elas são úteis na análise de dados. Na verdade, todos os três tipos de funções de janela SQL são usados com freqüência em análises complexas. Eles são uma grande característica em SQL.

Precisa de um exemplo da vida real? Bem, suponha que você esteja em um negócio de negociação Forex. Com bastante freqüência, ao vender ou comprar posições, você examina o preço de fechamento do minuto anterior ou da hora anterior; para isso, você usaria a função LAG(). Você também poderia classificar seus preços de fechamento usando as funções de janela de classificação dentro de uma janela de tempo específica. Ou você poderia encontrar o preço de fechamento inicial ou final usando as funções de janela de valor.

Usando as funções de janela SQL: LAG()

Vamos nos aprofundar no uso das funções analíticas SQL na vida real. Para cada linha, vamos ver o preço de fechamento da linha anterior. Vamos usar uma função de janela de ranking:

select *,LAG(close) OVER(PARTITION BY ticker ORDER BY datetime) AS previous_close from CURRENCYTRADE;

Estamos usando LAG(), que retorna o valor da linha anterior. Aqui, o OVER denota que esta é uma função de janela na qual estamos agrupando as linhas por par de moedas. Como estamos usando LAG(), também precisamos de uma cláusula ORDER BY para ordenar os dados dentro de cada quadro antes de atribuir preços de fechamento da linha anterior. Estamos ordenando os dados pela coluna de data/hora, o que significa que teremos o preço de fechamento para o minuto anterior em cada linha atual. A imagem abaixo mostra como fica a saída:

Função Window lag

Este código é mais simples e mais fácil de manter. Esse é um grande benefício de usar funções de janela. De fato, elas têm muitos benefícios, como veremos.

Benefícios do uso de SQL Funções de Janela (Window Functions) em SQL

As funções de janela são úteis quando não é necessário colapsar linhas no conjunto de resultados, ou seja, agrupar os dados do resultado em uma única linha de saída. Em vez de uma única linha de saída, um único valor para cada linha da consulta subjacente é retornado. Esse é o principal benefício, se você me perguntar.

Alguns dos outros benefícios das funções analíticas SQL incluem:

• As funções de janela permitem reunir valores agregados e não agregados de uma só vez. Isto porque para cada valor de linha retornado, não há agrupamento ou colapso dessa linha. Você pode manter todas as colunas de cada linha mais adicionar valores adicionais calculados pela função de janela. Isto é uma grande vantagem quando você precisa de valores agregados e não agregados em uma tabela.
• Sua sintaxe é simples, e é mais fácil manter o código em produção. Imagine quanto tempo seria necessário para implementar o equivalente à função LEAD(), LAG(), ou RANK() usando o SQL tradicional. Ou apenas para atribuir médias a cada linha sem funções de janela! Você precisaria primeiro usar a função agregada GROUP BY, seguida por um LEFT JOIN com a tabela de dados de entrada original. Seu código seria mais complicado e mais difícil de manter. Aqui está um artigo interessante que mostra como um exemplo pode ser resolvido tanto com cursores quanto com funções analíticas SQL. O código da função analítica é mais limpo e simples, você não acha?
• Você pode facilmente atribuir a uma linha atual um valor de uma linha anterior ou de uma linha sucessiva. Em alguns bancos de dados esta é na verdade uma opção muito mais rápida do que usar uma solução com o cursor ou uma subconsulta correlata. Tais códigos são mais complexos e mais difíceis de manter. Aqui está um belo artigo que compara o desempenho (funções de janela versus cursor versus subconsulta) em um banco de dados MS SQL. Legal, certo?

Saiba mais sobre SQL Funções de Janela (Window Functions) em SQL

A maioria dos usuários de SQL são iniciantes avançados (pelo modelo Dreyfus) e podem não estar realmente cientes do SQL moderno. Saber usar as funções de janela é uma técnica mais avançada, mas é uma técnica que realmente vale a pena aprender! Se você decidir aprender funções de janela, encontre um curso que tenha muitos exemplos de funções analíticas de SQL sendo usadas em casos concretos de negócios. E, acima de tudo, procure por um com muitos exercícios para você praticar! Não esqueça - quanto mais você pratica, mais rápido e melhor você aprende.

Além dos cursos on-line, você sempre pode conferir o blog LearnSQL. Está cheio de artigos interessantes que explicam funções de janela, como por exemplo:

• Exemplo de funções de janela SQL com explicações
• SQL comum Funções de Janela (Window Functions) em SQL: Funções Posicionais
• Como usar as funções de Rank em SQL
• SQL comum Funções de Janela (Window Functions) em SQL: Utilização de partições com funções de classificação