Sederet angka 1, 2, 3, 7, 8, 20 menyimpan tiga “pulau” nilai berdekatan — [1..3], [7..8], [20] — yang dipisah “celah” lompatan. Soal berbentuk ini bermunculan nyata di mana-mana: periode mesin terus-menerus menyala, sesi pengguna yang harus digabung karena jeda login kurang dari tiga puluh menit, tanggal absensi beruntun yang dihitung sebagai satu blok kerja. Kelas soal ini dikenal sebagai gap and island problems, dan SQL klasik berbasis GROUP BY tidak punya kata kerja untuk mengelompokkan baris berdasarkan kedekatan nilainya. Pada chapter ini kita membangun tekniknya bertahap dengan CTE dan window function, membuktikan setiap langkah dengan output antara, lalu menerapkannya pada dua studi kasus: penomoran halaman log yang bolong dan sesi aktivitas pengguna.

Menyiapkan Tabel Latihan #

Kita pakai tabel log_kunjungan berisi tanggal kunjungan seorang anggota perpustakaan. Tanggalnya sengaja bergerombol: beberapa hari berurutan, lalu loncat:

sql 
DROP DATABASE IF EXISTS belajar_adv;
CREATE DATABASE belajar_adv;
USE belajar_adv;

Output yang Dihasilkan:

text 
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
sql 
CREATE TABLE log_kunjungan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tanggal DATE NOT NULL,
  kegiatan VARCHAR(50) NOT NULL
);

INSERT INTO log_kunjungan (tanggal, kegiatan) VALUES
  ('2026-01-02', 'meminjam buku'),
  ('2026-01-03', 'mengembalikan buku'),
  ('2026-01-04', 'membaca di tempat'),
  ('2026-01-05', 'meminjam buku'),
  ('2026-01-09', 'meminjam buku'),
  ('2026-01-10', 'mengembalikan buku'),
  ('2026-01-11', 'memperpanjang pinjaman'),
  ('2026-01-18', 'membaca di tempat'),
  ('2026-01-19', 'meminjam buku'),
  ('2026-01-26', 'mengembalikan buku');

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Mata teliti dapat membagi data ini tanpa bantuan komputer: 2–5 Januari adalah satu blok kunjungan beruntun, 9–11 Januari blok kedua, 18–19 Januari blok ketiga, dan 26 Januari blok keempat. Tantangannya adalah membuat SQL melihat pola yang sama — tanpa GROUP BY tanggal yang jelas tidak berarti apa-apa.

Teknik Pertama: Selisih Baris dan Nomor Urut #

Kunci teknik klasiknya ada pada dua angka yang berjalan seiring: nomor urut baris, dan nilai datanya sendiri. Untuk deret yang benar-benar rapat, selisih keduanya konstan. Begitu muncul celah, selisihnya bergeser — dan nilai selisih inilah yang menjadi penanda pulau:

sql 
WITH bernomor AS (
  SELECT
    tanggal,
    ROW_NUMBER() OVER (ORDER BY tanggal) AS rn
  FROM log_kunjungan
)
SELECT
  tanggal,
  rn,
  tanggal - rn AS penanda
FROM bernomor
ORDER BY tanggal;

Output yang Dihasilkan:

text 
+------------+----+----------+
| tanggal    | rn | penanda  |
+------------+----+----------+
| 2026-01-02 |  1 | 20260101 |
| 2026-01-03 |  2 | 20260101 |
| 2026-01-04 |  3 | 20260101 |
| 2026-01-05 |  4 | 20260101 |
| 2026-01-09 |  5 | 20260104 |
| 2026-01-10 |  6 | 20260104 |
| 2026-01-11 |  7 | 20260104 |
| 2026-01-18 |  8 | 20260110 |
| 2026-01-19 |  9 | 20260110 |
| 2026-01-26 | 10 | 20260116 |
+------------+----+----------+
10 rows in set (0.00 sec)

Mari kita bedah cara kerjanya:

  1. CTE bernomor: ROW_NUMBER() memberi nomor 1 sampai 10 sesuai urutan tanggal. Baris 2026-01-02 dapat nomor 1, 2026-01-03 nomor 2, dan seterusnya — nomor ini naik rapat tanpa lompatan.
  2. tanggal - rn: di MySQL, mengurangkan angka dari DATE menghasilkan DATE yang bergeser mundur. Untuk empat baris pertama: 2 Jan - 1 = 1 Jan, 3 Jan - 2 = 1 Jan, 4 Jan - 3 = 1 Jan, 5 Jan - 4 = 1 Jan — konstan! Lalu 9 Jan - 5 = 4 Jan — bergeser karena tanggal melompat 4 hari sementara nomor hanya naik satu.
  3. Kolom penanda yang dihasilkan sama persis untuk semua baris dalam satu pulau, dan berbeda antar pulau: 2026-01-01, 2026-01-04, 2026-01-16, dan 2026-01-25 — satu nilai unik per pulau.

