MariaDB-Kurs / Laufende Summen berechnen und Zeilen mit LAG und LEAD vergleichen

SUM als Fensterfunktion berechnet eine laufende Summe und erhält dabei die einzelnen Zeilen. LAG und LEAD lesen Werte aus der vorherigen beziehungsweise nächsten Zeile der gewählten Reihenfolge. Legen Sie eine eindeutige Sortierung und den Berechnungsrahmen fest, damit gleiche Zeitstempel den Ablauf nicht mehrdeutig machen.

Reihenfolge und Einheit der laufenden Summe festlegen

Eine laufende Summe pro Bestellung unterscheidet sich von einer täglichen Summe. Dieses Beispiel summiert zuerst Menge mal Kaufpreis je Position einer bezahlten Bestellung. Anschließend addiert es diese Bestellbeträge nach Zeitstempel und Bestellungskennung. Die Kennzahl erfasst nur die als bezahlt definierten Warenwerte; Versandkosten, Stornierungen und Erstattungen benötigen zum jeweiligen Zweck passende Regeln.

Der Vergleich von Fensterfunktionen und Rangfolgen erklärt zeilenerhaltende Berechnungen. Die Anleitung zu Datumsunterschieden hilft, eine vorherige Zeile von einem verstrichenen Zeitintervall zu unterscheiden.

Berechnung Bereich im Beispiel Grenzfall
SUM mit OVER Erste bis aktuelle Zeile Erste Summe entspricht dem eigenen Betrag
LAG Eine Zeile davor in der Sortierung NULL ohne vorherige Zeile
LEAD Eine Zeile danach in der Sortierung NULL ohne folgende Zeile

Eine eindeutige Bestellungsfolge vorbereiten

Verbinden Sie sich als course_user mit dem Lernserver und wählen Sie shopcourse. Speichern Sie die Blöcke in einer UTF-8-Datei und führen Sie im Client SOURCE C:/mariadb-lab/lesson.sql; aus. Ersetzen Sie den Pfad durch die tatsächliche Datei. Die Anleitung zu Client und SQL-Dateien erläutert die Verbindung. Geben Sie SQL im MariaDB-Client ein.

Prüfen Sie vor dem Erstellen von c33_customers, c33_products, c33_orders und c33_items mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung. Diese Namen gehören ausschließlich zur Übung. Untersuchen Sie vorhandene Definitionen und Datensätze. Entfernen Sie die Tabellen nur, wenn sie als entbehrliche Übungsobjekte bestätigt sind; erhalten Sie Anwendungstabellen.

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

Das SQL erhält die koreanischen Originalnamen und Städte. Die Produkte 1 bis 4 sind Notizbuch, Kugelschreiber, Becher und Lampe. Die Kunden 1 und 3 wohnen in Seoul, Kunde 2 in Busan und Kunde 4 in Daejeon. Behalten Sie die UTF-8-Zeichenfolgen für reproduzierbare Ergebnisse bei.

Die bezahlte Folge umfasst Bestellung 101 vom 10. Januar, 102 vom 20. Januar und 103 vom 5. Februar. order_id löst gleiche Zeitstempel auf und macht die Position reproduzierbar. Dies ist eine Sortierregel des Berichts und bedeutet nicht, dass Kennungen grundsätzlich die tatsächliche zeitliche Reihenfolge abbilden.

Mit einem ausdrücklichen ROWS-Rahmen kumulieren

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;

Die CTE order_totals erzeugt eine Zeile pro bezahlter Bestellung mit Positionen. SUM addiert diese Beträge, ohne das Ergebnis auf eine einzelne Zeile zu reduzieren. Der innere JOIN dieses Beispiels lässt eine bezahlte Bestellung ohne Positionen aus. Ihre Aufnahme mit Betrag null würde eine angepasste Abfrage erfordern.

Bestellung Betrag Laufende Summe Davor Danach
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 bezieht ausdrücklich alle Zeilen vom Anfang bis zur aktuellen Position ein. Geben Sie den Rahmen für eine zeilenweise fortschreitende Summe an. ROWS und RANGE können gleiche Sortierwerte unterschiedlich behandeln. Die eindeutige Kennung dieses Beispiels bestimmt zusätzlich die Reihenfolge innerhalb gleicher Zeitstempel.

