Gap and Island Problem

Log sensor, absensi kelas, dan langganan pelanggan menyimpan data sebagai deret tanggal: satu baris untuk setiap hari ketika sesuatu terjadi. Pertanyaan analitik yang mengikutinya hampir selalu berpasangan: “berapa lama rentetan tanpa putus?” dan “kapan saja rentetan itu terputus?”. Baris-baris yang berurutan disebut island (pulau), tanggal-tanggal yang hilang di antaranya disebut gap (celah) — dan SQL tidak menyediakan fungsi bawaan bernama find_islands. Masalah klasik ini tetap harus dijawab dengan menyusun window function sendiri, dan bab ini membedah dua teknik penyusunannya sampai ke meja kerja antara: trik selisih tanggal dengan nomor baris, serta deteksi batas dengan LAG yang penomorannya dihitung lewat jumlah berjalan. Sebagai penutup, celah dicari lewat komplemen deret penuh generate_series — pola yang sama sekali memakai ulang teknik pulau.

Dataset: Log Sensor Dua Gudang #

Tabel log_sensor mencatat hari-hari ketika sensor suhu berhasil mengirim data: satu baris berarti sensor hidup pada hari itu, tidak ada baris berarti sensor tidak membaca. Perangkat 1 (Gudang Jakarta) membentuk pulau 4, 3, 2, dan 1 hari dengan celah 2, 3, dan 3 hari; perangkat 2 (Gudang Bandung) membentuk pulau 2, 5, dan 3 hari. Tanggal kedua perangkat sengaja tumpang tindih — bahan penting untuk membahas batas salah satu varian teknik di bagian berikutnya:

sql
buat-tabel.sql
-- Tabel latihan chapter Gap and Island: log bacaan sensor suhu dua gudang.
-- Satu baris = satu hari ketika sensor berhasil mengirim data; hari tanpa
-- baris = sensor tidak membaca. Tanggal sengaja berlubang (celah) demi demo.
-- Perangkat 1 = sensor Gudang Jakarta, perangkat 2 = Gudang Bandung.
CREATE TABLE log_sensor (
    perangkat_id  integer NOT NULL,
    tanggal       date NOT NULL,
    jumlah_bacaan integer NOT NULL,
    PRIMARY KEY (perangkat_id, tanggal)
);

-- Perangkat 1: pulau 4, 3, 2, 1 hari -- celah 2, 3, 3 hari.
INSERT INTO log_sensor (perangkat_id, tanggal, jumlah_bacaan) VALUES
    (1, DATE '2026-01-01', 30),
    (1, DATE '2026-01-02', 32),
    (1, DATE '2026-01-03', 28),
    (1, DATE '2026-01-04', 31),
    (1, DATE '2026-01-07', 29),
    (1, DATE '2026-01-08', 33),
    (1, DATE '2026-01-09', 30),
    (1, DATE '2026-01-13', 27),
    (1, DATE '2026-01-14', 31),
    (1, DATE '2026-01-18', 34);

-- Perangkat 2: pulau 2, 5, 3 hari -- celah 1, 4, 3 hari.
INSERT INTO log_sensor (perangkat_id, tanggal, jumlah_bacaan) VALUES
    (2, DATE '2026-01-02', 22),
    (2, DATE '2026-01-03', 25),
    (2, DATE '2026-01-08', 36),
    (2, DATE '2026-01-09', 38),
    (2, DATE '2026-01-10', 35),
    (2, DATE '2026-01-11', 37),
    (2, DATE '2026-01-12', 39),
    (2, DATE '2026-01-16', 28),
    (2, DATE '2026-01-17', 30),
    (2, DATE '2026-01-18', 33);

SELECT perangkat_id, tanggal, jumlah_bacaan
FROM log_sensor
ORDER BY perangkat_id, tanggal;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 10
INSERT 0 10
 perangkat_id |  tanggal   | jumlah_bacaan 
--------------+------------+---------------
            1 | 2026-01-01 |            30
            1 | 2026-01-02 |            32
            1 | 2026-01-03 |            28
            1 | 2026-01-04 |            31
            1 | 2026-01-07 |            29
            1 | 2026-01-08 |            33
            1 | 2026-01-09 |            30
            1 | 2026-01-13 |            27
            1 | 2026-01-14 |            31
            1 | 2026-01-18 |            34
            2 | 2026-01-02 |            22
            2 | 2026-01-03 |            25
            2 | 2026-01-08 |            36
            2 | 2026-01-09 |            38
            2 | 2026-01-10 |            35
            2 | 2026-01-11 |            37
            2 | 2026-01-12 |            39
            2 | 2026-01-16 |            28
            2 | 2026-01-17 |            30
            2 | 2026-01-18 |            33
(20 rows)

Trik Selisih: Tanggal Dikurangi Nomor Baris #

Mekanisme inti teknik pertama dapat dirumuskan dalam satu kalimat: dua penghitung yang maju bersama memiliki selisih yang beku. tanggal adalah penghitung alami yang bertambah tepat satu setiap hari, dan ROW_NUMBER() OVER (ORDER BY tanggal) adalah penghitung buatan yang bertambah tepat satu setiap baris. Selama hari-hari berurutan, keduanya maju seiring — selisihnya konstan. Begitu satu hari hilang, tanggal melompat dua langkah sementara nomor baris hanya melangkah satu — selisihnya berubah, dan baris-baris setelahnya membawa nilai selisih yang baru. Nilai selisih itulah kunci pengelompokan pulau.

Kita bedah pembentukannya bertahap pada deret perangkat 1 saja:

