Window functions

7-bob 10 kod bloki 4019 so'z

Agar talaba SQL dan bitta ilg'or mavzuni o'rgansa, o'sha shu bo'lsin. Window function lar database dan so'rov qila oladigan odamni database ni tahlil qila oladigan odamdan ajratib turadi — running total, ranking, oydan-oyga o'zgarish, "har bir kategoriyada top 3" — va ular deyarli har bir jiddiy data analytics intervyusida uchraydi.

Mavjudligi: SQLite 3.25+ (2018), PostgreSQL, MySQL 8.0+, SQL Server 2012+. Barcha zamonaviy versiyalar qo'llab-quvvatlaydi.

Ular hal qiladigan muammo

GROUP BY ning bitta qattiq cheklovi bor: u row laringizni yo'q qiladi. Course bo'yicha guruhlaganingizdan keyin alohida enrollment lar yo'qoladi. Lekin real savollarning ko'pi ikkalasini birdan talab qiladi:

• "Har bir enrollment ni va uning course o'rtachasiga nisbatan holatini ko'rsat."
• "Har bir course ichida student larni reyting bo'yicha joylashtir."
• "Har bir oyning yig'indisini va shu paytgacha to'plangan umumiy yig'indini ko'rsat."

Window function joriy row bilan bog'liq row lar to'plami ustida, joriy row ni saqlab qolgan holda aggregate hisoblaydi.

OVER() clause

Har qanday aggregate OVER (...) qo'shilgan zahoti window function ga aylanadi. Bo'sh qavs "window — natijadagi barcha row lar" degani.

SELECT
    enrollment_id,
    grade,
    ROUND(AVG(grade) OVER (), 1)      AS overall_avg,
    grade - AVG(grade) OVER ()        AS diff,
    COUNT(*) OVER ()                  AS total_rows
FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade DESC
LIMIT 4;

4 qator

1 ms

enrollment_idgradeoverall_avgdifftotal_rows

1310079.4+20.5714 69579.4+15.5714 19279.4+12.5714 149179.4+11.5714

O'n to'rtta row saqlandi, ustiga har biriga xulosa column i qo'shildi. Umumiy shakli:

PARTITION BY

PARTITION BY — "siqmaydigan GROUP BY". Function har bir partition uchun qaytadan boshlanadi.

SELECT
    e.enrollment_id,
    e.course_id,
    e.grade,
    ROUND(AVG(e.grade) OVER (PARTITION BY e.course_id), 1) AS course_avg,
    e.grade - AVG(e.grade) OVER (PARTITION BY e.course_id) AS vs_course
FROM enrollments e
WHERE e.grade IS NOT NULL AND e.course_id IN (1, 2)
ORDER BY e.course_id, e.grade DESC;

… 278 qatordan birinchi 15 tasi

278 qator

1 ms

enrollment_idcourse_idgradecourse_avgvs_course

119268.0+24.0 417568.0+7.0 915868.0−10.0 1514768.0−21.0 13210087.2+12.8 629587.2+7.8 228887.2+0.8 1728387.2−4.2 1027087.2−17.2

Har bir enrollment ko'rinib turibdi va har biri o'z course ining o'rtachasini olib yuribdi. Buni GROUP BY bilan qilish uchun o'rtachalarni alohida hisoblab, keyin qaytadan join qilish kerak bo'lardi — bitta clause o'rniga ikkita query.

ROW_NUMBER, RANK, DENSE_RANK

Uchta ranking function faqat tenglikni qanday hal qilishi bilan farq qiladi. Bizning course lardagi enrollment sanoqlarida haqiqiy tenglik bor (1 va 2-course da 5 tadan), bu farqni ko'rinadigan qiladi:

WITH per_course AS (
    SELECT course_id, COUNT(*) AS n FROM enrollments GROUP BY course_id
)
SELECT
    course_id, n,
    ROW_NUMBER() OVER (ORDER BY n DESC) AS row_number,
    RANK()       OVER (ORDER BY n DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY n DESC) AS dense_rank
FROM per_course;

14 qator

1 ms

course_idnROW_NUMBERRANKDENSE_RANK

15111 25211 54332 33443 43543

FunctionTeng qiymatlarKeyinQachon ishlatiladi

ROW_NUMBER()Har xil raqam oladi (tartib tasodifiy)Takrorlamaydi, o'tkazib yubormaydiGuruhdan aynan bitta row kerak bo'lganda RANK()Bir xil raqam oladiKeyingi qiymatlarni o'tkazib yuboradi (1,1,3)Sport uslubidagi reyting: ikkita oltin, kumush yo'q DENSE_RANK()Bir xil raqam oladiBo'shliq qoldirmaydi (1,1,2)"Bu qaysi darajaga tegishli?"

