Section Window Functions telah melengkapi kotak peralatannya: OVER, PARTITION BY, fungsi ranking, LAG/LEAD, running total, rata-rata bergerak, hingga spesifikasi frame. Chapter lanjutan ini menggabungkan alat-alat itu menjadi teknik bertingkat yang menjadi pembeda query analis menengah dan lanjutan: window function yang mengolah hasil window function lain (lewat CTE), pembedaan ROWS versus RANGE lengkap dengan klausa EXCLUDE, penamaan jendela ganda dalam satu query, rasio terhadap nilai sebelumnya untuk analisis jeda event, dan pemakaian window function di dalam UPDATE. Seluruh contoh memakai domain konsisten section ini — kunjungan harian dan log sesi dengan tanggal tetap.

Dataset: Kunjungan Harian per Kanal #

Tabel kunjungan_harian mencatat pengunjung harian dua kanal selama enam hari pertama Januari 2026. Kanal web menurun perlahan sementara kanal aplikasi menanjak — pola yang sengaja dipilih agar total kumulatifnya akan saling menyalip di tengah deret, bahan utama demo jendela bertingkat:

sql buat-tabel.sql 
-- Tabel latihan chapter Window Function Lanjutan: kunjungan harian per kanal.
-- Kanal web menurun, kanal aplikasi menanjak -- pola yang membuat posisi
-- kumulatifnya akan BERGANTI di tengah deret (bahan demo jendela bertingkat).
CREATE TABLE kunjungan_harian (
    tanggal    date NOT NULL,
    kanal      text NOT NULL,
    pengunjung integer NOT NULL,
    PRIMARY KEY (tanggal, kanal)
);

INSERT INTO kunjungan_harian (tanggal, kanal, pengunjung) VALUES
    (DATE '2026-01-01', 'web',      120),
    (DATE '2026-01-02', 'web',      110),
    (DATE '2026-01-03', 'web',      100),
    (DATE '2026-01-04', 'web',       95),
    (DATE '2026-01-05', 'web',       90),
    (DATE '2026-01-06', 'web',       85),
    (DATE '2026-01-01', 'aplikasi',  80),
    (DATE '2026-01-02', 'aplikasi',  95),
    (DATE '2026-01-03', 'aplikasi', 130),
    (DATE '2026-01-04', 'aplikasi', 160),
    (DATE '2026-01-05', 'aplikasi', 175),
    (DATE '2026-01-06', 'aplikasi', 190);

SELECT tanggal, kanal, pengunjung
FROM kunjungan_harian
ORDER BY tanggal, kanal;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 12
  tanggal   |  kanal   | pengunjung 
------------+----------+------------
 2026-01-01 | aplikasi |         80
 2026-01-01 | web      |        120
 2026-01-02 | aplikasi |         95
 2026-01-02 | web      |        110
 2026-01-03 | aplikasi |        130
 2026-01-03 | web      |        100
 2026-01-04 | aplikasi |        160
 2026-01-04 | web      |         95
 2026-01-05 | aplikasi |        175
 2026-01-05 | web      |         90
 2026-01-06 | aplikasi |        190
 2026-01-06 | web      |         85
(12 rows)

Jendela Bertingkat: Window atas Hasil Window #

Pertanyaan analitik berlapis seperti “setiap hari, kanal mana yang total kumulatifnya memimpin?” membutuhkan dua lapis window: sum(...) OVER untuk total kumulatif per kanal, lalu dense_rank() OVER untuk memeringkat kumulatif itu per tanggal. SQL tidak mengizinkan jendela bersarang dalam satu SELECT, jadi lapisan-lapisannya dijembatani CTE — hasil jendela pertama menjadi baris biasa bagi jendela kedua:

sql jendela-bertingkat.sql 
-- Jendela bertingkat: window function yang mengolah HASIL window function
-- sebelumnya. Sum kumulatif per kanal (jendela ke-1) lalu dense_rank atas
-- total kumulatif itu (jendela ke-2) -- dua lapis dua CTE.
WITH kumulatif AS (
    SELECT tanggal,
           kanal,
           pengunjung,
           sum(pengunjung) OVER (
               PARTITION BY kanal ORDER BY tanggal
           ) AS total_kumulatif
    FROM kunjungan_harian
),
peringkat AS (
    SELECT tanggal,
           kanal,
           pengunjung,
           total_kumulatif,
           dense_rank() OVER (
               PARTITION BY tanggal ORDER BY total_kumulatif DESC
           ) AS posisi_kumulatif
    FROM kumulatif
)
SELECT tanggal,
       kanal,
       pengunjung,
       total_kumulatif,
       posisi_kumulatif