sql
pulau-satu-deret.sql
-- Teknik 1 langkah demi langkah pada SATU deret (perangkat 1 saja):
-- kunci pulau = tanggal - nomor baris. Kedua operan naik satu per baris
-- pada tanggal berurutan, sehingga selisihnya beku di dalam satu pulau.

-- Langkah 1: nomori setiap baris sesuai urutan tanggal.
SELECT tanggal,
       jumlah_bacaan,
       row_number() OVER (ORDER BY tanggal) AS rn
FROM log_sensor
WHERE perangkat_id = 1
ORDER BY tanggal;

-- Langkah 2: tanggal dikurangi nomor baris. Perhatikan kolom kunci_pulau:
-- nilainya beku selama tanggal bertambah tepat satu hari, dan melompat
-- setiap kali ada hari yang hilang.
SELECT tanggal,
       row_number() OVER (ORDER BY tanggal) AS rn,
       (tanggal - row_number() OVER (ORDER BY tanggal)::integer) AS kunci_pulau
FROM log_sensor
WHERE perangkat_id = 1
ORDER BY tanggal;

-- Langkah 3: kelompokkan baris menurut kunci_pulau -- tiap kelompok
-- adalah satu pulau, diringkas jadi tanggal mulai, selesai, dan panjang.
WITH ber_nomor AS (
    SELECT tanggal,
           jumlah_bacaan,
           (tanggal - row_number() OVER (ORDER BY tanggal)::integer) AS kunci_pulau
    FROM log_sensor
    WHERE perangkat_id = 1
)
SELECT min(tanggal) AS mulai,
       max(tanggal) AS selesai,
       count(*) AS panjang_hari,
       sum(jumlah_bacaan) AS total_bacaan
FROM ber_nomor
GROUP BY kunci_pulau
ORDER BY mulai;

Output yang Dihasilkan:

text
  tanggal   | jumlah_bacaan | rn 
------------+---------------+----
 2026-01-01 |            30 |  1
 2026-01-02 |            32 |  2
 2026-01-03 |            28 |  3
 2026-01-04 |            31 |  4
 2026-01-07 |            29 |  5
 2026-01-08 |            33 |  6
 2026-01-09 |            30 |  7
 2026-01-13 |            27 |  8
 2026-01-14 |            31 |  9
 2026-01-18 |            34 | 10
(10 rows)

  tanggal   | rn | kunci_pulau 
------------+----+-------------
 2026-01-01 |  1 | 2025-12-31
 2026-01-02 |  2 | 2025-12-31
 2026-01-03 |  3 | 2025-12-31
 2026-01-04 |  4 | 2025-12-31
 2026-01-07 |  5 | 2026-01-02
 2026-01-08 |  6 | 2026-01-02
 2026-01-09 |  7 | 2026-01-02
 2026-01-13 |  8 | 2026-01-05
 2026-01-14 |  9 | 2026-01-05
 2026-01-18 | 10 | 2026-01-08
(10 rows)

   mulai    |  selesai   | panjang_hari | total_bacaan 
------------+------------+--------------+--------------
 2026-01-01 | 2026-01-04 |            4 |          121
 2026-01-07 | 2026-01-09 |            3 |           92
 2026-01-13 | 2026-01-14 |            2 |           58
 2026-01-18 | 2026-01-18 |            1 |           34
(4 rows)

Mari kita baca hasil antaranya baris demi baris:

Langkah 1 menghasilkan rn 1 sampai 10 mengikuti urutan tanggal — penghitung buatan yang dibutuhkan rumus.
Langkah 2 memperlihatkan pembekuan itu secara nyata: tanggal 1–4 Januari semuanya bernilai kunci_pulau 2025-12-31; tanggal 7–9 Januari bernilai 2026-01-02; pulau 13–14 Januari bernilai 2026-01-05. Nilai kunci tidak bermakna kalender — ia hanya perlu konstan per pulau dan berbeda antar pulau.
Langkah 3 mengelompokkan dengan GROUP BY kunci_pulau, dan min/max/count(*) merangkai setiap kelompok menjadi baris pulau: empat pulau ber-panjang 4, 3, 2, dan 1 hari.
Cast ::integer pada row_number() wajib: row_number() mengembalikan bigint, dan PostgreSQL tidak menyediakan operator date - bigint.

Pulau per Kelompok dan Batas Trik Selisih #

Data nyata jarang berupa satu deret — sensor, kelas, dan pelanggan masing-masing membawa deretnya sendiri. PARTITION BY perangkat_id memisahkan penghitung tiap perangkat, sehingga rumus yang sama bekerja serentak untuk semua kelompok. Tabel berikut juga menampilkan varian lain trik selisih yang sering dijumpai di berbagai tutorial: selisih dua ROW_NUMBER(), yaitu ROW_NUMBER() OVER (ORDER BY tanggal, perangkat_id) - ROW_NUMBER() OVER (PARTITION BY perangkat_id ORDER BY tanggal):

sql
pulau-per-kelompok.sql
-- Generalisasi untuk BANYAK kelompok (dua perangkat sekaligus):
-- PARTITION BY perangkat_id memisahkan deret tiap perangkat.
-- Kolom selisih_rn memperlihatkan bentuk lain trik selisih -- selisih dua
-- ROW_NUMBER() -- yang hanya konstan bila baris perangkat lain tidak
-- menyisip di antara tanggal-tanggal satu pulau. Pada data ini tanggal
-- kedua perangkat tumpang tindih, sehingga selisih_rn pecah di tengah
-- pulau, sementara kunci_tanggal tetap konstan per pulau.

