9.22. Funções de janela

9.22. Funções de janela
Anterior	Acima	Capítulo 9. Funções e operadores	Principal	Próxima

9.22. Funções de janela #

As funções de janela fornecem a capacidade de realizar cálculos em conjuntos de linhas relacionadas à linha corrente da consulta. Veja a Seção 3.5 para obter uma introdução a este recurso, e a Seção 4.2.8 para conhecer os detalhes da sintaxe.

As funções de janela nativas estão listadas na Tabela 9.67. Note que estas funções devem ser chamadas usando a sintaxe de função de janela, ou seja, requerem a cláusula OVER.

Além dessas funções, qualquer função de agregação comum, nativa ou definida pelo usuário (ou seja, não as funções de agregação de conjunto ordenado, nem as funções de agregação de conjunto hipotético), pode ser usada como uma função de janela; veja a relação das funções de agregação nativas na Seção 9.21. As funções de agregação agem como funções de janela somente quando a cláusula OVER segue a chamada da função; senão agem como agregações simples e retornam uma única linha para todo o conjunto.

Tabela 9.67. Funções de janela de propósito geral

Função Descrição
`row_number` () → `bigint` Retorna o número da linha corrente na sua partição, contando a partir de 1. ^[a]
`rank` () → `bigint` Retorna a classificação (rank) da linha corrente, com descontinuidades; ou seja, o `row_number` da primeira linha de seu grupo. ^[b]
`dense_rank` () → `bigint` Retorna a classificação (rank) da linha corrente, sem descontinuidades; esta função, na realidade, conta os grupos. ^[c]
`percent_rank` () → `double precision` Retorna a classificação (rank) relativa da linha corrente, ou seja, (`rank` - 1) / (total de linhas da partição - 1). O valor varia assim de 0 a 1, inclusive. ^[d]
`cume_dist` () → `double precision` Retorna a distribuição cumulativa, ou seja, (número de linhas de partição anteriores, ou número de grupos a partir da linha corrente) / (total de linhas da partição). O valor varia de 1/`N` a 1. ^[e]
`ntile` ( `num_buckets` `integer` ) → `integer` Retorna um número inteiro que varia de 1 ao valor do argumento, dividindo a partição da forma mais igual possível. ^[f]
`lag` ( `valor` `anycompatible` [, `deslocamento` `integer` [, `default` `anycompatible` ]] ) → `anycompatible` Retorna o `valor` calculado na linha que está a `deslocamento` linhas antes da linha corrente na partição; caso não haja essa linha, em seu lugar retorna o valor `default` (que deve ser de um tipo de dados compatível com o `valor`). Tanto `deslocamento` quanto `default` são avaliados em relação à linha corrente. Se forem omitidos, `deslocamento` recebe 1 por padrão, e `default` recebe `NULL`. ^[g]
`lead` ( `valor` `anycompatible` [, `deslocamento` `integer` [, `default` `anycompatible` ]] ) → `anycompatible` Retorna o `valor` calculado na linha que está a `deslocamento` linhas após a linha corrente dentro da partição; caso não haja essa linha, em vez disso retorna o valor `default` (que deve ser de um tipo de dados compatível com o `valor`). Tanto `deslocamento` quanto `default` são avaliados em relação à linha corrente. Se forem omitidos, `deslocamento` recebe 1 por padrão, e `default` recebe `NULL`. ^[h]
`first_value` ( `valor` `anyelement` ) → `anyelement` Retorna o `valor` calculado na linha que é a primeira linha do quadro da janela. ^[i]
`last_value` ( `valor` `anyelement` ) → `anyelement` Retorna o `valor` calculado na linha que é a última linha do quadro da janela. ^[j]
`nth_value` ( `valor` `anyelement`, `n` `integer` ) → `anyelement` Retorna o `valor` calculado na linha que é a `n`-ésima linha do quadro da janela (contando de 1); retorna `NULL` se a linha não existir. ^[k]
^[a] ROW_NUMBER atribui um número único a cada linha à qual é aplicada (cada linha na partição, ou cada linha retornada pela consulta), na sequência ordenada de linhas especificada na cláusula `ORDER BY`, começando com 1. (Oracle) (N. T.) ^[b] RANK calcula a classificação de um valor em um grupo de valores. As linhas com valores iguais para os critérios de classificação recebem a mesma classificação. O Oracle adiciona o número de linhas vinculadas à classificação vinculada para calcular a próxima classificação. Portanto, as classificações podem não ser números consecutivos. (Oracle) (N. T.) ^[c] DENSE_RANK calcula a classificação de uma linha em um grupo ordenado de linhas e retorna a classificação como um número. As classificações são números inteiros consecutivos começando com 1. O maior valor da classificação é o número de valores únicos retornados pela consulta. Os valores da classificação não são ignorados em caso de empate. As linhas com valores iguais para os critérios de classificação recebem a mesma classificação. (Oracle) (N. T.) ^[d] PERCENT_RANK é semelhante à função `CUME_DIST` (distribuição cumulativa). O intervalo dos valores retornado por PERCENT_RANK é de 0 a 1, inclusive. A primeira linha em qualquer conjunto tem um `PERCENT_RANK` de 0 (zero). Como uma função analítica, para uma linha r, `PERCENT_RANK` calcula a classificação de r menos 1, dividida por 1 a menos que o número de linhas sendo avaliadas (todo o conjunto de resultados da consulta, ou de uma partição). (Oracle) (N. T.) ^[e] CUME_DIST calcula a distribuição cumulativa de um valor em um grupo de valores. O intervalo dos valores retornado por `CUME_DIST` é >0 a <=1. Os valores empatados sempre recebem o mesmo valor de distribuição cumulativo. Como uma função analítica, `CUME_DIST` calcula a posição relativa de um valor específico em um grupo de valores. Para uma linha r, assumindo ordem crescente, o `CUME_DIST` de r é o número de linhas com valores menores ou iguais ao valor de r, dividido pelo número de linhas sendo avaliadas (todo o conjunto de resultados da consulta, ou uma partição). (Oracle) (N. T.) ^[f] NTILE divide um conjunto de dados ordenado no número de faixas (buckets) indicado pela expressão, e atribui o número do intervalo apropriado a cada linha. Os intervalos são numerados de 1 ao valor da expressão. (Oracle) (N. T.) ^[g] LAG fornece acesso a mais de uma linha da tabela ao mesmo tempo sem uma autojunção. Dada uma série de linhas retornadas de uma consulta e uma posição do cursor, `LAG` fornece acesso a uma linha a um determinado deslocamento físico anterior a essa posição. (Oracle) (N. T.) ^[h] LEAD fornece acesso a mais de uma linha da tabela ao mesmo tempo sem uma autojunção. Dada uma série de linhas retornadas de uma consulta e uma posição do cursor, `LEAD` fornece acesso a uma linha a um determinado deslocamento físico além dessa posição. (Oracle) (N. T.) ^[i] FIRST_VALUE retorna o primeiro valor de um conjunto ordenado de valores. Se o primeiro valor no conjunto for nulo, a função retornará `NULL`, a menos que se especifique `IGNORE NULLS`. (Oracle) (N. T.) ^[j] LAST_VALUE retorna o último valor de um conjunto ordenado de valores. (Oracle) (N. T.) ^[k] NTH_VALUE retorna o valor da enésima linha na janela definida pela cláusula analítica (Oracle) (N. T.)

