Deteksi Anomali Penjualan dengan SQL Window Function

Deteksi anomali penjualan di SQL adalah cara nandai hari atau produk yang angkanya jauh dari kebiasaan, dihitung langsung dari data historis tanpa perlu pindah ke Python.

Alat utamanya window function. Dia bisa ngitung rata-rata beberapa hari sebelumnya di tiap baris, tanpa bikin query jadi sekumpulan subquery bertumpuk.

Di sini aku susun dari query paling sederhana sampai versi yang siap dijalanin tiap pagi buat ngirim peringatan ke tim toko.

Apa itu window function dan kenapa cocok buat deteksi anomali?

Window function ngitung nilai agregat dari sekumpulan baris yang berhubungan sama baris yang lagi diproses, tapi hasilnya tetap ditempel per baris. Beda dari GROUP BY yang meringkas banyak baris jadi satu, window function nyimpan semua baris asli sambil nambah kolom hitungan. Itu yang bikin dia pas buat bandingin hari ini sama 28 hari sebelumnya.

Bentuk dasarnya begini:

AVG(total_harian) OVER (
    ORDER BY tanggal
    ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
)

Tiga bagiannya:

1. OVER nandain ini window function, bukan agregasi biasa
2. ORDER BY tanggal nentuin urutan barisnya
3. ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING nentuin jendela mana yang dihitung, yaitu 28 baris sebelum baris ini sampai satu baris sebelumnya

Bagian ketiga yang paling penting. Batas 1 PRECEDING bikin hari yang lagi diperiksa gak ikut ngitung rata-ratanya sendiri.

Kalau hari ini ikut masuk, lonjakan besar bakal narik rata-rata naik dan anomali itu jadi kelihatan lebih biasa dari kenyataannya.

Penjelasan lengkap soal frame dan urutan evaluasinya ada di dokumentasi window function PostgreSQL.

Gimana bikin rata-rata bergerak penjualan harian?

Langkah pertama selalu meringkas transaksi jadi satu baris per tanggal. Setelah itu baru window function jalan di atasnya.

WITH harian AS (
    SELECT tanggal, SUM(nominal) AS total_harian
    FROM penjualan
    GROUP BY tanggal
)
SELECT
    tanggal,
    total_harian,
    ROUND(AVG(total_harian) OVER w) AS rata_28h,
    ROUND(STDDEV_SAMP(total_harian) OVER w) AS simpangan_28h
FROM harian
WINDOW w AS (ORDER BY tanggal ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING)
ORDER BY tanggal;

Klausa WINDOW w AS (...) bikin kamu nulis definisi jendelanya sekali lalu dipakai ulang. Tanpa itu, tiap kolom harus nulis OVER (ORDER BY tanggal ROWS BETWEEN ...) lengkap.

Hasilnya kayak gini:

tanggal	total_harian	rata_28h	simpangan_28h
2025-09-12	8.420.000	7.910.000	1.140.000
2025-09-13	9.150.000	7.930.000	1.150.000
2025-09-14	3.210.000	7.980.000	1.160.000

Baris 14 September udah kelihatan mencurigakan. Tapi mata bisa capek kalau datanya 400 baris, jadi lanjut ke tahap berikutnya.

Gimana cara nandai anomali pakai z-score di SQL?

Z-score ngukur berapa kali simpangan baku satu nilai menyimpang dari rata-ratanya. Rumusnya nilai hari ini dikurang rata-rata, dibagi simpangan baku. Nilai di atas 3 atau di bawah minus 3 artinya hari itu jauh dari kebiasaan, dan biasanya layak dicek manual.

WITH harian AS (
    SELECT tanggal, SUM(nominal) AS total_harian
    FROM penjualan
    GROUP BY tanggal
),
statistik AS (
    SELECT
        tanggal,
        total_harian,
        AVG(total_harian) OVER w AS rata_28h,
        STDDEV_SAMP(total_harian) OVER w AS simpangan_28h
    FROM harian
    WINDOW w AS (ORDER BY tanggal ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING)
)
SELECT
    tanggal,
    total_harian,
    ROUND(rata_28h) AS rata_28h,
    ROUND((total_harian - rata_28h) / NULLIF(simpangan_28h, 0), 2) AS z_score,
    CASE
        WHEN total_harian > rata_28h THEN 'lonjakan'
        ELSE 'anjlok'
    END AS arah