:::gotcha ⚠️ Tenglikda ROW_NUMBER noaniq 1 va 2-course da 5 tadan enrollment bor, shuning uchun qaysi biri 1-raqamni olishi engine ga bog'liq va har safar o'zgarishi mumkin. Tartib barqaror bo'lishi kerak bo'lsa, ajratuvchi qo'shing: ORDER BY n DESC, course_id ASC. ROW_NUMBER() qaysi row qolishini hal qiladigan har qanday holatda beqaror saralash natijangizni takrorlanmaydigan qiladi. :::

Guruh bo'yicha Top-N

"Har bir course dagi eng yaxshi student", "har bir mijozning eng qimmat 3 ta buyurtmasi" — bu eng klassik window-function intervyu savoli, va uni window function siz toza qilishning iloji yo'q.

Pattern doim ikki qadam: har bir partition ichida row larni raqamlang, keyin kerakli raqamlarni qoldiring.

WITH ranked AS (
    SELECT
        e.course_id,
        s.first_name,
        e.grade,
        ROW_NUMBER() OVER (
            PARTITION BY e.course_id
            ORDER BY e.grade DESC
        ) AS rn
    FROM enrollments e
    JOIN students s ON s.student_id = e.student_id
    WHERE e.grade IS NOT NULL
)
SELECT c.title, r.first_name, r.grade
FROM ranked r
JOIN courses c ON c.course_id = r.course_id
WHERE r.rn = 1                       -- top-3 uchun rn <= 3 qiling
ORDER BY r.grade DESC;

14 qator

2 ms

:::gotcha ⚠️ Nega bu yerda CTE majburiy WHERE ROW_NUMBER() OVER (...) = 1 deb yozib bo'lmaydi. Window function lar SELECT qadamida hisoblanadi, u esa WHERE dan keyin (1-bobdagi bajarilish tartibi). WHERE ishlaganda window function hali mavjud emas. Uni CTE yoki derived table da hisoblang, keyin tashqi query da filtrlang. Talabalar bu xatoga doim duch keladi, va bajarilish tartibi diagrammasi buning tushuntirishi. :::

LAG va LEAD

LAG(col, n) n row orqaga, LEAD(col, n) n row oldinga qaraydi. Ikkalasining standart qiymati 1. Vaqt bo'yicha o'zgarishni shunday hisoblaysiz — va bu window function lar paydo bo'lishidan oldin ishlatilgan noqulay self-join ning o'rnini bosadi.

WITH monthly AS (
    SELECT strftime('%Y-%m', enrolled_at) AS month, COUNT(*) AS n
    FROM enrollments GROUP BY month
)
SELECT
    month,
    n,
    LAG(n)  OVER (ORDER BY month)                    AS prev_month,
    n - LAG(n) OVER (ORDER BY month)                 AS change,
    ROUND(100.0 * (n - LAG(n) OVER (ORDER BY month))
          / LAG(n) OVER (ORDER BY month), 1)         AS pct_change
FROM monthly
ORDER BY month;

4 qator

1 ms

monthnprev_monthchangepct_change

2025-096NULLNULLNULL 2026-02126+6+100.0 2026-09212−10−83.3

Birinchi row NULL, chunki oldingi oy yo'q — bu xato emas, to'g'ri xatti-harakat. Raqam kerak bo'lsa standart qiymat bering: LAG(n, 1, 0).

Running total va frame lar

OVER() ichiga ORDER BY qo'shish ma'noni tubdan o'zgartiradi: window "barcha row lar" o'rniga "boshidan joriy row gacha bo'lgan row lar" bo'lib qoladi. Bu — running total.

WITH monthly AS (
    SELECT strftime('%Y-%m', enrolled_at) AS month, COUNT(*) AS n
    FROM enrollments GROUP BY month
)
SELECT
    month, n,
    SUM(n) OVER (ORDER BY month)                     AS running_total,
    SUM(n) OVER ()                                   AS grand_total,
    ROUND(100.0 * SUM(n) OVER (ORDER BY month)
                / SUM(n) OVER (), 1)                 AS pct_of_total
FROM monthly ORDER BY month;

4 qator

1 ms

monthnrunning_totalgrand_totalpct_of_total

2025-09662030.0 2026-0212182090.0 2026-0922020100.0

Bitta va o'sha function — SUM(n) — ORDER BY bilan running total, ORDER BY siz umumiy yig'indi beradi. Ikkalasi bitta query da.

Frame lar

Frame window qaysi row larni qamrashini aniq belgilaydi. ORDER BY yozib, frame yozmasangiz, standart quyidagicha bo'ladi:

Moving average

Moving (rolling) average shovqinli qatorni silliqlaydi. Quyida u bitta course ning baholari ustida, yozilish tartibida:

SELECT
    e.enrolled_at,
    e.grade,
    ROUND(AVG(e.grade) OVER (
        ORDER BY e.enrolled_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3
FROM enrollments e
WHERE e.course_id = 2 AND e.grade IS NOT NULL
ORDER BY e.enrolled_at;

… 146 qatordan birinchi 15 tasi

146 qator

1 ms

enrolled_atgrademoving_avg_3

2025-09-058888.00 2025-09-189591.50 2026-02-047084.33 2026-02-0810088.33 2026-02-148384.33

Birinchi ikkita row uchtadan kam qiymat ustida o'rtacha oladi, chunki hali uchta row yo'q — window chekkalarda NULL qaytarmay, shunchaki qisqaradi. Hisobot faqat to'liq window larni ko'rsatishi kerak bo'lsa, COUNT(*) OVER (o'sha frame) = 3 bo'yicha filtrlang.

NTILE va PERCENT_RANK

NTILE(n) tartiblangan row larni n ta taxminan teng chelakka bo'ladi — kvartil, detsil, mijoz segmentlari.

SELECT
    enrollment_id, grade,
    NTILE(4)      OVER (ORDER BY grade DESC) AS quartile,
    ROUND(PERCENT_RANK() OVER (ORDER BY grade DESC), 2) AS pct_rank
FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade DESC;

… 1826 qatordan birinchi 15 tasi

1826 qator

3 ms

Window va GROUP BY

Ular yaxshi birlashadi — window function bitta query ichida GROUP BY natijasi ustida ishlashi mumkin:

SELECT
    course_id,
    COUNT(*)                                   AS enrollments,
    SUM(COUNT(*)) OVER ()                      AS total_enrollments,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_all,
    RANK() OVER (ORDER BY COUNT(*) DESC)       AS popularity_rank
FROM enrollments
GROUP BY course_id
ORDER BY enrollments DESC;

14 qator

1 ms

course_idenrollmentstotal_enrollmentspct_of_allpopularity_rank

152025.01 252025.01 542020.03 332015.04 432015.04

SUM(COUNT(*)) OVER () g'alati ko'rinadi, lekin bajarilish tartibidan to'g'ridan-to'g'ri kelib chiqadi: COUNT(*) guruhlash qadamida hisoblanadi, window function esa keyin o'sha guruhlangan row lar ustida ishlaydi. "Har bir row ning umumiy natijadagi ulushi" — eng ko'p so'raladigan hisobot column laridan biri, va uni bitta query da olish yo'li aynan shu.

Mashqlar to'plami

-- 1. Har bir enrollment, uning course o'rtachasi va farqi
SELECT enrollment_id, course_id, grade,
       ROUND(AVG(grade) OVER (PARTITION BY course_id), 1) AS course_avg
FROM enrollments WHERE grade IS NOT NULL ORDER BY course_id, grade DESC;

-- 2. Student larni umumiy o'rtacha baho bo'yicha reyting
WITH avg_per_student AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM enrollments WHERE grade IS NOT NULL GROUP BY student_id
)
SELECT s.first_name, ROUND(a.avg_grade,1) AS avg_grade,
       RANK() OVER (ORDER BY a.avg_grade DESC) AS rank
FROM avg_per_student a JOIN students s USING (student_id);

-- 3. Har bir course dagi eng yuqori baho (guruh bo'yicha top-1)
WITH r AS (
    SELECT course_id, student_id, grade,
           ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY grade DESC) AS rn
    FROM enrollments WHERE grade IS NOT NULL
)
SELECT * FROM r WHERE rn = 1;

-- 4. Har bir student ning enrollment lari sana tartibida raqamlangan
SELECT student_id, enrolled_at, course_id,
       ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY enrolled_at) AS nth_course
FROM enrollments ORDER BY student_id, enrolled_at;

-- 5. Student ning ketma-ket enrollment lari orasidagi kunlar
SELECT student_id, enrolled_at,
       LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at) AS prev,
       JULIANDAY(enrolled_at)
         - JULIANDAY(LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at)) AS days_gap
FROM enrollments ORDER BY student_id, enrolled_at;

-- 6. Vaqt bo'yicha enrollment larning running total i
SELECT enrolled_at, COUNT(*) AS n,
       SUM(COUNT(*)) OVER (ORDER BY enrolled_at) AS running_total
FROM enrollments GROUP BY enrolled_at ORDER BY enrolled_at;

-- 7. Baholarni kvartillarga bo'lish
SELECT enrollment_id, grade, NTILE(4) OVER (ORDER BY grade DESC) AS quartile
FROM enrollments WHERE grade IS NOT NULL;

-- 8. Har bir course ning umumiy daromaddagi ulushi
SELECT c.title, SUM(c.price) AS revenue,
       ROUND(100.0 * SUM(c.price) / SUM(SUM(c.price)) OVER (), 1) AS pct
