Bloque 7 · Funciones de ventana PostgreSQL 17 northwind

LAG, LEAD, FIRST_VALUE y LAST_VALUE

1. Qué es

Cuatro funciones que traen el valor de otra fila a la fila actual. No agregan nada: copian.

Función	Trae
`lag(col, n, default)`	el valor de n filas antes (por defecto 1)
`lead(col, n, default)`	el valor de n filas después
`first_value(col)`	el valor de la primera fila del marco
`last_value(col)`	el valor de la última fila del marco

Para qué sirven, en una frase: comparar una fila con otra sin hacer un self-join.

En cross-join-y-self-join comparábamos una tabla consigo misma para poner dos filas en la misma línea. Con lag eso es una función y no un JOIN: menos código, una sola pasada, y —lo que importa— imposible equivocarse en la condición de emparejamiento.

2. Sintaxis

lag(columna)                    -- la fila anterior
lag(columna, 3)                 -- tres filas antes
lag(columna, 1, 0)              -- la anterior; si no existe, 0 en vez de NULL

Reglas:

Necesitan ORDER BY dentro de OVER. Sin orden no hay "anterior".
lag y lead ignoran el marco. ROWS/RANGE no les afecta: siempre miran a la fila n posiciones más allá.
first_value y last_value sí dependen del marco, y ahí está el error clásico del tema (sección 5).
El tercer argumento es el valor por defecto cuando no hay fila que traer. Sin él sale NULL.

3. lag: comparar con el periodo anterior

El uso número uno en cualquier informe:

SELECT date_trunc('month', o.order_date)::date AS mes,
       round(sum(od.unit_price*od.quantity*(1-od.discount)), 2) AS facturacion,
       round(lag(sum(od.unit_price*od.quantity*(1-od.discount))) OVER w, 2) AS mes_anterior,
       round(100.0 * (sum(od.unit_price*od.quantity*(1-od.discount))
             - lag(sum(od.unit_price*od.quantity*(1-od.discount))) OVER w)
             / lag(sum(od.unit_price*od.quantity*(1-od.discount))) OVER w, 1) AS var_pct
FROM orders o JOIN order_details od ON od.order_id = o.order_id
WHERE o.order_date >= '1997-01-01' AND o.order_date < '1998-01-01'
GROUP BY date_trunc('month', o.order_date)
WINDOW w AS (ORDER BY date_trunc('month', o.order_date))
ORDER BY 1;

    mes     | facturacion | mes_anterior | var_pct
------------+-------------+--------------+---------
 1997-01-01 |    61258.07 |              |
 1997-02-01 |    38483.64 |     61258.07 |   -37.2
 1997-03-01 |    38547.22 |     38483.64 |     0.2
 1997-04-01 |    53032.95 |     38547.22 |    37.6
 1997-05-01 |    53781.29 |     53032.95 |     1.4
 1997-06-01 |    36362.80 |     53781.29 |   -32.4
 1997-07-01 |    51020.86 |     36362.80 |    40.3
 1997-08-01 |    47287.67 |     51020.86 |    -7.3
 1997-09-01 |    55629.24 |     47287.67 |    17.6
 1997-10-01 |    66749.23 |     55629.24 |    20.0
 1997-11-01 |    43533.81 |     66749.23 |   -34.8
 1997-12-01 |    71398.43 |     43533.81 |    64.0

La cláusula WINDOW w AS (...) evita escribir la misma ventana tres veces. Con lag es casi obligatoria: la expresión ya es larga sin repetirla.

Y ahora lee la columna var_pct, porque es una lección de análisis más que de SQL. Salta entre −37,2% y +64,0%. Seis meses en negativo, seis en positivo. Si esta tabla llega a una reunión mensual, cada mes hay una crisis o una celebración, y ninguna de las dos es real: en el-marco-rows-vs-range vimos que la media móvil de esta misma serie sube casi sin interrupción.

La variación mes contra mes es la métrica más pedida y la más ruidosa que existe. Cuando la entregues, va con la media móvil al lado o no va.

La primera fila es NULL y debe serlo. No hay diciembre de 1996 dentro del filtro. Rellenarlo con 0 diría "no creció", y lo cierto es que no se sabe — la distinción de null-y-la-logica-de-tres-valores.

3.1 El desplazamiento: comparar contra el año anterior

lag(col, 12) sobre una serie mensual es el año contra año:

SELECT date_trunc('month', o.order_date)::date AS mes, count(*) AS pedidos,
       lag(count(*), 12) OVER w AS mismo_mes_anio_anterior,
       round(100.0 * (count(*) - lag(count(*), 12) OVER w)
             / lag(count(*), 12) OVER w, 1) AS var_yoy
FROM orders o GROUP BY date_trunc('month', o.order_date)
WINDOW w AS (ORDER BY date_trunc('month', o.order_date))
ORDER BY 1 OFFSET 12;

    mes     | pedidos | mismo_mes_anio_anterior | var_yoy
------------+---------+-------------------------+---------
 1997-07-01 |      33 |                      22 |    50.0
 1997-08-01 |      33 |                      25 |    32.0
 1997-09-01 |      37 |                      23 |    60.9
 1997-10-01 |      38 |                      26 |    46.2
 1997-11-01 |      34 |                      25 |    36.0
 1997-12-01 |      48 |                      31 |    54.8
 1998-01-01 |      55 |                      33 |    66.7
 1998-02-01 |      54 |                      29 |    86.2
 1998-03-01 |      73 |                      30 |   143.3
 1998-04-01 |      74 |                      31 |   138.7
 1998-05-01 |      14 |                      32 |   -56.3

Esta es la tabla que había que enseñar desde el principio. Todos los meses crecen contra su equivalente del año anterior, del +32% al +143%, y la aceleración es continua. El año contra año elimina la estacionalidad, que es exactamente el ruido que hacía ilegible la tabla anterior.

Con una advertencia que ya conoces: el −56,3% de mayo de 1998 son seis días contra un mes completo. Aparece por tercera vez en este material y sigue siendo la misma trampa.

lag(col, 12) cuenta doce filas, no doce meses. Aquí coinciden porque orders tiene los 23 meses seguidos, sin huecos — lo comprobamos en cte-recursivas. Si faltara un mes, lag(12) compararía con el mes equivocado sin dar ningún error. Con series que pueden tener huecos, primero se genera el calendario completo.

3.2 El hueco entre dos filas

lag sobre fechas da la distancia entre eventos:

SELECT customer_id, order_id, order_date::date,
       lag(order_date::date) OVER w AS anterior,
       order_date::date - lag(order_date::date) OVER w AS dias
FROM orders WHERE customer_id = 'QUICK'
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date, order_id)
ORDER BY order_date LIMIT 8;

 customer_id | order_id | order_date |  anterior  | dias
-------------+----------+------------+------------+------
 QUICK       |    10273 | 1996-08-05 |            |
 QUICK       |    10285 | 1996-08-20 | 1996-08-05 |   15
 QUICK       |    10286 | 1996-08-21 | 1996-08-20 |    1
 QUICK       |    10313 | 1996-09-24 | 1996-08-21 |   34
 QUICK       |    10345 | 1996-11-04 | 1996-09-24 |   41
 QUICK       |    10361 | 1996-11-22 | 1996-11-04 |   18
 QUICK       |    10418 | 1997-01-17 | 1996-11-22 |   56
 QUICK       |    10451 | 1997-02-19 | 1997-01-17 |   33

El PARTITION BY customer_id es lo que impide que el primer pedido de un cliente se compare con el último del anterior. Sin él, la primera fila de cada cliente traería un valor de otro cliente — y no daría error.

Este patrón es la base de las casuísticas de islas y brechas (islas-y-brechas): una vez tienes la distancia entre eventos consecutivos, detectar rachas y huecos es filtrar por esa distancia.

4. lead: mirar hacia adelante

Lo mismo en la otra dirección. Aquí, el salto de precio hasta el siguiente producto de la categoría:

SELECT p.product_name, p.unit_price,
       lead(p.product_name) OVER w AS siguiente,
       lead(p.unit_price)   OVER w AS precio_siguiente,
       p.unit_price - lead(p.unit_price) OVER w AS salto
FROM products p JOIN categories c USING (category_id)
WHERE c.category_name = 'Meat/Poultry'
WINDOW w AS (PARTITION BY p.category_id ORDER BY p.unit_price DESC, p.product_id)
ORDER BY p.unit_price DESC;

      product_name       | unit_price |    siguiente    | precio_siguiente | salto
-------------------------+------------+-----------------+------------------+-------
 Thüringer Rostbratwurst |     123.79 | Mishi Kobe Niku |            97.00 | 26.79
 Mishi Kobe Niku         |      97.00 | Alice Mutton    |            39.00 | 58.00
 Alice Mutton            |      39.00 | Perth Pasties   |            32.80 |  6.20
 Perth Pasties           |      32.80 | Pâté chinois    |            24.00 |  8.80
 Pâté chinois            |      24.00 | Tourtière       |             7.45 | 16.55
 Tourtière               |       7.45 |                 |                  |

