MariaDB講座 / 累計・LAG・LEADで前後の行を比較する

ウィンドウのSUMは行をまとめずに累計を加え、LAGとLEADは選んだ順序の前後の値を読みます。同じ時刻でも順番が決まるキーと、累計へ含める範囲を明示します。

時間の順序と一行の単位を決める

注文ごとの累計と日ごとの累計は違います。ここではpaid注文の商品金額を一注文へまとめ、ordered_atとorder_idの順で積み上げます。送料・返金・取消は別方針で、この指標は定義したpaid商品だけです。

LAGは前日ではなく一つ前の行です。注文のない日を自動生成しません。日次比較なら日ごとの集計と欠損日の方針が必要で、日付の差は日付関数を使います。

再現できる注文列を用意する

course_userでshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。以下はこの記事専用の四表を用意する完全な例です。UTF-8ファイルでの実行もできます。同名表がある場合は定義と行を保存して用途を調べ、確認なしに置き換えません。

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で保存・接続し、IDと数値で結果を照合します。paidは101・102・103、104はcanceledです。

paidの順序は1月10日の101、1月20日の102、2月5日の103です。IDは同時刻の補助キーであり、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;

金額は11500・8000・19500、累計は11500・19500・39000です。previous_totalはNULL・11500・8000、next_totalは8000・19500・NULLです。最初と最後には隣の行がないのでNULLとなります。0へ変えるなら単なる飾りでなく基準値の定義です。

同時刻のROWSとRANGEを比較する

表を書き換えない独立した比較です。

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;

時刻とIDを両方に使ったROWSとRANGEはともに10・30・35です。RANGEの順序からIDだけを外すと時刻の同順位が一緒になり30・30・35です。ROWSでも一意な補助キーがなければ同時刻内の順が未定義になります。既定フレームへ意図を任せず明示します。

月条件を置く段階を比較する

全期間の累計を先に作り、2月だけ表示します。

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ですが、前者の累計39000、後者19500です。全期間の進捗か、表示月から始める累計かで選びます。CTEで段階を分けると、条件の位置を確認できます。

境界と集計前の行を確認する

明細を先にまとめないと、累計は注文でなく明細ごとに進みます。基本単位、状態、順序キー、フレーム、期間条件の段階を記録します。同額・同時刻、先頭LAG、末尾LEADを含めて小さな例を照合し、最後の表示ORDER BYも残します。

再実習では実際のDB・表定義・保存行を調べ、必要なデータをバックアップしてから、この記事の削除可能な表だけを子から親の順に消します。DROP TABLEは定義と行を消し、通常のROLLBACKでは戻せません。

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

関連する公式リファレンス

• lag（公式ドキュメント）
• window functions overview（公式ドキュメント）