Função

Descrição

row_number () → bigint

Retorna o número da linha corrente na sua partição, contando a partir de 1. ^[a]

rank () → bigint

Retorna a classificação (rank) da linha corrente, com descontinuidades; ou seja, o row_number da primeira linha de seu grupo. ^[b]

dense_rank () → bigint

Retorna a classificação (rank) da linha corrente, sem descontinuidades; esta função, na realidade, conta os grupos. ^[c]

percent_rank () → double precision

Retorna a classificação (rank) relativa da linha corrente, ou seja, (rank - 1) / (total de linhas da partição - 1). O valor varia assim de 0 a 1, inclusive. ^[d]

cume_dist () → double precision

Retorna a distribuição cumulativa, ou seja, (número de linhas de partição anteriores, ou número de grupos a partir da linha corrente) / (total de linhas da partição). O valor varia de 1/N a 1. ^[e]

ntile ( num_buckets integer ) → integer

Retorna um número inteiro que varia de 1 ao valor do argumento, dividindo a partição da forma mais igual possível. ^[f]

lag ( valor anycompatible [, deslocamento integer [, default anycompatible ]] ) → anycompatible

Retorna o valor calculado na linha que está a deslocamento linhas antes da linha corrente na partição; caso não haja essa linha, em seu lugar retorna o valor default (que deve ser de um tipo de dados compatível com o valor). Tanto deslocamento quanto default são avaliados em relação à linha corrente. Se forem omitidos, deslocamento recebe 1 por padrão, e default recebe NULL. ^[g]

