NTH_VALUE en PostgreSQL: el n-esimo valor de la ventana y el segundo maximo por grupo

NTH_VALUE(expr, n) es una funcion de ventana que devuelve expr de la n-esima fila del marco de la ventana. Suena trivial, pero es justo aqui donde la gente se quema: por defecto el marco se corta en la fila actual, y "el segundo valor mas alto del grupo" se convierte en NULL sin avisar.

Que calcula NTH_VALUE

La funcion mira el marco actual de la ventana, cuenta hasta la n-esima fila (empezando en uno) y extrae expr de ella. Si no existe una fila con esa posicion en el marco, el resultado es 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;

Detalles clave:

• n es la posicion dentro del marco, no dentro de toda la particion; el marco lo decide todo.
• La numeracion empieza en 1: NTH_VALUE(x, 1) equivale a FIRST_VALUE(x).
• n debe ser un entero positivo; puede ser una expresion, pero no puede referirse a columnas de la fila.

Para tareas como "el segundo mas alto" casi siempre necesitas un marco que abarque toda la particion, como en el ejemplo anterior.

La trampa del marco: la misma historia que LAST_VALUE

En cuanto una ventana recibe un ORDER BY, su marco por defecto pasa a ser RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Asi, desde la primera fila de una particion el "segundo mas alto" aun no es visible: el marco contiene una sola fila y NTH_VALUE(amount, 2) devuelve 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;

El arreglo es el mismo que para LAST_VALUE: amplia el marco de forma explicita a toda la particion.

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

Trampa: FIRST_VALUE solo "funciona bien" con el marco por defecto por casualidad, porque la primera fila siempre esta en el marco. NTH_VALUE y LAST_VALUE son sensibles al marco, asi que casi siempre escribe de forma explicita ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

FROM FIRST / FROM LAST e IGNORE NULLS

El SQL estandar describe dos ideas utiles: contar la n-esima fila desde el final del marco con FROM LAST, y saltar los valores vacios con IGNORE NULLS. Conviene reconocerlas porque aparecen en la documentacion de varios motores y en SQL portable.

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

Advertencia importante para PostgreSQL: las opciones estandar FROM FIRST/FROM LAST e IGNORE NULLS no son sintaxis fiable ni portable de PostgreSQL. Su comportamiento habitual cuenta desde el inicio del marco y respeta los NULL. Cuando necesitas "el segundo valor no vacio", lo normal es montar una subconsulta: filtra antes las filas vacias y luego aplica ROW_NUMBER o NTH_VALUE sobre el conjunto ya limpio.

El segundo maximo por grupo

La tarea clasica es "el segundo pedido mas grande de cada usuario". NTH_VALUE lo resuelve en una linea, pero cuidado con los empates: con valores de amount iguales la funcion devuelve el valor de la segunda fila fisica, no el 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;

Si lo que quieres es el segundo nivel distinto (tolerante a empates), recurre a 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;

Asi se respetan los empates de forma honesta: todas las filas con el importe maximo reciben el rango 1, y el siguiente nivel distinto es el rango 2.

Diferencias en otras bases de datos

• MySQL (8.0+) tiene NTH_VALUE(expr, n), pero el soporte de las opciones adicionales como FROM FIRST/LAST e IGNORE NULLS depende de la version; en la practica una subconsulta con ROW_NUMBER suele ser mas simple y clara. La trampa del marco por defecto es exactamente la misma aqui.
• ClickHouse no tiene un NTH_VALUE dedicado. El equivalente se construye con groupArray mas indexacion o arrayElement, o con row_number() en una subconsulta. La logica del "segundo mas alto" suele verse asi:

-- 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 es una herramienta precisa para "el n-esimo valor de la ventana", pero vive y muere por el marco. Amplia el marco de forma explicita, recuerda los duplicados, manten DENSE_RANK a mano para el caso del "segundo distinto" y comprueba las opciones desde el final y de salto de NULL segun tu dialecto concreto.