Die folgende eigenständige Abfrage vergleicht die Rahmen, ohne Tabellen zu verändern. Die Beispielbestellungen 501 und 502 haben denselben Zeitstempel, aber die Beträge 10 und 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 und range_unique verwenden dieselbe eindeutige Sortierung nach Zeitstempel und Kennung. Nur ROWS wird durch RANGE ersetzt; beide liefern 10, 30 und 35. Bei range_time entfällt lediglich die Kennung aus der RANGE-Sortierung. Die ersten beiden Zeilen teilen damit alle Sortierwerte und werden gemeinsam berücksichtigt: 30, 30 und 35. Auch ROWS ohne eindeutige Auflösung würde nicht festlegen, welche Bestellung mit gleichem Zeitstempel zuerst fortschreitet.

Nachbarzeilen statt fehlender Kalendertage interpretieren

LAG liest die vorherige Zeile der Sortierung, nicht zwingend den vorherigen Kalendertag. Hier liegt vor dem 5. Februar die Zeile vom 20. Januar. Die Funktion erzeugt keine Zeilen für Tage ohne Bestellung. Ein täglicher Vergleich benötigt zuerst eine Tagesaggregation und eine Entscheidung über Tage ohne Beobachtungen.

Die Differenz zwischen aktuellem und vorherigem Betrag beschreibt aufeinanderfolgende Bestellungen: hier NULL, −3500.00 und 11500.00. Der erste Wert ist NULL, weil kein Vorgänger existiert. Ihn durch null zu ersetzen, legt eine Vergleichsbasis fest und ist keine rein optische Änderung. Fehlende Beobachtung und gemessener Nullwert sind unterschiedliche Informationen.

Die offiziellen Referenzen zu LAG und LEAD erläutern den Zeilenversatz. Die Aufrufe hier verwenden mit dem Standardversatz eins die unmittelbar benachbarte Zeile.

Vor oder nach der laufenden Summe filtern

Ein Februarfilter vor der Fensterberechnung beginnt die laufende Summe innerhalb des Februars. Sollen Februarzeilen mit einer seit Januar fortgeschriebenen Summe erscheinen, berechnen Sie das Fenster zuerst in einer CTE und filtern danach die Anzeige. Beide Abfragen sind gültig, beantworten aber unterschiedliche Fragen.

Die erste folgende Abfrage summiert alle bezahlten Bestellungen und zeigt danach den Februar. Die zweite wendet dieselbe Datumsbedingung vor der Fensterberechnung an. Beide verwenden die vorhandenen c33_-Tabellen und ändern keine Daten.

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;
Filterposition order_id goods_total running_total
Nach der laufenden Summe 103 19500.00 39000.00
Vor der laufenden Summe 103 19500.00 19500.00

Beide zeigen Bestellung 103, der Startpunkt der Summe unterscheidet sich jedoch. Entscheiden Sie zwischen einer Summe über den gesamten Zeitraum und einer im angezeigten Monat neu beginnenden Summe. Ohne vorherige Zusammenfassung der Positionen würde die Berechnung pro Position statt pro Bestellung fortschreiten. Halten Sie Einheit, eingeschlossene Zustände, Sortierung, Rahmen und Filterstufe für die Kennzahl fest.

Grenzfälle prüfen und Übungsobjekte entfernen

Rechnen Sie die laufenden Summen 11500, 19500 und 39000 von Hand nach. Prüfen Sie NULL beim ersten LAG und letzten LEAD und vergleichen Sie gleiche Zeitstempel mit der unabhängigen Probe. Verwenden Sie ein ausdrückliches abschließendes ORDER BY statt der zufälligen Reihenfolge eines Index. Die offizielle Referenz zu Fensterrahmen beschreibt Grenzen und gleich sortierte Zeilen.

Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, Definitionen und gespeicherte Zeilen. Sichern Sie benötigte Inhalte. Gehören die folgenden entbehrlichen Objekte nur zu dieser Übung, entfernen Sie zuerst die abhängigen und danach die referenzierten Tabellen. DROP TABLE entfernt Struktur und Daten und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Trennen Sie die Bereinigung von Transaktionsversuchen.

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