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:

FIRST_VALUE , NTH_VALUE

Sintaxe

LAST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
  OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ <window_frame> ] )
Copy

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
Copy
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
Copy
{ 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') ...
    
    Copy

    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);
Copy
+---------+---------+--------------+
| 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 chamada NTH_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);
Copy

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;
Copy
+---------------+-----------+---+-----------+---------+----------+
| 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 |
+---------------+-----------+---+-----------+---------+----------+