Window Functions — SUM, AVG, LAG, LEAD, FIRST_VALUE, LAST_VALUE
Muammodan boshlaylik
Oylik sotuvlar hisobotini tayyorlayapsiz. Har bir oy uchun ikkita narsa kerak:
- O'sha oyning sotuvlari
- Oldingi oyga nisbatan o'sish yoki kamayish foizi
Birinchisi oddiy. Ikkinchisi uchun "oldingi qatorning qiymatini" olish kerak. SQL da qatorlar o'rtasida harakatlanish — bu LAG.
Yoki boshqa misol: har bir sotuv yonida o'sha oygacha to'plangan umumiy summa ko'rsatish kerak — bu "running total", ya'ni SUM window function bilan.
Aggregate funksiyalar window sifatida
Oldingi darsda tanishgan SUM, AVG, COUNT — GROUP BY bilan qatorlarni birlashtiradi. Lekin ular OVER() bilan window function sifatida ham ishlaydi — qatorlarni birlashtirmaydi.
GROUP BY bilan: OVER() bilan:
───────────────── ─────────────────────────────────
oy │ sotuv oy │ sotuv │ jami_shu_paytgacha
─────┼─────── ─────┼────────┼───────────────────
Jan │ 100 Jan │ 100 │ 100
Feb │ 150 → Feb │ 150 │ 250
Mar │ 120 Mar │ 120 │ 370
─────┼───────
jami│ 370 ← qatorlar yo'qoldi ↑ qatorlar saqlanib qoldi
Namuna jadval
CREATE TABLE oylik_sotuv (
id SERIAL PRIMARY KEY,
oy TEXT,
sotuv NUMERIC
);
INSERT INTO oylik_sotuv (oy, sotuv) VALUES
('Yanvar', 1200000),
('Fevral', 1850000),
('Mart', 1100000),
('Aprel', 2300000),
('May', 1950000),
('Iyun', 2700000);
SUM — to'planib boruvchi yig'indi (Running Total)
Har bir oy uchun o'sha oygacha bo'lgan umumiy sotuv:
SELECT
oy,
sotuv,
SUM(sotuv) OVER (ORDER BY id) AS jami_shu_paytgacha
FROM oylik_sotuv;
oy │ sotuv │ jami_shu_paytgacha
----------+---------+--------------------
Yanvar │ 1200000 │ 1200000
Fevral │ 1850000 │ 3050000
Mart │ 1100000 │ 4150000
Aprel │ 2300000 │ 6450000
May │ 1950000 │ 8400000
Iyun │ 2700000 │ 11100000
ORDER BY id — qatorlar tartibini belgilaydi. Har bir qatorda SUM o'sha qatorgacha bo'lgan barcha qiymatlarni qo'shadi.
AVG — harakatlanuvchi o'rtacha (Moving Average)
Oxirgi 3 oyning o'rtacha sotuvini ko'rsatish — mavsumiy tebranishlarni tekislash uchun:
SELECT
oy,
sotuv,
ROUND(
AVG(sotuv) OVER (
ORDER BY id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-- ↑ hozirgi qator + 2 ta oldingisi
)
) AS harakatlanuvchi_ortacha
FROM oylik_sotuv;
oy │ sotuv │ harakatlanuvchi_ortacha
----------+---------+-------------------------
Yanvar │ 1200000 │ 1200000 ← faqat bitta qator bor
Fevral │ 1850000 │ 1525000 ← ikki qatorning o'rtachasi
Mart │ 1100000 │ 1383333 ← uch qatorning o'rtachasi
Aprel │ 2300000 │ 1750000
May │ 1950000 │ 1783333
Iyun │ 2700000 │ 2316667
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — "oyna" chegarasini belgilaydi: hozirgi qator va 2 ta oldingi qator.
ROWS BETWEEN — oyna chegarasi
Window function qaysi qatorlar ustida hisoblashini ROWS BETWEEN bilan aniq belgilash mumkin:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ← boshidan hozirgi qatorgacha
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ← 2 ta oldingi + hozirgi
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ← hozirgi + 1 ta keyingi
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ← barcha qatorlar
LAG — oldingi qatorning qiymati
Har bir qator uchun oldingi qatorning qiymatini olish. Oylik o'sishni hisoblash uchun:
SELECT
oy,
sotuv,
LAG(sotuv) OVER (ORDER BY id) AS oldingi_oy,
sotuv - LAG(sotuv) OVER (ORDER BY id) AS farq
FROM oylik_sotuv;
oy │ sotuv │ oldingi_oy │ farq
----------+---------+------------+---------
Yanvar │ 1200000 │ NULL │ NULL ← birinchi qator, oldingisi yo'q
Fevral │ 1850000 │ 1200000 │ 650000
Mart │ 1100000 │ 1850000 │ -750000
Aprel │ 2300000 │ 1100000 │ 1200000
May │ 1950000 │ 2300000 │ -350000
Iyun │ 2700000 │ 1950000 │ 750000
LAG(sotuv) — bir qator yuqoridagi sotuv ustunining qiymatini oladi. Birinchi qatorda oldingi yo'q — NULL.
LAG ga standart qiymat berish — NULL o'rniga:
LAG(sotuv, 1, 0) OVER (ORDER BY id)
-- ↑ ↑
-- necha qadam standart qiymat
LEAD — keyingi qatorning qiymati
LAG ning aksi — keyingi qatorning qiymatini oladi.
SELECT
oy,
sotuv,
LEAD(sotuv) OVER (ORDER BY id) AS keyingi_oy
FROM oylik_sotuv;
oy │ sotuv │ keyingi_oy
----------+---------+------------
Yanvar │ 1200000 │ 1850000
Fevral │ 1850000 │ 1100000
Mart │ 1100000 │ 2300000
Aprel │ 2300000 │ 1950000
May │ 1950000 │ 2700000
Iyun │ 2700000 │ NULL ← oxirgi qator, keyingisi yo'q
LEAD ishlatilishi: "keyingi oyda narx oshishini" oldindan ko'rsatish, vaqt oralig'ini hisoblash.
FIRST_VALUE va LAST_VALUE
Oyna ichidagi birinchi yoki oxirgi qatorning qiymatini oladi.
Har bir oy uchun — yil boshidagi sotuv va hozirgi sotuv:
SELECT
oy,
sotuv,
FIRST_VALUE(sotuv) OVER (ORDER BY id) AS yanvar_sotuv,
LAST_VALUE(sotuv) OVER (
ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-- ↑ LAST_VALUE uchun shart — to'liq oyna
) AS iyun_sotuv
FROM oylik_sotuv;
oy │ sotuv │ yanvar_sotuv │ iyun_sotuv
----------+---------+--------------+------------
Yanvar │ 1200000 │ 1200000 │ 2700000
Fevral │ 1850000 │ 1200000 │ 2700000
Mart │ 1100000 │ 1200000 │ 2700000
Aprel │ 2300000 │ 1200000 │ 2700000
May │ 1950000 │ 1200000 │ 2700000
Iyun │ 2700000 │ 1200000 │ 2700000
⚠️ Diqqat:
LAST_VALUEstandart holatda faqat hozirgi qatorgacha ko'radi — shu sababliROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGqo'shish shart. Aks holda har qator o'zini "oxirgi" deb ko'rsatadi.
Amaliy misol — oylik o'sish foizi
SELECT
oy,
sotuv,
LAG(sotuv) OVER (ORDER BY id) AS oldingi,
CASE
WHEN LAG(sotuv) OVER (ORDER BY id) IS NULL THEN NULL
ELSE ROUND(
(sotuv - LAG(sotuv) OVER (ORDER BY id))
* 100.0
/ LAG(sotuv) OVER (ORDER BY id),
1
)
END AS osish_foizi
FROM oylik_sotuv;
oy │ sotuv │ oldingi │ osish_foizi
----------+---------+-----------+-------------
Yanvar │ 1200000 │ NULL │ NULL
Fevral │ 1850000 │ 1200000 │ 54.2
Mart │ 1100000 │ 1850000 │ -40.5
Aprel │ 2300000 │ 1100000 │ 109.1
May │ 1950000 │ 2300000 │ -15.2
Iyun │ 2700000 │ 1950000 │ 38.5
Tez-tez uchraydigan xatolar
LAST_VALUE da oyna chegarasini belgilmaslik:
-- ❌ — har qator o'zini "oxirgi" deb ko'rsatadi
LAST_VALUE(sotuv) OVER (ORDER BY id)
-- ✅ — to'liq oyna ko'rsatildi
LAST_VALUE(sotuv) OVER (
ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
Bu eng ko'p uchraydigan xato. LAST_VALUE intuitiv emas — standart oyna faqat boshidan hozirgi qatorgacha.
LAG/LEAD da NULL ni hisobga olmaslik:
-- ❌ — birinchi qatorda NULL, keyin hisob buziladi
sotuv - LAG(sotuv) OVER (ORDER BY id)
-- ✅ — NULL uchun standart qiymat
sotuv - LAG(sotuv, 1, sotuv) OVER (ORDER BY id)
-- ↑ oldingi yo'q bo'lsa hozirgi qiymat → farq = 0
Xulosa
Bu darsda window function larning ikkinchi guruhi — aggregate va navigatsiya funksiyalari:
SUM/AVG OVER → running total, moving average — to'planib boruvchi hisob
LAG → oldingi qatorning qiymati
LEAD → keyingi qatorning qiymati
FIRST_VALUE → oyna ichidagi birinchi qiymat
LAST_VALUE → oyna ichidagi oxirgi qiymat (ROWS BETWEEN shart!)
ROWS BETWEEN — oyna chegarasini aniq belgilash. Ko'p funksiyalar uchun standart oyna — boshidan hozirgi qatorgacha. LAST_VALUE uchun bu kutilmagan natija beradi.
Keyingi dars
20-dars: UNION, INTERSECT, EXCEPT