- Categorias:
Sintaxe e uso da função de janela (classificação)
LAST_VALUE¶
Retorna o último valor dentro de um grupo ordenado de valores.
- Consulte também:
Sintaxe¶
LAST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ <window_frame> ] )
Para uma sintaxe window_frame
detalhada, consulte Sintaxe e uso da função de janela.
Argumentos¶
expr
A expressão que determina o valor de retorno.
expr1
A expressão pela qual as linhas são particionadas. Você pode especificar uma única expressão ou uma lista de expressões separadas por vírgula. Por exemplo:
PARTITION BY column_1, column_2
expr2
A expressão pela qual se ordenam as linhas. Você pode especificar uma única expressão ou uma lista de expressões separadas por vírgula. Por exemplo:
ORDER BY column_3, column_4
{ IGNORE | RESPECT } NULLS
Se você deve ignorar ou respeitar os valores de NULL quando um
expr
contiver valores de NULL:IGNORE NULLS
retorna o último valor diferente de NULL.RESPECT NULLS
retorna um valor NULL se for o último valor da expressão.
Padrão:
RESPECT NULLS
Notas de uso¶
Esta função é uma função relacionada à classificação, portanto, deve especificar uma janela. Uma cláusula de janela consiste nas seguintes subcláusulas:
Subcláusula
PARTITION BY expr1
(opcional).Subcláusula
ORDER BY expr2
(obrigatória). Para obter detalhes sobre outras opções de ordenação compatíveis (ordem de classificação, ordenação dos valores NULL e assim por diante), consulte a documentação da cláusula ORDER BY, que segue as mesmas regras.Subcláusula
window_frame
(opcional).
A ordem das linhas em uma janela (e portanto o resultado da consulta) é totalmente determinística somente se as chaves na cláusula ORDER BY tornarem cada linha única. Considere o seguinte exemplo:
... OVER (PARTITION BY p ORDER BY o COLLATE 'lower') ...
O resultado da consulta pode variar se qualquer partição contiver valores da coluna
o
que sejam idênticos, ou seriam idênticos em uma comparação que não diferencia maiúsculas e minúsculas.A cláusula ORDER BY dentro da cláusula OVER controla a ordem das linhas somente dentro da janela, não a ordem das linhas na saída de toda a consulta. Para controlar a ordem de saída, use uma cláusula ORDER BY separada no nível mais externo da consulta.
O
window_frame
opcional especifica o subconjunto de linhas dentro da janela para as quais a função é calculada. Se nenhum quadro de janela for especificado, o quadro padrão será a janela inteira:ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Esse comportamento difere do padrão ANSI, que especifica o seguinte padrão para quadros de janela:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Para uma sintaxe window_frame
detalhada, consulte Sintaxe e uso da função de janela.
Exemplos¶
O primeiro exemplo retorna os resultados de LAST_VALUE para column2
particionado por column1
:
SELECT
column1,
column2,
LAST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2) AS column2_last
FROM VALUES
(1, 10), (1, 11), (1, 12),
(2, 20), (2, 21), (2, 22);
+---------+---------+--------------+
| COLUMN1 | COLUMN2 | COLUMN2_LAST |
|---------+---------+--------------|
| 1 | 10 | 12 |
| 1 | 11 | 12 |
| 1 | 12 | 12 |
| 2 | 20 | 22 |
| 2 | 21 | 22 |
| 2 | 22 | 22 |
+---------+---------+--------------+
O exemplo a seguir retorna os resultados de três funções relacionadas: FIRST_VALUE, NTH_VALUE e LAST_VALUE.
A consulta cria um quadro de janela deslizante com três linhas de largura, que contém:
A linha que precede a linha atual.
A linha atual.
A linha que segue a linha atual.
A
2
na chamadaNTH_VALUE(i, 2)
especifica a segunda linha no quadro da janela (que, neste caso, é também a linha atual).Quando a linha atual é a primeira linha no quadro da janela, não há linha anterior para referência, portanto FIRST_VALUE retorna um NULL para aquela linha.
Às vezes, os limites da estrutura se estendem além das linhas em uma partição, mas as linhas inexistentes não são incluídas nos cálculos da função de janela. Por exemplo, quando a linha atual é a primeira linha da partição e o quadro de janela é
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
, não há nenhuma linha anterior a ser referenciada, portanto, a função FIRST_VALUE retorna o valor da primeira linha da partição.Os resultados nunca são iguais para todas as três funções, considerando os dados da tabela. Essas funções selecionam os valores first, last ou nth para cada linha do quadro, e a seleção de valores se aplica separadamente a cada partição.
Para executar esse exemplo, primeiro crie e carregue a tabela:
CREATE TABLE demo1 (i INTEGER, partition_col INTEGER, order_col INTEGER);
INSERT INTO demo1 (i, partition_col, order_col) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 1, 5),
(1, 2, 1),
(2, 2, 2),
(3, 2, 3),
(4, 2, 4);
Agora execute a seguinte consulta:
SELECT partition_col, order_col, i,
FIRST_VALUE(i) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS FIRST_VAL,
NTH_VALUE(i, 2) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS NTH_VAL,
LAST_VALUE(i) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LAST_VAL
FROM demo1
ORDER BY partition_col, i, order_col;
+---------------+-----------+---+-----------+---------+----------+
| PARTITION_COL | ORDER_COL | I | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+-----------+---+-----------+---------+----------|
| 1 | 1 | 1 | 1 | 2 | 2 |
| 1 | 2 | 2 | 1 | 2 | 3 |
| 1 | 3 | 3 | 2 | 3 | 4 |
| 1 | 4 | 4 | 3 | 4 | 5 |
| 1 | 5 | 5 | 4 | 5 | 5 |
| 2 | 1 | 1 | 1 | 2 | 2 |
| 2 | 2 | 2 | 1 | 2 | 3 |
| 2 | 3 | 3 | 2 | 3 | 4 |
| 2 | 4 | 4 | 3 | 4 | 4 |
+---------------+-----------+---+-----------+---------+----------+