lead ( valor anycompatible [, deslocamento integer [, default anycompatible ]] ) → anycompatible

Retorna o valor calculado na linha que está a deslocamento linhas após a linha corrente dentro da partição; caso não haja essa linha, em vez disso retorna o valor default (que deve ser de um tipo de dados compatível com o valor). Tanto deslocamento quanto default são avaliados em relação à linha corrente. Se forem omitidos, deslocamento recebe 1 por padrão, e default recebe NULL. ^[h]

first_value ( valor anyelement ) → anyelement

Retorna o valor calculado na linha que é a primeira linha do quadro da janela. ^[i]

last_value ( valor anyelement ) → anyelement

Retorna o valor calculado na linha que é a última linha do quadro da janela. ^[j]

nth_value ( valor anyelement, n integer ) → anyelement

Retorna o valor calculado na linha que é a n-ésima linha do quadro da janela (contando de 1); retorna NULL se a linha não existir. ^[k]

^[a] ROW_NUMBER atribui um número único a cada linha à qual é aplicada (cada linha na partição, ou cada linha retornada pela consulta), na sequência ordenada de linhas especificada na cláusula ORDER BY, começando com 1. (Oracle) (N. T.)

^[b] RANK calcula a classificação de um valor em um grupo de valores. As linhas com valores iguais para os critérios de classificação recebem a mesma classificação. O Oracle adiciona o número de linhas vinculadas à classificação vinculada para calcular a próxima classificação. Portanto, as classificações podem não ser números consecutivos. (Oracle) (N. T.)

^[c] DENSE_RANK calcula a classificação de uma linha em um grupo ordenado de linhas e retorna a classificação como um número. As classificações são números inteiros consecutivos começando com 1. O maior valor da classificação é o número de valores únicos retornados pela consulta. Os valores da classificação não são ignorados em caso de empate. As linhas com valores iguais para os critérios de classificação recebem a mesma classificação. (Oracle) (N. T.)

^[d] PERCENT_RANK é semelhante à função CUME_DIST (distribuição cumulativa). O intervalo dos valores retornado por PERCENT_RANK é de 0 a 1, inclusive. A primeira linha em qualquer conjunto tem um PERCENT_RANK de 0 (zero). Como uma função analítica, para uma linha r, PERCENT_RANK calcula a classificação de r menos 1, dividida por 1 a menos que o número de linhas sendo avaliadas (todo o conjunto de resultados da consulta, ou de uma partição). (Oracle) (N. T.)

^[e] CUME_DIST calcula a distribuição cumulativa de um valor em um grupo de valores. O intervalo dos valores retornado por CUME_DIST é >0 a <=1. Os valores empatados sempre recebem o mesmo valor de distribuição cumulativo. Como uma função analítica, CUME_DIST calcula a posição relativa de um valor específico em um grupo de valores. Para uma linha r, assumindo ordem crescente, o CUME_DIST de r é o número de linhas com valores menores ou iguais ao valor de r, dividido pelo número de linhas sendo avaliadas (todo o conjunto de resultados da consulta, ou uma partição). (Oracle) (N. T.)

