SQL window funksiyalar 2: LAG, LEAD, yig'ilib boruvchi jami va frame'lar

Qisqacha: LAG(x) — oldingi qatordagi qiymat, LEAD(x) — keyingisi: "kechagidan qancha o'sdi?" savoliga javob. sum(x) OVER (ORDER BY kun) — yig'ilib boruvchi jami (running total). Oynaning qaysi qatorlari hisobga kirishini frame belgilaydi: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — oxirgi 3 qator, RANGE BETWEEN interval '2 days' PRECEDING AND CURRENT ROW — oxirgi 3 kun. Eng ko'p uchraydigan tuzoq: ORDER BY li oynada sukut frame joriy qatorda tugaydi, shuning uchun last_value "oxirgi" emas, joriy qatorni qaytaradi.

Bu darsda

• LAG va LEAD bilan qo'shni qatorlarni solishtirasiz.
• Yig'ilib boruvchi jami va sirpanuvchi o'rtacha (moving average) hisoblaysiz.
• ROWS va RANGE frame'lari farqini tenglar va bo'sh kunlar misolida ko'rasiz.
• first_value/last_value tuzog'ini va uning yechimini bilasiz.
• ntile, percent_rank va WINDOW bandini ishlatasiz.

Oldin bilishingiz kerak: Window funksiyalar 1: ranking, Ifodalar va o'rnatilgan funksiyalar (interval), CTE (WITH).

1. Nega bu kerak?

Window funksiyalar 1 darsida har qatorga guruh ichidagi o'rin berdik. Hisobotlarda esa boshqa savollar ko'p:

• "Aziz yangi vazifani oldingisidan qancha vaqt o'tib yozdi?" — qo'shni qator bilan solishtirish.
• "Oktyabr boshidan jami nechta vazifa yaratildi — har kun bo'yicha?" — yig'ilib boruvchi jami.
• "Oxirgi 3 kunda kuniga o'rtacha nechta?" — sirpanuvchi o'rtacha, sakrashlarni tekislaydi.

Bularning hammasida bitta g'oya: har qator uchun oynaning bir qismi kerak — oldingi qator, boshidan shu qatorgacha, oxirgi 3 kun. Oynaning shu qismi frame (ramka) deyiladi. O'xshatish: poyezd derazasi. Butun yo'l — oyna (partition), derazadan ayni paytda ko'rinadigan bo'lak — frame. Poyezd yurgan sari deraza ham suriladi.

2. LAG va LEAD

2.1 Oldingi va keyingi qator

Azizning yuqori darajadagi vazifalari: har biri oldingisidan qancha vaqt o'tib yozilgan va undan keyin qaysi vazifa keldi (biz ishga tushirdik, PG 17.6, vazifalar holat 23):

SELECT id, yaratilgan_vaqt,
       yaratilgan_vaqt - lag(yaratilgan_vaqt)
         OVER (ORDER BY yaratilgan_vaqt) AS oldingidan,
       lead(id) OVER (ORDER BY yaratilgan_vaqt) AS keyingi_id
FROM vazifalar WHERE egasi_id = 1 AND ota_id IS NULL
ORDER BY yaratilgan_vaqt;

 id |    yaratilgan_vaqt     |   oldingidan    | keyingi_id
----+------------------------+-----------------+------------
  1 | 2026-10-01 03:30:00+00 |                 |          2
  2 | 2026-10-01 04:10:00+00 | 00:40:00        |          8
  8 | 2026-10-02 13:40:00+00 | 1 day 09:30:00  |          9
  9 | 2026-10-03 02:55:00+00 | 13:15:00        |         17
 17 | 2026-10-05 16:00:00+00 | 2 days 13:05:00 |         23
 23 | 2026-10-07 05:15:00+00 | 1 day 13:15:00  |         28
 28 | 2026-10-10 07:00:00+00 | 3 days 01:45:00 |         34
 34 | 2026-10-14 14:30:00+00 | 4 days 07:30:00 |         38
 38 | 2026-10-18 12:20:00+00 | 3 days 21:50:00 |
