Oyna funksiyalari

1711 daq

GROUP BY qatorlarni bittaga «yig‘ib» qo‘yadi. Oyna funksiyalari esa har bir qatorni o‘z joyida qoldirib, yoniga guruh bo‘yicha hisobni qo‘shadi: o‘rin, o‘sib boruvchi yig‘indi, oldingi qatordagi qiymat.

Qolip

funksiya() OVER (PARTITION BY guruh ORDER BY tartib)

PARTITION BY — qaysi guruhlar ichida hisoblash (yozilmasa — butun natija bitta guruh);
ORDER BY — guruh ichidagi tartib.

O‘rinlar: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

university· O‘zgartirib ko‘ring

SELECT first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM teachers
LIMIT 10;

ROW_NUMBER — 1, 2, 3… teng qiymatlarga ham har xil raqam;
RANK — tenglarga bir xil o‘rin, keyingisi sakraydi (1, 1, 3);
DENSE_RANK — tenglarga bir xil o‘rin, sakramaydi (1, 1, 2).

Har bir guruh ichida

Har bir fakultetda eng yuqori maoshli ustoz:

university· O‘zgartirib ko‘ring

WITH ranked AS (
  SELECT faculty_id, first_name, last_name, salary,
         RANK() OVER (PARTITION BY faculty_id ORDER BY salary DESC) AS rnk
  FROM teachers
)
SELECT faculty_id, first_name, last_name, salary FROM ranked WHERE rnk = 1;

Oyna funksiyasini WHERE ichida ishlatib bo‘lmaydi — shuning uchun avval CTE’da hisoblab, keyin filtrlaymiz.

Agregat + `OVER`: ulush va o‘sib boruvchi yig‘indi

shop· O‘zgartirib ko‘ring

SELECT c.name, COUNT(*) AS products,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share
FROM products p JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY products DESC;

SUM(...) OVER () — butun natija bo‘yicha jami, har bir qator yonida.

Oylar bo‘yicha o‘sib boruvchi tushum:

taxi· O‘zgartirib ko‘ring

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, SUM(fare) AS revenue
  FROM trips
  WHERE status = 'completed' AND requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, revenue, SUM(revenue) OVER (ORDER BY mon) AS running_total
FROM m ORDER BY mon;

`LAG` va `LEAD` — qo‘shni qator

LAG(x) — oldingi qatordagi qiymat, LEAD(x) — keyingisi. O‘tgan oyga nisbatan o‘zgarish:

taxi· O‘zgartirib ko‘ring

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, COUNT(*) AS trips
  FROM trips WHERE requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, trips, trips - LAG(trips) OVER (ORDER BY mon) AS diff
FROM m ORDER BY mon;

1Mashq

Har bir xodimning first_name, last_name, salary ustunlarini va butun kompaniya bo‘yicha oylik o‘rnini (DENSE_RANK, eng yuqori — 1) chiqaring. O‘rin o‘sib borish tartibida.

2Mashq

Har bir talabaning id, first_name ustunlarini va o‘z guruhidagi o‘rnini chiqaring: kim avval o‘qishga kirgan (enrolled_at) — o‘sha birinchi, sana teng bo‘lsa — id kichigi birinchi (ROW_NUMBER, PARTITION BY group_id).

Esda qoldiring

Oyna funksiyasi qatorlarni yig‘maydi — har bir qator yoniga hisob qo‘shadi.
PARTITION BY — guruh, ORDER BY — tartib.
Natija bo‘yicha filtrlash uchun avval CTE yoki ichki so‘rov kerak.

Oyna funksiyalari

1711 daq

Qolip

funksiya() OVER (PARTITION BY guruh ORDER BY tartib)

PARTITION BY — qaysi guruhlar ichida hisoblash (yozilmasa — butun natija bitta guruh);
ORDER BY — guruh ichidagi tartib.

O‘rinlar: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

university· O‘zgartirib ko‘ring

SELECT first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM teachers
LIMIT 10;