FROM peringkat
ORDER BY tanggal, posisi_kumulatif;

Output yang Dihasilkan:

text 
  tanggal   |  kanal   | pengunjung | total_kumulatif | posisi_kumulatif 
------------+----------+------------+-----------------+------------------
 2026-01-01 | web      |        120 |             120 |                1
 2026-01-01 | aplikasi |         80 |              80 |                2
 2026-01-02 | web      |        110 |             230 |                1
 2026-01-02 | aplikasi |         95 |             175 |                2
 2026-01-03 | web      |        100 |             330 |                1
 2026-01-03 | aplikasi |        130 |             305 |                2
 2026-01-04 | aplikasi |        160 |             465 |                1
 2026-01-04 | web      |         95 |             425 |                2
 2026-01-05 | aplikasi |        175 |             640 |                1
 2026-01-05 | web      |         90 |             515 |                2
 2026-01-06 | aplikasi |        190 |             830 |                1
 2026-01-06 | web      |         85 |             600 |                2
(12 rows)

Mari kita bedah dua lapisnya:

  1. CTE kumulatif menghitung sum(pengunjung) OVER (PARTITION BY kanal ORDER BY tanggal) — running total terpisah untuk tiap kanal: web menumpuk 120, 230, 330, 425, 515, 600; aplikasi menumpuk 80, 175, 305, 465, 640, 830.
  2. CTE peringkat memperlakukan kolom total_kumulatif sebagai kolom biasa dan memeringkatnya per tanggal: dense_rank() OVER (PARTITION BY tanggal ORDER BY total_kumulatif DESC).
  3. Hasilnya memperlihatkan pergantian posisi yang tidak terlihat pada angka harian: sampai 3 Januari web masih memimpin (330 lawan 305); pada 4 Januari aplikasi menyalip (465 lawan 425) dan tidak lagi dilepaskan.

Mencoba menyarangkan kedua jendela itu langsung dalam satu ekspresi ditolak parser — bukti bahwa “window atas window” memang harus lewat CTE atau derived table:

sql jendela-bersarang-error.sql 
-- Kenapa harus lewat CTE: window function TIDAK BOLEH bersarang langsung.
-- Membungkus dense_rank di dalam definisi jendela sum ditolak parser.
SELECT sum(pengunjung) OVER (
           ORDER BY dense_rank() OVER (ORDER BY pengunjung)
       ) AS total_lengkap
FROM kunjungan_harian;

Output yang Dihasilkan:

text 
psql:jendela-bersarang-error.sql:6: ERROR:  window functions are not allowed in window definitions
LINE 2:            ORDER BY dense_rank() OVER (ORDER BY pengunjung)
                            ^

ROWS vs RANGE dan Peer #

Frame default window function bukan ROWS melainkan RANGE, dan perbedaannya hanya terlihat ketika nilai ORDER BY jendela ada yang seri — baris-baris seri itu disebut peer. Tabel kecil skor_kuis berisi dua pasang skor kembar (80-80 dan 70-70) untuk membedakan keduanya secara terlihat:

sql rows-vs-range.sql 
-- ROWS vs RANGE dan klausa EXCLUDE (PostgreSQL 11+).
-- Tabel kecil dengan skor seri -- "peer" -- untuk melihat beda keduanya.
CREATE TABLE skor_kuis (
    peserta text PRIMARY KEY,
    skor    integer NOT NULL
);

INSERT INTO skor_kuis (peserta, skor) VALUES
    ('Budi',  80),
    ('Siti',  80),
    ('Andi',  70),
    ('Dewi',  90),
    ('Rina',  70);

