Funções de janela (Lakehouse v1)

Visão geral

Uma função de janela opera sobre um grupo (“janela”) de linhas relacionadas.

Para cada linha de entrada, uma função de janela retorna uma linha de saída que depende da linha específica passada à função e dos valores das outras linhas da janela.

Há dois tipos principais de funções de janela sensíveis à ordem:

• Rank-related functions: as funções relacionadas a classificação listam informações com base no “ranking” de uma linha. Por exemplo, ao classificar lojas em ordem decrescente de lucro por ano, a loja mais lucrativa recebe a posição 1, a segunda mais lucrativa recebe a posição 2, e assim por diante.

• Window frame functions: as funções de quadro de janela permitem executar operações móveis, como calcular um total acumulado ou uma média móvel, sobre um subconjunto das linhas da janela.

Lista de funções que oferecem suporte a janelas

A lista abaixo mostra todas as funções de janela.

Nome da função	Categoria	Janela	Quadro de janela	Observações
ARRAY_AGG	Geral	✔
AVG	Geral	✔	✔
AVG_IF	Geral	✔	✔
COUNT	Geral	✔	✔
COUNT_IF	Geral	✔	✔
COVAR_POP	Geral	✔
COVAR_SAMP	Geral	✔
MAX	Geral	✔	✔
MAX_IF	Geral	✔	✔
MIN	Geral	✔	✔
MIN_IF	Geral	✔	✔
STDDEV_POP	Geral	✔	✔
STDDEV_SAMP	Geral	✔	✔
MEDIAN	Geral	✔	✔
QUANTILE_CONT	Geral	✔	✔
QUANTILE_DISC	Geral	✔	✔
KURTOSIS	Geral	✔	✔
SKEWNESS	Geral	✔	✔
SUM	Geral	✔	✔
SUM_IF	Geral	✔	✔
CUME_DIST	Classificação	✔
PERCENT_RANK	Classificação	✔	✔
DENSE_RANK	Classificação	✔	✔
RANK	Classificação	✔	✔
ROW_NUMBER	Classificação	✔
NTILE	Classificação	✔
FIRST_VALUE	Classificação	✔	✔
FIRST	Classificação	✔	✔
LAST_VALUE	Classificação	✔	✔
LAST	Classificação	✔	✔
NTH_VALUE	Classificação	✔	✔
LEAD	Classificação	✔
LAG	Classificação	✔

Sintaxe de janela

<function> ( [ <arguments> ] ) OVER ( { named window | inline window } )

named window ::=
    { window_name | ( window_name ) }

inline window ::=
    [ PARTITION BY <expression_list> ]
    [ ORDER BY <expression_list> ]
    [ window frame ]

A named window é uma janela definida na cláusula WINDOW da instrução SELECT, por exemplo: SELECT a, SUM(a) OVER w FROM t WINDOW w AS ( inline window ).

A <function> é uma entre (função de agregação, função de classificação, função de valor).

A cláusula OVER especifica que a função está sendo usada como função de janela.

A subcláusula PARTITION BY permite agrupar as linhas em subgrupos, por exemplo por cidade, por ano etc. A cláusula PARTITION BY é opcional. Você pode analisar um grupo inteiro de linhas sem dividi-lo em subgrupos.

A cláusula ORDER BY ordena as linhas dentro da janela.

A cláusula window frame especifica o tipo e a extensão do quadro da janela. A cláusula window frame é opcional. Se você omitir a cláusula window frame, o tipo de quadro padrão é RANGE e a extensão padrão é UNBOUNDED PRECEDING AND CURRENT ROW.

Sintaxe de quadro de janela

window frame pode ser de um dos seguintes tipos:

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }

slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }

Exemplos de SQL

Criar a tabela

CREATE TABLE employees (
  employee_id INT,
  first_name VARCHAR,
  last_name VARCHAR,
  department VARCHAR,
  salary INT
);

Inserir dados

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
  (1, 'John', 'Doe', 'IT', 75000),
  (2, 'Jane', 'Smith', 'HR', 85000),
  (3, 'Mike', 'Johnson', 'IT', 90000),
  (4, 'Sara', 'Williams', 'Sales', 60000),
  (5, 'Tom', 'Brown', 'HR', 82000),
  (6, 'Ava', 'Davis', 'Sales', 62000),
  (7, 'Olivia', 'Taylor', 'IT', 72000),
  (8, 'Emily', 'Anderson', 'HR', 77000),
  (9, 'Sophia', 'Lee', 'Sales', 58000),
  (10, 'Ella', 'Thomas', 'IT', 67000);

Exemplo 1: classificar funcionários por salário

Neste exemplo, usamos a função RANK() para classificar os funcionários com base nos salários, em ordem decrescente. O maior salário recebe a posição 1, e o menor salário recebe o maior número de posição.

SELECT employee_id, first_name, last_name, department, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Resultado:

employee_id	first_name	last_name	department	salary	rank
3	Mike	Johnson	IT	90000	1
2	Jane	Smith	HR	85000	2
5	Tom	Brown	HR	82000	3
8	Emily	Anderson	HR	77000	4
1	John	Doe	IT	75000	5
7	Olivia	Taylor	IT	72000	6
10	Ella	Thomas	IT	67000	7
6	Ava	Davis	Sales	62000	8
4	Sara	Williams	Sales	60000	9
9	Sophia	Lee	Sales	58000	10

Exemplo 2: calcular o salário total por departamento

Neste exemplo, usamos a função SUM() com PARTITION BY para calcular o total de salários pagos por departamento. Cada linha mostra o departamento e o salário total desse departamento.

SELECT department, SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;

Resultado:

department	total_salary
HR	244000
HR	244000
HR	244000
IT	304000
IT	304000
IT	304000
IT	304000
Sales	180000
Sales	180000
Sales	180000

Exemplo 3: calcular um total acumulado de salários por departamento

Neste exemplo, usamos a função SUM() com um quadro de janela cumulativo para calcular um total acumulado de salários dentro de cada departamento. O total acumulado é calculado com base no salário do funcionário, ordenado por employee_id.

SELECT employee_id, first_name, last_name, department, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY employee_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

Resultado:

employee_id	first_name	last_name	department	salary	running_total
2	Jane	Smith	HR	85000	85000
5	Tom	Brown	HR	82000	167000
8	Emily	Anderson	HR	77000	244000
1	John	Doe	IT	75000	75000
3	Mike	Johnson	IT	90000	165000
7	Olivia	Taylor	IT	72000	237000
10	Ella	Thomas	IT	67000	304000
4	Sara	Williams	Sales	60000	60000
6	Ava	Davis	Sales	62000	122000
9	Sophia	Lee	Sales	58000	180000