^[f] NTILE divide um conjunto de dados ordenado no número de faixas (buckets) indicado pela expressão, e atribui o número do intervalo apropriado a cada linha. Os intervalos são numerados de 1 ao valor da expressão. (Oracle) (N. T.)

^[g] LAG fornece acesso a mais de uma linha da tabela ao mesmo tempo sem uma autojunção. Dada uma série de linhas retornadas de uma consulta e uma posição do cursor, LAG fornece acesso a uma linha a um determinado deslocamento físico anterior a essa posição. (Oracle) (N. T.)

^[h] LEAD fornece acesso a mais de uma linha da tabela ao mesmo tempo sem uma autojunção. Dada uma série de linhas retornadas de uma consulta e uma posição do cursor, LEAD fornece acesso a uma linha a um determinado deslocamento físico além dessa posição. (Oracle) (N. T.)

^[i] FIRST_VALUE retorna o primeiro valor de um conjunto ordenado de valores. Se o primeiro valor no conjunto for nulo, a função retornará NULL, a menos que se especifique IGNORE NULLS. (Oracle) (N. T.)

^[j] LAST_VALUE retorna o último valor de um conjunto ordenado de valores. (Oracle) (N. T.)

^[k] NTH_VALUE retorna o valor da enésima linha na janela definida pela cláusula analítica (Oracle) (N. T.)

Todas as funções listadas na Tabela 9.67 dependem da ordem de classificação especificada na cláusula ORDER BY da definição de janela associada. As linhas que não são distintas ao considerar apenas as colunas ORDER BY são chamadas de pares. As quatro funções de classificação (incluindo cume_dist) são definidas para fornecerem a mesma resposta para todas as linhas de um grupo de pares.

Note que as funções first_value, last_value e nth_value consideram apenas as linhas dentro do “quadro da janela”, que por padrão contém as linhas desde o início da partição até o último par da linha corrente. Isso provavelmente produzirá um resultado inútil para a função last_value, e algumas vezes para a função nth_value também. O quadro pode ser redefinido adicionando uma especificação de quadro adequada (RANGE, ROWS ou GROUPS) para a cláusula OVER. Veja a Seção 4.2.8 para obter mais informações sobre as especificações de quadro.

Quando uma função de agregação é usada como uma função de janela, ela agrega as linhas dentro do quadro da janela da linha corrente. Uma agregação usada com ORDER BY e a definição padrão de quadro de janela, produz um comportamento do tipo “soma em execução” (running sum), que pode ou não ser o desejado. Para obter uma agregação de toda a partição, omita a cláusula ORDER BY, ou use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Outras especificações de quadro podem ser usadas para obter outros efeitos.

Nota

O padrão SQL define a opção RESPECT NULLS ou IGNORE NULLS para as funções lead, lag, first_value, last_value e nth_value. Isso não é implementado no PostgreSQL: o comportamento é sempre o comportamento padrão do SQL, ou seja RESPECT NULLS. Da mesma forma, a opção do padrão SQL FROM FIRST ou FROM LAST para a função nth_value também não é implementada: apenas o comportamento padrão do SQL FROM FIRST tem suporte. (Pode ser obtido o resultado de FROM LAST invertendo a ordem na cláusula ORDER BY.)

Exemplo 9.8. Exemplo do tradutor

Este exemplo tem por finalidade mostrar os resultados produzidos pelas funções de janela de propósito geral listadas na Tabela 9.67. Os resultados obtidos foram idênticos aos produzidos pelos sistemas gerenciadores de banco de dados Oracle 21c e DB2 11.5 da IBM, que também implementam estas mesmas funções.

Todas as consultas deste exemplo usam a mesma tabela salario_emp vista na Seção 3.5.

SELECT nome_dep, num_emp, salario,
       row_number()
           OVER (PARTITION BY nome_dep
                 ORDER BY salario, num_emp)
FROM salario_emp;

