Kueri analitik: perubahan, peringkat, dan kontribusi

Window function sudah diajarkan di kelas SQL Lanjutan. Di gudang data, fungsi ini hampir selalu dipasang di atas ringkasan, bukan langsung di tabel fakta: ringkas dulu ke periode atau anggota yang ditanyakan dengan kueri bintang di sebuah CTE, lalu terapkan OVER pada hasilnya. Dokumentasi PostgreSQL menjelaskan bahwa window function dihitung setelah pengelompokan, agregasi, dan HAVING, sehingga yang dilihatnya adalah baris kelompok. Satu kueri pun bisa memuat SUM(...) OVER () di atas SUM yang dikelompokkan, tetapi pemisahan dengan CTE membuat tahapnya terbaca.

Tiga pola paling sering muncul. Perubahan antarperiode memakai LAG untuk mengambil nilai periode sebelumnya pada urutan waktu dari dimensi tanggal. Peringkat dalam kelompok memakai RANK dengan PARTITION BY atribut dimensi, misalnya kategori. Kontribusi terhadap total membagi nilai setiap baris dengan SUM(...) OVER () tanpa partisi, yang menghasilkan total seluruh baris di setiap baris.

-- Kafe: perubahan jumlah cangkir dari minggu ke minggu
WITH mingguan AS (
  SELECT t.minggu_ke, SUM(f.cangkir) AS cangkir
  FROM fakta_pesanan_kafe f JOIN dim_tanggal t ON t.tanggal_key = f.tanggal_key
  GROUP BY t.minggu_ke
)
SELECT minggu_ke, cangkir,
       cangkir - LAG(cangkir) OVER (ORDER BY minggu_ke) AS perubahan
FROM mingguan;

Peringkat yang hanya menampilkan beberapa teratas per kelompok butuh satu lapis lagi, karena hasil window function tidak bisa disaring di WHERE kueri yang sama. Hitung peringkat di CTE, lalu saring di SELECT luar.