-- Langkah 1: meja kerja dengan semua varian kunci per baris.
SELECT perangkat_id,
       tanggal,
       row_number() OVER (ORDER BY tanggal, perangkat_id) AS rn_global,
       row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS rn_perangkat,
       row_number() OVER (ORDER BY tanggal, perangkat_id)
           - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS selisih_rn,
       (tanggal
            - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal)::integer) AS kunci_tanggal
FROM log_sensor
ORDER BY perangkat_id, tanggal;

-- Langkah 2: pulau per perangkat -- kelompokkan dengan kunci_tanggal.
WITH ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           (tanggal
                - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal)::integer) AS kunci_pulau
    FROM log_sensor
)
SELECT perangkat_id,
       min(tanggal) AS mulai,
       max(tanggal) AS selesai,
       count(*) AS panjang_hari,
       sum(jumlah_bacaan) AS total_bacaan
FROM ber_nomor
GROUP BY perangkat_id, kunci_pulau
ORDER BY perangkat_id, mulai;

Output yang Dihasilkan:

text
 perangkat_id |  tanggal   | rn_global | rn_perangkat | selisih_rn | kunci_tanggal 
--------------+------------+-----------+--------------+------------+---------------
            1 | 2026-01-01 |         1 |            1 |          0 | 2025-12-31
            1 | 2026-01-02 |         2 |            2 |          0 | 2025-12-31
            1 | 2026-01-03 |         4 |            3 |          1 | 2025-12-31
            1 | 2026-01-04 |         6 |            4 |          2 | 2025-12-31
            1 | 2026-01-07 |         7 |            5 |          2 | 2026-01-02
            1 | 2026-01-08 |         8 |            6 |          2 | 2026-01-02
            1 | 2026-01-09 |        10 |            7 |          3 | 2026-01-02
            1 | 2026-01-13 |        15 |            8 |          7 | 2026-01-05
            1 | 2026-01-14 |        16 |            9 |          7 | 2026-01-05
            1 | 2026-01-18 |        19 |           10 |          9 | 2026-01-08
            2 | 2026-01-02 |         3 |            1 |          2 | 2026-01-01
            2 | 2026-01-03 |         5 |            2 |          3 | 2026-01-01
            2 | 2026-01-08 |         9 |            3 |          6 | 2026-01-05
            2 | 2026-01-09 |        11 |            4 |          7 | 2026-01-05
            2 | 2026-01-10 |        12 |            5 |          7 | 2026-01-05
            2 | 2026-01-11 |        13 |            6 |          7 | 2026-01-05
            2 | 2026-01-12 |        14 |            7 |          7 | 2026-01-05
            2 | 2026-01-16 |        17 |            8 |          9 | 2026-01-08
            2 | 2026-01-17 |        18 |            9 |          9 | 2026-01-08
            2 | 2026-01-18 |        20 |           10 |         10 | 2026-01-08
(20 rows)

 perangkat_id |   mulai    |  selesai   | panjang_hari | total_bacaan 
--------------+------------+------------+--------------+--------------
            1 | 2026-01-01 | 2026-01-04 |            4 |          121
            1 | 2026-01-07 | 2026-01-09 |            3 |           92
            1 | 2026-01-13 | 2026-01-14 |            2 |           58
            1 | 2026-01-18 | 2026-01-18 |            1 |           34
            2 | 2026-01-02 | 2026-01-03 |            2 |           47
            2 | 2026-01-08 | 2026-01-12 |            5 |          185
            2 | 2026-01-16 | 2026-01-18 |            3 |           91
(7 rows)

Bandingkan dua kolom kunci pada pulau pertama perangkat 1 (1–4 Januari): kunci_tanggal membaca 2025-12-31 empat kali, tetapi selisih_rn berubah-ubah menjadi 0, 0, 1, 2. Penyebabnya terlihat pada rn_global: tanggal 2 dan 3 Januari juga dihuni baris perangkat 2, sehingga penghitung global maju lebih cepat daripada penghitung partisi. Selisih dua ROW_NUMBER() memang hanya beku ketika baris kelompok lain tidak pernah menyisip — misalnya bila setiap kelompok memegang blok tanggalnya sendiri tanpa tumpang tindih.

Teknik Kedua: Deteksi Batas dengan LAG #

Trik selisih mengenal satu definisi kebersamaan saja: “tepat satu hari”. Ketika aturannya berubah — sensor boleh mati sampai tiga hari sebelum dianggap putus, atau streak kehadiran memaafkan satu hari bolos — trik selisih tidak bisa diatur ulang. Teknik kedua bisa: LAG membawa tanggal sebelumnya ke baris sekarang, selisihnya dibandingkan dengan ambang apa pun yang Anda pilih, dan bendera “batas pulau baru” dijumlahkan secara berjalan untuk menjadi nomor pulau:

sql
deteksi-lag.sql
-- Teknik 2: deteksi batas pulau dengan LAG, lalu penomoran pulau lewat
-- jumlah berjalan (running SUM) dari bendera batas. Definisi "jeda" menjadi
-- eksplisit -- itulah keunggulan LAG dibanding trik selisih tanggal.

-- Langkah 1: tiap baris membawa tanggal sebelumnya dari perangkat yang
-- sama; selisih lebih dari 1 hari menandai baris ini sebagai batas pulau.
SELECT perangkat_id,
       tanggal,
       lag(tanggal) OVER w AS sebelumnya,
       tanggal - lag(tanggal) OVER w AS selisih_hari,
       CASE
           WHEN lag(tanggal) OVER w IS NULL THEN 1
           WHEN tanggal - lag(tanggal) OVER w > 1 THEN 1
           ELSE 0
       END AS batas_baru