ROW_NUMBER — 1, 2, 3… teng qiymatlarga ham har xil raqam;
RANK — tenglarga bir xil o‘rin, keyingisi sakraydi (1, 1, 3);
DENSE_RANK — tenglarga bir xil o‘rin, sakramaydi (1, 1, 2).

Har bir guruh ichida

Har bir fakultetda eng yuqori maoshli ustoz:

university· O‘zgartirib ko‘ring

WITH ranked AS (
  SELECT faculty_id, first_name, last_name, salary,
         RANK() OVER (PARTITION BY faculty_id ORDER BY salary DESC) AS rnk
  FROM teachers
)
SELECT faculty_id, first_name, last_name, salary FROM ranked WHERE rnk = 1;

Oyna funksiyasini WHERE ichida ishlatib bo‘lmaydi — shuning uchun avval CTE’da hisoblab, keyin filtrlaymiz.

Agregat + `OVER`: ulush va o‘sib boruvchi yig‘indi

shop· O‘zgartirib ko‘ring

SELECT c.name, COUNT(*) AS products,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share
FROM products p JOIN categories c ON c.id = p.category_id
GROUP BY c.name
ORDER BY products DESC;

SUM(...) OVER () — butun natija bo‘yicha jami, har bir qator yonida.

Oylar bo‘yicha o‘sib boruvchi tushum:

taxi· O‘zgartirib ko‘ring

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, SUM(fare) AS revenue
  FROM trips
  WHERE status = 'completed' AND requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, revenue, SUM(revenue) OVER (ORDER BY mon) AS running_total
FROM m ORDER BY mon;

`LAG` va `LEAD` — qo‘shni qator

LAG(x) — oldingi qatordagi qiymat, LEAD(x) — keyingisi. O‘tgan oyga nisbatan o‘zgarish:

taxi· O‘zgartirib ko‘ring

WITH m AS (
  SELECT EXTRACT(MONTH FROM requested_at) AS mon, COUNT(*) AS trips
  FROM trips WHERE requested_at >= '2026-01-01'
  GROUP BY EXTRACT(MONTH FROM requested_at)
)
SELECT mon, trips, trips - LAG(trips) OVER (ORDER BY mon) AS diff
FROM m ORDER BY mon;

1Mashq

Har bir xodimning first_name, last_name, salary ustunlarini va butun kompaniya bo‘yicha oylik o‘rnini (DENSE_RANK, eng yuqori — 1) chiqaring. O‘rin o‘sib borish tartibida.

2Mashq

Esda qoldiring

Oyna funksiyasi qatorlarni yig‘maydi — har bir qator yoniga hisob qo‘shadi.
PARTITION BY — guruh, ORDER BY — tartib.
Natija bo‘yicha filtrlash uchun avval CTE yoki ichki so‘rov kerak.

SQLab

Oyna funksiyalari

Qolip

O‘rinlar: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Har bir guruh ichida

Agregat + `OVER`: ulush va o‘sib boruvchi yig‘indi

`LAG` va `LEAD` — qo‘shni qator

Esda qoldiring

Oyna funksiyalari

Qolip

O‘rinlar: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Har bir guruh ichida

Agregat + `OVER`: ulush va o‘sib boruvchi yig‘indi

`LAG` va `LEAD` — qo‘shni qator

Esda qoldiring

SQLab

Oyna funksiyalari

Qolip

O‘rinlar: ROW_NUMBER, RANK, DENSE_RANK

Har bir guruh ichida

Agregat + OVER: ulush va o‘sib boruvchi yig‘indi

LAG va LEAD — qo‘shni qator

Esda qoldiring

Oyna funksiyalari

Qolip

O‘rinlar: ROW_NUMBER, RANK, DENSE_RANK

Har bir guruh ichida

Agregat + OVER: ulush va o‘sib boruvchi yig‘indi

LAG va LEAD — qo‘shni qator

Esda qoldiring

O‘rinlar: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Agregat + `OVER`: ulush va o‘sib boruvchi yig‘indi

`LAG` va `LEAD` — qo‘shni qator

O‘rinlar: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Agregat + `OVER`: ulush va o‘sib boruvchi yig‘indi

`LAG` va `LEAD` — qo‘shni qator