|nome_dep       |num_emp|salario|row_number|
|---------------|-------|-------|----------|
|desenvolvimento|7      |4.200  |1         |
|desenvolvimento|9      |4.500  |2         |
|desenvolvimento|10     |5.200  |3         |
|desenvolvimento|11     |5.200  |4         |
|desenvolvimento|8      |6.000  |5         |
|pessoal        |5      |3.500  |1         |
|pessoal        |2      |3.900  |2         |
|vendas         |3      |4.800  |1         |
|vendas         |4      |4.800  |2         |
|vendas         |1      |5.000  |3         |

SELECT nome_dep, num_emp, salario,
       rank()
           OVER (PARTITION BY nome_dep
                 ORDER BY salario)
FROM salario_emp;

|nome_dep       |num_emp|salario|rank|
|---------------|-------|-------|----|
|desenvolvimento|7      |4.200  |1   |
|desenvolvimento|9      |4.500  |2   |
|desenvolvimento|11     |5.200  |3   |
|desenvolvimento|10     |5.200  |3   |
|desenvolvimento|8      |6.000  |5   |
|pessoal        |5      |3.500  |1   |
|pessoal        |2      |3.900  |2   |
|vendas         |3      |4.800  |1   |
|vendas         |4      |4.800  |1   |
|vendas         |1      |5.000  |3   |

SELECT nome_dep, num_emp, salario,
       dense_rank()
           OVER (PARTITION BY nome_dep
                 ORDER BY salario)
FROM salario_emp;

|nome_dep       |num_emp|salario|dense_rank|
|---------------|-------|-------|----------|
|desenvolvimento|7      |4.200  |1         |
|desenvolvimento|9      |4.500  |2         |
|desenvolvimento|11     |5.200  |3         |
|desenvolvimento|10     |5.200  |3         |
|desenvolvimento|8      |6.000  |4         |
|pessoal        |5      |3.500  |1         |
|pessoal        |2      |3.900  |2         |
|vendas         |3      |4.800  |1         |
|vendas         |4      |4.800  |1         |
|vendas         |1      |5.000  |2         |

SELECT nome_dep, num_emp, salario,
       percent_rank()
           OVER (PARTITION BY nome_dep
                 ORDER BY salario)
FROM salario_emp;

|nome_dep       |num_emp|salario|percent_rank|
|---------------|-------|-------|------------|
|desenvolvimento|7      |4.200  |0           |
|desenvolvimento|9      |4.500  |0,25        |
|desenvolvimento|11     |5.200  |0,5         |
|desenvolvimento|10     |5.200  |0,5         |
|desenvolvimento|8      |6.000  |1           |
|pessoal        |5      |3.500  |0           |
|pessoal        |2      |3.900  |1           |
|vendas         |3      |4.800  |0           |
|vendas         |4      |4.800  |0           |
|vendas         |1      |5.000  |1           |

SELECT nome_dep, num_emp, salario,
       cume_dist()
           OVER (PARTITION BY nome_dep
                 ORDER BY salario)
FROM salario_emp;

|nome_dep       |num_emp|salario|cume_dist   |
|---------------|-------|-------|------------|
|desenvolvimento|7      |4.200  |0,2         |
|desenvolvimento|9      |4.500  |0,4         |
|desenvolvimento|11     |5.200  |0,8         |
|desenvolvimento|10     |5.200  |0,8         |
|desenvolvimento|8      |6.000  |1           |
|pessoal        |5      |3.500  |0,5         |
|pessoal        |2      |3.900  |1           |
|vendas         |3      |4.800  |0,6666666667|
|vendas         |4      |4.800  |0,6666666667|
|vendas         |1      |5.000  |1           |

SELECT nome_dep, num_emp, salario,
       ntile(3)
           OVER (PARTITION BY nome_dep
                 ORDER BY salario)
FROM salario_emp;

|nome_dep       |num_emp|salario|ntile|
|---------------|-------|-------|-----|
|desenvolvimento|7      |4.200  |1    |
|desenvolvimento|9      |4.500  |1    |
|desenvolvimento|11     |5.200  |2    |
|desenvolvimento|10     |5.200  |2    |
|desenvolvimento|8      |6.000  |3    |
|pessoal        |5      |3.500  |1    |
|pessoal        |2      |3.900  |2    |
|vendas         |3      |4.800  |1    |
|vendas         |4      |4.800  |2    |
|vendas         |1      |5.000  |3    |