FROM log_sensor
WINDOW w AS (PARTITION BY perangkat_id ORDER BY tanggal)
ORDER BY perangkat_id, tanggal;

-- Langkah 2: jumlah berjalan atas bendera batas = nomor pulau.
WITH bertanda AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           CASE
               WHEN lag(tanggal) OVER w IS NULL THEN 1
               WHEN tanggal - lag(tanggal) OVER w > 1 THEN 1
               ELSE 0
           END AS batas_baru
    FROM log_sensor
    WINDOW w AS (PARTITION BY perangkat_id ORDER BY tanggal)
)
SELECT perangkat_id,
       tanggal,
       batas_baru,
       sum(batas_baru) OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS pulau_ke
FROM bertanda
ORDER BY perangkat_id, tanggal;

-- Langkah 3: ringkasan per pulau dari nomor pulau tersebut.
WITH bertanda AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           CASE
               WHEN lag(tanggal) OVER w IS NULL THEN 1
               WHEN tanggal - lag(tanggal) OVER w > 1 THEN 1
               ELSE 0
           END AS batas_baru
    FROM log_sensor
    WINDOW w AS (PARTITION BY perangkat_id ORDER BY tanggal)
),
ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           sum(batas_baru) OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS pulau_ke
    FROM bertanda
)
SELECT perangkat_id,
       pulau_ke,
       min(tanggal) AS mulai,
       max(tanggal) AS selesai,
       count(*) AS panjang_hari,
       sum(jumlah_bacaan) AS total_bacaan
FROM ber_nomor
GROUP BY perangkat_id, pulau_ke
ORDER BY perangkat_id, pulau_ke;

-- Variasi: definisi jeda longgar. Sensor 2 dianggap masih "menyala" bila
-- jeda ke bacaan berikutnya tidak lebih dari 4 hari (3 hari kosong
-- dimaafkan) -- pulau 8-12 Jan dan 16-18 Jan melebur menjadi satu.
WITH bertanda AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           CASE
               WHEN lag(tanggal) OVER w IS NULL THEN 1
               WHEN tanggal - lag(tanggal) OVER w > 4 THEN 1
               ELSE 0
           END AS batas_baru
    FROM log_sensor
    WINDOW w AS (PARTITION BY perangkat_id ORDER BY tanggal)
),
ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           sum(batas_baru) OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS pulau_ke
    FROM bertanda
)
SELECT perangkat_id,
       pulau_ke,
       min(tanggal) AS mulai,
       max(tanggal) AS selesai,
       count(*) AS panjang_hari
FROM ber_nomor
WHERE perangkat_id = 2
GROUP BY perangkat_id, pulau_ke
ORDER BY perangkat_id, pulau_ke;

Output yang Dihasilkan:

text
 perangkat_id |  tanggal   | sebelumnya | selisih_hari | batas_baru 
--------------+------------+------------+--------------+------------
            1 | 2026-01-01 |            |              |          1
            1 | 2026-01-02 | 2026-01-01 |            1 |          0
            1 | 2026-01-03 | 2026-01-02 |            1 |          0
            1 | 2026-01-04 | 2026-01-03 |            1 |          0
            1 | 2026-01-07 | 2026-01-04 |            3 |          1
            1 | 2026-01-08 | 2026-01-07 |            1 |          0
            1 | 2026-01-09 | 2026-01-08 |            1 |          0
            1 | 2026-01-13 | 2026-01-09 |            4 |          1
            1 | 2026-01-14 | 2026-01-13 |            1 |          0
            1 | 2026-01-18 | 2026-01-14 |            4 |          1
            2 | 2026-01-02 |            |              |          1
            2 | 2026-01-03 | 2026-01-02 |            1 |          0
            2 | 2026-01-08 | 2026-01-03 |            5 |          1
            2 | 2026-01-09 | 2026-01-08 |            1 |          0
            2 | 2026-01-10 | 2026-01-09 |            1 |          0
            2 | 2026-01-11 | 2026-01-10 |            1 |          0
            2 | 2026-01-12 | 2026-01-11 |            1 |          0
            2 | 2026-01-16 | 2026-01-12 |            4 |          1
            2 | 2026-01-17 | 2026-01-16 |            1 |          0
            2 | 2026-01-18 | 2026-01-17 |            1 |          0
(20 rows)

 perangkat_id |  tanggal   | batas_baru | pulau_ke 
--------------+------------+------------+----------
            1 | 2026-01-01 |          1 |        1
            1 | 2026-01-02 |          0 |        1
            1 | 2026-01-03 |          0 |        1
            1 | 2026-01-04 |          0 |        1
            1 | 2026-01-07 |          1 |        2
            1 | 2026-01-08 |          0 |        2
            1 | 2026-01-09 |          0 |        2
            1 | 2026-01-13 |          1 |        3
            1 | 2026-01-14 |          0 |        3
            1 | 2026-01-18 |          1 |        4
            2 | 2026-01-02 |          1 |        1
            2 | 2026-01-03 |          0 |        1
            2 | 2026-01-08 |          1 |        2
            2 | 2026-01-09 |          0 |        2
            2 | 2026-01-10 |          0 |        2
            2 | 2026-01-11 |          0 |        2
            2 | 2026-01-12 |          0 |        2
            2 | 2026-01-16 |          1 |        3
            2 | 2026-01-17 |          0 |        3
            2 | 2026-01-18 |          0 |        3
