NTH_VALUE

NTH_VALUE devuelve el valor de una expresión evaluada en la fila número N de la ventana, según el orden especificado. Mientras FIRST_VALUE siempre obtiene la posición 1 y LAST_VALUE la última, NTH_VALUE permite obtener cualquier posición intermedia: el segundo salario más alto, el tercer mejor vendedor, el quinto producto más vendido.

Es una función muy flexible que generaliza a FIRST_VALUE (equivalente a NTH_VALUE(expr, 1)) y complementa a LAST_VALUE. Sin embargo, comparte con LAST_VALUE la misma trampa del marco de ventana por defecto, así que debes prestar atención a la especificación del marco.

Sintaxis

NTH_VALUE(expresion, n) OVER (
    [PARTITION BY columna1, columna2, ...]
    ORDER BY columna1 [ASC|DESC], columna2 [ASC|DESC], ...
    [especificación_de_marco]
)

La función acepta dos argumentos obligatorios. El primero es la expresión cuyo valor quieres obtener. El segundo, n, es un entero positivo que indica la posición de la fila dentro de la ventana (empezando desde 1). Si no hay suficientes filas para alcanzar la posición N, devuelve NULL. MySQL siempre evalúa NTH_VALUE desde la primera fila de la ventana (FROM FIRST), que es el comportamiento por defecto del estándar SQL.

La trampa del marco (igual que LAST_VALUE)

Al igual que LAST_VALUE, NTH_VALUE se ve afectada por el marco de ventana por defecto cuando usas ORDER BY. Si pides la posición 3 y la fila actual es la segunda, el marco por defecto (UNBOUNDED PRECEDING a CURRENT ROW) no incluye la tercera fila, y el resultado será NULL:

-- INCORRECTO: marco por defecto puede devolver NULL inesperadamente
SELECT
    nombre,
    salario,
    NTH_VALUE(nombre, 3) OVER (
        ORDER BY salario DESC
    ) AS tercero
FROM empleados;

Las dos primeras filas muestran NULL porque cuando la ventana solo incluye hasta la fila actual (1 o 2 filas), no hay una tercera fila disponible. A partir de la tercera fila, el valor se estabiliza. La solución es la misma que con LAST_VALUE:

-- CORRECTO: marco que abarca toda la partición
SELECT
    nombre,
    salario,
    NTH_VALUE(nombre, 3) OVER (
        ORDER BY salario DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS tercero
FROM empleados;

Ahora todas las filas muestran "Ana García" como la tercera empleada con mayor salario.

Comportamiento básico

Con el marco correcto, NTH_VALUE permite obtener el valor de cualquier posición de la ventana:

SELECT
    nombre,
    departamento,
    salario,
    NTH_VALUE(nombre, 1) OVER w AS primero,
    NTH_VALUE(nombre, 2) OVER w AS segundo,
    NTH_VALUE(nombre, 3) OVER w AS tercero
FROM empleados
WINDOW w AS (
    PARTITION BY departamento
    ORDER BY salario DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY departamento, salario DESC;

Cada fila muestra los tres empleados con mayor salario de su departamento. El valor se repite en todas las filas de la misma partición, proporcionando un contexto completo junto a los datos individuales.

Caso práctico: el segundo producto más vendido

Un análisis de producto frecuente es obtener no solo el líder de cada categoría, sino también al segundo. NTH_VALUE lo hace sin subconsultas:

SELECT
    nombre,
    categoria,
    unidades_vendidas,
    NTH_VALUE(nombre, 1) OVER w AS lider_categoria,
    NTH_VALUE(unidades_vendidas, 1) OVER w AS ventas_lider,
    NTH_VALUE(nombre, 2) OVER w AS segundo_categoria,
    NTH_VALUE(unidades_vendidas, 2) OVER w AS ventas_segundo
FROM productos
WINDOW w AS (
    PARTITION BY categoria
    ORDER BY unidades_vendidas DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY categoria, unidades_vendidas DESC;

Cada producto puede ver quién es el líder y el segundo de su categoría. Esto es útil para análisis competitivo interno donde necesitas comparar no solo contra el mejor, sino contra los primeros puestos.

Caso práctico: comparar con la mediana

Si tienes un número impar de filas, puedes usar NTH_VALUE para obtener la mediana. Con 5 empleados ordenados por salario, la mediana está en la posición 3:

SELECT
    nombre,
    departamento,
    salario,
    NTH_VALUE(salario, 3) OVER (
        PARTITION BY departamento
        ORDER BY salario
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS mediana_depto,
    salario - NTH_VALUE(salario, 3) OVER (
        PARTITION BY departamento
        ORDER BY salario
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS vs_mediana
FROM empleados
WHERE departamento = 'Ventas'
ORDER BY salario;

Pedro está 12000 por debajo de la mediana, mientras que Carlos está 13000 por encima. La mediana (posición 3 de 5) es el salario de Marta: 55000.

Caso práctico: tercero en el podio por mes

En rankings deportivos o de ventas, el "podio" incluye los tres primeros. NTH_VALUE puede obtener la cifra del tercer puesto para determinar quiénes están "en el podio":

SELECT
    nombre,
    mes,
    monto_ventas,
    NTH_VALUE(monto_ventas, 3) OVER (
        PARTITION BY mes
        ORDER BY monto_ventas DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS corte_podio,
    CASE
        WHEN monto_ventas >= NTH_VALUE(monto_ventas, 3) OVER (
            PARTITION BY mes
            ORDER BY monto_ventas DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) THEN 'En el podio'
        ELSE 'Fuera del podio'
    END AS estado
FROM ventas_empleados
WHERE mes = '2024-06'
ORDER BY monto_ventas DESC;

El corte del podio es 29100 (el monto del tercer vendedor). Cualquiera con ventas iguales o superiores a esa cifra está en el podio.

Manejo de NULL

Si la posición N no existe porque hay menos de N filas en la partición, NTH_VALUE devuelve NULL:

SELECT
    nombre,
    departamento,
    salario,
    NTH_VALUE(nombre, 5) OVER (
        PARTITION BY departamento
        ORDER BY salario DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS quinto
FROM empleados;

Marketing tiene solo 3 empleados, así que no hay quinto y devuelve NULL. Ventas tiene al menos 5, así que sí devuelve un valor. Si la fila en la posición N contiene NULL en la columna referenciada, NTH_VALUE devuelve NULL porque refleja el valor real de esa posición.

Combinación con otras funciones

NTH_VALUE se combina con FIRST_VALUE y funciones de agregación para crear cuadros de referencia completos:

SELECT
    nombre,
    categoria,
    precio,
    FIRST_VALUE(precio) OVER w AS precio_top1,
    NTH_VALUE(precio, 2) OVER w AS precio_top2,
    NTH_VALUE(precio, 3) OVER w AS precio_top3,
    ROUND(AVG(precio) OVER w, 2) AS precio_medio,
    CASE
        WHEN precio >= NTH_VALUE(precio, 1) OVER w THEN 'Premium'
        WHEN precio >= COALESCE(NTH_VALUE(precio, 3) OVER w, 0) THEN 'Estándar'
        ELSE 'Económico'
    END AS segmento
FROM productos
WINDOW w AS (
    PARTITION BY categoria
    ORDER BY precio DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY categoria, precio DESC;

El COALESCE en el CASE previene errores cuando la categoría tiene menos de 3 productos, evitando comparar con NULL.

Practica con NTH_VALUE

Usa el editor para obtener el valor en la posicion N:

En el siguiente artículo veremos CUME_DIST para calcular la distribución acumulativa.