NTILE SQL: Bagi Data Jadi Kuartil dan Desil

NTILE(n) adalah window function SQL yang bagi hasil query kamu jadi n kelompok sama besar, ngikutin urutan yang kamu tentuin.

NTILE(4) bikin kuartil. NTILE(10) bikin desil. NTILE(100) bikin persentil. Semuanya cuma butuh satu baris tambahan di SELECT.

Ini fungsi yang kepakai tiap kali ada yang nanya "siapa 20% pelanggan terbaik kita?" atau "produk mana yang masuk 10% terlaris?". Tanpa NTILE, kamu harus hitung total baris dulu, bagi manual, terus filter pakai ROW_NUMBER. Ribet.

Apa itu NTILE di SQL?

NTILE adalah window function yang ngasih nomor kelompok ke tiap baris setelah baris-barisnya diurutin. Kalau kamu punya 100 pelanggan dan pakai NTILE(4), pelanggan nomor 1 sampai 25 dapat angka 1, nomor 26 sampai 50 dapat angka 2, dan seterusnya sampai 4.

Yang bikin NTILE beda dari RANK: NTILE nggak peduli sama nilainya. Dia cuma motong daftar jadi potongan yang jumlah barisnya sama rata. Dua pelanggan dengan belanja identik bisa masuk kuartil berbeda kalau posisinya kebetulan pas di garis potong.

Gimana sintaks NTILE?

NTILE(jumlah_kelompok) OVER (
    [PARTITION BY kolom_grup]
    ORDER BY kolom_urutan [ASC|DESC]
)

Tiga bagian yang perlu kamu isi:

• jumlah_kelompok: angka bulat positif. 4 buat kuartil, 10 buat desil.
• ORDER BY: wajib. NTILE nggak bisa jalan tanpa urutan, soalnya dia butuh tau siapa duluan.
• PARTITION BY: opsional. Kalau diisi, pembagiannya diulang dari awal per grup.

Contoh paling sederhana:

SELECT
    nama_produk,
    total_terjual,
    NTILE(4) OVER (ORDER BY total_terjual DESC) AS kuartil
FROM produk;

Hasilnya: produk terlaris dapat kuartil 1, produk paling nggak laku dapat kuartil 4.

Gimana cara bikin segmentasi pelanggan pakai NTILE?

Ini use case paling sering. Kamu mau bagi pelanggan jadi 4 tier berdasarkan total belanja.

WITH belanja_pelanggan AS (
    SELECT
        p.pelanggan_id,
        p.nama,
        SUM(t.total_harga) AS total_belanja
    FROM transaksi t
    JOIN pelanggan p ON p.pelanggan_id = t.pelanggan_id
    GROUP BY p.pelanggan_id, p.nama
)
SELECT
    nama,
    total_belanja,
    NTILE(4) OVER (ORDER BY total_belanja DESC) AS kuartil
FROM belanja_pelanggan
ORDER BY total_belanja DESC;

Perhatiin aku pakai CTE dulu buat ngitung total belanja, baru NTILE di query luar. Ini penting. Window function jalan setelah GROUP BY, jadi kalau kamu campur di satu level, urutan eksekusinya gampang bikin bingung.

Mau kasih nama tier yang enak dibaca? Bungkus lagi:

SELECT
    nama,
    total_belanja,
    CASE kuartil
        WHEN 1 THEN 'VIP'
        WHEN 2 THEN 'Reguler'
        WHEN 3 THEN 'Jarang'
        WHEN 4 THEN 'Pasif'
    END AS segmen
FROM (
    SELECT
        nama,
        total_belanja,
        NTILE(4) OVER (ORDER BY total_belanja DESC) AS kuartil
    FROM belanja_pelanggan
) x;

Gimana cara ambil top 10% pakai NTILE?

Pakai NTILE(10), terus filter yang kelompoknya 1.

SELECT nama, total_belanja
FROM (
    SELECT
        nama,
        total_belanja,
        NTILE(10) OVER (ORDER BY total_belanja DESC) AS desil
    FROM belanja_pelanggan
) x
WHERE desil = 1;

Kamu nggak bisa taruh WHERE desil = 1 langsung di query yang sama, soalnya WHERE dieksekusi sebelum window function dihitung. Makanya harus dibungkus subquery atau CTE.

Contoh kasus: 47.312 pelanggan toko_berkah

Dataset toko_berkah punya 47.312 pelanggan unik dengan riwayat belanja 3 tahun. Aku bagi pakai NTILE(10) berdasarkan total belanja.