FROM statistik
WHERE simpangan_28h IS NOT NULL
  AND ABS(total_harian - rata_28h) > 3 * simpangan_28h
ORDER BY tanggal;

Dua pengaman yang wajib ada. NULLIF(simpangan_28h, 0) nyegah pembagian nol waktu 28 hari sebelumnya nilainya sama persis.

Dan simpangan_28h IS NOT NULL nyingkirin 28 baris pertama yang riwayatnya belum cukup.

Konsep outlier yang dipakai di sini sama persis dengan yang biasa dihitung di Excel pakai STDEV.S, cuma dijalanin per baris secara otomatis.

Milih ambang batas yang gak bikin tim capek

Ambang	Sensitivitas	Cocok buat
2,0	Sering bunyi	Data yang stabil, misalnya pemakaian listrik
2,5	Sedang	Penjualan cabang besar
3,0	Jarang bunyi	Titik awal yang aman buat kebanyakan kasus
3,5	Jarang banget	Toko kecil yang naik turunnya liar

Cara milihnya gampang. Jalanin dengan tiga ambang, hitung berapa peringatan per bulan, lalu ambil yang timmu sanggup cek satu per satu.

Peringatan yang gak pernah dicek sama aja dengan gak ada peringatan.

Gimana bandingin penjualan sama periode sebelumnya pakai LAG?

LAG ngambil nilai dari baris sebelumnya sesuai jarak yang kamu tentukan. Buat penjualan harian, jarak 7 paling berguna soalnya dia bandingin Senin sama Senin, bukan Senin sama Minggu.

WITH harian AS (
    SELECT tanggal, SUM(nominal) AS total_harian
    FROM penjualan
    GROUP BY tanggal
)
SELECT
    tanggal,
    total_harian,
    LAG(total_harian, 7) OVER (ORDER BY tanggal) AS minggu_lalu,
    ROUND(
        100.0 * (total_harian - LAG(total_harian, 7) OVER (ORDER BY tanggal))
        / NULLIF(LAG(total_harian, 7) OVER (ORDER BY tanggal), 0),
        1
    ) AS delta_persen
FROM harian
ORDER BY tanggal DESC
LIMIT 30;

Angka 100.0 ditulis dengan titik desimal biar hasilnya gak dibulatkan jadi bilangan bulat. Ini jebakan yang sering bikin semua persentase muncul sebagai 0.

LAG cocok buat laporan harian yang dibaca manusia. Z-score cocok buat peringatan otomatis.

Gimana deteksi anomali per produk, bukan cuma total toko?

Tambahin PARTITION BY ke definisi window. Jendelanya bakal dihitung ulang buat tiap produk, jadi riwayat produk A gak nyampur sama produk B.

WITH harian_produk AS (
    SELECT kode_produk, tanggal, SUM(qty) AS qty_harian
    FROM penjualan
    GROUP BY kode_produk, tanggal
),
statistik AS (
    SELECT
        kode_produk,
        tanggal,
        qty_harian,
        AVG(qty_harian) OVER w AS rata_14h,
        STDDEV_SAMP(qty_harian) OVER w AS simpangan_14h,
        COUNT(*) OVER w AS jumlah_riwayat
    FROM harian_produk
    WINDOW w AS (
        PARTITION BY kode_produk
        ORDER BY tanggal
        ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING
    )
)
SELECT kode_produk, tanggal, qty_harian, ROUND(rata_14h, 1) AS rata_14h,
       ROUND((qty_harian - rata_14h) / NULLIF(simpangan_14h, 0), 2) AS z_score
FROM statistik
WHERE jumlah_riwayat >= 10
  AND simpangan_14h > 0
  AND ABS(qty_harian - rata_14h) > 3 * simpangan_14h