SELECT nome_dep, num_emp, salario,
       lag(num_emp, 1, 0)
           OVER (PARTITION BY nome_dep
                 ORDER BY salario, num_emp)
FROM salario_emp;

|nome_dep       |num_emp|salario|lag|
|---------------|-------|-------|---|
|desenvolvimento|7      |4.200  |0  |
|desenvolvimento|9      |4.500  |7  |
|desenvolvimento|10     |5.200  |9  |
|desenvolvimento|11     |5.200  |10 |
|desenvolvimento|8      |6.000  |11 |
|pessoal        |5      |3.500  |0  |
|pessoal        |2      |3.900  |5  |
|vendas         |3      |4.800  |0  |
|vendas         |4      |4.800  |3  |
|vendas         |1      |5.000  |4  |

SELECT nome_dep, num_emp, salario,
       lead(num_emp, 1, 0)
           OVER (PARTITION BY nome_dep
                 ORDER BY salario, num_emp)
FROM salario_emp;

|nome_dep       |num_emp|salario|lead|
|---------------|-------|-------|----|
|desenvolvimento|7      |4.200  |9   |
|desenvolvimento|9      |4.500  |10  |
|desenvolvimento|10     |5.200  |11  |
|desenvolvimento|11     |5.200  |8   |
|desenvolvimento|8      |6.000  |0   |
|pessoal        |5      |3.500  |2   |
|pessoal        |2      |3.900  |0   |
|vendas         |3      |4.800  |4   |
|vendas         |4      |4.800  |1   |
|vendas         |1      |5.000  |0   |

SELECT nome_dep, num_emp, salario,
       first_value(num_emp)
           OVER (PARTITION BY nome_dep
                 ORDER BY salario, num_emp)
FROM salario_emp;

|nome_dep       |num_emp|salario|first_value|
|---------------|-------|-------|-----------|
|desenvolvimento|7      |4.200  |7          |
|desenvolvimento|9      |4.500  |7          |
|desenvolvimento|10     |5.200  |7          |
|desenvolvimento|11     |5.200  |7          |
|desenvolvimento|8      |6.000  |7          |
|pessoal        |5      |3.500  |5          |
|pessoal        |2      |3.900  |5          |
|vendas         |3      |4.800  |3          |
|vendas         |4      |4.800  |3          |
|vendas         |1      |5.000  |3          |

SELECT nome_dep, num_emp, salario,
       last_value(num_emp)
           OVER (PARTITION BY nome_dep
                 ORDER BY salario, num_emp)
FROM salario_emp;

|nome_dep       |num_emp|salario|last_value|
|---------------|-------|-------|----------|
|desenvolvimento|7      |4.200  |7         |
|desenvolvimento|9      |4.500  |9         |
|desenvolvimento|10     |5.200  |10        |
|desenvolvimento|11     |5.200  |11        |
|desenvolvimento|8      |6.000  |8         |
|pessoal        |5      |3.500  |5         |
|pessoal        |2      |3.900  |2         |
|vendas         |3      |4.800  |3         |
|vendas         |4      |4.800  |4         |
|vendas         |1      |5.000  |1         |

SELECT nome_dep, num_emp, salario,
       nth_value(num_emp, 2)
           OVER (PARTITION BY nome_dep
                 ORDER BY salario, num_emp)
FROM salario_emp;

|nome_dep       |num_emp|salario|nth_value|
|---------------|-------|-------|---------|
|desenvolvimento|7      |4.200  |         |
|desenvolvimento|9      |4.500  |9        |
|desenvolvimento|10     |5.200  |9        |
|desenvolvimento|11     |5.200  |9        |
|desenvolvimento|8      |6.000  |9        |
|pessoal        |5      |3.500  |         |
|pessoal        |2      |3.900  |2        |
|vendas         |3      |4.800  |         |
|vendas         |4      |4.800  |4        |
|vendas         |1      |5.000  |4        |