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 sales
tabela 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 BY
clá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 BY
clá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 OVER
clá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 BY
clá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 BY
e HAVING
e antes de ORDER BY
, LIMIT
e 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
OVER
clá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 OVER
clá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_clause
divide 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_clause
sintaxe é 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 BY
cláusula. Várias expressões são separadas por vírgulas.
sintaxe order_by_clause
O order_by_clause
tem a seguinte sintaxe:
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A ORDER BY
clá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 BY
cláusula, a ORDER BY
cláusula também é suportada por todas as funções de janela. No entanto, só faz sentido usar a ORDER BY
clá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 ROWS
ou RANGE
. Os deslocamentos da linha atual e das linhas do quadro são os números das linhas se a unidade do quadro for ROWS
e os valores da linha que a unidade do quadro for RANGE
.
O frame_start
e frame_between
define o limite do quadro.
O frame_start
conté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_1
e frame_boundary_2
pode 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_definition
cláusula OVER
, o MySQL usará o seguinte quadro por padrão:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Linguagem 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 BY clá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.