Esta página foi traduzida do inglês com assistência de IA. Se houver diferenças entre as duas, a versão em inglês é a referência.
Funções de janela
As funções de janela realizam cálculos em um conjunto de linhas relacionadas à linha atual. Ao contrário das funções de agregação, que retornam um único resultado para um grupo de linhas, as funções de janela retornam um valor para cada linha, considerando uma "janela" de linhas relacionadas.
Veja também:
[SELECT] [WITH (CTEs)] [ Referência de funções de janela ]
Sintaxe
As funções de janela usam a cláusula OVER para definir a especificação da janela:
SELECT <windowFunction> OVER ( <windowSpec> ) FROM <multipartIdentifier>;
windowFunctionCall::=
Função de janela
As funções de janela são funções especializadas que realizam cálculos em um conjunto de linhas definido pela especificação da janela. Elas podem ser funções de janela dedicadas (como ROW_NUMBER ou LAG) ou funções de agregação (como SUM ou COUNT) usadas com uma cláusula OVER.
{ <aggregateFunction> | ROW_NUMBER() | RANK() | DENSE_RANK() | PERCENT_RANK()
| CUME_DIST() | NTILE(<int>) | LAG(<expr> [,<offset> [,<default>]])
| LEAD(<expr> [,<offset> [,<default>]]) | FIRST_VALUE(<expr>)
| LAST_VALUE(<expr>) | NTH_VALUE(<expr>, <int>) }
windowFunction::=
Especificação da janela
A especificação da janela define como as linhas são particionadas, ordenadas e enquadradas para o cálculo da função de janela. PARTITION BY divide as linhas em grupos, ORDER BY determina a sequência dentro de cada partição e a cláusula de quadro opcional especifica quais linhas, em relação à linha atual, são incluídas no cálculo.
[ <windowName> ]
[ PARTITION BY <fieldList> ]
[ ORDER BY <fieldList> [ ASC | DESC ] ]
[ { ROWS | RANGE | GROUPS }
{ UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW }
| { ROWS | RANGE | GROUPS } BETWEEN
{ UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW | <int> FOLLOWING }
AND
{ <int> PRECEDING | CURRENT ROW | <int> FOLLOWING | UNBOUNDED FOLLOWING } ]
windowSpec::=
Funções de janela disponíveis
| Categoria | Funções |
|---|---|
| Classificação | ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() |
| Deslocamento | LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() |
| Distribuição | PERCENT_RANK(), CUME_DIST() |
| Agregação | SUM(), COUNT(), AVG(), MIN(), MAX() com a cláusula OVER |
Exemplos
Classificação com ROW_NUMBER, RANK e DENSE_RANK
Use funções de classificação para atribuir classificações às linhas com base nos valores das colunas.
-- Rank contributors by contribution count
SELECT
login,
contributions,
ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num,
RANK() OVER (ORDER BY contributions DESC) as rank,
DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';
Totais acumulados e porcentagens
Funções de agregação como SUM e COUNT podem ser usadas como funções de janela para calcular totais acumulados e porcentagens.
-- Running total and percentage of contributions
SELECT
login,
contributions,
SUM(contributions) OVER (ORDER BY contributions DESC) as running_total,
SUM(contributions) OVER () as total_contributions,
ROUND(100.0 * contributions / SUM(contributions) OVER (), 2) as pct_of_total
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';
Comparar linhas com LAG e LEAD
Use LAG e LEAD para acessar valores de linhas anteriores ou posteriores.
-- Compare each release to previous release
SELECT
tag_name,
name,
published_at,
LAG(tag_name, 1) OVER (ORDER BY published_at) as previous_release,
LEAD(tag_name, 1) OVER (ORDER BY published_at) as next_release,
julianday(published_at) - julianday(LAG(published_at, 1) OVER (ORDER BY published_at)) as days_since_last_release
FROM github.repos.releases
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY published_at;
Criar grupos com NTILE
Use NTILE para dividir as linhas em um número especificado de grupos.
-- Group contributors into quartiles
SELECT
login,
contributions,
NTILE(4) OVER (ORDER BY contributions DESC) as quartile,
CASE NTILE(4) OVER (ORDER BY contributions DESC)
WHEN 1 THEN 'Top Contributors'
WHEN 2 THEN 'Active Contributors'
WHEN 3 THEN 'Moderate Contributors'
WHEN 4 THEN 'Occasional Contributors'
END as tier
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';
Usar PARTITION BY para análise agrupada
Use PARTITION BY para realizar cálculos de janela dentro de grupos.
-- Track cumulative issues by state
SELECT
number,
title,
state,
created_at,
ROW_NUMBER() OVER (ORDER BY created_at) as issue_sequence,
COUNT(*) OVER (ORDER BY created_at) as cumulative_issues,
COUNT(*) OVER (PARTITION BY state ORDER BY created_at) as cumulative_by_state
FROM github.issues.issues
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY created_at;
Usar quadros de janela
Especifique uma cláusula de quadro para controlar quais linhas são incluídas no cálculo da janela.
-- Calculate moving average of commit activity
WITH weekly_totals AS (
SELECT
week,
SUM(json_each.value) as commits_this_week
FROM github.repos.stats_commit_activity, JSON_EACH(days)
WHERE owner = 'stackql'
AND repo = 'stackql'
GROUP BY week
)
SELECT
week,
commits_this_week,
ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
SUM(commits_this_week) OVER (ORDER BY week) as cumulative_commits
FROM weekly_totals
ORDER BY week;
Para obter a documentação detalhada de cada função de janela, consulte a Referência de funções de janela.
Para obter mais informações, consulte https://sqlite.org/windowfunctions.html.