WITH belanja AS (
    SELECT pelanggan_id, SUM(total_harga) AS total
    FROM transaksi
    GROUP BY pelanggan_id
),
desil AS (
    SELECT
        pelanggan_id,
        total,
        NTILE(10) OVER (ORDER BY total DESC) AS d
    FROM belanja
)
SELECT
    d AS desil,
    COUNT(*) AS jumlah_pelanggan,
    ROUND(AVG(total)) AS rata_belanja,
    ROUND(SUM(total) * 100.0 / SUM(SUM(total)) OVER (), 1) AS kontribusi_persen
FROM desil
GROUP BY d
ORDER BY d;

Hasilnya bikin aku ngangguk-ngangguk sendiri:

Desil	Jumlah pelanggan	Rata belanja	Kontribusi omzet
1	4.732	Rp 4.180.000	41,3%
2	4.731	Rp 1.520.000	15,0%
3	4.731	Rp 980.000	9,7%
10	4.731	Rp 78.000	0,8%

Desil 1, yang cuma 10% dari pelanggan, nyumbang 41,3% omzet. Desil paling bawah nyumbang kurang dari 1%.

Angka ini yang bikin tim marketing berhenti kirim promo blast ke semua orang dan mulai bikin program khusus buat 4.732 pelanggan teratas. Biaya promo turun, konversinya naik.

Kesalahan umum waktu pakai NTILE

Lupa ORDER BY di dalam OVER. Tanpa ORDER BY, NTILE nggak tau harus urut dari mana. Sebagian database kasih error, sebagian lagi kasih hasil acak yang lebih bahaya.

Filter hasil NTILE di WHERE yang sama. Window function dihitung belakangan, jadi WHERE ntile_col = 1 bakal error. Bungkus di subquery atau CTE.

Ngira NTILE peduli sama nilai. Kalau 3.000 dari 10.000 pelanggan belanjanya persis Rp 0, mereka bakal tersebar ke beberapa desil sekaligus. NTILE motong berdasarkan posisi, bukan nilai. Kalau ini bikin masalah, pakai PERCENT_RANK atau CASE manual berdasarkan ambang nilai.

Pakai NTILE(10) padahal datanya cuma 7 baris. Hasilnya: 7 kelompok terisi masing-masing 1 baris, 3 kelompok kosong. Nggak error, tapi laporannya jadi aneh.

FAQ

NTILE sama RANK bedanya apa?

RANK ngasih nomor urut berdasarkan nilai, dan nilai yang sama dapat ranking sama. NTILE nggak peduli nilainya, dia cuma motong daftar yang udah diurutin jadi n potongan sama besar. Kalau kamu butuh ranking berbasis nilai, pakai RANK atau DENSE_RANK.

Gimana kalau jumlah baris nggak habis dibagi n?

SQL kasih baris ekstra ke kelompok paling awal. 10 baris dibagi NTILE(3): kelompok 1 dapat 4 baris, kelompok 2 dan 3 masing-masing 3 baris.

NTILE bisa dipakai per grup nggak?

Bisa, pakai PARTITION BY. Misalnya NTILE(4) OVER (PARTITION BY kota ORDER BY total DESC) bikin kuartil terpisah untuk tiap kota. Jadi pelanggan Jakarta dibandingin sama sesama pelanggan Jakarta.

Database apa aja yang dukung NTILE?

PostgreSQL, MySQL 8.0 ke atas, SQL Server 2005 ke atas, Oracle, BigQuery, Snowflake, dan SQLite 3.25 ke atas. MySQL 5.7 belum punya window function sama sekali. Sintaks lengkapnya bisa kamu cek di dokumentasi window function PostgreSQL.

Kapan pakai NTILE dan kapan pakai PERCENT_RANK?

Pakai NTILE kalau kamu butuh label kelompok yang rapi buat dilaporin ke tim bisnis. Pakai PERCENT_RANK kalau kamu butuh posisi relatif 0 sampai 1 dan pengen batas antar kelompok ngikutin nilai, bukan jumlah baris.

Yang perlu kamu inget

NTILE motong berdasarkan posisi, bukan nilai. Ini kelebihan sekaligus jebakannya.

ORDER BY di dalam OVER itu wajib, dan hasil NTILE harus difilter di layer luar.

Buat segmentasi pelanggan, NTILE(10) plus perhitungan kontribusi omzet biasanya udah cukup buat bikin keputusan.

Coba jalanin query desil di atas ke data pelanggan kamu sendiri, dan lihat berapa persen omzet yang dateng dari desil 1. Angkanya biasanya lebih ekstrem dari yang kamu kira. Kalau mau lanjut, baca juga FIRST_VALUE dan LAST_VALUE buat ambil nilai awal dan akhir tiap grup.