Kursus MariaDB / Menghitung Total Berjalan dan Membandingkan Baris dengan LAG serta LEAD
SUM sebagai fungsi jendela menghitung total berjalan tanpa menyatukan baris masukan, sedangkan LAG dan LEAD membaca nilai baris sebelumnya serta berikutnya dalam urutan yang dipilih. Tetapkan urutan yang pasti dan rentang akumulasi yang dimaksud agar waktu yang sama tidak membuat hasil menjadi ambigu.
Tentukan urutan waktu dan satuan yang diakumulasi
Total berjalan per pesanan berbeda dari total berjalan per hari. Contoh ini merangkum quantity × unit_price saat pembelian untuk setiap pesanan dibayar, lalu mengakumulasikannya berdasarkan waktu pesanan dan ID. Ongkos kirim, pembatalan, serta pengembalian dana membutuhkan kebijakan tersendiri; ukuran di sini hanya mencakup nilai barang dibayar yang didefinisikan.
Panduan fungsi peringkat menjelaskan perhitungan yang mempertahankan baris, sedangkan panduan selisih tanggal membedakan baris sebelumnya dari jarak waktu kalender. LAG tanpa offset tambahan membaca satu baris sebelumnya dan LEAD satu baris berikutnya sesuai urutan jendela.
| Perhitungan | Cakupan contoh | Perilaku batas |
|---|---|---|
| SUM berjendela | Baris pertama sampai baris sekarang | Total pertama sama dengan nilai barisnya |
| LAG | Baris sebelumnya dalam urutan | NULL jika tidak ada baris sebelumnya |
| LEAD | Baris berikutnya dalam urutan | NULL jika tidak ada baris berikutnya |
Referensi LAG MariaDB dan gambaran umum fungsi jendela menjelaskan urutan serta rentang perhitungannya.
Siapkan urutan pesanan yang pasti
Hubungkan ke server belajar sebagai course_user dan pilih shopcourse. Panduan klien dan berkas SQL UTF-8 menjelaskan penyimpanan serta pelaksanaan dengan SOURCE C:/mariadb-lab/lesson.sql;. Ganti jalur dengan berkas sebenarnya dan jalankan SQL di dalam klien MariaDB.
Sebelum membuat c33_customers, c33_products, c33_orders, dan c33_items, periksa SELECT VERSION(), DATABASE(); serta SHOW TABLES;. Jika nama sudah ada, periksa definisi dan barisnya dahulu. Gunakan hanya objek latihan yang boleh dibuang dan pertahankan tabel aplikasi. Persiapan berikut menyediakan seluruh tabel agar contoh dapat dijalankan mandiri.
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);
SQL mempertahankan nama dan kota Korea dari sumber. Produk 1–4 adalah buku catatan, pena, cangkir, dan lampu. Pelanggan 1 serta 3 berada di Seoul, pelanggan 2 di Busan, dan pelanggan 4 di Daejeon. Simpan teks contoh dengan UTF-8 dan gunakan ID untuk membandingkan hasil.
Urutan pesanan dibayar adalah 101 pada 10 Januari, 102 pada 20 Januari, serta 103 pada 5 Februari. order_id memberi urutan sekunder yang pasti jika waktu sama. Pemakaian ini tidak berarti ID selalu mencerminkan urutan kronologis pada setiap aplikasi.
Gunakan rentang ROWS yang jelas untuk akumulasi
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;
| Pesanan | Nilai | Total berjalan | Sebelumnya | Berikutnya |
|---|---|---|---|---|
| 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 menyatakan bahwa baris pertama sampai baris sekarang ikut dijumlahkan. Jangan bergantung pada rentang bawaan yang tidak ditulis jika maksudnya akumulasi per baris. ROWS dan RANGE dapat memperlakukan nilai urutan yang sama secara berbeda; kunci sekunder unik pada contoh membuat urutan pasti.
Kueri mandiri berikut membandingkan baris dengan waktu sama tanpa mengubah tabel. Pesanan 501 dan 502 mempunyai waktu sama, tetapi nilai berbeda.
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 dan range_unique memakai urutan unik waktu–ID yang sama dan hanya berbeda dalam ROWS versus RANGE; keduanya menghasilkan 10, 30, 35. range_time menghapus hanya ID dari pengurutan RANGE sehingga dua baris dengan waktu sama dijumlahkan bersama menjadi 30, 30, 35. ROWS tanpa pemecah seri unik tidak menentukan urutan di dalam waktu yang sama.
Tafsirkan baris tetangga, bukan tanggal kalender yang hilang
LAG berarti baris sebelumnya pada urutan terpilih, bukan selalu hari kemarin. Fungsi tersebut tidak membuat baris untuk tanggal tanpa pesanan. Ukuran perubahan harian memerlukan agregasi per hari dan kebijakan yang jelas untuk tanggal tanpa data.
Selisih nilai dengan pesanan sebelumnya pada contoh adalah NULL, -3500, dan 11500. Hasil pertama NULL karena tidak ada pendahulu. Menggantinya dengan nol menentukan nilai dasar perbandingan, bukan sekadar memperindah tampilan. Tidak ada pengamatan berbeda dari nilai nol yang benar-benar diukur. Antara pesanan contoh terdapat jarak 10 dan 16 hari, bukan satu hari.
Pilih penyaringan sebelum atau sesudah akumulasi
Menyaring ke Februari sebelum menghitung jendela memulai akumulasi dari Februari. Untuk menampilkan Februari dengan total yang sudah mencakup bulan sebelumnya, selesaikan perhitungan jendela dalam CTE lalu saring tanggal tampilan pada kueri luar. Keduanya dapat benar, tetapi mempunyai titik awal berbeda.
Jika rincian belum diringkas, total akan bergerak per baris barang dan bukan per pesanan. Catat satuan data, status yang memenuhi syarat, kunci urutan, rentang akumulasi, serta tahap filter saat mendefinisikan metrik.
Kueri pertama mengakumulasi seluruh pesanan dibayar lalu menampilkan Februari. Kueri kedua menempatkan filter tanggal yang sama sebelum perhitungan jendela. Keduanya menggunakan tabel c33 yang sudah disiapkan tanpa mengubah data.
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;
| Posisi filter | order_id | goods_total | running_total |
|---|---|---|---|
| Sesudah akumulasi | 103 | 19500.00 | 39000.00 |
| Sebelum akumulasi | 103 | 19500.00 | 19500.00 |
Kedua hasil menampilkan pesanan 103, tetapi batas akumulasinya berbeda. Tentukan apakah laporan membutuhkan total sepanjang periode sebelumnya atau total yang dimulai kembali dalam bulan yang ditampilkan.
Periksa batas pertama, terakhir, dan waktu yang sama
Cocokkan total berjalan secara manual: 11500, 19500, dan 39000. Periksa NULL pada LAG pertama serta LEAD terakhir. Pada percobaan terpisah, waktu yang sama harus tetap mempunyai posisi pasti melalui kunci sekunder. Pertahankan ORDER BY akhir secara jelas daripada bergantung pada urutan indeks yang kebetulan digunakan.
Sebelum mengulang latihan, periksa basis data yang dipilih, SHOW CREATE TABLE, serta isi keempat tabel dan cadangkan data yang perlu dipertahankan. Hapus hanya objek c33_ milik artikel ini, dimulai dari tabel anak lalu induk. DROP TABLE menghapus struktur dan baris sekaligus serta tidak dibalik dengan ROLLBACK biasa; pisahkan pembersihan dari percobaan transaksi.
DROP TABLE shopcourse.c33_items;
DROP TABLE shopcourse.c33_orders;
DROP TABLE shopcourse.c33_products;
DROP TABLE shopcourse.c33_customers;




