Curso de MariaDB / Calcular acumulados y comparar filas con LAG y LEAD

SUM como función de ventana calcula un total acumulado conservando las filas; LAG y LEAD consultan valores de la fila anterior y de la siguiente según el orden elegido. Defina un orden sin ambigüedades y el marco de acumulación para que las fechas iguales no dejen indefinido cómo avanza el cálculo.

Definir la secuencia y la unidad que se acumula

Acumular pedido a pedido no equivale a acumular por día. Este ejemplo suma primero cantidad por precio de compra de cada línea de un pedido pagado; después acumula los importes por fecha e ID del pedido. Solo mide los productos de los pedidos definidos como pagados: el envío, las cancelaciones y los reembolsos requieren reglas propias y no deben incorporarse sin decidir qué representa el indicador.

La comparación de funciones de ventana y clasificación explica cómo conservar las filas de entrada. La guía de diferencias entre fechas permite distinguir una fila anterior de un intervalo de tiempo transcurrido.

Cálculo Alcance del ejemplo Límite
SUM con OVER Desde la primera fila hasta la actual El primer acumulado es su propio importe
LAG Una fila anterior en el orden elegido NULL si no existe la anterior
LEAD Una fila siguiente en ese orden NULL si no existe la siguiente

Preparar una secuencia de pedidos definida

Conéctese al servidor de prácticas como course_user y seleccione shopcourse. Puede guardar los bloques en un archivo UTF-8 y ejecutarlo dentro del cliente con SOURCE C:/mariadb-lab/lesson.sql;, sustituyendo la ruta por la real. Consulte la guía del cliente y los archivos SQL para preparar la conexión. El SQL se escribe dentro del cliente MariaDB, no en la consola del sistema operativo.

Compruebe SELECT VERSION(), DATABASE(); y SHOW TABLES; antes de crear c33_customers, c33_products, c33_orders y c33_items. Los nombres son exclusivos de este ejercicio. Si ya existen, inspeccione sus definiciones y registros y confirme que son objetos prescindibles de prácticas antes de eliminarlos; no sustituya tablas de una aplicación.

USE shopcourse;
CREATE TABLE c33_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c33_order_customer FOREIGN KEY (customer_id)
  REFERENCES c33_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c33_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c33_item_order FOREIGN KEY (order_id)
  REFERENCES c33_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c33_item_product FOREIGN KEY (product_id)
  REFERENCES c33_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c33_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c33_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c33_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c33_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

El SQL conserva los nombres y las ciudades originales en coreano. Los productos 1–4 son un cuaderno, un bolígrafo, una taza y una lámpara. Los clientes 1 y 3 viven en Seúl; el 2 en Busan y el 4 en Daejeon. Mantenga los literales UTF-8 para reproducir los resultados.

La secuencia pagada contiene el pedido 101 del 10 de enero, el 102 del 20 de enero y el 103 del 5 de febrero. order_id desempata las fechas iguales y hace que la posición sea reproducible. Es una regla de ordenación del informe; no implica que los identificadores siempre describan el orden cronológico real.

Acumular con un marco ROWS explícito

