Window Functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE
Muammodan boshlaylik
Har bir shahardagi foydalanuvchilarni buyurtma summasiga qarab reyting qilish kerak. Ya'ni: Toshkentda 1-o'rin kim, 2-o'rin kim; Samarqandda 1-o'rin kim, 2-o'rin kim — alohida-alohida.
GROUP BY bilan bu imkonsiz — u qatorlarni birlashtirib bitta natija chiqaradi. Har bir qator saqlanib qolishi va unga reyting raqami qo'shilishi kerak.
Bu muammo uchun window functions yaratilgan.
Window Functions nima?
Window Functions — har bir qatorga boshqa qatorlarning ma'lumotlariga asoslanib qiymat hisoblaydi. Lekin GROUP BY dan farqli ravishda qatorlarni birlashtirmaydi — har bir qator o'z holida saqlanib qoladi.
Hayotiy o'xshatma: olimpiada natijalari jadvali. Har bir sportchi o'z natijasi bilan turibdi — lekin uning yonida "umumiy o'rin" ustuni ham bor. Sportchi jadvaldan chiqib ketmadi, faqat uning yoniga qo'shimcha ma'lumot qo'shildi. Window function — o'sha "yoniga qo'shimcha ma'lumot qo'shish".
GROUP BY: Window Function:
────────────────────── ──────────────────────────────
shahar │ max_summa ism │ shahar │ summa │ reyting
─────────┼────────── ──────┼──────────┼────────┼────────
Toshkent │ 5000000 Ali │ Toshkent │ 500000 │ 1
Samarq. │ 350000 Soli │ Toshkent │ 200000 │ 2
Vali │ Samarq. │ 350000 │ 1
↑ qatorlar yo'qoldi ↑ qatorlar saqlanib qoldi
Sintaksis
funksiya_nomi() OVER (
PARTITION BY ustun -- ← qaysi guruh ichida hisoblash
ORDER BY ustun -- ← guruh ichida qanday tartibda
)
PARTITION BY — GROUP BY ga o'xshash, lekin qatorlarni birlashtirmaydi. Har bir partitsiya — alohida "oyna" (window).
ORDER BY — partitsiya ichida qatorlar tartibi.
Namuna jadval
CREATE TABLE sotuvchilar (
id SERIAL PRIMARY KEY,
ism TEXT,
shahar TEXT,
sotuv NUMERIC
);
INSERT INTO sotuvchilar (ism, shahar, sotuv) VALUES
('Ali', 'Toshkent', 5000000),
('Soli', 'Toshkent', 3200000),
('Kamol', 'Toshkent', 3200000),
('Jasur', 'Toshkent', 1800000),
('Vali', 'Samarqand', 4100000),
('Malika', 'Samarqand', 4100000),
('Sherzod', 'Samarqand', 2700000);
E'tibor bering: Soli va Kamol bir xil sotuv (3 200 000). Vali va Malika ham bir xil (4 100 000). Bu bir xil qiymatlar reyting funksiyalari orasidagi farqni ko'rsatadi.
ROW_NUMBER — tartib raqami
Har bir qatorga oddiy tartib raqami beradi. Bir xil qiymatlar bo'lsa ham — raqamlar farqli bo'ladi.
SELECT
ism,
shahar,
sotuv,
ROW_NUMBER() OVER (PARTITION BY shahar ORDER BY sotuv DESC) AS raqam
FROM sotuvchilar;
ism │ shahar │ sotuv │ raqam
----------+-----------+---------+-------
Ali │ Toshkent │ 5000000 │ 1
Soli │ Toshkent │ 3200000 │ 2 ← Soli = Kamol, lekin raqamlar farqli
Kamol │ Toshkent │ 3200000 │ 3
Jasur │ Toshkent │ 1800000 │ 4
Vali │ Samarqand │ 4100000 │ 1 ← yangi partitsiya, 1 dan boshlanadi
Malika │ Samarqand │ 4100000 │ 2
Sherzod │ Samarqand │ 2700000 │ 3
PARTITION BY shahar — har bir shahar uchun raqamlash 1 dan boshlanadi. Samarqand bilan Toshkent alohida oyna.
ROW_NUMBER ishlatilishi: sahifalash (pagination), har bir guruhdan birinchi N qatorni olish.
RANK — bir xil qiymatga bir xil reyting
Bir xil qiymatlar bir xil reyting oladi — lekin keyingi raqam sakraydi.
SELECT
ism,
sotuv,
RANK() OVER (PARTITION BY shahar ORDER BY sotuv DESC) AS rank
FROM sotuvchilar;
ism │ sotuv │ rank
----------+---------+------
Ali │ 5000000 │ 1
Soli │ 3200000 │ 2 ← ikkalasi 2-o'rin
Kamol │ 3200000 │ 2 ← ikkalasi 2-o'rin
Jasur │ 1800000 │ 4 ← 3 raqami sakrab o'tildi!
Nima uchun 4? Ikkita 2-o'rin bor edi — shuning uchun 3-o'rin "egallangan" deb hisoblanadi va Jasur 4-o'rin oldi.
Olimpiada reytingida xuddi shunday: ikkita kumush — bronza yo'q, keyingisi 4-o'rin.
DENSE_RANK — bo'shliqsiz reyting
RANK ga o'xshash, lekin raqamlar sakramaydi.
SELECT
ism,
sotuv,
DENSE_RANK() OVER (PARTITION BY shahar ORDER BY sotuv DESC) AS dense_rank
FROM sotuvchilar;
ism │ sotuv │ dense_rank
----------+---------+------------
Ali │ 5000000 │ 1
Soli │ 3200000 │ 2 ← ikkalasi 2
Kamol │ 3200000 │ 2 ← ikkalasi 2
Jasur │ 1800000 │ 3 ← bo'shliq yo'q, 3 keldi
Jasur RANK da 4, DENSE_RANK da 3. Farq shunda.
RANK va DENSE_RANK farqi
Natijalar: 100, 90, 90, 80
RANK: 1, 2, 2, 4 ← 3 sakradi
DENSE_RANK: 1, 2, 2, 3 ← bo'shliq yo'q
ROW_NUMBER: 1, 2, 3, 4 ← har doim farqli
Qachon qaysi?
- ROW_NUMBER — pagination, birinchi N ta olish (bir xil qiymat bo'lsa tartib muhim emas)
- RANK — sport reytingi, olimpiada (bir xil natija — bir xil o'rin, keyin bo'shliq)
- DENSE_RANK — akademik reyting (bir xil baho — bir xil daraja, bo'shliqsiz)
NTILE — guruhga bo'lish
Qatorlarni N ta teng guruhga bo'ladi. Masalan, sotuvchilarni 4 ta kvartilga bo'lish:
SELECT
ism,
sotuv,
NTILE(4) OVER (ORDER BY sotuv DESC) AS kvartil
FROM sotuvchilar;
ism │ sotuv │ kvartil
----------+---------+---------
Ali │ 5000000 │ 1 ← top 25%
Vali │ 4100000 │ 1
Malika │ 4100000 │ 2
Soli │ 3200000 │ 2
Kamol │ 3200000 │ 3
Sherzod │ 2700000 │ 3
Jasur │ 1800000 │ 4 ← bottom 25%
7 ta qator, 4 ta guruh — to'liq bo'linmaydi. PostgreSQL yuqori guruhlarga bir qator ko'proq beradi: 1-guruh 2 ta, 4-guruh 1 ta.
NTILE ishlatilishi: A/B/C/D toifalariga bo'lish, daromad kvartillari, mijozlarni segmentlash.
Amaliy misol — har guruhdan faqat birinchisini olish
Har bir shahardagi eng ko'p sotgan sotuvchi:
WITH reytinglar AS (
SELECT
ism,
shahar,
sotuv,
ROW_NUMBER() OVER (PARTITION BY shahar ORDER BY sotuv DESC) AS raqam
FROM sotuvchilar
)
SELECT ism, shahar, sotuv
FROM reytinglar
WHERE raqam = 1;
ism │ shahar │ sotuv
--------+-----------+---------
Ali │ Toshkent │ 5000000
Vali │ Samarqand │ 4100000
Bu pattern juda ko'p ishlatiladi: "har bir guruhdan eng yuqori/past/oxirgi qatorni olish". ROW_NUMBER + WHERE raqam = 1 — klassik yechim.
Tez-tez uchraydigan xatolar
Window function ni WHERE da ishlatishga urinish:
-- ❌ — WHERE da window function ishlamaydi
SELECT ism, ROW_NUMBER() OVER (ORDER BY sotuv DESC) AS raqam
FROM sotuvchilar
WHERE raqam = 1;
-- ERROR: column "raqam" does not exist
-- ✅ — avval CTE/subquery, keyin filtr
WITH r AS (
SELECT ism, ROW_NUMBER() OVER (ORDER BY sotuv DESC) AS raqam
FROM sotuvchilar
)
SELECT ism FROM r WHERE raqam = 1;
Window function natijasi WHERE da hali mavjud emas — avval hisoblash tugallanishi kerak. Shu sababli CTE yoki subquery orqali filtr qilinadi.
PARTITION BY ni unutish:
-- ❌ — PARTITION BY yo'q — barcha qatorlar bitta oynada
SELECT ism, shahar,
RANK() OVER (ORDER BY sotuv DESC) AS rank
FROM sotuvchilar;
-- Toshkent va Samarqand aralash reyting bo'ladi
-- ✅ — har shahar alohida oyna
SELECT ism, shahar,
RANK() OVER (PARTITION BY shahar ORDER BY sotuv DESC) AS rank
FROM sotuvchilar;
Xulosa
Window Functions — qatorlarni saqlab, ular ustida hisoblash. GROUP BY dan farqi: qatorlar yo'qolmaydi.
ROW_NUMBER → oddiy tartib raqami (har doim farqli)
RANK → bir xil qiymat = bir xil reyting + bo'shliq
DENSE_RANK → bir xil qiymat = bir xil reyting, bo'shliqsiz
NTILE(n) → n ta teng guruhga bo'lish
Eng ko'p ishlatiladigan pattern:
WITH r AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY guruh ORDER BY qiymat DESC) AS n
FROM jadval
)
SELECT * FROM r WHERE n = 1; -- har guruhdan birinchisi
Keyingi dars
19-dars: Window Functions — SUM, AVG, LAG, LEAD, FIRST_VALUE, LAST_VALUE