(20 rows)

 perangkat_id | pulau_ke |   mulai    |  selesai   | panjang_hari | total_bacaan 
--------------+----------+------------+------------+--------------+--------------
            1 |        1 | 2026-01-01 | 2026-01-04 |            4 |          121
            1 |        2 | 2026-01-07 | 2026-01-09 |            3 |           92
            1 |        3 | 2026-01-13 | 2026-01-14 |            2 |           58
            1 |        4 | 2026-01-18 | 2026-01-18 |            1 |           34
            2 |        1 | 2026-01-02 | 2026-01-03 |            2 |           47
            2 |        2 | 2026-01-08 | 2026-01-12 |            5 |          185
            2 |        3 | 2026-01-16 | 2026-01-18 |            3 |           91
(7 rows)

 perangkat_id | pulau_ke |   mulai    |  selesai   | panjang_hari 
--------------+----------+------------+------------+--------------
            2 |        1 | 2026-01-02 | 2026-01-03 |            2
            2 |        2 | 2026-01-08 | 2026-01-18 |            8
(2 rows)

Empat bagian file tersebut membentuk satu alur:

Langkah 1 menghasilkan kolom sebelumnya dan selisih_hari; baris pertama tiap perangkat berharga NULL sehingga CASE menandainya batas_baru = 1 — setiap deret memulai pulau pertamanya.
Langkah 2 menjumlahkan bendera itu secara berjalan: sum(batas_baru) OVER (PARTITION BY perangkat_id ORDER BY tanggal) menghasilkan 1, 1, 1, 1 lalu melompat ke 2 pada 7 Januari — nomor pulau lahir dari jumlah kumulatif batas.
Langkah 3 cukup GROUP BY perangkat_id, pulau_ke untuk merangkum — hasilnya identik dengan teknik selisih, dan nomor pulau yang berurut menjadi bonus (teknik selisih menghasilkan kunci yang nilainya asing).
Variasi ambang hanya mengubah > 1 menjadi > 4: celah 13–15 Januari (tiga hari kosong, selisih empat) kini dimaafkan, dan pulau perangkat 2 melebur menjadi 8–18 Januari dengan panjang 8 hari. Klausa WINDOW w dipakai agar definisi jendela yang sama tidak ditulis empat kali.

Celah: Komplemen Terhadap Deret Penuh #

Pulau menjawab “kapan data ada”. Pertanyaan “kapan data tidak ada” dijawab lewat komplemennya: bangkitkan seluruh kalender periode observasi dengan generate_series, LEFT JOIN ke data, dan ambil baris tanpa pasangan. Tanggal-tanggal kosong yang terkumpul itu sendiri membentuk deret berlubang — pulau pada komplemen adalah celah pada data asli, sehingga trik selisih dipakai ulang untuk memadatkannya menjadi rentang:

sql
celah-deret.sql
-- Celah = komplemen pulau terhadap rentang penuh. Bangkitkan seluruh
-- kalender Januari 2026 untuk tiap perangkat dengan generate_series, lalu
-- anti-join ke log_sensor: tanggal tanpa pasangan = hari tanpa bacaan.

-- Langkah 1: deret penuh LEFT JOIN data -- baris yang tidak menemukan
-- pasangan (log_sensor NULL) adalah tanggal kosong.
SELECT k.perangkat_id,
       t.tanggal::date AS tanggal
FROM (SELECT DISTINCT perangkat_id FROM log_sensor ORDER BY 1) AS k
CROSS JOIN generate_series(DATE '2026-01-01', DATE '2026-01-18', INTERVAL '1 day') AS t(tanggal)
LEFT JOIN log_sensor s
       ON s.perangkat_id = k.perangkat_id
      AND s.tanggal = t.tanggal::date
WHERE s.perangkat_id IS NULL
ORDER BY k.perangkat_id, t.tanggal;

-- Langkah 2: tanggal-tanggal kosong itu sendiri membentuk pulau -- terapkan
-- kembali trik selisih tanggal - nomor baris pada komplemen, lalu ringkas
-- menjadi rentang celah per perangkat.
WITH kalender AS (
    SELECT k.perangkat_id,
           t.tanggal::date AS tanggal
    FROM (SELECT DISTINCT perangkat_id FROM log_sensor ORDER BY 1) AS k
    CROSS JOIN generate_series(DATE '2026-01-01', DATE '2026-01-18', INTERVAL '1 day') AS t(tanggal)
    LEFT JOIN log_sensor s
           ON s.perangkat_id = k.perangkat_id
          AND s.tanggal = t.tanggal::date
    WHERE s.perangkat_id IS NULL
),
ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           (tanggal
                - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal)::integer) AS kunci_celah
    FROM kalender
)
SELECT perangkat_id,
       min(tanggal) AS mulai_celah,
       max(tanggal) AS selesai_celah,
       count(*) AS panjang_hari
FROM ber_nomor
GROUP BY perangkat_id, kunci_celah
ORDER BY perangkat_id, mulai_celah;

Output yang Dihasilkan:

text
 perangkat_id |  tanggal   
--------------+------------
            1 | 2026-01-05
            1 | 2026-01-06
            1 | 2026-01-10
            1 | 2026-01-11
            1 | 2026-01-12
            1 | 2026-01-15
            1 | 2026-01-16
            1 | 2026-01-17
            2 | 2026-01-01
            2 | 2026-01-04
            2 | 2026-01-05
            2 | 2026-01-06
            2 | 2026-01-07
            2 | 2026-01-13
            2 | 2026-01-14
            2 | 2026-01-15