ORDER BY ABS((qty_harian - rata_14h) / NULLIF(simpangan_14h, 0)) DESC;

Dua saringan tambahan di sini penting banget. jumlah_riwayat >= 10 mastiin produknya punya cukup sejarah.

Dan simpangan_14h > 0 nyingkirin produk yang penjualannya persis sama tiap hari, soalnya satu unit beda aja bikin z-score-nya meledak.

Gimana nangani efek hari dalam seminggu?

Toko sembako biasanya ramai Sabtu dan sepi Selasa. Kalau baseline-nya rata-rata 28 hari campur aduk, tiap Sabtu bakal kelihatan kayak lonjakan.

Solusinya bandingin tiap hari sama hari yang sama di minggu-minggu sebelumnya.

WITH harian AS (
    SELECT tanggal, EXTRACT(DOW FROM tanggal) AS hari_ke, SUM(nominal) AS total_harian
    FROM penjualan
    GROUP BY tanggal
)
SELECT
    tanggal,
    hari_ke,
    total_harian,
    ROUND(AVG(total_harian) OVER (
        PARTITION BY hari_ke ORDER BY tanggal ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING
    )) AS rata_4_minggu
FROM harian
ORDER BY tanggal DESC;

EXTRACT(DOW FROM tanggal) di PostgreSQL balikin angka 0 sampai 6, dengan 0 buat Minggu. Partisi berdasarkan angka itu bikin tiap Sabtu cuma dibandingin sama empat Sabtu sebelumnya.

Kalau pola musimannya lebih rumit dari sekadar hari dalam seminggu, pisahin dulu komponennya lewat dekomposisi time series sebelum bikin aturan peringatan.

Contoh kasus: 47 peringatan di toko_berkah

Dataset ngulikdata untuk toko_berkah punya 11.407 transaksi selama 14 bulan, 386 SKU aktif, dan 421 hari operasional.

Query z-score dengan ambang 3 dan jendela 28 hari nemuin 12 hari anomali di level total toko. Setelah dicek satu per satu:

Penyebab	Jumlah hari	Perlu tindakan
H-3 sampai H-1 Lebaran	3	Gak, ini udah diperkirakan
Libur nasional panjang	4	Gak
Kiriman grosir besar satu pelanggan	2	Gak, tapi perlu dipisah dari analisis
Mesin kasir mati setengah hari	1	Ya, data hari itu gak lengkap
Salah input nol tambahan	2	Ya, koreksi data

Angka yang paling berguna dari latihan ini: dari 12 peringatan, cuma 3 yang beneran masalah. Sisanya sembilan hari itu kejadian yang sebenernya udah bisa ditebak dari kalender.

Setelah tanggal libur nasional dan periode Lebaran dikeluarkan dari perhitungan, peringatan turun dari 12 jadi 5 dalam 14 bulan. Rasio peringatan yang perlu ditindak naik dari 25 persen ke 60 persen.

Di level produk, query PARTITION BY nemuin 47 kombinasi produk dan tanggal. Tiga puluh satu di antaranya dari 9 SKU yang jarang laku, dan setelah saringan minimal 10 hari riwayat dipasang, sisanya 16.

Kesalahan umum waktu bikin deteksi anomali di SQL

Masukin hari ini ke baseline. Pakai ROWS BETWEEN 28 PRECEDING AND CURRENT ROW bikin anomali nyamarin dirinya sendiri. Berhenti di 1 PRECEDING.

Lupa hari tanpa transaksi. Kalau toko tutup dan gak ada baris sama sekali, ROWS BETWEEN 28 PRECEDING sebenernya nyakup lebih dari 28 hari kalender. Bikin tabel kalender lengkap lalu LEFT JOIN kalau ini penting buat kamu.

Pakai RANGE padahal maksudnya ROWS. RANGE ngitung berdasarkan nilai kolom pengurut, ROWS ngitung jumlah baris. Buat tanggal, dua-duanya bisa kepakai tapi hasilnya beda kalau ada tanggal bolong.

