NTH_VALUE¶

Sintaxe¶

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

Para uma sintaxe window_frame detalhada, consulte Sintaxe e uso da função de janela.

Argumentos¶

n

Isto especifica qual valor de N a ser usado quando se procura o valor N.

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

FROM { FIRST | LAST }

Se você deve ignorar ou respeitar os valores de NULL quando um expr contiver valores de NULL:

• FROM FIRST começa no início da lista ordenada e avança.

• FROM LAST começa no final da lista ordenada e se move para trás.

Padrão: FROM FIRST

{ IGNORE | RESPECT } NULLS

Se você deve ignorar ou respeitar os valores de NULL quando um expr contiver valores de NULL:

• IGNORE NULLS ignora valores NULL na expressão.

• RESPECT NULLS retorna um valor NULL se for o enésimo valor na expressão.

Padrão: RESPECT NULLS

Notas de uso¶

• O valor de entrada n não pode ser maior que 1000.

• 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 (cumulativo ou deslizante) especifica o subconjunto de linhas dentro do intervalo para o qual a função é calculada. Se nenhum window_frame for especificado, o padrão é a janela inteira:

  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

  Observe que isso se desvia da norma ANSI, que especifica o seguinte padrão para quadros de janelas:

  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Para obter mais informações sobre quadros de janela, incluindo sintaxe, notas de uso e exemplos, consulte Sintaxe e uso da função de janela.

Exemplos¶

SELECT column1, column2, NTH_VALUE(column2, 2) OVER (PARTITION BY column1 ORDER BY column2) AS column2_2nd FROM VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21), (2, 22); 

+---------+---------+-------------+ | COLUMN1 | COLUMN2 | COLUMN2_2ND | |---------+---------+-------------| | 1 | 10 | 11 | | 1 | 11 | 11 | | 1 | 12 | 11 | | 2 | 20 | 21 | | 2 | 21 | 21 | | 2 | 22 | 21 | +---------+---------+-------------+ 

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); 

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