(16 rows)

 perangkat_id | mulai_celah | selesai_celah | panjang_hari 
--------------+-------------+---------------+--------------
            1 | 2026-01-05  | 2026-01-06    |            2
            1 | 2026-01-10  | 2026-01-12    |            3
            1 | 2026-01-15  | 2026-01-17    |            3
            2 | 2026-01-01  | 2026-01-01    |            1
            2 | 2026-01-04  | 2026-01-07    |            4
            2 | 2026-01-13  | 2026-01-15    |            3
(6 rows)

Subquery SELECT DISTINCT perangkat_id menjadi sisi kiri CROSS JOIN sehingga kalender 1–18 Januari dibangkitkan untuk kedua perangkat; generate_series mengembalikan timestamp, oleh karena itu ::date diperlukan agar bisa dibandingkan dengan kolom tanggal. Hasil akhirnya enam rentang celah: perangkat 1 kehilangan 5–6, 10–12, dan 15–17 Januari; perangkat 2 kehilangan 1, 4–7, dan 13–15 Januari. Verifikasi silangnya pas sempurna — celah perangkat 1 adalah pulau perangkat 2 dan sebaliknya pada tanggal bersama.

Penerapan dan Streak Terpanjang #

Tiga penerapan yang paling sering bertemu pola ini di pekerjaan sehari-hari:

Penerapan	Deret data	Pulau menjawab	Celah menjawab
Streak kehadiran kelas	tanggal hadir per siswa	rentetan hadir berturut-turut	hari bolos
Uptime sensor/server	timestamp bacaan per perangkat	periode perangkat sehat	periode mati
Masa berlaku langganan	periode aktif per pelanggan	durasi loyalitas tanpa putus	jeda berhenti lalu kembali

Streak terpanjang adalah turunan langsung dari tabel pulau: hitung pulau, urutkan menurut panjang, ambil yang teratas per kelompok. DISTINCT ON milik PostgreSQL menjatuhkan hasil itu dalam satu query:

sql
streak-terpanjang.sql
-- Penerapan penutup: streak terpanjang = pulau terpanjang per perangkat.
-- DISTINCT ON menjatuhkan satu baris terpilih per perangkat: pulau dengan
-- panjang_hari terbesar (tie-break tanggal mulai agar deterministik).
WITH ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           (tanggal
                - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal)::integer) AS kunci_pulau
    FROM log_sensor
),
pulau AS (
    SELECT perangkat_id,
           min(tanggal) AS mulai,
           max(tanggal) AS selesai,
           count(*) AS panjang_hari,
           sum(jumlah_bacaan) AS total_bacaan
    FROM ber_nomor
    GROUP BY perangkat_id, kunci_pulau
)
SELECT DISTINCT ON (perangkat_id)
       perangkat_id,
       mulai,
       selesai,
       panjang_hari,
       total_bacaan
FROM pulau
ORDER BY perangkat_id, panjang_hari DESC, mulai;

Output yang Dihasilkan:

text
 perangkat_id |   mulai    |  selesai   | panjang_hari | total_bacaan 
--------------+------------+------------+--------------+--------------
            1 | 2026-01-01 | 2026-01-04 |            4 |          121
            2 | 2026-01-08 | 2026-01-12 |            5 |          185
(2 rows)

Hasilnya membaca seperti laporan: sensor Gudang Jakarta sekian waktu terpanjangnya empat hari (1–4 Januari, 121 bacaan), sensor Gudang Bandung lima hari (8–12 Januari, 185 bacaan). Untuk streak kehadiran, ganti perangkat_id dengan siswa_id; untuk langganan, ganti tanggal hadir dengan periode aktif.

Dua teknik ini membangun gudang dasar untuk pertanyaan analitik berbentuk “kapan”. Pertanyaan analitik berikutnya berbentuk “siapa dan berapa”: funnel pembelian, retensi kohort, segmentasi RFM, dan sesi kunjungan — semua membutuhkan pipeline bertingkat yang menyusun window function, agregasi FILTER, dan CTE menjadi satu query utuh. Pembahasannya tuntas di chapter Analytical Query .

Referensi #

Window Functions — perilaku ROW_NUMBER, LAG, dan jumlah berjalan SUM dalam jendela.
Set Returning Functions — generate_series untuk membangkitkan deret kalender penuh.

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- Tabel latihan chapter Gap and Island: log bacaan sensor suhu dua gudang.
-- Satu baris = satu hari ketika sensor berhasil mengirim data; hari tanpa
-- baris = sensor tidak membaca. Tanggal sengaja berlubang (celah) demi demo.
-- Perangkat 1 = sensor Gudang Jakarta, perangkat 2 = Gudang Bandung.
CREATE TABLE log_sensor (
    perangkat_id  integer NOT NULL,
    tanggal       date NOT NULL,
    jumlah_bacaan integer NOT NULL,
    PRIMARY KEY (perangkat_id, tanggal)
);

-- Perangkat 1: pulau 4, 3, 2, 1 hari -- celah 2, 3, 3 hari.
INSERT INTO log_sensor (perangkat_id, tanggal, jumlah_bacaan) VALUES
    (1, DATE '2026-01-01', 30),
    (1, DATE '2026-01-02', 32),
    (1, DATE '2026-01-03', 28),
    (1, DATE '2026-01-04', 31),
    (1, DATE '2026-01-07', 29),
    (1, DATE '2026-01-08', 33),
    (1, DATE '2026-01-09', 30),
    (1, DATE '2026-01-13', 27),
    (1, DATE '2026-01-14', 31),
    (1, DATE '2026-01-18', 34);