(9 rows)

Kanon tajribasida xuddi shu so'rov lead(matn) bilan sinalgan (PostgreSQL 18.6) — telefon ekraniga sig'ishi uchun biz lead(id) qildik, vaqtlar bir xil.

• lag(x) — oyna tartibida oldingi qatordagi x. Birinchi qatorda oldingi yo'q — NULL, shuning uchun oldingidan bo'sh.
• lead(x) — keyingi qatordagi x. Oxirgi qatorda — NULL.

Natijadan ko'rinadi: Aziz boshida tez-tez yozgan (40 daqiqa, 13 soat), oxirida 3–4 kunda bir marta. Bu — "faollik pasaymoqda" signali.

2.2 Qadam va sukut qiymat

lag ning to'liq shakli: lag(x, qadam, sukut). lag(x, 2) — ikki qator oldingi. lag(x, 1, 0) — oldingi bo'lmasa NULL o'rniga 0. Masalan kunlik sonlar o'sishini hisoblashda birinchi kun uchun 0 qulayroq.

Tekshirib ko'ring: lead(yaratilgan_vaqt) OVER (ORDER BY yaratilgan_vaqt) - yaratilgan_vaqt nimani beradi va u yuqoridagi oldingidan ustunidan qanday farq qiladi?

3. Yig'ilib boruvchi jami

3.1 Running total

Har kuni nechta vazifa yaratilgan va boshidan jami. Avval CTE'da kunlik sonlar, keyin ular ustida window:

WITH kunlik AS (
  SELECT yaratilgan_vaqt::date AS kun, count(*) AS soni
  FROM vazifalar GROUP BY 1
)
SELECT kun, soni, sum(soni) OVER (ORDER BY kun) AS jami
FROM kunlik ORDER BY kun LIMIT 8;

    kun     | soni | jami
------------+------+------
 2026-10-01 |    3 |    3
 2026-10-02 |    7 |   10
 2026-10-03 |    6 |   16
 2026-10-04 |    2 |   18
 2026-10-05 |    6 |   24
 2026-10-06 |    4 |   28
 2026-10-07 |    2 |   30
 2026-10-08 |    2 |   32
(8 rows)

Kanon tajribasi. sum(soni) OVER (ORDER BY kun) — har qatorda: boshidan shu kungacha bo'lgan sonlar yig'indisi. Avvalgi darsda OVER () (tartibsiz) butun natija yig'indisini berardi. Oynaga ORDER BY qo'shilishi bilan frame "boshidan joriy qatorgacha" bo'ladi. Bu farq butun darsning kaliti.

Grafikda ikki chiziqqa qarang: kunlik son sakraydi (7, 2, 6), jami esa doim o'sadi — u faqat kamaymaydi. Dashboard'lardagi "jami foydalanuvchilar" grafiklari aynan shu so'rov bilan chiziladi.

3.2 Sukut frame nima?

Postgres hujjatiga ko'ra, oynada ORDER BY bo'lsa, sukut frame:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

"Boshidan (cheklanmagan oldingi) joriy qatorgacha". ORDER BY bo'lmasa — butun partition. Frame'ni o'zingiz yozishingiz ham mumkin. Uning uch qismi bor:

PRECEDING — oldingi, FOLLOWING — keyingi, UNBOUNDED — chegarasiz (partition oxirigacha).

Diagrammada joriy qator — 3-qator. Sukut frame unga qadar bo'lgan hamma qatorlarni oladi, keyingilarini esa ko'rmaydi:

flowchart LR
  subgraph F["sukut frame: boshidan joriy qatorgacha"]
    Q1["1-qator"] --> Q2["2-qator"] --> Q3["3-qator (joriy)"]
  end
  Q3 --> Q4["4-qator: frame tashqarisida"]
  Q4 --> Q5["5-qator: frame tashqarisida"]

