Neste tutorial mostro como operar com funções janela ou window functions tanto no R quanto no PostgreSQL.
Funções janela aplicam uma função agregadora em uma partição de linhas selecionadas numa query. Elas retornam a mesma tabela, ou a seleção de colunas, adicionada de uma ou mais colunas com o resultado da aplicação da função agregadora, especialmente quando opera como agregado de rolagem, tal como soma cumulativa ou média móvel.
Neste primeiro tutorial, irei mostrar como operam as principais funções. No próximo, irei explorar window frames e aplicações concretas, especialmente para séries temporais, tais como soma cumulativas e média móvel.
Quando você quer criar sumários dos dados no R e no PostgreSQL, a maneira mais comumumente utilizada é por meio da funções group_by e summarize no R GROUP BY e das funções de agregação no PostgreSQL.
Vamos usar o clássico dataframe mtcars para dar alguns exemplos. No R, eu faria assim para gerar as estatísticas descritivas da coluna mpg agrupadas pela coluna cyl.
library(tidyverse)
mtcars %>%
group_by(cyl) %>%
summarize(
n = n(),
min = min(mpg),
max = max(mpg),
media = mean(mpg),
mediana = median(mpg),
desvio_padrao = sd(mpg)
)
# A tibble: 3 x 7
cyl n min max media mediana desvio_padrao
<dbl> <int> <dbl> <dbl> <dbl> <dbl> <dbl>
1 4 11 21.4 33.9 26.7 26 4.51
2 6 7 17.8 21.4 19.7 19.7 1.45
3 8 14 10.4 19.2 15.1 15.2 2.56O mesmo resultado você obtêm com a query abaixo. Cuidado apenas para ordenar a coluna mpg por grupos ao calcular a mediana, pois vale sempre relembrar que SQL não possui noção intrínseca de ordem.
SELECT cyl, COUNT(*) AS n,
MIN(mpg) AS min,
MAX(mpg) AS max,
AVG(mpg) AS media,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY mpg ASC) AS mediana,
STDDEV(mpg) AS desvio_padrao
FROM mtcars
GROUP BY cyl; Summarize no R e as funções de agregação no PostgreSQL retornam apenas os agregados. No entanto, por vezes, queremos também as colunas originais, mesmo que os agregados se repitam. Para isso, no R utilizamos group_by com mutate:
mtcars %>%
select(cyl, mpg) %>%
group_by(cyl) %>%
mutate(
media = mean(mpg)
)
# A tibble: 32 x 3
# Groups: cyl [3]
cyl mpg media
<dbl> <dbl> <dbl>
1 6 21 19.7
2 6 21 19.7
3 4 22.8 26.7
4 6 21.4 19.7
5 8 18.7 15.1
6 6 18.1 19.7
7 8 14.3 15.1
8 4 24.4 26.7
9 4 22.8 26.7
10 6 19.2 19.7
# … with 22 more rowsNo PostgreSQL, este mesmo resultado pode ser obtido por meio da cláusula OVER, adicionada depois da função janela, no caso AVG, com o argumento PARTITION BY seguido da coluna agrupadora.
Você deve ter notado que eu criei apenas um sumário. Para criar vários sumários, com o mutate é simples:
mtcars %>%
select(cyl,mpg) %>%
group_by(cyl) %>%
mutate(
n = n(),
min = min(mpg),
max = max(mpg),
media = mean(mpg),
mediana = median(mpg),
desvio_padrao = sd(mpg)
)
# A tibble: 32 x 8
# Groups: cyl [3]
cyl mpg n min max media mediana desvio_padrao
<dbl> <dbl> <int> <dbl> <dbl> <dbl> <dbl> <dbl>
1 6 21 7 17.8 21.4 19.7 19.7 1.45
2 6 21 7 17.8 21.4 19.7 19.7 1.45
3 4 22.8 11 21.4 33.9 26.7 26 4.51
4 6 21.4 7 17.8 21.4 19.7 19.7 1.45
5 8 18.7 14 10.4 19.2 15.1 15.2 2.56
6 6 18.1 7 17.8 21.4 19.7 19.7 1.45
7 8 14.3 14 10.4 19.2 15.1 15.2 2.56
8 4 24.4 11 21.4 33.9 26.7 26 4.51
9 4 22.8 11 21.4 33.9 26.7 26 4.51
10 6 19.2 7 17.8 21.4 19.7 19.7 1.45
# … with 22 more rowsCom funções janela, é um pouquinho mais complicado.
select mpg,cyl,
COUNT(*) over w as n,
MIN(mpg) over w as min,
MAX(mpg) over w as max,
AVG(mpg) over w as media,
STDDEV(mpg) over w as desvio_padrao
from mtcars
window w as (partition by cyl);Ou seja, para criar vários sumários, é necessário criar uma cláusula WINDOW e referenciá-la com OVER em cada um dos agregados.
Além das funções de agregação, tais como mean, median, sd, outras funções operam como funções janela. Abaixo segue a lista delas:
| nome_sql | nome_dplyr | descricao |
|---|---|---|
| CUME_DIST | cume_dist | Retorna a classificação relativa da linha atual |
| DENSE_RANK | dense_rank | Classifica a linha atual dentro da partição sem intervalos |
| FIRST_VALUE | first | Retorna o valor avaliado em relação à primeira linha dentro da partição |
| LAG | lag | Retorna o valor avaliado na linha que está em uma distância física especificada antes da linha atual dentro da partição |
| LAST_VALUE | last | Retorna o valor avaliado em ralação à última linha dentro da partição |
| LEAD | lead | Retorna o valor avaliado na linha que está a certa distância depois da linha atual dentro da partição |
| NTILE | ntile | Divide linhas em uma partição, tanto quanto possível, em iguais quantidades, e atribui a cada linha um inteiro começando por um até o valor do argumento |
| NTH_VALUE | nth | Retorna o valor avaliado em relação à linha nth em partição ordenada |
| PERCENT_RANK | percent_rank | Retona a classificação relativa da linha atual (rank-1) / (total de linhas -1) |
| RANK | min_rank | Retorna a linha atual dentro da partição com intervalos |
| ROW_NUMBER | row_number | Número da linha atual dentro da partição começando por 1 |
Essas funções regulamente supõem a existência de linhas ordenadas, que no SQL não existe. Se para você, classificar as linhas conforme uma ordem é importante, será necessário adicionar a cláusula ORDER BY dentro do OVER, a fim de assegurar a classificação segundo uma ordem. No R, a ordem é fixa, de modo que, se você já está satisfeita com ordem das linhas, não é necessário argumento adicional.
Vamos dar uma olhada nessas funções conforme o uso.
Esta função retorna uma sequência numérica, começando por um, dos valores agrupados.
mtcars %>%
select(cyl,mpg) %>%
group_by(cyl) %>%
mutate(classificacao = row_number(mpg)) %>%
arrange(cyl,classificacao)
# A tibble: 32 x 3
# Groups: cyl [3]
cyl mpg classificacao
<dbl> <dbl> <int>
1 4 21.4 1
2 4 21.5 2
3 4 22.8 3
4 4 22.8 4
5 4 24.4 5
6 4 26 6
7 4 27.3 7
8 4 30.4 8
9 4 30.4 9
10 4 32.4 10
# … with 22 more rowsEu coloquei o mpg dentro do row_number. Você pode retirar, mas aí a classificação respeitará a ordem original. Outra opção, é colocar o arrange antes do mutate. Dessa forma, você não precisa incluir a coluna dentro do row_number.
Vejamos como realizar a mesma operação no PostgreSQL.
Com rank, cria-se uma sequência também começando por um, com a diferença de que, ele retorna a mesma classificação para valores idênticos no mesmo grupo. Além disso, ele realiza saltos.
Eu não sei porque razão, mas rank no PostgreSQL não corresponde a exatamente rank no dplyr, mas a min_rank, ou rank com o argumento ties.method = “min”. Se você usar rank sem argumento, dplyr irá dividir um pelo número de valores identicos repetidos e adicionar ao inteiro da classificação. Veja no exemplo abaixo.
mtcars %>%
select(mpg,cyl) %>%
group_by(cyl) %>%
arrange(cyl,mpg) %>%
mutate(classificacao = rank(mpg))
# A tibble: 32 x 3
# Groups: cyl [3]
mpg cyl classificacao
<dbl> <dbl> <dbl>
1 21.4 4 1
2 21.5 4 2
3 22.8 4 3.5
4 22.8 4 3.5
5 24.4 4 5
6 26 4 6
7 27.3 4 7
8 30.4 4 8.5
9 30.4 4 8.5
10 32.4 4 10
# … with 22 more rowsNote que 22.8 com cyl 4 se repetem. Como eles aparecem pela primeira vez na terceira posição e só há uma repetição, o número 3 é adicionado de 0,5. Provavelmente, o que, na verdade, você quer é usar min_rank:
mtcars %>%
select(mpg,cyl) %>%
group_by(cyl) %>%
arrange(cyl,mpg) %>%
mutate(classificacao = min_rank(mpg))
# A tibble: 32 x 3
# Groups: cyl [3]
mpg cyl classificacao
<dbl> <dbl> <int>
1 21.4 4 1
2 21.5 4 2
3 22.8 4 3
4 22.8 4 3
5 24.4 4 5
6 26 4 6
7 27.3 4 7
8 30.4 4 8
9 30.4 4 8
10 32.4 4 10
# … with 22 more rowsResultado idêntico você obtêm com o PostgreSQL utilizando apenas a função RANK:
Note que o número quatro é saltado na coluna classificação. Isso porque o segundo 3 ocupa o seu lugar.
Dense_rank é similar a rank, com a diferença de que não há saltos.
mtcars %>%
select(mpg,cyl) %>%
group_by(cyl) %>%
arrange(cyl,mpg) %>%
mutate(classificacao = dense_rank(mpg))
# A tibble: 32 x 3
# Groups: cyl [3]
mpg cyl classificacao
<dbl> <dbl> <int>
1 21.4 4 1
2 21.5 4 2
3 22.8 4 3
4 22.8 4 3
5 24.4 4 4
6 26 4 5
7 27.3 4 6
8 30.4 4 7
9 30.4 4 7
10 32.4 4 8
# … with 22 more rowsAgora no PostgreSQL
Percent_rank retorna a classificação relativa da linha atual, num intervalo de zero a um. O cálculo é feito da seguinte forma: (rank-1)/total de linhas na partição -1).
mtcars %>%
select(mpg,cyl) %>%
group_by(cyl) %>%
arrange(cyl,mpg) %>%
mutate(classificacao = percent_rank(mpg))
# A tibble: 32 x 3
# Groups: cyl [3]
mpg cyl classificacao
<dbl> <dbl> <dbl>
1 21.4 4 0
2 21.5 4 0.1
3 22.8 4 0.2
4 22.8 4 0.2
5 24.4 4 0.4
6 26 4 0.5
7 27.3 4 0.6
8 30.4 4 0.7
9 30.4 4 0.7
10 32.4 4 0.9
# … with 22 more rowsNo PostgreSQL:
Estas funções retornam o primeiro, o último e valor n dentro da partição no R. First_value irá retornar o primeiro valor no postgreSQL, mas last_value e nth_value poderão lhe surpreender.
mtcars %>%
select(cyl,mpg) %>%
group_by(cyl) %>%
arrange(cyl,mpg) %>%
mutate(primeiro_valor = first(mpg),
valor_5 = nth(mpg,5),
ultimo_valor = last(mpg))
# A tibble: 32 x 5
# Groups: cyl [3]
cyl mpg primeiro_valor valor_5 ultimo_valor
<dbl> <dbl> <dbl> <dbl> <dbl>
1 4 21.4 21.4 24.4 33.9
2 4 21.5 21.4 24.4 33.9
3 4 22.8 21.4 24.4 33.9
4 4 22.8 21.4 24.4 33.9
5 4 24.4 21.4 24.4 33.9
6 4 26 21.4 24.4 33.9
7 4 27.3 21.4 24.4 33.9
8 4 30.4 21.4 24.4 33.9
9 4 30.4 21.4 24.4 33.9
10 4 32.4 21.4 24.4 33.9
# … with 22 more rowsDa primeira vez que eu usei o PostgreSQL para obter o último e o valor n dentro da partição, fiquei bastante supreso. Veja o resultado abaixo
SELECT cyl,mpg,
FIRST_VALUE(mpg) OVER w,
LAST_VALUE(mpg) OVER w,
NTH_VALUE(mpg,5) OVER w
FROM mtcars
WINDOW w AS (PARTITION BY cyl ORDER BY mpg);| cyl | mpg | primeiro_valor | valor_5 | ultimo_valor |
|---|---|---|---|---|
| 4 | 21.4 | 21.4 | NA | 21.4 |
| 4 | 21.5 | 21.4 | NA | 21.5 |
| 4 | 22.8 | 21.4 | NA | 22.8 |
| 4 | 22.8 | 21.4 | NA | 22.8 |
| 4 | 24.4 | 21.4 | 24.4 | 24.4 |
| 4 | 26 | 21.4 | 24.4 | 26 |
| 4 | 27.3 | 21.4 | 24.4 | 27.3 |
| 4 | 30.4 | 21.4 | 24.4 | 30.4 |
| 4 | 30.4 | 21.4 | 24.4 | 30.4 |
| 4 | 32.4 | 21.4 | 24.4 | 32.4 |
| 4 | 33.9 | 21.4 | 24.4 | 33.9 |
| 6 | 17.8 | 17.8 | NA | 17.8 |
| 6 | 18.1 | 17.8 | NA | 18.1 |
| 6 | 19.2 | 17.8 | NA | 19.2 |
| 6 | 19.7 | 17.8 | NA | 19.7 |
| 6 | 21 | 17.8 | 21 | 21 |
| 6 | 21 | 17.8 | 21 | 21 |
| 6 | 21.4 | 17.8 | 21 | 21.4 |
| 8 | 10.4 | 10.4 | NA | 10.4 |
| 8 | 10.4 | 10.4 | NA | 10.4 |
| 8 | 13.3 | 10.4 | NA | 13.3 |
| 8 | 14.3 | 10.4 | NA | 14.3 |
| 8 | 14.7 | 10.4 | 14.7 | 14.7 |
| 8 | 15 | 10.4 | 14.7 | 15 |
| 8 | 15.2 | 10.4 | 14.7 | 15.2 |
| 8 | 15.2 | 10.4 | 14.7 | 15.2 |
| 8 | 15.5 | 10.4 | 14.7 | 15.5 |
| 8 | 15.8 | 10.4 | 14.7 | 15.8 |
| 8 | 16.4 | 10.4 | 14.7 | 16.4 |
| 8 | 17.3 | 10.4 | 14.7 | 17.3 |
| 8 | 18.7 | 10.4 | 14.7 | 18.7 |
| 8 | 19.2 | 10.4 | 14.7 | 19.2 |
Note que, o primeiro valor retornou os valores esperado. Já last_value retornou os mesmos valores de mpg, e nth_value retornou NULL até o quarto valor (Aparece NA pq importei o resultado para o R). Isso aconteceu por causa de algo chamado windown frame, que contêm uma cláusula implícita, cada vez que chamamos a cláusula OVER. Veremos no próximo tutorial o que são window frames. Por ora, vamos aprender a contornar esse resultado.
Para obter resultado idêntido ao dplyr, devemos alterar a moldura da janela (window frame):
SELECT cyl,mpg,
FIRST_VALUE(mpg) OVER w,
NTH_VALUE(mpg,5) OVER w,
LAST_VALUE(mpg) OVER w
FROM mtcars
WINDOW w AS (
PARTITION BY cyl ORDER BY mpg
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);Duas funções extremamente úteis para quem trabalha com séries temporais são lag e lead. A primeira retorna o valor imediatamente anterior ao atual, por padrão, ou o valor distante n linhas. Lead funciona de forma similar, mas para valores posteriores.
mtcars %>%
select(mpg,cyl) %>%
group_by(cyl) %>%
arrange(cyl,mpg) %>%
mutate(anterior = lag(mpg),
posterior = lead(mpg))
# A tibble: 32 x 4
# Groups: cyl [3]
mpg cyl anterior posterior
<dbl> <dbl> <dbl> <dbl>
1 21.4 4 NA 21.5
2 21.5 4 21.4 22.8
3 22.8 4 21.5 22.8
4 22.8 4 22.8 24.4
5 24.4 4 22.8 26
6 26 4 24.4 27.3
7 27.3 4 26 30.4
8 30.4 4 27.3 30.4
9 30.4 4 30.4 32.4
10 32.4 4 30.4 33.9
# … with 22 more rowsNo PostgreSQL:
SELECT mpg,cyl,
lag(mpg) OVER w,
lead(mpg) OVER w
FROM mtcars
WINDOW w AS (PARTITION BY cyl ORDER BY mpg);Por fim, resta falar de ntile, que eu particularmente considero muito útil para identificar grupos num dataframe a fim de paralelizar operações no R. Tanto no PostgreSQL quanto no R, faz também sentido usar sem PARTITION BY ou group_by respectivamente, pois assim a tabela inteira é agrupada. Note que ntile cria grupos, tanto quanto possível, iguais.
mtcars %>%
select(mpg,cyl) %>%
arrange(cyl,mpg) %>%
mutate(grupo = ntile(n=4))
mpg cyl grupo
1 21.4 4 1
2 21.5 4 1
3 22.8 4 1
4 22.8 4 1
5 24.4 4 1
6 26.0 4 1
7 27.3 4 1
8 30.4 4 1
9 30.4 4 2
10 32.4 4 2
11 33.9 4 2
12 17.8 6 2
13 18.1 6 2
14 19.2 6 2
15 19.7 6 2
16 21.0 6 2
17 21.0 6 3
18 21.4 6 3
19 10.4 8 3
20 10.4 8 3
21 13.3 8 3
22 14.3 8 3
23 14.7 8 3
24 15.0 8 3
25 15.2 8 4
26 15.2 8 4
27 15.5 8 4
28 15.8 8 4
29 16.4 8 4
30 17.3 8 4
31 18.7 8 4
32 19.2 8 4No PostgreSQL, você obtêm o mesmo resultado com:
Exploraramos as principais funcionalidades e nos detivemos mais nos aspectos estáticos. Funções janela possuem aplicações infindáveis. Iremos mostrar no próximo tutorial como funcionam as molduras de janela ou window frames, as quais permitem realizar excelentes operações, especialmente com séries temporais, que regularmente requerem transformações dinâmicas, tais soma cumulativa e média móvel.
Funções janela são os últimos ítens a serem avalidados numa query, com exceção da cláusula ORDER BY. Por serem a última avaliação, funções janela não podem ser usadas dentro de cláusulas WHERE, GROUP BY ou HAVING.