WITH order_totals AS (
 SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
 FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
 WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
 SUM(goods_total) OVER(ORDER BY ordered_at,order_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
 LAG(goods_total) OVER(ORDER BY ordered_at,order_id) AS previous_total,
 LEAD(goods_total) OVER(ORDER BY ordered_at,order_id) AS next_total
FROM order_totals ORDER BY ordered_at,order_id;

La CTE order_totals produce una fila por pedido pagado que tiene líneas. SUM acumula esos importes sin reducir el resultado a una sola fila. Si un pedido pagado no tiene líneas, el JOIN interno de este ejemplo lo excluye: incluirlo con importe cero exigiría adaptar la consulta.

Pedido Importe Acumulado Anterior Siguiente
101 11500.00 11500.00 NULL 8000.00
102 8000.00 19500.00 11500.00 19500.00
103 19500.00 39000.00 8000.00 NULL

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW incluye explícitamente las filas desde la primera hasta la actual en la secuencia. No deje el marco implícito si necesita expresar un avance fila a fila. ROWS y RANGE pueden tratar de forma distinta los empates en los valores de ordenación; el ID único de este ejemplo define además el orden dentro de fechas iguales.

La consulta independiente siguiente permite comparar marcos sin modificar ninguna tabla. Los pedidos de muestra 501 y 502 comparten la fecha y hora, pero sus importes son 10 y 20.

WITH sample AS (
 SELECT 501 AS order_id,CAST('2026-01-10 10:00:00' AS DATETIME) AS ordered_at,10 AS amount
 UNION ALL SELECT 502,'2026-01-10 10:00:00',20
 UNION ALL SELECT 503,'2026-01-11 10:00:00',5
)
SELECT order_id,amount,
 SUM(amount) OVER (ORDER BY ordered_at,order_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_unique,
 SUM(amount) OVER (ORDER BY ordered_at,order_id
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_unique,
 SUM(amount) OVER (ORDER BY ordered_at
   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_time
FROM sample ORDER BY ordered_at,order_id;
order_id amount rows_unique range_unique range_time
501 10 10 10 30
502 20 30 30 30
503 5 35 35 35

rows_unique y range_unique tienen el mismo orden único por fecha e ID; solo cambia ROWS por RANGE y ambos producen 10, 30 y 35. En range_time se elimina únicamente el ID del orden de RANGE: las dos primeras filas pasan a compartir los mismos valores de ordenación y acumulan juntas, con 30, 30 y 35. Usar ROWS sin un desempate único tampoco definiría cuál de los dos pedidos de la misma hora avanza primero.

Interpretar filas vecinas, no días ausentes

LAG consulta la fila anterior en la secuencia elegida, no necesariamente el día anterior del calendario. En este ejemplo, la fila anterior al 5 de febrero corresponde al 20 de enero. La función no genera filas para los días sin pedidos. Una comparación diaria necesita primero una agregación por día y una decisión sobre las fechas sin observaciones.

Restar el importe anterior al actual expresa la diferencia entre pedidos consecutivos: en estas filas sería NULL, −3500.00 y 11500.00. El primer valor es NULL porque no hay predecesor. Convertirlo en cero fija una base de comparación; no es solo un cambio visual. Una observación ausente y un importe medido de cero representan información diferente.

Las referencias oficiales de LAG y LEAD explican el desplazamiento entre filas; en las llamadas del ejemplo se utiliza la fila vecina, con desplazamiento predeterminado de uno.

Decidir si el filtro se aplica antes o después del acumulado

Filtrar febrero antes de calcular la ventana inicia el acumulado con los pedidos de febrero. Si necesita mostrar febrero manteniendo lo acumulado desde enero, calcule primero la ventana dentro de una CTE y aplique después el filtro de presentación. Ambas consultas son válidas, pero responden a preguntas distintas.

La primera consulta siguiente acumula todos los pedidos pagados y después muestra febrero. La segunda aplica la misma condición de fechas antes del cálculo de ventana. Ambas usan las tablas c33_ existentes y no modifican datos.

WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
), running AS (
 SELECT order_id,ordered_at,goods_total,
        SUM(goods_total) OVER (ORDER BY ordered_at,order_id
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
 FROM order_totals
)
SELECT order_id,goods_total,running_total FROM running
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;
WITH order_totals AS (
SELECT o.order_id,o.ordered_at,SUM(i.quantity*i.unit_price) AS goods_total
FROM c33_orders o JOIN c33_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.ordered_at
)
SELECT order_id,goods_total,
       SUM(goods_total) OVER (ORDER BY ordered_at,order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM order_totals
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
ORDER BY ordered_at,order_id;
Posición del filtro order_id goods_total running_total
Después de acumular 103 19500.00 39000.00
Antes de acumular 103 19500.00 19500.00

Las dos muestran el pedido 103, pero el punto de inicio del acumulado es diferente. Elija entre un acumulado de todo el período y uno que se reinicia dentro del mes mostrado. Además, si no resume las líneas primero, el acumulado avanza por línea y no por pedido. Al definir el indicador, anote su unidad, los estados incluidos, el orden, el marco y la etapa del filtro.

Verificar los límites y limpiar las prácticas

Compruebe a mano los acumulados: 11500, 19500 y 39000. Revise el NULL del primer LAG y del último LEAD y compare los casos de fechas iguales con la muestra independiente. Mantenga un ORDER BY final explícito, sin depender del orden que un índice pueda producir de forma incidental. La guía oficial de funciones de ventana amplía la explicación de los marcos y del momento del filtrado.

Antes de repetir la preparación, revise la base seleccionada, las definiciones y los registros; respalde lo que deba conservar. Si los siguientes objetos pertenecen solo a este ejercicio y son prescindibles, elimínelos en este orden, primero las tablas hijas y después las referenciadas. DROP TABLE borra estructuras y datos y no se revierte con un ROLLBACK ordinario; separe esta limpieza de cualquier prueba de transacciones.

DROP TABLE shopcourse.c33_items;
DROP TABLE shopcourse.c33_orders;
DROP TABLE shopcourse.c33_products;
DROP TABLE shopcourse.c33_customers;