-- Perangkat 2: pulau 2, 5, 3 hari -- celah 1, 4, 3 hari.
INSERT INTO log_sensor (perangkat_id, tanggal, jumlah_bacaan) VALUES
    (2, DATE '2026-01-02', 22),
    (2, DATE '2026-01-03', 25),
    (2, DATE '2026-01-08', 36),
    (2, DATE '2026-01-09', 38),
    (2, DATE '2026-01-10', 35),
    (2, DATE '2026-01-11', 37),
    (2, DATE '2026-01-12', 39),
    (2, DATE '2026-01-16', 28),
    (2, DATE '2026-01-17', 30),
    (2, DATE '2026-01-18', 33);

SELECT perangkat_id, tanggal, jumlah_bacaan
FROM log_sensor
ORDER BY perangkat_id, tanggal;

sql
-- Celah = komplemen pulau terhadap rentang penuh. Bangkitkan seluruh
-- kalender Januari 2026 untuk tiap perangkat dengan generate_series, lalu
-- anti-join ke log_sensor: tanggal tanpa pasangan = hari tanpa bacaan.

-- Langkah 1: deret penuh LEFT JOIN data -- baris yang tidak menemukan
-- pasangan (log_sensor NULL) adalah tanggal kosong.
SELECT k.perangkat_id,
       t.tanggal::date AS tanggal
FROM (SELECT DISTINCT perangkat_id FROM log_sensor ORDER BY 1) AS k
CROSS JOIN generate_series(DATE '2026-01-01', DATE '2026-01-18', INTERVAL '1 day') AS t(tanggal)
LEFT JOIN log_sensor s
       ON s.perangkat_id = k.perangkat_id
      AND s.tanggal = t.tanggal::date
WHERE s.perangkat_id IS NULL
ORDER BY k.perangkat_id, t.tanggal;

-- Langkah 2: tanggal-tanggal kosong itu sendiri membentuk pulau -- terapkan
-- kembali trik selisih tanggal - nomor baris pada komplemen, lalu ringkas
-- menjadi rentang celah per perangkat.
WITH kalender AS (
    SELECT k.perangkat_id,
           t.tanggal::date AS tanggal
    FROM (SELECT DISTINCT perangkat_id FROM log_sensor ORDER BY 1) AS k
    CROSS JOIN generate_series(DATE '2026-01-01', DATE '2026-01-18', INTERVAL '1 day') AS t(tanggal)
    LEFT JOIN log_sensor s
           ON s.perangkat_id = k.perangkat_id
          AND s.tanggal = t.tanggal::date
    WHERE s.perangkat_id IS NULL
),
ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           (tanggal
                - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal)::integer) AS kunci_celah
    FROM kalender
)
SELECT perangkat_id,
       min(tanggal) AS mulai_celah,
       max(tanggal) AS selesai_celah,
       count(*) AS panjang_hari
FROM ber_nomor
GROUP BY perangkat_id, kunci_celah
ORDER BY perangkat_id, mulai_celah;

sql
-- Teknik 2: deteksi batas pulau dengan LAG, lalu penomoran pulau lewat
-- jumlah berjalan (running SUM) dari bendera batas. Definisi "jeda" menjadi
-- eksplisit -- itulah keunggulan LAG dibanding trik selisih tanggal.

-- Langkah 1: tiap baris membawa tanggal sebelumnya dari perangkat yang
-- sama; selisih lebih dari 1 hari menandai baris ini sebagai batas pulau.
SELECT perangkat_id,
       tanggal,
       lag(tanggal) OVER w AS sebelumnya,
       tanggal - lag(tanggal) OVER w AS selisih_hari,
       CASE
           WHEN lag(tanggal) OVER w IS NULL THEN 1
           WHEN tanggal - lag(tanggal) OVER w > 1 THEN 1
           ELSE 0
       END AS batas_baru
FROM log_sensor
WINDOW w AS (PARTITION BY perangkat_id ORDER BY tanggal)
ORDER BY perangkat_id, tanggal;

-- Langkah 2: jumlah berjalan atas bendera batas = nomor pulau.
WITH bertanda AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           CASE
               WHEN lag(tanggal) OVER w IS NULL THEN 1
               WHEN tanggal - lag(tanggal) OVER w > 1 THEN 1
               ELSE 0
           END AS batas_baru
    FROM log_sensor
    WINDOW w AS (PARTITION BY perangkat_id ORDER BY tanggal)
)
SELECT perangkat_id,
       tanggal,
       batas_baru,
       sum(batas_baru) OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS pulau_ke
FROM bertanda
ORDER BY perangkat_id, tanggal;

-- Langkah 3: ringkasan per pulau dari nomor pulau tersebut.
WITH bertanda AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           CASE
               WHEN lag(tanggal) OVER w IS NULL THEN 1
               WHEN tanggal - lag(tanggal) OVER w > 1 THEN 1
               ELSE 0
           END AS batas_baru
    FROM log_sensor
    WINDOW w AS (PARTITION BY perangkat_id ORDER BY tanggal)
),
ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           sum(batas_baru) OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS pulau_ke
    FROM bertanda
)
SELECT perangkat_id,
       pulau_ke,
       min(tanggal) AS mulai,
       max(tanggal) AS selesai,
       count(*) AS panjang_hari,
       sum(jumlah_bacaan) AS total_bacaan
FROM ber_nomor
GROUP BY perangkat_id, pulau_ke
ORDER BY perangkat_id, pulau_ke;

