Curso de MariaDB / Calcular totais acumulados e comparar linhas com LAG e LEAD
SUM como função de janela calcula um total acumulado preservando as linhas; LAG e LEAD consultam valores da linha anterior e seguinte na ordem escolhida. Defina uma ordem sem ambiguidades e o quadro de acumulação para que datas iguais não deixem indefinido como o cálculo avança.
Definir a sequência e a unidade acumulada
Acumular pedido por pedido difere de acumular por dia. Este exemplo soma primeiro quantidade vezes preço de compra de cada item de um pedido pago; depois acumula os valores por data e ID do pedido. Ele mede somente mercadorias dos pedidos definidos como pagos: frete, cancelamentos e reembolsos exigem regras próprias, conforme o significado do indicador.
A comparação de funções de janela e classificação explica como conservar as linhas de entrada. O guia de diferenças entre datas ajuda a distinguir uma linha anterior de um intervalo de tempo decorrido.
| Cálculo | Alcance do exemplo | Limite |
|---|---|---|
| SUM com OVER | Da primeira linha até a atual | O primeiro acumulado é o próprio valor |
| LAG | Uma linha anterior na ordem escolhida | NULL se a anterior não existir |
| LEAD | Uma linha seguinte nessa ordem | NULL se a seguinte não existir |
Preparar uma sequência definida de pedidos
Conecte ao servidor de prática como course_user e selecione shopcourse. Salve os blocos em um arquivo UTF-8 e execute dentro do cliente SOURCE C:/mariadb-lab/lesson.sql;, substituindo o caminho pelo real. Consulte o guia do cliente e dos arquivos SQL para preparar a conexão. Introduza o SQL dentro do cliente MariaDB.
Confira SELECT VERSION(), DATABASE(); e SHOW TABLES; antes de criar c33_customers, c33_products, c33_orders e c33_items. Os nomes são exclusivos deste exercício. Se já existirem, examine definições e registros e confirme que são objetos de prática prescindíveis antes de removê-los; preserve tabelas de aplicações.
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);
O SQL mantém os nomes e cidades originais em coreano. Os produtos 1–4 são caderno, caneta, caneca e luminária. Os clientes 1 e 3 vivem em Seul; o 2, em Busan; e o 4, em Daejeon. Conserve os literais UTF-8 para reproduzir os resultados.
A sequência paga contém o pedido 101 de 10 de janeiro, o 102 de 20 de janeiro e o 103 de 5 de fevereiro. order_id desempata datas iguais e torna a posição reproduzível. É uma regra de ordenação do relatório; não implica que identificadores sempre descrevam a ordem cronológica real.
Acumular com um quadro 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;
A CTE order_totals produz uma linha por pedido pago que tem itens. SUM acumula esses valores sem reduzir o resultado a uma única linha. O JOIN interno deste exemplo exclui um pedido pago sem itens; incluí-lo com valor zero exigiria adaptar a consulta.
| Pedido | Valor | Acumulado | Anterior | Seguinte |
|---|---|---|---|---|
| 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 inclui explicitamente as linhas da primeira até a atual na sequência. Declare o quadro quando precisar expressar um avanço por linha. ROWS e RANGE podem tratar diferentemente os empates nos valores de ordenação; o ID único deste exemplo também define a ordem dentro de datas iguais.
A consulta independente seguinte permite comparar quadros sem modificar tabelas. Os pedidos de amostra 501 e 502 compartilham data e hora, mas seus valores são 10 e 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 e range_unique têm a mesma ordem única por data e ID; somente ROWS muda para RANGE e ambos produzem 10, 30 e 35. Em range_time, somente o ID é removido da ordenação de RANGE: as duas primeiras linhas compartilham os valores de ordenação e acumulam juntas, com 30, 30 e 35. Usar ROWS sem desempate único também não definiria qual dos pedidos da mesma hora avança primeiro.
Interpretar linhas vizinhas, não dias ausentes
LAG consulta a linha anterior na sequência escolhida, não necessariamente o dia anterior do calendário. Neste exemplo, a linha anterior a 5 de fevereiro corresponde a 20 de janeiro. A função não cria linhas para os dias sem pedidos. Uma comparação diária exige primeiro agregação por dia e uma decisão sobre datas sem observações.
Subtrair o valor anterior do atual expressa a diferença entre pedidos consecutivos: nestas linhas, seria NULL, −3500.00 e 11500.00. O primeiro valor é NULL porque não existe antecessor. Convertê-lo para zero define uma base de comparação; não é apenas uma mudança visual. Uma observação ausente e um valor medido de zero representam informações diferentes.
As referências oficiais de LAG e LEAD explicam o deslocamento entre linhas; as chamadas deste exemplo usam a linha vizinha, com deslocamento padrão de um.
Escolher se o filtro vem antes ou depois do acumulado
Filtrar fevereiro antes de calcular a janela inicia o acumulado com os pedidos de fevereiro. Para mostrar fevereiro mantendo o acumulado desde janeiro, calcule primeiro a janela dentro de uma CTE e aplique depois o filtro de apresentação. Ambas as consultas são válidas, mas respondem a perguntas diferentes.
A primeira consulta seguinte acumula todos os pedidos pagos e depois mostra fevereiro. A segunda aplica a mesma condição de datas antes do cálculo da janela. Ambas usam as tabelas c33_ existentes e não modificam dados.
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;
| Posição do filtro | order_id | goods_total | running_total |
|---|---|---|---|
| Depois de acumular | 103 | 19500.00 | 39000.00 |
| Antes de acumular | 103 | 19500.00 | 19500.00 |
As duas mostram o pedido 103, mas o ponto inicial do acumulado difere. Escolha entre um acumulado de todo o período e um que recomeça dentro do mês mostrado. Além disso, se não resumir os itens primeiro, o acumulado avança por item, não por pedido. Ao definir o indicador, registre unidade, estados incluídos, ordenação, quadro e etapa do filtro.
Verificar os limites e limpar a prática
Confira à mão os acumulados: 11500, 19500 e 39000. Examine o NULL do primeiro LAG e do último LEAD e compare datas iguais com a amostra independente. Mantenha um ORDER BY final explícito, independentemente da sequência incidental de um índice. A referência oficial dos quadros de janela detalha os limites e o tratamento de linhas empatadas.
Antes de repetir a preparação, examine banco selecionado, definições e registros; copie o que precisar conservar. Se os objetos seguintes pertencerem somente a este exercício e forem prescindíveis, remova nesta ordem, primeiro as tabelas filhas e depois as referenciadas. DROP TABLE remove estrutura e dados e não é desfeito por um ROLLBACK comum; separe a limpeza dos testes de transações.
DROP TABLE shopcourse.c33_items;
DROP TABLE shopcourse.c33_orders;
DROP TABLE shopcourse.c33_products;
DROP TABLE shopcourse.c33_customers;