-- RANGE (default) memperlakukan baris dengan nilai ORDER BY sama sebagai
-- satu kelompok peer: Andi dan Rina (70) mendapat total yang sama, 140,
-- karena keduanya "masuk frame bersama-sama". ROWS berjalan baris demi
-- baris; pemecah urut peserta membuat hasilnya deterministik.
SELECT peserta,
       skor,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_range,
       sum(skor) OVER (ORDER BY skor, peserta
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_rows
FROM skor_kuis
ORDER BY skor, peserta;

-- Empat varian EXCLUDE pada frame RANGE UNBOUNDED PRECEDING:
-- NO OTHERS (default)  : semua baris skor <= skor sekarang.
-- CURRENT ROW           : peer lain tetap masuk, dirinya sendiri dibuang.
-- TIES                  : dirinya masuk, peer lain dibuang.
-- GROUP                 : dirinya dan seluruh peer dibuang (frame bisa kosong -> NULL).
SELECT peserta,
       skor,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS tanpa_exclude,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       EXCLUDE CURRENT ROW) AS exclude_current,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       EXCLUDE TIES) AS exclude_ties,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       EXCLUDE GROUP) AS exclude_group
FROM skor_kuis
ORDER BY skor, peserta;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 5
 peserta | skor | total_range | total_rows 
---------+------+-------------+------------
 Andi    |   70 |         140 |         70
 Rina    |   70 |         140 |        140
 Budi    |   80 |         300 |        220
 Siti    |   80 |         300 |        300
 Dewi    |   90 |         390 |        390
(5 rows)

 peserta | skor | tanpa_exclude | exclude_current | exclude_ties | exclude_group 
---------+------+---------------+-----------------+--------------+---------------
 Andi    |   70 |           140 |              70 |           70 |              
 Rina    |   70 |           140 |              70 |           70 |              
 Budi    |   80 |           300 |             220 |          220 |           140
 Siti    |   80 |           300 |             220 |          220 |           140
 Dewi    |   90 |           390 |             300 |          390 |           300
(5 rows)

Query pertama menempatkan perbedaannya pada dua baris: Andi dan Rina (skor 70) sama-sama menerima total_range 140 karena RANGE memasukkan seluruh peer sekaligus, sedangkan total_rows mereka 70 dan 140 — ROWS berjalan satu per satu mengikuti pemecah urut peserta. Query kedua membedah empat varian EXCLUDE (fitur PostgreSQL 11 ke atas) pada Budi (skor 80, satu peer Siti): tanpa exclude 300 (semua skor ≤ 80), EXCLUDE CURRENT ROW 220 (Siti ikut, Budi dibuang), EXCLUDE TIES 220 (Budi ikut, Siti dibuang), EXCLUDE GROUP 140 (keduanya dibuang). Baris Andi/Rina di kolom exclude_group menunjukkan kasus tepi yang berguna: frame yang menjadi kosong menghasilkan NULL, bukan nol. Dewi (skor 90, tanpa peer) memperlihatkan sifat EXCLUDE TIES yang tanpa peer tidak mengubah apa pun — nilainya tetap 390.

Dua Jendela Bernama: Rasio dan Jeda Antar-Event #

Ketika satu query memakai berulang kali definisi jendela yang sama, klausa WINDOW memberinya nama — dan nama itu sendiri boleh menjadi dasar bagi jendela lain yang lebih spesifik. File berikut menghitung tiga hal sekaligus dari log sesi: rasio durasi terhadap event sebelumnya (LAG lalu dibagi), klasifikasi jeda lebih dari 30 menit sebagai sesi baru, dan rata-rata bergerak dua baris:

sql jeda-dan-rasio.sql 
-- LAG untuk rasio terhadap nilai sebelumnya dan analisis jeda antar-event.
-- Klausa WINDOW memberi nama dua jendela: w1 = urutan waktu per pengguna,
-- w2 = w1 yang diberi frame 2 baris (rata-rata bergerak durasi).
CREATE TABLE log_sesi (
    id            integer PRIMARY KEY,
    pengguna_id   integer NOT NULL,
    waktu         timestamp NOT NULL,
    durasi_menit  integer NOT NULL
);

