NTH_VALUE no PostgreSQL: o n-esimo valor da janela e o segundo maior por grupo

NTH_VALUE(expr, n) e uma funcao de janela que retorna expr da n-esima linha do quadro da janela. Parece trivial, mas e exatamente aqui que as pessoas se queimam: por padrao o quadro e cortado na linha atual, e "o segundo maior valor do grupo" vira NULL sem avisar.

O que o NTH_VALUE calcula

A funcao olha o quadro atual da janela, conta ate a n-esima linha (comecando em um) e extrai expr dela. Se nao existir uma linha com essa posicao no quadro, o resultado e NULL.

SELECT
    id,
    amount,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM orders;

Detalhes importantes:

• n e a posicao dentro do quadro, nao dentro de toda a particao; o quadro decide tudo.
• A numeracao comeca em 1: NTH_VALUE(x, 1) equivale a FIRST_VALUE(x).
• n deve ser um inteiro positivo; pode ser uma expressao, mas nao pode referenciar colunas da linha.

Para tarefas como "o segundo maior" voce quase sempre quer um quadro que cubra toda a particao, como no exemplo acima.

A pegadinha do quadro: a mesma historia do LAST_VALUE

No momento em que uma janela recebe um ORDER BY, seu quadro padrao passa a ser RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Assim, a partir da primeira linha de uma particao o "segundo maior" ainda nao esta visivel: o quadro contem uma unica linha e NTH_VALUE(amount, 2) retorna NULL.

-- WRONG: default frame stops at the current row
SELECT
    id,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
    ) AS maybe_null
FROM orders;

A correcao e a mesma do LAST_VALUE: amplie o quadro de forma explicita para toda a particao.

-- RIGHT: widen the frame to the whole partition
SELECT
    id,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM orders;

Pegadinha: FIRST_VALUE so "funciona bem" com o quadro padrao por acaso, porque a primeira linha esta sempre no quadro. NTH_VALUE e LAST_VALUE sao sensiveis ao quadro, entao quase sempre escreva de forma explicita ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

FROM FIRST / FROM LAST e IGNORE NULLS

O SQL padrao descreve duas ideias uteis: contar a n-esima linha a partir do fim do quadro com FROM LAST, e pular os valores vazios com IGNORE NULLS. Vale reconhece-las porque aparecem na documentacao de varios motores e em SQL portavel.

-- second value counting from the end of the frame
SELECT
    id,
    NTH_VALUE(amount, 2) FROM LAST OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_lowest
FROM orders;

-- second non-null status change, skipping NULLs
SELECT
    id,
    NTH_VALUE(status, 2) IGNORE NULLS OVER (
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_status
FROM orders;

Ressalva importante para o PostgreSQL: as opcoes padrao FROM FIRST/FROM LAST e IGNORE NULLS nao sao sintaxe confiavel nem portavel do PostgreSQL. Seu comportamento habitual conta a partir do inicio do quadro e respeita os NULL. Quando voce precisa do "segundo valor nao vazio", o caminho usual e uma subconsulta: filtre antes as linhas vazias e depois aplique ROW_NUMBER ou NTH_VALUE sobre o conjunto ja limpo.

O segundo maior por grupo

A tarefa classica e "o segundo maior pedido de cada usuario". NTH_VALUE resolve em uma linha, mas cuidado com os empates: com valores de amount iguais a funcao retorna o valor da segunda linha fisica, nao o segundo valor distinto.

SELECT DISTINCT
    user_id,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_order
FROM orders;

Se o que voce quer e o segundo nivel distinto (tolerante a empates), recorra ao DENSE_RANK:

SELECT user_id, amount AS second_highest_distinct
FROM (
    SELECT
        user_id,
        amount,
        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk
    FROM orders
) s
WHERE rnk = 2;

Assim os empates sao preservados de forma honesta: todas as linhas com o valor maximo recebem o rank 1, e o proximo nivel distinto e o rank 2.

Diferencas em outros bancos

• MySQL (8.0+) tem NTH_VALUE(expr, n), mas o suporte das opcoes adicionais como FROM FIRST/LAST e IGNORE NULLS depende da versao; na pratica uma subconsulta com ROW_NUMBER costuma ser mais simples e clara. A armadilha do quadro padrao e exatamente a mesma aqui.
• ClickHouse nao tem um NTH_VALUE dedicado. O equivalente e construido com groupArray mais indexacao ou arrayElement, ou com row_number() em uma subconsulta. A logica do "segundo maior" costuma ficar assim:

-- ClickHouse: second highest per user via array indexing
SELECT
    user_id,
    arrayElement(arraySort(x -> -x, groupArray(amount)), 2) AS second_highest
FROM orders
GROUP BY user_id;

NTH_VALUE e uma ferramenta precisa para "o n-esimo valor da janela", mas vive e morre pelo quadro. Amplie o quadro de forma explicita, lembre dos duplicados, mantenha o DENSE_RANK a mao para o caso do "segundo distinto" e confira as opcoes a partir do fim e de pular NULL conforme o seu dialeto especifico.