El salto más grande no está arriba: está entre el segundo y el tercero. De 97,00 a 39,00 hay 58 euros de hueco. Meat/Poultry tiene dos productos premium y cuatro normales, y la frontera está ahí, no donde la pondría una media.

Detectar huecos en una serie ordenada es un uso de lead que se subestima. Sirve para encontrar tramos vacíos de precio, cortes naturales en una distribución, o números de factura que faltan.

La última fila es NULL, porque no hay siguiente. lead(col, 1, 0) pondría un 0 — pero cuidado: aquí un 0 diría "el siguiente producto cuesta cero", que es falso. El valor por defecto solo se pone cuando tiene sentido de negocio.

5. first_value y last_value: la trampa del marco

SELECT p.product_name, p.unit_price,
       first_value(p.product_name) OVER w AS mas_caro,
       last_value(p.product_name)  OVER w AS ultimo_visto
FROM products p WHERE p.category_id = 7
WINDOW w AS (ORDER BY p.unit_price DESC)
ORDER BY p.unit_price DESC;

          product_name           | unit_price |       mas_caro        |          ultimo_visto
---------------------------------+------------+-----------------------+---------------------------------
 Manjimup Dried Apples           |      53.00 | Manjimup Dried Apples | Manjimup Dried Apples
 Rössle Sauerkraut               |      45.60 | Manjimup Dried Apples | Rössle Sauerkraut
 Uncle Bob's Organic Dried Pears |      30.00 | Manjimup Dried Apples | Uncle Bob's Organic Dried Pears
 Tofu                            |      23.25 | Manjimup Dried Apples | Tofu
 Longlife Tofu                   |      10.00 | Manjimup Dried Apples | Longlife Tofu

first_value funciona. last_value devuelve la propia fila, que es inútil.

No es un fallo: es el marco por defecto. Con ORDER BY y sin marco explícito, la ventana llega hasta la fila actual — la regla de el-marco-rows-vs-range. Así que:

La primera fila del marco siempre es la primera de la partición → first_value acierta.
La última fila del marco es siempre la fila actual → last_value se devuelve a sí misma.

El arreglo es extender el marco hasta el final:

WINDOW w AS (ORDER BY p.unit_price DESC
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

          product_name           | unit_price |       mas_caro        |  mas_barato
---------------------------------+------------+-----------------------+---------------
 Manjimup Dried Apples           |      53.00 | Manjimup Dried Apples | Longlife Tofu
 Rössle Sauerkraut               |      45.60 | Manjimup Dried Apples | Longlife Tofu
 Uncle Bob's Organic Dried Pears |      30.00 | Manjimup Dried Apples | Longlife Tofu
 Tofu                            |      23.25 | Manjimup Dried Apples | Longlife Tofu
 Longlife Tofu                   |      10.00 | Manjimup Dried Apples | Longlife Tofu

Ahora sí. Y hay una alternativa más limpia que recordar el marco: invertir el orden y usar first_value.

first_value(product_name) OVER (ORDER BY unit_price ASC)   -- el más barato

last_value casi nunca es la mejor opción. Si te sorprendes escribiéndola, comprueba si first_value con el orden al revés dice lo mismo — suele decirlo, y sin depender de una cláusula que es fácil olvidar.

6. Errores comunes

last_value sin marco explícito. La sección 5. Devuelve la fila actual y no da ningún error: el informe sale con una columna que parece un dato y es un espejo.
Olvidar PARTITION BY al comparar dentro de grupos. El primer pedido de un cliente traería el último del cliente anterior. Silencioso y grave.
Confiar en lag(col, 12) sobre una serie con huecos. Cuenta filas, no meses. Si falta un mes, compara contra el equivocado.
Rellenar el NULL de la primera fila con 0 por costumbre. Un 0 en una columna de variación se lee como "sin cambio". Deja el NULL salvo que el 0 sea cierto.
Dividir entre el lag sin protegerse del cero. ERROR: division by zero Si un periodo puede valer 0, la variación porcentual no existe. nullif(lag(...), 0) convierte el error en NULL, que es la respuesta honesta.
Poner lag en el WHERE. Como toda ventana: no se puede. CTE y filtro fuera.
Usar lag cuando lo que quieres es el acumulado. lag trae una fila anterior; para todas las anteriores es sum() OVER (ORDER BY ...).

7. Diferencias con SQL Server

	PostgreSQL	SQL Server
`LAG` / `LEAD`	igual	igual (2012+)
Tercer argumento (valor por defecto)	sí	sí
`FIRST_VALUE` / `LAST_VALUE`	igual, misma trampa del marco	igual, misma trampa
`IGNORE NULLS`	no (hasta PG 17)	sí (2022+)
Cláusula `WINDOW` con nombre	sí	2022+

Portan sin cambios, y la trampa de last_value es idéntica en los dos motores — no es una peculiaridad de PostgreSQL, es lo que dice el estándar.

La diferencia que puede morder es IGNORE NULLS. Sirve para arrastrar el último valor no nulo hacia abajo, que es el patrón típico de rellenar huecos en una serie. PostgreSQL 17 no lo tiene; se resuelve con un truco de dos pasos usando count(col) OVER (...) para agrupar los tramos.

8. Ejercicios

La variación mensual del número de pedidos, con la columna del mes anterior al lado.
Los días entre pedidos consecutivos de cada cliente. Después, el mayor hueco de cada uno y su hueco medio.
Año contra año en pedidos, con lag(..., 12).
Para cada producto de Meat/Poultry, el siguiente más barato de su categoría y el salto de precio.
Esta consulta devuelve una columna inútil. Arréglala de dos formas distintas: sql SELECT product_name, last_value(product_name) OVER (ORDER BY unit_price DESC) FROM products WHERE category_id = 7;

9. Pregunta de negocio

Comercial quiere anticiparse: no saber quién dejó de comprar, sino quién está dejando de comprar. Detectar clientes cuyo silencio actual se sale de lo normal para ellos.

"Lo normal para ellos" es la clave. Un cliente que compra cada dos meses y lleva tres sin comprar es más preocupante que uno que compra cada año y lleva seis meses.

? RespuestaRespóndela antes de abrir

WITH huecos AS (
    SELECT customer_id, order_date::date AS dia,
           order_date::date - lag(order_date::date)
               OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS dias
    FROM orders
),
ritmo AS (
    SELECT customer_id,
           round(avg(dias), 1)  AS cadencia,
           count(dias) + 1      AS pedidos,
           max(dia)             AS ultimo
    FROM huecos
    GROUP BY customer_id
    HAVING count(dias) >= 4          -- al menos 5 pedidos: sin historial no hay "normal"
)
SELECT customer_id, pedidos, cadencia, ultimo,
       (DATE '1998-05-06' - ultimo)               AS dias_desde_ultimo,
       round((DATE '1998-05-06' - ultimo) / cadencia, 1) AS veces_su_cadencia
FROM ritmo ORDER BY veces_su_cadencia DESC LIMIT 10;

 customer_id | pedidos | cadencia |   ultimo   | dias_desde_ultimo | veces_su_cadencia
-------------+---------+----------+------------+-------------------+-------------------
 MEREP       |      13 |     31.5 | 1997-10-30 |               188 |               6.0
 HUNGC       |       5 |     69.0 | 1997-09-08 |               240 |               3.5
 FAMIA       |       7 |     59.8 | 1997-10-31 |               187 |               3.1
 BLONP       |      11 |     53.6 | 1998-01-12 |               114 |               2.1
 BERGS       |      18 |     33.5 | 1998-03-04 |                63 |               1.9
 SEVES       |       9 |     55.0 | 1998-02-04 |                91 |               1.7
 VICTE       |      10 |     62.7 | 1998-01-23 |               103 |               1.6
 FOLIG       |       5 |     87.0 | 1997-12-22 |               135 |               1.6
 ANTON       |       7 |     71.2 | 1998-01-28 |                98 |               1.4
 VINET       |       5 |    124.0 | 1997-11-12 |               175 |               1.4

La columna que responde a la pregunta es la última, no la penúltima.

MEREP es el caso urgente: 13 pedidos, uno cada 31,5 días de media, y lleva 188 sin comprar — seis veces su propio ritmo. Un cliente que compraba todos los meses lleva medio año callado. Eso no es estacionalidad, es una relación que se rompió.
Compáralo con HUNGC, que lleva más tiempo fuera —240 días— y preocupa menos. Su cadencia era de 69 días, así que lleva 3,5 veces su ritmo. Y con 5 pedidos, su "normal" está peor medido.
BERGS es el hallazgo que una lista por antigüedad entierra. Solo lleva 63 días sin comprar: en una lista ordenada por fecha ocupa el puesto 20 de 89, a media tabla, donde nadie mira. Aquí es el quinto. Y merece serlo: compraba cada 33,5 días con 18 pedidos —el cliente más regular de toda la lista— y ya lleva casi el doble de su ritmo. Es la señal más temprana de la tabla, y la única que todavía se puede atender antes de perderlo.

Y el contraste con la ficha de lateral es la lección del tema. Allí, ordenando por días desde la última compra, salían primero PARIS y FISSA, que nunca compraron, y luego CENTC con 657 días. Ninguno de los tres es un cliente que se está enfriando — dos no son clientes y el tercero nunca lo fue. Ordenar por antigüedad absoluta responde a "quién lleva más tiempo sin comprar", que no es la pregunta.

Normalizar por la cadencia propia de cada cliente convierte una lista de fechas en una lista de acciones. Y todo el cálculo sale de un lag sobre order_date.

Dos honestidades que van con la entrega:

El HAVING count(dias) >= 4 deja fuera a los clientes con menos de 5 pedidos, y eso incluye a algunos que sí se están perdiendo. Es deliberado: con dos o tres pedidos, la "cadencia media" es un número sin significado. Esos clientes necesitan otra lista, no esta.
La media aritmética de los huecos supone un ritmo estable. Un cliente que compraba cada 15 días y pasó a cada 90 tiene una media que no describe ninguna de las dos fases. Detectar ese cambio de régimen es un paso más, y es exactamente lo que hace periodo-contra-periodo.

✓ SolucionesResuélvelos antes de abrir

SELECT date_trunc('month', order_date)::date AS mes, count(*) AS pedidos,
       lag(count(*)) OVER w AS mes_anterior,
       count(*) - lag(count(*)) OVER w AS diferencia
FROM orders GROUP BY date_trunc('month', order_date)
WINDOW w AS (ORDER BY date_trunc('month', order_date)) ORDER BY 1;

lag(count(*)) — la ventana opera sobre el resultado del GROUP BY, como en todo el bloque.

WITH h AS (
    SELECT customer_id,
           order_date::date - lag(order_date::date)
               OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS dias
    FROM orders
)
SELECT customer_id, max(dias) AS mayor_hueco, round(avg(dias), 1) AS hueco_medio,
       count(dias) AS intervalos
FROM h WHERE dias IS NOT NULL
GROUP BY customer_id ORDER BY mayor_hueco DESC LIMIT 6;

 customer_id | mayor_hueco | hueco_medio | intervalos
-------------+-------------+-------------+------------
 ROMEY       |         532 |       150.8 |          4
 GROSR       |         506 |       506.0 |          1
 BOLID       |         445 |       265.0 |          2
 VINET       |         435 |       124.0 |          4
 THEBI       |         392 |       186.0 |          3
 PRINI       |         387 |       133.0 |          4

count(dias) es el número de intervalos, uno menos que el de pedidos, porque el primer pedido no tiene anterior. El WHERE dias IS NOT NULL lo dejaría fuera igualmente, pero conviene tenerlo claro al interpretar la tabla.

GROSR tiene un solo intervalo de 506 días, así que su "hueco medio" es ese mismo número: con dos pedidos no hay media que valga. Cualquier estadística sobre un cliente con dos pedidos es el dato, no una tendencia — de ahí el HAVING de la pregunta de negocio.

3. En la sección 3. El resultado se lee al revés que la variación mensual: once meses seguidos de crecimiento contra el año anterior, del +32,0% al +143,3%, y solo el mes incompleto en negativo.

4. En la sección 4. El salto mayor —58,00 euros— está entre el segundo y el tercer producto, no entre el primero y el segundo. Es donde se parte la categoría en gama alta y gama normal.

5. Las dos formas:

-- (a) extender el marco
SELECT product_name,
       last_value(product_name) OVER (ORDER BY unit_price DESC
                                      ROWS BETWEEN UNBOUNDED PRECEDING
                                               AND UNBOUNDED FOLLOWING) AS mas_barato
FROM products WHERE category_id = 7;

-- (b) invertir el orden y usar first_value
SELECT product_name,
       first_value(product_name) OVER (ORDER BY unit_price ASC) AS mas_barato
FROM products WHERE category_id = 7;

Las dos dan Longlife Tofu en las cinco filas. La (b) es preferible: no depende de recordar una cláusula de marco, y si alguien la lee dentro de seis meses entiende qué hace sin pensar.