Gak misahin transaksi grosir. Satu pelanggan grosir yang belanja Rp 24 juta bikin hari itu jadi anomali tiap bulan. Pisahin segmen grosir jadi query sendiri.

Ambang batas kekecilan. Peringatan 40 kali sebulan bakal diabaikan tim dalam dua minggu. Mulai dari ambang longgar, ketatin pelan-pelan.

Berhenti di deteksi. Peringatan tanpa tindak lanjut cuma nambah notifikasi. Tentuin siapa yang cek dan apa yang dia lakukan sebelum query-nya dijadwalin.

FAQ

Kenapa window frame-nya berhenti di 1 PRECEDING, bukan CURRENT ROW?

Supaya hari yang lagi diperiksa gak ikut ngitung rata-ratanya sendiri. Kalau hari ini ikut masuk baseline, lonjakan besar bakal narik rata-rata naik dan z-score-nya jadi lebih kecil dari seharusnya. Batas 1 PRECEDING bikin baseline murni datang dari masa lalu, dan itu juga yang bikin query ini bisa dipakai buat pemantauan harian.

Berapa ambang z-score yang tepat buat nandai anomali?

Tiga adalah titik awal yang umum dan bikin peringatannya jarang muncul. Kalau kamu mau lebih sensitif, turunin ke 2,5. Untuk data penjualan harian toko kecil yang naik turunnya liar, 3,5 sering lebih tenang. Coba jalanin dengan beberapa ambang, hitung berapa peringatan per bulan, lalu pilih jumlah yang timmu sanggup cek satu per satu.

Apa window function ini jalan di MySQL dan SQL Server?

Jalan, dengan sedikit penyesuaian. MySQL 8.0 ke atas dan SQL Server 2012 ke atas dukung klausa OVER dengan ROWS BETWEEN. Nama fungsi simpangan bakunya beda: PostgreSQL pakai STDDEV_SAMP, SQL Server pakai STDEV, MySQL pakai STDDEV_SAMP juga. Klausa WINDOW terpisah gak ada di SQL Server, jadi tulis ulang definisi window-nya di tiap kolom.

Gimana cara nangani hari libur yang penjualannya emang beda?

Bikin tabel kalender berisi tanggal libur nasional dan periode Lebaran, lalu keluarkan tanggal itu dari perhitungan baseline atau tandai peringatannya sebagai terduga libur. Tanpa ini, H-3 Lebaran bakal selalu muncul sebagai anomali padahal kamu udah tahu penyebabnya. Peringatan yang penyebabnya udah jelas cuma bikin orang berhenti baca peringatan.

Kenapa banyak produk muncul sebagai anomali padahal biasa aja?

Biasanya karena produk itu jarang laku, jadi simpangan bakunya nyaris nol dan satu penjualan kecil terlihat ekstrem. Saring dulu: minta minimal 10 hari riwayat dan simpangan baku lebih besar dari nol, kayak di query per produk. Bisa juga tambahin syarat minimal selisih dalam satuan nyata, misalnya beda qty harus lebih dari 5.

Penutup

Tiga hal yang perlu kamu bawa.

Window function bikin perbandingan antar baris jadi satu query pendek. Kunci utamanya di klausa frame, dan berhenti di 1 PRECEDING biar hari yang diperiksa gak ikut ngitung baseline-nya sendiri.

Ambang z-score bukan angka suci. Pilih yang bikin jumlah peringatan sesuai kapasitas tim, bukan yang paling benar secara teori.

Peringatan yang penyebabnya udah bisa ditebak dari kalender itu kebisingan. Keluarin tanggal libur dari baseline dan rasio peringatan bergunanya langsung naik.

Coba jalanin query z-score di data penjualanmu sendiri minggu ini. Kalau kamu perlu bikin laporan dampaknya buat atasan, cara mengukur dampak kerja analis ngasih pola nulisnya. Rekap awalnya juga bisa kamu bandingin dulu di spreadsheet pakai SUMIFS, dan istilah aggregate function yang dipakai di sini bisa kamu cek ulang di glossary.