Setelah penanda ditemukan, pengelompokannya menjadi urusan GROUP BY biasa:

sql 
WITH bernomor AS (
  SELECT
    tanggal,
    ROW_NUMBER() OVER (ORDER BY tanggal) AS rn
  FROM log_kunjungan
)
SELECT
  MIN(tanggal) AS mulai,
  MAX(tanggal) AS selesai,
  COUNT(*) AS jumlah_hari
FROM bernomor
GROUP BY tanggal - rn
ORDER BY mulai;

Output yang Dihasilkan:

text 
+------------+------------+-------------+
| mulai      | selesai    | jumlah_hari |
+------------+------------+-------------+
| 2026-01-02 | 2026-01-05 |           4 |
| 2026-01-09 | 2026-01-11 |           3 |
| 2026-01-18 | 2026-01-19 |           2 |
| 2026-01-26 | 2026-01-26 |           1 |
+------------+------------+-------------+
4 rows in set (0.00 sec)

Empat pulau muncul persis seperti pembagian manual kita di awal. Teknik tanggal - rn bekerja untuk data rapat sempurna; begita jarak antar baris yang dianggap “masih satu pulau” punya aturan khusus — misalnya jeda hingga dua hari masih dianggap berurutan — kita butuh varian yang lebih lentur.

Teknik Kedua: LAG untuk Deteksi Batas Pulau #

Teknik kedua mengamati perubahan antar baris tetangga. LAG() membaca tanggal sebelumnya; selisih hari antar keduanya menentukan apakah baris saat ini membuka pulau baru:

sql 
WITH batas AS (
  SELECT
    tanggal,
    LAG(tanggal) OVER (ORDER BY tanggal) AS tanggal_sebelumnya,
    CASE
      WHEN LAG(tanggal) OVER (ORDER BY tanggal) IS NULL THEN 1
      WHEN DATEDIFF(tanggal, LAG(tanggal) OVER (ORDER BY tanggal)) > 1 THEN 1
      ELSE 0
    END AS mulai_pulau
  FROM log_kunjungan
)
SELECT * FROM batas ORDER BY tanggal;

Output yang Dihasilkan:

text 
+------------+--------------------+-------------+
| tanggal    | tanggal_sebelumnya | mulai_pulau |
+------------+--------------------+-------------+
| 2026-01-02 | NULL               |           1 |
| 2026-01-03 | 2026-01-02         |           0 |
| 2026-01-04 | 2026-01-03         |           0 |
| 2026-01-05 | 2026-01-04         |           0 |
| 2026-01-09 | 2026-01-05         |           1 |
| 2026-01-10 | 2026-01-09         |           0 |
| 2026-01-11 | 2026-01-10         |           0 |
| 2026-01-18 | 2026-01-11         |           1 |
| 2026-01-19 | 2026-01-18         |           0 |
| 2026-01-26 | 2026-01-19         |           1 |
+------------+--------------------+-------------+
10 rows in set (0.00 sec)

Kolom mulai_pulau bernilai 1 pada baris pertama (LAG menghasilkan NULL) dan pada setiap baris yang jaraknya lebih dari satu hari dari sebelumnya. Nilai selisih hari bisa kita baca langsung: baris 9 Januari berjarak 4 hari dari 5 Januari, sehingga ia membuka pulau baru.

Bendera mulai_pulau kemudian dijumlahkan berjalan untuk menghasilkan nomor pulau — setiap bendera mengangkat kelompok ke nomor berikutnya:

sql 
WITH batas AS (
  SELECT
    tanggal,
    CASE
      WHEN LAG(tanggal) OVER (ORDER BY tanggal) IS NULL THEN 1
      WHEN DATEDIFF(tanggal, LAG(tanggal) OVER (ORDER BY tanggal)) > 1 THEN 1
      ELSE 0
    END AS mulai_pulau
  FROM log_kunjungan
),
pulau AS (
  SELECT
    tanggal,
    mulai_pulau,
    SUM(mulai_pulau) OVER (ORDER BY tanggal) AS nomor_pulau
  FROM batas
)
SELECT
  nomor_pulau,
  MIN(tanggal) AS mulai,
  MAX(tanggal) AS selesai,
  COUNT(*) AS jumlah_hari,
  DATEDIFF(MAX(tanggal), MIN(tanggal)) + 1 AS rentang_hari
FROM pulau
GROUP BY nomor_pulau
ORDER BY nomor_pulau;

Output yang Dihasilkan:

text 
+-------------+------------+------------+-------------+--------------+
| nomor_pulau | mulai      | selesai    | jumlah_hari | rentang_hari |
+-------------+------------+------------+-------------+--------------+
|           1 | 2026-01-02 | 2026-01-05 |           4 |            4 |
|           2 | 2026-01-09 | 2026-01-11 |           3 |            3 |
|           3 | 2026-01-18 | 2026-01-19 |           2 |            2 |
|           4 | 2026-01-26 | 2026-01-26 |           1 |            1 |
+-------------+------------+------------+-------------+--------------+
4 rows in set (0.00 sec)

Kolom rentang_hari menambahkan sudut pandang baru: pulau 9–11 Januari mengandung 3 hari kunjungan dengan rentang juga 3 hari, sedangkan pulau 2–5 Januari sama-sama padat; bila suatu hari ada kunjungan yang terlewat, jumlah_hari akan lebih kecil dari rentang_hari — cara cepat mendeteksi bolong di dalam pulau. Kelebihan teknik LAG dibanding teknik tanggal - rn: ambang “masih satu pulau” tinggal mengubah angka > 1 menjadi > 2 atau > 7 tanpa mengubah struktur query — sesuatu yang tidak bisa dilakukan teknik pertama.

Studi Kasus: Sesi Aktivitas Pengguna #

Untuk menutup bab, kita terapkan versi ambang pada masalah sesi: aktivitas pengguna dengan jeda kurang dari sama dengan 30 menit digabung ke satu sesi. Data uji kita simpan dengan waktu penuh:

sql 
CREATE TABLE aktivitas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  waktu DATETIME NOT NULL,
  aksi VARCHAR(50) NOT NULL
);

INSERT INTO aktivitas (waktu, aksi) VALUES
  ('2026-01-15 08:00:00', 'login'),
  ('2026-01-15 08:12:00', 'lihat produk'),
  ('2026-01-15 08:25:00', 'tambah keranjang'),
  ('2026-01-15 09:40:00', 'lihat produk'),
  ('2026-01-15 09:52:00', 'checkout'),
  ('2026-01-15 11:30:00', 'login'),
  ('2026-01-15 11:35:00', 'logout');

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Dengan ambang 30 menit — atau 1800 detik pada TIMESTAMPDIFF — tiga sesi terbentuk: blok pagi pukul 08:00 sampai 08:25, blok siang pukul 09:40 sampai 09:52, dan kunjungan singkat pukul 11:30:

sql 
WITH batas AS (
  SELECT
    waktu,
    CASE
      WHEN LAG(waktu) OVER (ORDER BY waktu) IS NULL THEN 1
      WHEN TIMESTAMPDIFF(MINUTE, LAG(waktu) OVER (ORDER BY waktu), waktu) > 30 THEN 1
      ELSE 0
    END AS mulai_sesi
  FROM aktivitas
),
sesi AS (
  SELECT
    waktu,
    SUM(mulai_sesi) OVER (ORDER BY waktu) AS nomor_sesi
  FROM batas
)
SELECT
  nomor_sesi,
  MIN(waktu) AS mulai,
  MAX(waktu) AS selesai,
  COUNT(*) AS jumlah_aksi,
  TIMESTAMPDIFF(MINUTE, MIN(waktu), MAX(waktu)) AS durasi_menit
FROM sesi
GROUP BY nomor_sesi
ORDER BY nomor_sesi;

Output yang Dihasilkan:

text 
+------------+---------------------+---------------------+-------------+--------------+
| nomor_sesi | mulai               | selesai             | jumlah_aksi | durasi_menit |
+------------+---------------------+---------------------+-------------+--------------+
|          1 | 2026-01-15 08:00:00 | 2026-01-15 08:25:00 |           3 |           25 |
|          2 | 2026-01-15 09:40:00 | 2026-01-15 09:52:00 |           2 |           12 |
|          3 | 2026-01-15 11:30:00 | 2026-01-15 11:35:00 |           2 |            5 |
+------------+---------------------+---------------------+-------------+--------------+
3 rows in set (0.00 sec)

Perhatikan dua jeda besar pada data: 75 menit antara 08:25 dan 09:40 memutus sesi pertama, dan 98 menit antara 09:52 dan 11:30 memutus sesi kedua — keduanya melewati ambang 30. Sementara jeda 12–13 menit di dalam blok tidak pernah memutus apa pun. Query ini juga siap dilengkapi: menambah PARTITION BY pengguna_id pada kedua window function langsung mengubahnya menjadi analisis sesi multi-pengguna tanpa mengubah logika lainnya.

Referensi #

Pulau dan celah kini bisa kita petakan dengan dua teknik yang saling melengkapi. Bab terakhir section advanced-sql merangkai seluruh perlengkapan — CTE, window function, LATERAL, hingga pola upsert — menjadi query analitis yang utuh.