-- Variasi: definisi jeda longgar. Sensor 2 dianggap masih "menyala" bila
-- jeda ke bacaan berikutnya tidak lebih dari 4 hari (3 hari kosong
-- dimaafkan) -- pulau 8-12 Jan dan 16-18 Jan melebur menjadi satu.
WITH bertanda AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           CASE
               WHEN lag(tanggal) OVER w IS NULL THEN 1
               WHEN tanggal - lag(tanggal) OVER w > 4 THEN 1
               ELSE 0
           END AS batas_baru
    FROM log_sensor
    WINDOW w AS (PARTITION BY perangkat_id ORDER BY tanggal)
),
ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           sum(batas_baru) OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS pulau_ke
    FROM bertanda
)
SELECT perangkat_id,
       pulau_ke,
       min(tanggal) AS mulai,
       max(tanggal) AS selesai,
       count(*) AS panjang_hari
FROM ber_nomor
WHERE perangkat_id = 2
GROUP BY perangkat_id, pulau_ke
ORDER BY perangkat_id, pulau_ke;

sql
-- Generalisasi untuk BANYAK kelompok (dua perangkat sekaligus):
-- PARTITION BY perangkat_id memisahkan deret tiap perangkat.
-- Kolom selisih_rn memperlihatkan bentuk lain trik selisih -- selisih dua
-- ROW_NUMBER() -- yang hanya konstan bila baris perangkat lain tidak
-- menyisip di antara tanggal-tanggal satu pulau. Pada data ini tanggal
-- kedua perangkat tumpang tindih, sehingga selisih_rn pecah di tengah
-- pulau, sementara kunci_tanggal tetap konstan per pulau.

-- Langkah 1: meja kerja dengan semua varian kunci per baris.
SELECT perangkat_id,
       tanggal,
       row_number() OVER (ORDER BY tanggal, perangkat_id) AS rn_global,
       row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS rn_perangkat,
       row_number() OVER (ORDER BY tanggal, perangkat_id)
           - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal) AS selisih_rn,
       (tanggal
            - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal)::integer) AS kunci_tanggal
FROM log_sensor
ORDER BY perangkat_id, tanggal;

-- Langkah 2: pulau per perangkat -- kelompokkan dengan kunci_tanggal.
WITH ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           (tanggal
                - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal)::integer) AS kunci_pulau
    FROM log_sensor
)
SELECT perangkat_id,
       min(tanggal) AS mulai,
       max(tanggal) AS selesai,
       count(*) AS panjang_hari,
       sum(jumlah_bacaan) AS total_bacaan
FROM ber_nomor
GROUP BY perangkat_id, kunci_pulau
ORDER BY perangkat_id, mulai;

sql
-- Teknik 1 langkah demi langkah pada SATU deret (perangkat 1 saja):
-- kunci pulau = tanggal - nomor baris. Kedua operan naik satu per baris
-- pada tanggal berurutan, sehingga selisihnya beku di dalam satu pulau.

-- Langkah 1: nomori setiap baris sesuai urutan tanggal.
SELECT tanggal,
       jumlah_bacaan,
       row_number() OVER (ORDER BY tanggal) AS rn
FROM log_sensor
WHERE perangkat_id = 1
ORDER BY tanggal;

-- Langkah 2: tanggal dikurangi nomor baris. Perhatikan kolom kunci_pulau:
-- nilainya beku selama tanggal bertambah tepat satu hari, dan melompat
-- setiap kali ada hari yang hilang.
SELECT tanggal,
       row_number() OVER (ORDER BY tanggal) AS rn,
       (tanggal - row_number() OVER (ORDER BY tanggal)::integer) AS kunci_pulau
FROM log_sensor
WHERE perangkat_id = 1
ORDER BY tanggal;

-- Langkah 3: kelompokkan baris menurut kunci_pulau -- tiap kelompok
-- adalah satu pulau, diringkas jadi tanggal mulai, selesai, dan panjang.
WITH ber_nomor AS (
    SELECT tanggal,
           jumlah_bacaan,
           (tanggal - row_number() OVER (ORDER BY tanggal)::integer) AS kunci_pulau
    FROM log_sensor
    WHERE perangkat_id = 1
)
SELECT min(tanggal) AS mulai,
       max(tanggal) AS selesai,
       count(*) AS panjang_hari,
       sum(jumlah_bacaan) AS total_bacaan
FROM ber_nomor
GROUP BY kunci_pulau
ORDER BY mulai;

sql
-- Penerapan penutup: streak terpanjang = pulau terpanjang per perangkat.
-- DISTINCT ON menjatuhkan satu baris terpilih per perangkat: pulau dengan
-- panjang_hari terbesar (tie-break tanggal mulai agar deterministik).
WITH ber_nomor AS (
    SELECT perangkat_id,
           tanggal,
           jumlah_bacaan,
           (tanggal
                - row_number() OVER (PARTITION BY perangkat_id ORDER BY tanggal)::integer) AS kunci_pulau
    FROM log_sensor
),
pulau AS (
    SELECT perangkat_id,
           min(tanggal) AS mulai,
           max(tanggal) AS selesai,
           count(*) AS panjang_hari,
           sum(jumlah_bacaan) AS total_bacaan
    FROM ber_nomor
    GROUP BY perangkat_id, kunci_pulau
)
SELECT DISTINCT ON (perangkat_id)
       perangkat_id,
       mulai,
       selesai,
       panjang_hari,
       total_bacaan
FROM pulau
ORDER BY perangkat_id, panjang_hari DESC, mulai;