INSERT INTO log_sesi (id, pengguna_id, waktu, durasi_menit) VALUES
    (1, 1, TIMESTAMP '2026-01-10 10:00:00', 25),
    (2, 1, TIMESTAMP '2026-01-10 10:20:00', 10),
    (3, 1, TIMESTAMP '2026-01-10 10:25:00',  5),
    (4, 1, TIMESTAMP '2026-01-10 12:00:00', 40),
    (5, 2, TIMESTAMP '2026-01-10 09:00:00', 30),
    (6, 2, TIMESTAMP '2026-01-10 09:10:00',  8),
    (7, 2, TIMESTAMP '2026-01-10 09:15:00', 12),
    (8, 4, TIMESTAMP '2026-01-10 14:00:00', 20),
    (9, 4, TIMESTAMP '2026-01-10 16:30:00', 35),
    (10, 4, TIMESTAMP '2026-01-10 16:35:00', 6);

SELECT pengguna_id,
       waktu,
       durasi_menit,
       round(durasi_menit::numeric / lag(durasi_menit) OVER w1, 2) AS rasio_vs_sebelumnya,
       waktu - lag(waktu) OVER w1 AS jeda,
       CASE
           WHEN lag(waktu) OVER w1 IS NULL THEN 'event pertama'
           WHEN waktu - lag(waktu) OVER w1 > INTERVAL '30 minutes' THEN 'sesi baru'
           ELSE 'masih satu sesi'
       END AS status_sesi,
       round(avg(durasi_menit) OVER w2, 1) AS rata2_bergerak
