Funções da janela MySQL

Resumo : neste tutorial, você aprenderá sobre as funções de janela do MySQL e suas aplicações práticas para resolver desafios de consulta analítica.

O MySQL suporta funções de janela desde a versão 8.0, permitindo resolver problemas de consulta com mais facilidade e melhor desempenho.

Suponha que temos a salestabela que armazena as vendas por funcionários e exercícios:

CREATE TABLE sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);

INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);

SELECT * FROM sales;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Provavelmente é mais fácil entender as funções da janela se começarmos com funções agregadas .

As funções agregadas resumem dados de várias linhas em uma única linha de resultado. Por exemplo, a SUM()função a seguir retorna o total de vendas de todos os funcionários nos anos registrados:

SELECT 
    SUM(sale)
FROM
    sales;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A GROUP BYcláusula permite aplicar funções agregadas a um subconjunto de linhas. Por exemplo, você pode querer calcular o total de vendas por anos fiscais:

SELECT 
    fiscal_year, 
    SUM(sale)
FROM
    sales
GROUP BY 
    fiscal_year;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em ambos os exemplos, as funções agregadas reduzem o número de linhas retornadas pela consulta.

Assim como as funções agregadas com a GROUP BYcláusula, as funções de janela também operam em um subconjunto de linhas, mas não reduzem o número de linhas retornadas pela consulta.

Por exemplo, a consulta a seguir retorna as vendas de cada funcionário, juntamente com o total de vendas dos funcionários por ano fiscal:

SELECT 
    fiscal_year, 
    sales_employee,
    sale,
    SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
FROM
    sales;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, a SUM()função funciona como uma função de janela que opera em um conjunto de linhas definidas pelo conteúdo da OVERcláusula. Um conjunto de linhas ao qual a SUM()função se aplica é chamado de janela.

A SUM()função de janela informa não apenas o total de vendas por ano fiscal, como faz na consulta com a GROUP BYcláusula, mas também o resultado em cada linha, em vez do número total de linhas retornadas.

Observe que as funções de janela são executadas no conjunto de resultados depois de todas as cláusulas JOIN, WHERE, GROUP BYe HAVINGe antes de ORDER BY, LIMITe SELECT DISTINCT.

Sintaxe da função de janela

A sintaxe geral para chamar uma função de janela é a seguinte:

window_function_name(expression) OVER ( 
   [partition_defintion]
   [order_definition]
   [frame_definition]
)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

• Primeiro, especifique o nome da função da janela seguido por uma expressão.
• Segundo, especifique a OVERcláusula que possui três elementos possíveis: definição de partição, definição de ordem e definição de quadro.

Os parênteses de abertura e fechamento, que aparecem após a OVERcláusula, são obrigatórios, mesmo sem expressão, por exemplo:

window_function_name(expression) OVER()Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Sintaxe de cláusula_partição

O partition_clausedivide as linhas em pedaços ou partições. Duas partições são separadas por um limite de partição.

A função de janela é executada dentro das partições e reinicializada ao cruzar o limite da partição.

A partition_clausesintaxe é semelhante a esta:

PARTITION BY <expression>[{,<expression>...}]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Você pode especificar uma ou mais expressões na PARTITION BYcláusula. Várias expressões são separadas por vírgulas.

sintaxe order_by_clause

O order_by_clausetem a seguinte sintaxe:

ORDER BY <expression> [ASC|DESC], [{,<expression>...}]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A ORDER BYcláusula especifica como as linhas são ordenadas dentro de uma partição. É possível ordenar dados dentro de uma partição em múltiplas chaves, cada chave é especificada por uma expressão. Várias expressões também são separadas por vírgulas.

Semelhante à PARTITION BYcláusula, a ORDER BYcláusula também é suportada por todas as funções de janela. No entanto, só faz sentido usar a ORDER BYcláusula para funções de janela sensíveis à ordem.

sintaxe frame_clause

Um quadro é um subconjunto da partição atual. Para definir o subconjunto, você usa a cláusula frame da seguinte forma:

frame_unit {<frame_start>|<frame_between>}Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Um quadro é definido em relação à linha atual, o que permite que um quadro se mova dentro de uma partição dependendo da posição da linha atual dentro de sua partição.

A unidade do quadro especifica o tipo de relacionamento entre a linha atual e as linhas do quadro. Pode ser ROWSou RANGE. Os deslocamentos da linha atual e das linhas do quadro são os números das linhas se a unidade do quadro for ROWSe os valores da linha que a unidade do quadro for RANGE.

O frame_starte frame_betweendefine o limite do quadro.

O frame_startcontém um dos seguintes:

• UNBOUNDED PRECEDING: o quadro começa na primeira linha da partição.
• N PRECEDING: um N físico de linhas antes da primeira linha atual. N pode ser um número literal ou uma expressão avaliada como um número.
• CURRENT ROW: a linha do cálculo atual

O frame_betweené o seguinte:

BETWEEN frame_boundary_1 AND frame_boundary_2   Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Cada um frame_boundary_1e frame_boundary_2pode conter um dos seguintes:

• frame_start: como dito anteriormente.
• UNBOUNDED FOLLOWING: o quadro termina na linha final da partição.
• N FOLLOWING: um N físico de linhas após a linha atual.

Se você não especificar na frame_definitioncláusula OVER, o MySQL usará o seguinte quadro por padrão:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Lista de funções da janela MySQL

A tabela a seguir mostra as funções da janela no MySQL:

Nome	Descrição
CUME_DIST	Calcula a distribuição cumulativa de um valor em um conjunto de valores.
DENSE_RANK	Atribui uma classificação a cada linha dentro de sua partição com base na ORDER BYcláusula. Ele atribui a mesma classificação às linhas com valores iguais. Se duas ou mais linhas tiverem a mesma classificação, não haverá lacunas na sequência de valores classificados.
FIRST_VALUE	Retorna o valor da expressão especificada em relação à primeira linha no quadro da janela.
LAG	Retorna o valor da enésima linha antes da linha atual em uma partição. Retorna NULL se não existir nenhuma linha anterior.
LAST_VALUE	Retorna o valor da expressão especificada em relação à última linha do quadro da janela.
LIDERAR	Retorna o valor da enésima linha após a linha atual em uma partição. Ele retorna NULL se não existir nenhuma linha subsequente.
NTH_VALUE	Retorna o valor do argumento da enésima linha da moldura da janela
NTIL	Distribui as linhas de cada partição de janela em um número especificado de grupos classificados.
PERCENT_RANK	Calcula a classificação percentual de uma linha em uma partição ou conjunto de resultados
CLASSIFICAÇÃO	Semelhante à DENSE_RANK()função, exceto que há lacunas na sequência de valores classificados quando duas ou mais linhas têm a mesma classificação.
ROW_NUMBER	Atribui um número inteiro sequencial a cada linha dentro de sua partição

Neste tutorial, você aprendeu sobre as funções da janela MySQL e sua sintaxe. Nos próximos tutoriais você aprenderá mais detalhadamente sobre cada função da janela e suas aplicações.