Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

~25 daq · MySQL · Funksiyalar

Muammodan boshlaylik

Uch savol:

  1. "Har bir toifadagi eng qimmat 2 ta mahsulot"
  2. "Har bir oydagi tushum va o'tgan oyga nisbatan o'sish foizi"
  3. "Har bir buyurtma — mijozning nechanchi buyurtmasi ekani bilan"

GROUP BY bilan bularni yechish qiyin: u qatorlarni bittaga yig'ib yuboradi. Bizga esa har bir qator saqlansin, lekin yonida "guruh bo'yicha" hisoblangan qiymat bo'lsin.

Window funksiyalar (MySQL 8.0+) aynan shuni qiladi: har bir qator uchun uning "oynasi" (bog'liq qatorlar to'plami) bo'yicha hisoblaydi, qatorlarni yo'qotmasdan.

GROUP BY va OVER farqi

-- GROUP BY: 7 qator → 4 qator
SELECT toifa_id, AVG(narx) FROM mahsulotlar GROUP BY toifa_id;

-- Window: 7 qator → 7 qator, har birida o'z toifasining o'rtachasi
SELECT nom, toifa_id, narx,
       ROUND(AVG(narx) OVER (PARTITION BY toifa_id)) AS toifa_ortacha
FROM mahsulotlar;

+--------------------+----------+----------+---------------+
| nom                | toifa_id | narx     | toifa_ortacha |
+--------------------+----------+----------+---------------+
| Sovg'a kartasi     |     NULL |   500000 |        500000 |
| iPhone 15          |        1 | 12500000 |       8650000 |
| Samsung Galaxy A55 |        1 |  4800000 |       8650000 |
| MacBook Air M3     |        2 | 16900000 |      12050000 |
| Lenovo IdeaPad     |        2 |  7200000 |      12050000 |
| AirPods Pro        |        3 |  3100000 |       1610000 |
| Qopqoq             |        3 |   120000 |       1610000 |
+--------------------+----------+----------+---------------+

OVER (...) — "bu aggregate ni oyna bo'yicha hisobla":

  • PARTITION BY — qatorlarni guruhlarga bo'lish (lekin yig'masdan)
  • ORDER BY — oyna ichidagi tartib
  • OVER () — bo'sh: butun jadval bitta oyna
-- Har bir mahsulot narxining umumiy summadagi ulushi
SELECT nom, narx,
       ROUND(narx * 100 / SUM(narx) OVER (), 1) AS ulush_foiz
FROM mahsulotlar;

Raqamlash: ROW_NUMBER, RANK, DENSE_RANK

SELECT nom, toifa_id, narx,
       ROW_NUMBER() OVER (PARTITION BY toifa_id ORDER BY narx DESC) AS orin
FROM mahsulotlar
WHERE toifa_id IS NOT NULL;

+--------------------+----------+----------+------+
| nom                | toifa_id | narx     | orin |
+--------------------+----------+----------+------+
| iPhone 15          |        1 | 12500000 |    1 |
| Samsung Galaxy A55 |        1 |  4800000 |    2 |
| MacBook Air M3     |        2 | 16900000 |    1 |
| Lenovo IdeaPad     |        2 |  7200000 |    2 |
| AirPods Pro        |        3 |  3100000 |    1 |
| Qopqoq             |        3 |   120000 |    2 |
+--------------------+----------+----------+------+

Har bir toifa ichida raqamlash 1 dan qayta boshlanadi.

Uch xil raqamlash farqi

Ballar: 100, 95, 95, 90:

Funksiya 100 95 95 90
ROW_NUMBER() 1 2 3 4
RANK() 1 2 2 4
DENSE_RANK() 1 2 2 3
  • ROW_NUMBER — doim noyob, tenglarni tasodifiy tartibda
  • RANK — tenglar bir xil o'rin, keyingisi "sakraydi" (sport musobaqalari kabi)
  • DENSE_RANK — tenglar bir xil, keyingisi sakramaydi

Top-N har bir guruhda

1-savolning yechimi:

WITH tartiblangan AS (
    SELECT nom, toifa_id, narx,
           ROW_NUMBER() OVER (PARTITION BY toifa_id ORDER BY narx DESC) AS orin
    FROM mahsulotlar
)
SELECT nom, toifa_id, narx FROM tartiblangan WHERE orin <= 2;

Window funksiyani to'g'ridan-to'g'ri WHERE da ishlatib bo'lmaydi (u WHERE dan keyin hisoblanadi), shuning uchun CTE ga o'raymiz.

Xuddi shu naqsh bilan — "har bir mijozning oxirgi buyurtmasi" (18-darsdagidan ancha sodda):

WITH t AS (
    SELECT b.*, ROW_NUMBER() OVER (PARTITION BY mijoz_id ORDER BY yaratilgan DESC) AS rn
    FROM buyurtmalar b
)
SELECT * FROM t WHERE rn = 1;

Dublikatlarni topish va o'chirish

-- Bir xil emailli dublikatlardan eng eskisidan boshqasini topish
WITH d AS (
    SELECT id, email, ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY id) AS rn
    FROM import_mijozlar
)
SELECT id, email FROM d WHERE rn > 1;

Oldingi va keyingi qator: LAG, LEAD

2-savol — oyma-oy o'sish:

WITH oylik AS (
    SELECT DATE_FORMAT(b.yaratilgan, '%Y-%m') AS oy,
           SUM(q.soni * q.narx) AS tushum
    FROM buyurtmalar b
    JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    WHERE b.holat <> 'bekor'
    GROUP BY oy
)
SELECT
    oy,
    tushum,
    LAG(tushum) OVER (ORDER BY oy) AS otgan_oy,
    ROUND((tushum - LAG(tushum) OVER (ORDER BY oy)) * 100
          / LAG(tushum) OVER (ORDER BY oy), 1) AS osish_foiz
FROM oylik;

+---------+----------+----------+------------+
| oy      | tushum   | otgan_oy | osish_foiz |
+---------+----------+----------+------------+
| 2026-09 |  5040000 |     NULL |       NULL |
| 2026-10 | 23100000 |  5040000 |      358.3 |
| 2026-11 |  1360000 | 23100000 |      -94.1 |
+---------+----------+----------+------------+
  • LAG(ustun) — oldingi qatordagi qiymat (birinchisida NULL)
  • LEAD(ustun) — keyingi qatordagi qiymat
  • LAG(ustun, 2) — ikki qator oldingi; LAG(ustun, 1, 0) — yo'q bo'lsa 0

Nomlangan oyna — takrorlamaslik uchun

SELECT oy, tushum,
       LAG(tushum) OVER w AS otgan_oy,
       LEAD(tushum) OVER w AS keyingi_oy
FROM oylik
WINDOW w AS (ORDER BY oy);

Buyurtmalar orasidagi vaqt

SELECT mijoz_id, id, yaratilgan,
       DATEDIFF(yaratilgan, LAG(yaratilgan) OVER (PARTITION BY mijoz_id ORDER BY yaratilgan))
           AS oldingisidan_kun
FROM buyurtmalar;

Mijozlar qanchalik tez-tez qaytishini ko'rsatadi.

Yig'ilib boruvchi jami (running total)

ORDER BY bor aggregate window — "boshidan shu qatorgacha":

SELECT oy, tushum,
       SUM(tushum) OVER (ORDER BY oy) AS yil_boshidan
FROM oylik;

+---------+----------+--------------+
| oy      | tushum   | yil_boshidan |
+---------+----------+--------------+
| 2026-09 |  5040000 |      5040000 |
| 2026-10 | 23100000 |     28140000 |
| 2026-11 |  1360000 |     29500000 |
+---------+----------+--------------+

3-savol — mijozning nechanchi buyurtmasi:

SELECT id, mijoz_id, yaratilgan,
       ROW_NUMBER() OVER (PARTITION BY mijoz_id ORDER BY yaratilgan) AS nechanchi
FROM buyurtmalar;

"Birinchi xarid" va "qayta xarid" ni ajratish — marketing uchun asosiy ko'rsatkich.

Siljuvchi o'rtacha (moving average)

SELECT kun, tushum,
       AVG(tushum) OVER (ORDER BY kun ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ortacha_7kun
FROM kunlik_tushum;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — joriy qator va oldingi 6 tasi. Grafiklarda kunlik "shovqin" ni tekislaydi.

Boshqa window funksiyalar

SELECT nom, narx,
       FIRST_VALUE(nom) OVER (ORDER BY narx DESC) AS eng_qimmat,
       NTILE(4) OVER (ORDER BY narx) AS kvartil,              -- 4 ta teng guruhga bo'lish
       ROUND(PERCENT_RANK() OVER (ORDER BY narx), 2) AS foizli_orin
FROM mahsulotlar;

NTILE(4) — mijozlarni xarid summasi bo'yicha 4 ta teng guruhga bo'lish (eng yaxshi 25% va h.k.).

Bajarilish tartibidagi o'rni

FROM → WHERE → GROUP BY → HAVING → WINDOW funksiyalar → SELECT → ORDER BY → LIMIT

Window funksiyalar GROUP BY dan keyin hisoblanadi — shuning uchun guruhlangan natija ustida ham ishlaydi (SUM(SUM(x)) OVER ()). Va WHERE da ishlatib bo'lmaydi — CTE yoki subquery ga o'rang.

Nima uchun shunday?

Nima uchun window funksiyalar bunchalik muhim hisoblanadi?

Ulardan oldin "har bir guruhdagi eng ...", "oldingi qator bilan solishtirish", "yig'ilib boruvchi jami" kabi savollar uchun murakkab self-join yoki correlated subquery yozilardi — o'qish qiyin va ko'pincha sekin (har bir qator uchun qayta hisoblash). Window funksiyalar bularni bitta o'tishda, o'qishli shaklda hal qiladi. Analitik so'rovlarning katta qismi endi window funksiyalar atrofida quriladi. MySQL ularni 8.0 versiyada qo'shdi — bu MySQL 8 ning eng muhim yangiliklaridan biri.

Xulosa

Eslab qoling:

  • funksiya() OVER (PARTITION BY ... ORDER BY ...) — qatorlarni yo'qotmasdan guruh bo'yicha hisob
  • ROW_NUMBER / RANK / DENSE_RANK — raqamlash; top-N har bir guruhda
  • LAG / LEAD — oldingi/keyingi qator; o'sish foizi, oraliq vaqtlar
  • SUM(...) OVER (ORDER BY ...) — yig'ilib boruvchi jami
  • ROWS BETWEEN n PRECEDING AND CURRENT ROW — siljuvchi oyna
  • WHERE da ishlatib bo'lmaydi — CTE ga o'rang
  • WINDOW w AS (...) — oynani bir marta e'lon qilish
  • MySQL 8.0+

Keyingi dars

30-dars: Index nima, qanday ishlaydi, qachon kerak