FROM log_sesi
WINDOW w1 AS (PARTITION BY pengguna_id ORDER BY waktu),
       w2 AS (w1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY pengguna_id, waktu;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 10
 pengguna_id |        waktu        | durasi_menit | rasio_vs_sebelumnya |   jeda   |   status_sesi   | rata2_bergerak 
-------------+---------------------+--------------+---------------------+----------+-----------------+----------------
           1 | 2026-01-10 10:00:00 |           25 |                     |          | event pertama   |           25.0
           1 | 2026-01-10 10:20:00 |           10 |                0.40 | 00:20:00 | masih satu sesi |           17.5
           1 | 2026-01-10 10:25:00 |            5 |                0.50 | 00:05:00 | masih satu sesi |            7.5
           1 | 2026-01-10 12:00:00 |           40 |                8.00 | 01:35:00 | sesi baru       |           22.5
           2 | 2026-01-10 09:00:00 |           30 |                     |          | event pertama   |           30.0
           2 | 2026-01-10 09:10:00 |            8 |                0.27 | 00:10:00 | masih satu sesi |           19.0
           2 | 2026-01-10 09:15:00 |           12 |                1.50 | 00:05:00 | masih satu sesi |           10.0
           4 | 2026-01-10 14:00:00 |           20 |                     |          | event pertama   |           20.0
           4 | 2026-01-10 16:30:00 |           35 |                1.75 | 02:30:00 | sesi baru       |           27.5
           4 | 2026-01-10 16:35:00 |            6 |                0.17 | 00:05:00 | masih satu sesi |           20.5
(10 rows)

Tiga mekanisme bekerja bersama di query ini:

  1. WINDOW w1 AS (PARTITION BY pengguna_id ORDER BY waktu) didefinisikan sekali lalu dipakai lima kali — lag(durasi_menit) OVER w1, dua pemanggilan lag(waktu) OVER w1 di dalam CASE, dan lainnya — menghapus duplikasi definisi yang panjang.
  2. w2 AS (w1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) memperluas w1 dengan frame dua baris; rata-rata bergeraknya (25,0 → 17,5 → 7,5 untuk pengguna 1) mewarisi partisi dan urutan w1 tanpa ditulis ulang.
  3. Rasio durasi_menit / lag(durasi_menit) adalah pola “persentase dari nilai sebelumnya” — cast ::numeric wajib sebelum membagi dua integer, dan pembagian oleh NULL (baris pertama tiap pengguna) secara alami menghasilkan NULL.

Kolom jeda dan status_sesi adalah pola inti analisis sesi: selisih waktu - lag(waktu) mengukur jeda ke event sebelumnya, dan ambang 30 menit memutus mana event pembuka sesi baru — pengguna 1 memiliki dua sesi (10:00 lalu 12:00), pengguna 4 juga dua (14:00 lalu 16:30). Pola penandaan ini menjadi bahan chapter Gap and Island Problem dan Analytical Query .

Window Function di Dalam UPDATE #

Hasil window function sering dibutuhkan untuk mengubah data — misalnya menyimpan penanda “event pembuka sesi” ke kolom tabel. UPDATE tidak menerima window function di klausa SET; percobaan langsung ditolak:

sql update-tanpa-cte-error.sql 
-- Percobaan memakai window function LANGSUNG di SET klausa UPDATE:
-- menandai event pertama tiap sesi dengan lag(waktu). Parser menolaknya.
ALTER TABLE log_sesi ADD COLUMN mulai_sesi boolean;

UPDATE log_sesi
SET mulai_sesi = (lag(waktu) OVER (PARTITION BY pengguna_id ORDER BY waktu) IS NULL
                  OR waktu - lag(waktu) OVER (PARTITION BY pengguna_id ORDER BY waktu)
                     > INTERVAL '30 minutes');

Output yang Dihasilkan:

text 
psql:update-tanpa-cte-error.sql:8: ERROR:  window functions are not allowed in UPDATE
LINE 2: SET mulai_sesi = (lag(waktu) OVER (PARTITION BY pengguna_id ...
                          ^

Jalan keluarnya: hitung kolom jendela di CTE terpisah, lalu sambungkan ke UPDATE ... FROM — CTE membaca tabel sekali dengan SELECT biasa (tempat window function sah), dan UPDATE tinggal menempelkan hasilnya baris demi baris lewat join penanda.id = s.id:

sql window-dalam-update.sql 
-- Window function di dalam UPDATE: hitung kolom jendela di CTE dulu,
-- lalu UPDATE ... FROM menggandengkan hasilnya baris demi baris.
-- Kolom mulai_sesi sudah ditambahkan oleh file sebelumnya.
WITH penanda AS (
    SELECT id,
           lag(waktu) OVER (PARTITION BY pengguna_id ORDER BY waktu) AS waktu_sebelumnya
    FROM log_sesi
)
UPDATE log_sesi s
SET mulai_sesi = (penanda.waktu_sebelumnya IS NULL
                  OR s.waktu - penanda.waktu_sebelumnya > INTERVAL '30 minutes')
FROM penanda
WHERE penanda.id = s.id;

-- Hasil penandaan: baris 't' = pembuka sesi baru.
SELECT id,
       pengguna_id,
       waktu,
       durasi_menit,
       mulai_sesi
FROM log_sesi
ORDER BY id;

Output yang Dihasilkan:

text 
UPDATE 10
 id | pengguna_id |        waktu        | durasi_menit | mulai_sesi 
----+-------------+---------------------+--------------+------------
  1 |           1 | 2026-01-10 10:00:00 |           25 | t
  2 |           1 | 2026-01-10 10:20:00 |           10 | f
  3 |           1 | 2026-01-10 10:25:00 |            5 | f
  4 |           1 | 2026-01-10 12:00:00 |           40 | t
  5 |           2 | 2026-01-10 09:00:00 |           30 | t
  6 |           2 | 2026-01-10 09:10:00 |            8 | f
  7 |           2 | 2026-01-10 09:15:00 |           12 | f
  8 |           4 | 2026-01-10 14:00:00 |           20 | t
  9 |           4 | 2026-01-10 16:30:00 |           35 | t
 10 |           4 | 2026-01-10 16:35:00 |            6 | f
(10 rows)

ALTER TABLE pada file error tadi sempat dijalankan sebelum UPDATE ditolak, jadi kolom mulai_sesi sudah tersedia di sini. Hasil akhirnya bisa dibaca langsung: empat baris bertanda t (id 1, 4, 5, 8) persis baris-baris event pertama dan sesi baru pada tabel sebelumnya — penandaan kini tersimpan permanen di kolom tabel, siap dipakai indeks, filter, atau agregasi sesi di query-query berikutnya.

Dengan jendela bertingkat, frame ber-EXCLUDE, jendela bernama, dan window-di-dalam-UPDATE, peralatan analitis Anda kini lengkap untuk deret data yang bentuknya masih rapi. Bab lanjutan berikutnya membawa struktur pembangunnya sendiri: CTE yang dirantai menjadi pipeline perhitungan bernama, CTE rekursif yang diagregasi, dan CTE pengubah data yang memindahkan baris antar-tabel secara atomik di Advanced CTE .

Referensi #

Berkas Sumber

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

buat-tabel.sql

Unduh berkas
sql 
-- Tabel latihan chapter Window Function Lanjutan: kunjungan harian per kanal.
-- Kanal web menurun, kanal aplikasi menanjak -- pola yang membuat posisi
-- kumulatifnya akan BERGANTI di tengah deret (bahan demo jendela bertingkat).
CREATE TABLE kunjungan_harian (
    tanggal    date NOT NULL,
    kanal      text NOT NULL,
    pengunjung integer NOT NULL,
    PRIMARY KEY (tanggal, kanal)
);

INSERT INTO kunjungan_harian (tanggal, kanal, pengunjung) VALUES
    (DATE '2026-01-01', 'web',      120),
    (DATE '2026-01-02', 'web',      110),
    (DATE '2026-01-03', 'web',      100),
    (DATE '2026-01-04', 'web',       95),
    (DATE '2026-01-05', 'web',       90),
    (DATE '2026-01-06', 'web',       85),
    (DATE '2026-01-01', 'aplikasi',  80),
    (DATE '2026-01-02', 'aplikasi',  95),
    (DATE '2026-01-03', 'aplikasi', 130),
    (DATE '2026-01-04', 'aplikasi', 160),
    (DATE '2026-01-05', 'aplikasi', 175),
    (DATE '2026-01-06', 'aplikasi', 190);

SELECT tanggal, kanal, pengunjung
FROM kunjungan_harian
ORDER BY tanggal, kanal;

jeda-dan-rasio.sql

Unduh berkas
sql 
-- LAG untuk rasio terhadap nilai sebelumnya dan analisis jeda antar-event.
-- Klausa WINDOW memberi nama dua jendela: w1 = urutan waktu per pengguna,
-- w2 = w1 yang diberi frame 2 baris (rata-rata bergerak durasi).
CREATE TABLE log_sesi (
    id            integer PRIMARY KEY,
    pengguna_id   integer NOT NULL,
    waktu         timestamp NOT NULL,
    durasi_menit  integer NOT NULL
);

INSERT INTO log_sesi (id, pengguna_id, waktu, durasi_menit) VALUES
    (1, 1, TIMESTAMP '2026-01-10 10:00:00', 25),
    (2, 1, TIMESTAMP '2026-01-10 10:20:00', 10),
    (3, 1, TIMESTAMP '2026-01-10 10:25:00',  5),
    (4, 1, TIMESTAMP '2026-01-10 12:00:00', 40),
    (5, 2, TIMESTAMP '2026-01-10 09:00:00', 30),
    (6, 2, TIMESTAMP '2026-01-10 09:10:00',  8),
    (7, 2, TIMESTAMP '2026-01-10 09:15:00', 12),
    (8, 4, TIMESTAMP '2026-01-10 14:00:00', 20),
    (9, 4, TIMESTAMP '2026-01-10 16:30:00', 35),
    (10, 4, TIMESTAMP '2026-01-10 16:35:00', 6);

SELECT pengguna_id,
       waktu,
       durasi_menit,
       round(durasi_menit::numeric / lag(durasi_menit) OVER w1, 2) AS rasio_vs_sebelumnya,
       waktu - lag(waktu) OVER w1 AS jeda,
       CASE
           WHEN lag(waktu) OVER w1 IS NULL THEN 'event pertama'
           WHEN waktu - lag(waktu) OVER w1 > INTERVAL '30 minutes' THEN 'sesi baru'
           ELSE 'masih satu sesi'
       END AS status_sesi,
       round(avg(durasi_menit) OVER w2, 1) AS rata2_bergerak
FROM log_sesi
WINDOW w1 AS (PARTITION BY pengguna_id ORDER BY waktu),
       w2 AS (w1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY pengguna_id, waktu;

jendela-bersarang-error.sql

Unduh berkas
sql 
-- Kenapa harus lewat CTE: window function TIDAK BOLEH bersarang langsung.
-- Membungkus dense_rank di dalam definisi jendela sum ditolak parser.
SELECT sum(pengunjung) OVER (
           ORDER BY dense_rank() OVER (ORDER BY pengunjung)
       ) AS total_lengkap
FROM kunjungan_harian;

jendela-bertingkat.sql

Unduh berkas
sql 
-- Jendela bertingkat: window function yang mengolah HASIL window function
-- sebelumnya. Sum kumulatif per kanal (jendela ke-1) lalu dense_rank atas
-- total kumulatif itu (jendela ke-2) -- dua lapis dua CTE.
WITH kumulatif AS (
    SELECT tanggal,
           kanal,
           pengunjung,
           sum(pengunjung) OVER (
               PARTITION BY kanal ORDER BY tanggal
           ) AS total_kumulatif
    FROM kunjungan_harian
),
peringkat AS (
    SELECT tanggal,
           kanal,
           pengunjung,
           total_kumulatif,
           dense_rank() OVER (
               PARTITION BY tanggal ORDER BY total_kumulatif DESC
           ) AS posisi_kumulatif
    FROM kumulatif
)
SELECT tanggal,
       kanal,
       pengunjung,
       total_kumulatif,
       posisi_kumulatif
FROM peringkat
ORDER BY tanggal, posisi_kumulatif;

rows-vs-range.sql

Unduh berkas
sql 
-- ROWS vs RANGE dan klausa EXCLUDE (PostgreSQL 11+).
-- Tabel kecil dengan skor seri -- "peer" -- untuk melihat beda keduanya.
CREATE TABLE skor_kuis (
    peserta text PRIMARY KEY,
    skor    integer NOT NULL
);

INSERT INTO skor_kuis (peserta, skor) VALUES
    ('Budi',  80),
    ('Siti',  80),
    ('Andi',  70),
    ('Dewi',  90),
    ('Rina',  70);

-- RANGE (default) memperlakukan baris dengan nilai ORDER BY sama sebagai
-- satu kelompok peer: Andi dan Rina (70) mendapat total yang sama, 140,
-- karena keduanya "masuk frame bersama-sama". ROWS berjalan baris demi
-- baris; pemecah urut peserta membuat hasilnya deterministik.
SELECT peserta,
       skor,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_range,
       sum(skor) OVER (ORDER BY skor, peserta
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_rows
FROM skor_kuis
ORDER BY skor, peserta;

-- Empat varian EXCLUDE pada frame RANGE UNBOUNDED PRECEDING:
-- NO OTHERS (default)  : semua baris skor <= skor sekarang.
-- CURRENT ROW           : peer lain tetap masuk, dirinya sendiri dibuang.
-- TIES                  : dirinya masuk, peer lain dibuang.
-- GROUP                 : dirinya dan seluruh peer dibuang (frame bisa kosong -> NULL).
SELECT peserta,
       skor,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS tanpa_exclude,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       EXCLUDE CURRENT ROW) AS exclude_current,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       EXCLUDE TIES) AS exclude_ties,
       sum(skor) OVER (ORDER BY skor
                       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       EXCLUDE GROUP) AS exclude_group
FROM skor_kuis
ORDER BY skor, peserta;

update-tanpa-cte-error.sql

Unduh berkas
sql 
-- Percobaan memakai window function LANGSUNG di SET klausa UPDATE:
-- menandai event pertama tiap sesi dengan lag(waktu). Parser menolaknya.
ALTER TABLE log_sesi ADD COLUMN mulai_sesi boolean;

UPDATE log_sesi
SET mulai_sesi = (lag(waktu) OVER (PARTITION BY pengguna_id ORDER BY waktu) IS NULL
                  OR waktu - lag(waktu) OVER (PARTITION BY pengguna_id ORDER BY waktu)
                     > INTERVAL '30 minutes');

window-dalam-update.sql

Unduh berkas
sql 
-- Window function di dalam UPDATE: hitung kolom jendela di CTE dulu,
-- lalu UPDATE ... FROM menggandengkan hasilnya baris demi baris.
-- Kolom mulai_sesi sudah ditambahkan oleh file sebelumnya.
WITH penanda AS (
    SELECT id,
           lag(waktu) OVER (PARTITION BY pengguna_id ORDER BY waktu) AS waktu_sebelumnya
    FROM log_sesi
)
UPDATE log_sesi s
SET mulai_sesi = (penanda.waktu_sebelumnya IS NULL
                  OR s.waktu - penanda.waktu_sebelumnya > INTERVAL '30 minutes')
FROM penanda
WHERE penanda.id = s.id;

-- Hasil penandaan: baris 't' = pembuka sesi baru.
SELECT id,
       pengguna_id,
       waktu,
       durasi_menit,
       mulai_sesi
FROM log_sesi
ORDER BY id;