MariaDB 教程 / 计算累计金额并用 LAG、LEAD 比较相邻行

窗口 SUM 可以在不折叠输入行的情况下计算累计金额,LAG 和 LEAD 则按指定顺序读取前一行与后一行的值。应明确唯一的排序规则和累计窗口,避免相同时间戳下的顺序或累计范围含糊不清。

定义时间顺序与被累计的记录单位

逐单累计金额与按天累计金额是两个指标。本例先把每笔 paid 订单的购买数量乘成交单价汇总,再按订单时间与编号累计。配送费、取消与退款需要各自的统计规则;这里的金额只包含已经定义的已支付商品金额。

计算 范围 边界结果
窗口 SUM 从第一行到当前行 首行累计等于自己的金额
LAG 排序中的前一行 没有前一行时为 NULL
LEAD 排序中的后一行 没有后一行时为 NULL

窗口排名教程说明了保留行的计算与分组的差别,日期差计算教程则帮助区分“前一条记录”与“相隔多少天”。具体语法可查阅官方 LAG、LEAD与 窗口框架文档。

准备顺序明确的独立订单样本

以 course_user 连接学习服务器,核对 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;。本节独立使用 c33_customers、c33_products、c33_orders、c33_items 四表;如果已有同名对象,先检查结构和记录,不要覆盖未知用途的数据。

在 MariaDB 客户端执行下面的完整代码。若保存文件,使用 UTF-8,再按 SQL 文件与 SOURCE 教程提供实际路径加载;不要把 SQL 直接当作操作系统命令运行。

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);

原始韩文姓名、城市和商品字符串保留不变。商品 1~4 为笔记本、圆珠笔、马克杯和台灯;客户 1、3 位于首尔,2 位于釜山,4 位于大田。按编号核对连接与金额,保持字面量的 UTF-8 编码一致。

paid 序列是 1 月 10 日的订单 101、1 月 20 日的 102、2 月 5 日的 103。order_id 在时间相同时充当明确的次级排序键,但这不表示编号在任何系统中都能直接代表真实时间先后。

用明确的 ROWS 框架逐行累计

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;
订单 金额 累计金额 前一笔 后一笔
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 明确包括排序后的第一行到当前行。要表达逐行累计时,不应仅省略框架并假定默认行为总符合意图。相同排序值形成同位行时,ROWS 与 RANGE 的累计边界可能不同。

下面的独立查询不改变任何表。订单 501、502 共享一个时间戳,金额分别为 10、20,503 在下一天,金额为 5:

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 与 RANGE,得到的都是 10、30、35。第三列只在 RANGE 排序中去掉编号,使两个同时间记录作为同位组一起累计,结果为 30、30、35。若 ROWS 也没有唯一的次级键,时间相同的两行内部先后就没有明确规定。

比较时应一次只改一个因素。可以把 range_time 的排序也补上 order_id,确认它变为 10、30、35,再恢复只按时间排序。这样能分清差异来自框架与同位定义,而不是数据被修改。

把相邻记录与缺少的日历日期分开

LAG 默认读取指定顺序中的上一行,LEAD 默认读取下一行;它们不是自动查找昨天或明天。样本中 101 到 102 相隔 10 天,102 到 103 相隔 16 天,没有订单的日子不会自动生成记录。

如果要做逐日比较,应先按日汇总,再明确没有记录的日期怎样处理。把“没有观测”补成零,会改变指标含义;需要完整日期序列时,应另行提供日期来源,而不是期待 LAG 自动补齐。

用本单金额减去 LAG 得到相邻订单差:第一笔为 NULL,后面分别为 -3500、11500。首笔没有前驱,所以 NULL 有明确原因;把它替换为零,是选择一个比较基准,不只是让显示更漂亮。末笔 LEAD 同样为 NULL,因为没有后一行。

决定日期筛选在累计前还是累计后执行

先过滤到二月再计算窗口,累计从二月内开始。如果要只显示二月订单,却保留从一月开始的历史累计,应先在 CTE 内完成窗口计算,再在外层按日期筛选。两者都能合法执行,但起点不同。

第一条先累计所有 paid 订单,再显示二月;第二条把相同日期条件放到窗口计算之前。两者都使用本节四表,不修改数据:

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;
筛选位置 订单编号 商品金额 累计金额
累计后筛选 103 19500.00 39000.00
累计前筛选 103 19500.00 19500.00

两条结果都包含订单 103,商品金额也相同,但 running_total 不同。报表需要的是全期间历史累计,还是在显示月份内重新累计,应先写清楚,不能只看显示月份相同就认定两条查询等价。

还要先把明细汇总到订单层级,否则窗口会按商品项推进,而不是按订单推进。定义累计指标时,同时记录输入单位、订单状态、排序键、框架与筛选阶段,才能稳定解释结果。

核对首尾边界与相同时间的情况

先手工检查 11500、19500、39000 三个累计值,再检查首行 LAG 与末行 LEAD 的 NULL。若在独立练习事务中让两单时间相同,次级编号仍应给出明确顺序;比较后回滚,确认时间与全部源记录恢复。

本例的 INNER JOIN 只累计有明细的 paid 订单,没有明细的订单不会自动贡献一行。是否需要保留这些订单、怎样定义其金额,应另行明确。最终显示顺序也要写出 ORDER BY,不依赖索引或一次执行的偶然顺序。

清理前检查四表定义和记录,备份需要保留的数据,再只删除确认可丢弃的本节表。下面按子表到父表的顺序执行;DROP TABLE 会移除定义和全部数据,普通 ROLLBACK 无法恢复,应与事务实验分开。

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