FROM enrollments e JOIN courses c USING (course_id)
GROUP BY c.course_id, c.title ORDER BY revenue DESC;

1-natija

… 1826 qatordan birinchi 15 tasi

1826 qator

2-natija

… 300 qatordan birinchi 15 tasi

300 qator

3-natija

14 qator

4-natija

… 2500 qatordan birinchi 15 tasi

2500 qator

5-natija

… 2500 qatordan birinchi 15 tasi

2500 qator

6-natija

… 94 qatordan birinchi 15 tasi

94 qator

7-natija

… 1826 qatordan birinchi 15 tasi

1826 qator

8-natija

14 qator

16 ms

Intervyu savollari

S: Window function nima va u aggregate dan nimasi bilan farq qiladi?

Ikkalasi ham row lar to'plami ustida hisoblaydi, lekin GROUP BY li aggregate o'sha row larni bittaga siqadi, window function esa har bir row uchun qiymat qaytaradi va hammasini saqlaydi.

S: ROW_NUMBER, RANK va DENSE_RANK?

Tenglikda: ROW_NUMBER har xil ketma-ket raqam beradi; RANK bir xil raqam berib, keyin o'tkazib yuboradi (1,1,3); DENSE_RANK bir xil raqam berib, bo'shliq qoldirmaydi (1,1,2).

S: Har bir guruhdan top 3 row ni qanday olasiz?

CTE ichida ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC), keyin tashqi query da WHERE rn <= 3.

S: Nega window function ni WHERE da filtrlab bo'lmaydi?

Chunki window function lar SELECT bilan (5-qadam), WHERE dan (2-qadam) keyin hisoblanadi. Qiymat hali mavjud emas. Query ni CTE yoki derived table ga o'rab, tashqarida filtrlang.

S: OVER() ichidagi ORDER BY nimani o'zgartiradi?

U partition ichidagi row larni tartiblaydi va aggregate lar uchun standart frame ni "partition boshidan joriy row gacha" ga o'zgartiradi — ya'ni SUM ni running total ga aylantiradi.

S: Oydan-oyga o'sishni qanday hisoblaysiz?

(n - LAG(n) OVER (ORDER BY month)) * 100.0 / LAG(n) OVER (ORDER BY month). Avval har bir oy row sifatida mavjudligiga ishonch hosil qiling, aks holda LAG noto'g'ri davrga qadam tashlaydi.

S: Frame da ROWS va RANGE?

ROWS jismoniy row larni sanaydi; RANGE bir xil ORDER BY qiymatiga ega row larni bitta birlik deb qaraydi. Ular faqat tartiblash qiymatlari takrorlanganda farq qiladi.

S: SUM(COUNT(*)) OVER () nima qilyapti?

Ichkaridagi COUNT(*) har bir guruh bo'yicha aggregate qiladi; window function esa o'sha guruh yig'indilarini barcha guruhlar bo'yicha qo'shadi — natijada har bir guruhning umumiy natijadagi ulushi bitta query da chiqadi.

Xulosa

• Window function bog'liq row lar ustida ularni siqmasdan hisoblaydi — tafsilot qoladi, xulosa column sifatida qo'shiladi.
• OVER () = barcha row lar · OVER (PARTITION BY x) = har bir guruhda qaytadan · OVER (ORDER BY x) = running / to'planuvchi.
• Ranking: ROW_NUMBER (doim unique), RANK (teng qiymatlar bir xil, keyin o'tkazib yuboriladi), DENSE_RANK (teng qiymatlar bir xil, bo'shliqsiz). ROW_NUMBER ni aniq qilish uchun ajratuvchi qo'shing.
• Guruh bo'yicha Top-N = CTE ichida ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), keyin filtr. Bu — klassik intervyu topshirig'i.
• Window function ni WHERE da filtrlab bo'lmaydi — u SELECT qadamida hisoblanadi. CTE ishlating.
• LAG/LEAD qo'shni row larga qaraydi — avval to'liq sana qatorini yarating, aks holda davrlar arifmetikasi indamay noto'g'ri davrlarni solishtiradi.
• Frame lar (ROWS BETWEEN ...) window ni belgilaydi: running total, moving average, oldinga qarab yig'indi.
• NTILE(n) row larni segmentlarga bo'ladi (kvartil, RFM ball); PERCENT_RANK 0–1 oralig'ida nisbiy o'rin beradi.
• Window function lar GROUP BY ustida ishlashi mumkin — SUM(COUNT(*)) OVER () har bir guruhning umumiy ulushini beradi.

Keyingi: 8-bob — string va date function lar hamda CASE WHEN, ya'ni query ichida qiymatlarni qayta shakllantirish va kategoriyalash vositalari.