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;