Joriy qator 4-qatorga o'tganda frame ham bir qatorga cho'ziladi — poyezd derazasi surildi.

4. ROWS va RANGE

4.1 Tenglarda farq

Sukut rejim — RANGE, va u tenglarni birga oladi. Bitta kunda bir nechta vazifa bo'lsa, ular oyna tartibida teng. Kanon tajribasi. Natijada 1-oktyabrning uch qatoriga qarang va ikki ustunni solishtiring:

SELECT id, yaratilgan_vaqt::date AS kun,
  count(*) OVER (ORDER BY yaratilgan_vaqt::date) AS range_sukut,
  count(*) OVER (ORDER BY yaratilgan_vaqt::date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM vazifalar WHERE yaratilgan_vaqt < '2026-10-04'
ORDER BY yaratilgan_vaqt, id;

 id |    kun     | range_sukut | rows_frame
----+------------+-------------+------------
  1 | 2026-10-01 |           3 |          3
  2 | 2026-10-01 |           3 |          2
  3 | 2026-10-01 |           3 |          1
  4 | 2026-10-02 |          10 |          6
  5 | 2026-10-02 |          10 |          7
  6 | 2026-10-02 |          10 |          5
  7 | 2026-10-02 |          10 |          8
 41 | 2026-10-02 |          10 |          9
 42 | 2026-10-02 |          10 |         10
  8 | 2026-10-02 |          10 |          4
  9 | 2026-10-03 |          16 |         15
 10 | 2026-10-03 |          16 |         11
 11 | 2026-10-03 |          16 |         14
 12 | 2026-10-03 |          16 |         12
 46 | 2026-10-03 |          16 |         13
 47 | 2026-10-03 |          16 |         16
(16 rows)

range_sukut: 1-oktyabrning hamma uch qatorida 3 — RANGE uchun bir kundagi qatorlar "bitta nuqta", frame ularning hammasini oladi. rows_frame esa qatorma-qator sanaydi: 1, 2, 3, 4...

Lekin rows_frame raqamlariga diqqat qiling: 1-vazifada 3, 3-vazifada 1. Nega teskari? Oyna tartibi faqat kun bo'yicha. Bir kun ichidagi qatorlar tartibi kafolatlanmagan, Postgres ularni o'zi tanlagan tartibda sanadi. Bu Window funksiyalar 1 darsidagi ROW_NUMBER tuzog'ining o'zi: tenglarda ROWS tasodifiy. Kanon (PostgreSQL 18.6) va bizning sinov 17.6-bob aynan shu raqamlarni berdi, lekin bunga tayanib bo'lmaydi.

Qoida: running total uchun oyna tartibi noyob bo'lsin (ORDER BY yaratilgan_vaqt, id) — unda ROWS va RANGE bir xil va barqaror natija beradi.

4.2 Bo'sh kunlarda farq: "3 qator" va "3 kun"

Ikkinchi farq — oraliq bilan. Azizning kunlik vazifalari va ularning 3 kunlik yig'indisi ikki usulda (biz ishga tushirdik, PG 17.6):

WITH kunlik AS (
  SELECT yaratilgan_vaqt::date AS kun, count(*) AS soni
  FROM vazifalar WHERE egasi_id = 1 GROUP BY 1
)
SELECT kun, soni,
  sum(soni) OVER (ORDER BY kun
    RANGE BETWEEN interval '2 days' PRECEDING AND CURRENT ROW)
    AS uch_kun,
  sum(soni) OVER (ORDER BY kun
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS uch_qator
FROM kunlik ORDER BY kun;

    kun     | soni | uch_kun | uch_qator
------------+------+---------+-----------
 2026-10-01 |    2 |       2 |         2
 2026-10-02 |    1 |       3 |         3
 2026-10-03 |    1 |       4 |         4
 2026-10-05 |    4 |       5 |         6
 2026-10-07 |    1 |       5 |         6
 2026-10-10 |    1 |       1 |         6
 2026-10-14 |    1 |       1 |         3
 2026-10-18 |    1 |       1 |         3
(8 rows)

Aziz har kuni yozmagan: 4, 6, 8, 9-oktyabrlar yo'q. 10-oktyabr qatoriga qarang:

"Oxirgi 3 kunda" degan savolga to'g'ri javob — RANGE (1). ROWS "oxirgi 3 ta yozuv" ni oldi va 5-oktyabrdagi 4 ta vazifani ham qo'shib yubordi. Qoida: vaqt bo'yicha oyna — RANGE + interval. Yozuvlar soni bo'yicha — ROWS. RANGE bilan n PRECEDING faqat bitta ORDER BY ustuni bilan ishlaydi (son, sana yoki vaqt).

Uchinchi rejim — GROUPS (PG 11+). U tenglar guruhini sanaydi: GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW — "joriy qiymat va undan oldingi bitta turli qiymat". Kunlik qatorlarga o'xshagan, lekin hali guruhlanmagan ma'lumotda qulay: har vazifa qatorida "bugun va oldingi faol kun" vazifalari. Amalda kam ishlatiladi, lekin hujjatda va boshqa kodda uchrasa, tanib olasiz.

4.3 Sirpanuvchi o'rtacha

sum o'rniga avg — moving average (sirpanuvchi o'rtacha). Hamma foydalanuvchilarning kunlik vazifalari, 3 kunlik o'rtacha bilan (kanon tajribasi):

WITH kunlik AS (
  SELECT yaratilgan_vaqt::date AS kun, count(*) AS soni
  FROM vazifalar GROUP BY 1
)
SELECT kun, soni,
  round(avg(soni) OVER (ORDER BY kun
    RANGE BETWEEN interval '2 days' PRECEDING AND CURRENT ROW), 2)
    AS oxirgi_3_kun
FROM kunlik ORDER BY kun LIMIT 8;

    kun     | soni | oxirgi_3_kun
------------+------+--------------
 2026-10-01 |    3 |         3.00
 2026-10-02 |    7 |         5.00
 2026-10-03 |    6 |         5.33
 2026-10-04 |    2 |         5.00
 2026-10-05 |    6 |         4.67
 2026-10-06 |    4 |         4.00
 2026-10-07 |    2 |         4.00
 2026-10-08 |    2 |         2.67
(8 rows)

Kunlik son 3 → 7 → 6 → 2 sakraydi, o'rtacha esa 3.00 → 5.00 → 5.33 → 5.00 — silliq. Birinchi kunlarda frame to'liq emas (1-oktyabrda faqat o'zi) — o'rtacha kamroq kunlardan olinadi. Hisobotda buni hisobga oling yoki dastlabki kunlarni ko'rsatmang.

Diqqat: bu yerda count(*) ning bo'sh kunlari yo'q (hamma kunda vazifa bor). Bo'sh kunlar bo'lsa, ular kunlik da umuman qator bo'lmaydi va o'rtacha "ko'tarilib" ketadi. Bo'sh kunlarni 0 bilan to'ldirish — Tahliliy SQL naqshlari darsida generate_series bilan.

Tekshirib ko'ring: 7 kunlik sirpanuvchi o'rtacha uchun frame qanday yoziladi — ROWS bilanmi, RANGE bilanmi?

5. first_value, last_value va WINDOW bandi

5.1 last_value tuzog'i

Gulnoraning vazifalari: birinchisi va oxirgisi har qatorda (biz ishga tushirdik, PG 17.6; kanon tajribasi xuddi shu so'rovni matnlar bilan bajargan):

SELECT id,
  first_value(id) OVER w AS birinchi,
  last_value(id) OVER w AS last_sukut,
  last_value(id) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                         AND UNBOUNDED FOLLOWING) AS last_toliq
FROM vazifalar WHERE egasi_id = 6
WINDOW w AS (ORDER BY yaratilgan_vaqt)
ORDER BY yaratilgan_vaqt;

 id | birinchi | last_sukut | last_toliq
----+----------+------------+------------
  4 |        4 |          4 |         30
 12 |        4 |         12 |         30
 22 |        4 |         22 |         30
 30 |        4 |         30 |         30
(4 rows)

first_value to'g'ri: hamma joyda 4. last_value esa har qatorda o'zini qaytardi: 4, 12, 22, 30. Nega? Sukut frame "boshidan joriy qatorgacha". 12-vazifa qatorida frame = {4, 12}, uning oxirgisi — 12. Funksiya to'g'ri ishladi, frame biz kutgandek emas edi.

Yechim — frame'ni partition oxirigacha cho'zish: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Shunda hamma qatorda 30. Ko'pincha undan ham soddasi bor: oyna tartibini teskari qilib, first_value ishlatish.

Xuddi shu tuzoq nth_value(x, n) da ham bor ("oynadagi n-qiymat"): sukut frame'da u n-qatorga yetmaguncha NULL beradi.

5.2 WINDOW bandi

Yuqoridagi so'rovda yangi narsa: WINDOW w AS (...) — oynaga nom berish. Bir xil oyna uch joyda kerak bo'lsa, uni bir marta yozib, OVER w deb murojaat qilasiz. OVER (w ROWS BETWEEN ...) — nomli oynani olib, unga frame qo'shish. WINDOW bandi HAVING dan keyin, ORDER BY dan oldin yoziladi.

Bu CTE'ga o'xshash g'oya — takrorni nom bilan almashtirish. Oyna tartibini o'zgartirish kerak bo'lsa, bitta joyda o'zgartirasiz.

6. ntile va percent_rank

Bajarilgan vazifalar — qanchalik tez bajarilgani bo'yicha. Kanon tajribasida 22 qator bor edi, bu yerda birinchi 8 tasi va matn o'rniga id (biz ishga tushirdik, PG 17.6):

SELECT id, bajarilgan_vaqt - yaratilgan_vaqt AS vaqt,
  ntile(4) OVER (ORDER BY bajarilgan_vaqt - yaratilgan_vaqt)
    AS chorak,
  round(percent_rank() OVER (ORDER BY bajarilgan_vaqt
        - yaratilgan_vaqt)::numeric, 2) AS foiz_rank
FROM vazifalar WHERE bajarildi ORDER BY vaqt, id LIMIT 8;

 id |   vaqt   | chorak | foiz_rank
----+----------+--------+-----------
 28 | 00:05:00 |      1 |      0.00
 37 | 00:10:00 |      1 |      0.05
 15 | 00:30:00 |      1 |      0.10
  8 | 00:40:00 |      1 |      0.14
  4 | 00:45:00 |      1 |      0.19
 16 | 01:00:00 |      1 |      0.24
 20 | 01:00:00 |      2 |      0.24
 35 | 01:30:00 |      2 |      0.33
(8 rows)

• ntile(4) — qatorlarni 4 ta teng guruhga bo'ladi (chorak, quartile). 22 qator / 4 = 5.5 — birinchi ikki guruhda 6 tadan, qolganlarida 5 tadan. 1-chorak — eng tez bajarilganlar.
• percent_rank() — nisbiy o'rin 0 dan 1 gacha: (rank - 1) / (qatorlar - 1). 16-vazifada rank = 6, demak (6 − 1) / (22 − 1) = 5 / 21 ≈ 0.24: "qolgan 21 vazifadan 5 tasi (taxminan 24 %) bundan tezroq bajarilgan".

16 va 20-vazifalarni solishtiring. Ikkalasi ham 1 soatda bajarilgan, lekin biri 1-chorakda, biri 2-chorakda. ntile guruhlarni teng qilish uchun tenglarni ham ajratadi. percent_rank esa tenglarga bir xil qiymat beradi 0.24-bob — u RANK ga asoslangan.

cume_dist() — o'xshash funksiya: "shu qiymatgacha va shu qiymatni o'z ichiga olib necha foiz". Mediana kabi foiz chegaralari uchun esa window emas, agregat bor: percentile_cont(0.5) WITHIN GROUP (ORDER BY ...).

7. Ko'p uchraydigan xatolar

7.1 last_value joriy qatorni qaytaradi

Sukut frame joriy qatorda tugaydi. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING qo'shing yoki teskari tartib bilan first_value.

7.2 Running total "sakraydi"

Oyna tartibida tenglar bor va sukut RANGE ularni birga qo'shdi. Tartibga noyob ustun qo'shing.

7.3 "Oxirgi 3 kun" noto'g'ri

ROWS 2 PRECEDING — 3 kun emas, 3 qator. Vaqt oynasi uchun RANGE ... interval.

7.4 window functions are not allowed in WHERE

SELECT id, matn FROM vazifalar WHERE egasi_id = 1 AND id = 23
  AND lag(id) OVER (ORDER BY id) IS NOT NULL;

ERROR:  window functions are not allowed in WHERE
LINE 2:   AND lag(id) OVER (ORDER BY id) IS NOT NULL;
              ^

Window natijasi bilan filtrlash — CTE orqali (4-mashqda ko'rasiz). Bu holda yana bitta tuzoq bor: WHERE id = 23 window'dan oldin ishlaydi. CTE'siz yozsangiz, oynada faqat bitta qator qoladi va lag doim NULL bo'ladi.

8. Mashqlar

Mashq fayllari kurs/mashqlar/26/29-window-qiymat/ da. Hammasi vazifalar holat 23 da.

1-mashq (oson): oldingi vazifa

Bekzodning (egasi_id = 7) vazifalari yaratilish tartibida: id, matn va oldingi vazifaning id si. Yordam: lag(id) OVER (ORDER BY yaratilgan_vaqt).

SELECT id, matn, lag(id) OVER (ORDER BY yaratilgan_vaqt) AS oldingi
FROM vazifalar WHERE egasi_id = 7 ORDER BY yaratilgan_vaqt;

 id |               matn               | oldingi
----+----------------------------------+---------
 19 | Universitet kontrakti #to'lov    |
 25 | Algoritmlar: 5 ta masala #o'qish |      19
 33 | Noutbukka SSD qo'yish #ta'mir    |      25
 39 | Do'stlar bilan choyxona #dam     |      33
(4 rows)

2-mashq (o'rta): bajarilganlar jamg'armasi

Har kuni nechta vazifa bajarilgan va boshidan jami — birinchi 6 kun. Yordam: «Running total» bo'limidagi so'rov, yaratilgan_vaqt o'rniga bajarilgan_vaqt va WHERE bajarildi.

WITH kunlik AS (
  SELECT bajarilgan_vaqt::date AS kun, count(*) AS soni
  FROM vazifalar WHERE bajarildi GROUP BY 1
)
SELECT kun, soni, sum(soni) OVER (ORDER BY kun) AS jami
FROM kunlik ORDER BY kun LIMIT 6;

    kun     | soni | jami
------------+------+------
 2026-10-01 |    2 |    2
 2026-10-02 |    2 |    4
 2026-10-03 |    2 |    6
 2026-10-04 |    1 |    7
 2026-10-05 |    2 |    9
 2026-10-06 |    2 |   11
(6 rows)

3-mashq (qiyin): tanaffuslar

Aziz vazifa yozgan kunlar va har biri oldingi faol kundan necha kun keyin kelgani: kun, oldingi, farq_kun. Yordam: CTE'da DISTINCT yaratilgan_vaqt::date, keyin lag(kun) va ikki sananing ayirmasi (date - date — kunlar soni, butun son).

WITH kunlar AS (
  SELECT DISTINCT yaratilgan_vaqt::date AS kun
  FROM vazifalar WHERE egasi_id = 1
)
SELECT kun, lag(kun) OVER (ORDER BY kun) AS oldingi,
       kun - lag(kun) OVER (ORDER BY kun) AS farq_kun
FROM kunlar ORDER BY kun;

    kun     |  oldingi   | farq_kun
------------+------------+----------
 2026-10-01 |            |
 2026-10-02 | 2026-10-01 |        1
 2026-10-03 | 2026-10-02 |        1
 2026-10-05 | 2026-10-03 |        2
 2026-10-07 | 2026-10-05 |        2
 2026-10-10 | 2026-10-07 |        3
 2026-10-14 | 2026-10-10 |        4
 2026-10-18 | 2026-10-14 |        4
(8 rows)

4-mashq: SQL qadami — "oldingi / keyingi" tugmalari

Vazifa sahifasida "← oldingi" va "keyingi →" tugmalari bo'ladi. Azizning yuqori darajadagi vazifalari (ota_id IS NULL) yaratilish tartibida: 23-vazifaning oldingisi va keyingisining id sini toping. Sxema o'zgarmaydi — commit yo'q. Yordam: CTE'da lag/lead hamma qatorlar uchun, tashqarida WHERE id = 23 («window functions are not allowed in WHERE» xatosini eslang).

WITH t AS (
  SELECT id,
         lag(id)  OVER (ORDER BY yaratilgan_vaqt, id) AS oldingi,
         lead(id) OVER (ORDER BY yaratilgan_vaqt, id) AS keyingi
  FROM vazifalar WHERE egasi_id = 1 AND ota_id IS NULL
)
SELECT * FROM t WHERE id = 23;

 id | oldingi | keyingi
----+---------+---------
 23 |      17 |      28
(1 row)

9. Real ishda

• Moliya va savdo: kunlik tushum va oy boshidan jami (running total), "kechagiga nisbatan +12 %" (lag), 7 kunlik o'rtacha — har dashboard'da.
• Mahsulot analitikasi: foydalanuvchi sessiyalari orasidagi vaqt (lag), "ketma-ket kunlar" seriyasi, birinchi va oxirgi harakat (first_value).
• Monitoring: server javob vaqtining sirpanuvchi o'rtachasi va 95-persentili.
• Valyuta kurslari: 24-qismdagi valyuta vositasidagi kunlik kurs o'zgarishi — bazada lag(kurs) OVER (PARTITION BY valyuta ORDER BY sana).
• Intervyu: "oylik savdoning o'tgan oyga nisbatan o'sishini toping", "ROWS va RANGE farqi" — tez-tez so'raladi.

Xulosa

• lag/lead — oldingi/keyingi qator qiymati; chetida NULL (yoki uchinchi argument).
• sum(x) OVER (ORDER BY ...) — yig'ilib boruvchi jami: ORDER BY li oynada sukut frame "boshidan joriy qatorgacha".
• ROWS — qatorlar soni, RANGE — qiymat oralig'i (tenglar birga, interval bilan vaqt oynasi).
• last_value sukut frame'da joriy qatorni beradi — frame'ni UNBOUNDED FOLLOWING gacha cho'zing.
• ntile(n) — teng guruhlar, percent_rank — 0…1 nisbiy o'rin; WINDOW w AS (...) — oynaga nom.

Keyingi dars: To'plam amallari — UNION, INTERSECT va EXCEPT bilan ikki so'rov natijasini birlashtiramiz.

Manbalar

• PostgreSQL 18 hujjati — Window Function Calls (frame_clause, sukut frame): https://www.postgresql.org/docs/18/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
• PostgreSQL 18 hujjati — Window Functions (lag, lead, ntile, percent_rank, first_value, last_value, nth_value): https://www.postgresql.org/docs/18/functions-window.html