FIRST_VALUE y LAST_VALUE en PostgreSQL: primer y ultimo valor por particion y la trampa del frame

FIRST_VALUE y LAST_VALUE son funciones de ventana que devuelven el valor de la primera o la ultima fila de una ventana, sin colapsar el resultado en una sola fila. Responden a preguntas como "cual fue el primer pedido de este usuario" o "quien gana mas en este departamento", manteniendo cada fila original en su sitio.

Sintaxis y ejemplo basico

Ambas funciones operan sobre OVER (...) con un ORDER BY obligatorio dentro de la ventana; sin el, "primera" y "ultima" no significan nada. PARTITION BY define los grupos dentro de los que se calcula la ventana.

SELECT
    id,
    dept,
    name,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY dept
        ORDER BY salary DESC
    ) AS top_earner
FROM employees;

Aqui, para cada empleado, la columna top_earner contiene el nombre de la persona mejor pagada de su departamento. Algunos comportamientos que conviene conocer:

• El resultado es un escalar de una fila concreta de la ventana, no un agregado. Su tipo coincide con el del argumento.
• FIRST_VALUE funciona "como esperas" con el frame por defecto, porque el inicio de la ventana siempre esta fijado en la primera fila.
• LAST_VALUE con ese mismo frame por defecto es traicionera, como se ve abajo.

La trampa del frame de LAST_VALUE

El error mas comun: escribir LAST_VALUE y obtener la fila actual en lugar de la ultima. La causa es el frame por defecto de la ventana, que es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. La ventana crece desde el inicio de la particion hasta la fila actual, asi que su "ultima" fila es siempre la actual.

-- WRONG: returns the current row's score, not the partition's last
SELECT
    id,
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
    ) AS wrong_last
FROM scores;

Para que LAST_VALUE vea toda la particion, amplia el frame de forma explicita hasta los dos bordes de la ventana:

SELECT
    id,
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_score
FROM scores;

Trampa: FIRST_VALUE es inmune a esto porque el borde izquierdo del frame siempre es UNBOUNDED PRECEDING. Pero en cuanto anades un ORDER BY a la ventana, el frame por defecto recorta el borde derecho a CURRENT ROW, y eso es justo lo que rompe LAST_VALUE. Ante la duda, declara el frame.

Primero y ultimo en una sola consulta

A menudo necesitas ambos bordes a la vez, por ejemplo el primer y el ultimo pedido de un usuario por fecha. Para no repetir una definicion de ventana larga, extraela a una clausula WINDOW:

SELECT DISTINCT
    user_id,
    FIRST_VALUE(amount) OVER w AS first_order_amount,
    LAST_VALUE(amount)  OVER w AS last_order_amount
FROM orders
WINDOW w AS (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

DISTINCT colapsa las filas identicas de la particion en una. Fijate en que ambas llamadas comparten el mismo frame w, asi que FIRST_VALUE toma el pedido mas antiguo y LAST_VALUE el mas reciente.

Cuando algo mas simple encaja

FIRST_VALUE y LAST_VALUE brillan cuando necesitas conservar todas las filas y agregarle a cada una un valor de borde. Pero si solo quieres una fila por grupo, otras construcciones suelen ser mas concisas:

• En PostgreSQL, DISTINCT ON (user_id) ... ORDER BY user_id, created_at devuelve la primera fila de un grupo sin ninguna ventana.
• Para un agregado por grupo, basta un simple MAX(salary) / MIN(salary) con GROUP BY dept.
• NTH_VALUE(amount, 2) OVER w toma la fila N-esima de la ventana y tambien necesita un frame amplio.

Diferencias en MySQL y ClickHouse

MySQL 8+ tiene FIRST_VALUE y LAST_VALUE y se comportan de forma identica, incluida la misma trampa del frame por defecto; amplia la ventana con ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING exactamente igual. MySQL 5.7 no tiene funciones de ventana, asi que las emulas con subconsultas o GROUP_CONCAT.

-- MySQL 8: same frame trap, same fix
SELECT
    team_id,
    score,
    LAST_VALUE(score) OVER (
        PARTITION BY team_id
        ORDER BY score DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_score
FROM scores;

ClickHouse tambien las soporta, pero argMax(name, salary) y argMin(name, salary) suelen ser mas comodas: devuelven el valor de una columna en la fila con el maximo o el minimo de otra, sin frame explicito. Cuando necesitas un resultado por fila que conserve todas las filas, la forma de ventana con un frame amplio sigue siendo la mas portable.