Tahliliy SQL naqshlari: ROLLUP, CUBE, bo'sh kunlar, pivot va gaps and islands

Qisqacha: Hisobot va dashboard so'rovlari bir necha tayyor naqshdan yig'iladi. Masalan, GROUP BY ROLLUP (a, b) — oraliq va umumiy jamlar bilan guruhlash. CUBE — hamma kombinatsiyalar, GROUPING SETS — o'zingiz tanlagan guruhlar. generate_series + LEFT JOIN — ma'lumotsiz kunlarni grafikda 0 bilan ko'rsatadi. count(*) FILTER (WHERE ...) — qatorlarni ustunlarga aylantiradi (pivot). Gaps and islands — "sana − tartib raqami" hiylasi bilan ketma-ket kunlar seriyasini topadi. Kogorta so'rovi — "ro'yxatdan o'tganlar keyingi haftalarda qaytdimi?".

Bu darsda

• ROLLUP, CUBE va GROUPING SETS bilan jamli hisobot yasaysiz, grouping() bilan "jami" qatorini taniysiz.
• generate_series bilan bo'sh kunlarni 0 bilan to'ldirasiz.
• FILTER va crosstab bilan pivot (qator → ustun) jadval qurasiz.
• Gaps and islands naqshi bilan ketma-ket faol kunlar seriyasini topasiz.
• Haftalik kogorta (retention) so'rovini o'qiysiz va yozasiz.

Oldin bilishingiz kerak: Window funksiyalar 2: qiymat va frame'lar, Agregatlar, GROUP BY va HAVING, CTE (WITH), Ma'lumot turlari (timestamptz).

1. Nega bu kerak?

Siz vazifalar ilovasini ishga tushirdingiz. Bir oydan keyin rahbar (yoki o'zingiz) savollar beradi:

• "Kim qancha vazifa yozdi, ustuvorlik bo'yicha, jami bilan?"
• "Har kuni nechta vazifa yaratildi? Grafik kerak — bo'sh kunlar ham ko'rinsin."
• "Jadval ko'rinishida: qatorlar — odamlar, ustunlar — ustuvorliklar."
• "Kim ketma-ket necha kun ilovadan foydalangan?"
• "Sentyabrda ro'yxatdan o'tganlarning qanchasi keyingi haftalarda qaytdi?"

Har biri — oldingi darslardagi bilimlardan yig'iladigan tayyor retsept. Bu dars yangi buyruqlardan ko'ra naqshlar haqida: bir marta tushunasiz va har dashboard'da qayta ishlatasiz. O'xshatish: oshpazning retseptlar daftari. Pichoq va qozon (SELECT, JOIN, window) sizda bor — endi ulardan tayyor taomlar.

Dars davomida sanalar Toshkent vaqti bo'yicha olinadi. Vaqt zonalari mavzusi keyingi darsda: SQL'da sana va vaqt zonalari. Hozircha bilish yetarli: SET TIME ZONE 'Asia/Tashkent' buyrug'idan keyin yaratilgan_vaqt::date Toshkent kunini beradi. Har blok alohida psql seansida bajarilgan, shuning uchun sanaga bog'liq bloklar shu buyruq bilan boshlanadi.

2. Jamli guruhlash: ROLLUP, CUBE, GROUPING SETS

2.1 ROLLUP — oraliq va umumiy jamlar

Aziz va Dilnozaning vazifalari ustuvorlik bo'yicha, har odamning jami va umumiy jami bilan. Kanon tajribasi (PostgreSQL 18.6, vazifalar holat 23):

SELECT f.ism, v.ustuvorlik, count(*)
FROM vazifalar v JOIN foydalanuvchilar f ON f.id = v.egasi_id
WHERE f.id IN (1, 2)
GROUP BY ROLLUP (f.ism, v.ustuvorlik)
ORDER BY f.ism NULLS LAST, v.ustuvorlik NULLS LAST;

       ism        | ustuvorlik | count
------------------+------------+-------
 Aziz Karimov     | past       |     1
 Aziz Karimov     | orta       |     8
 Aziz Karimov     | yuqori     |     3
 Aziz Karimov     |            |    12
 Dilnoza Rahimova | past       |     1
 Dilnoza Rahimova | orta       |     4
 Dilnoza Rahimova | yuqori     |     1
 Dilnoza Rahimova |            |     6
                  |            |    18
(9 rows)

ROLLUP (ism, ustuvorlik) bitta so'rovda uch darajali guruhlash qiladi:

Bo'sh katak (NULL) bu yerda "hammasi" degani. Excel'dagi "oraliq jamlar" (subtotals) yoki buxgalteriya hisobotlari aynan shunday. ROLLUP ierarxiyani nazarda tutadi: chapdagi ustun — katta guruh, o'ngdagisi — ichki.

2.2 GROUPING SETS va grouping()

Ba'zan ierarxiya yo'q, faqat bir nechta mustaqil kesim kerak: "ustuvorlik bo'yicha", "holat bo'yicha" va "jami". GROUPING SETS — guruhlarni o'zingiz sanab berasiz:

SELECT CASE WHEN grouping(ustuvorlik) = 1 THEN '(hammasi)'
            ELSE ustuvorlik::text END AS ustuvorlik,
       CASE WHEN grouping(bajarildi) = 1 THEN '(hammasi)'
            ELSE bajarildi::text END AS bajarildi,
       count(*)
FROM vazifalar
GROUP BY GROUPING SETS ((ustuvorlik), (bajarildi), ())
ORDER BY 1, 2;

 ustuvorlik | bajarildi | count
------------+-----------+-------
 (hammasi)  | false     |    25
 (hammasi)  | (hammasi) |    47
 (hammasi)  | true      |    22
 orta       | (hammasi) |    33
 past       | (hammasi) |     4
 yuqori     | (hammasi) |    10
(6 rows)

Kanon tajribasi. Uchta guruh: (ustuvorlik) — 3 qator, (bajarildi) — 2 qator, () — jami 1 qator.

Muammo: "jami" qatoridagi NULL va ustunning haqiqiy NULL qiymatini qanday ajratish kerak? Masalan muddat bo'yicha guruhlasak, muddatsiz vazifalar ham NULL beradi. grouping(ustun) funksiyasi buni hal qiladi: ustun shu qatorda "yig'ilgan" bo'lsa 1, oddiy qiymat bo'lsa 0. CASE bilan biz uni (hammasi) yozuviga aylantirdik — hisobotda tushunarli.

ORDER BY 1, 2 matn bo'yicha tartiblaydi. Tartibga diqqat qiling: ikkinchi ustunda false, (hammasi), true. Kanon bazasining collation'i (en_US.utf8) qavslarni birinchi bosqichda e'tiborsiz qoldiradi, ya'ni hammasi so'zi solishtiriladi: false < hammasi < true. Birinchi ustunda ham shunday: hammasi < orta. C collation'li bazada qavs harflardan oldin keladi va (hammasi) qatorlari boshqacha joylashadi — biz PostgreSQL 17.6 da sinab ko'rdik. Tartib muhim bo'lsa, grouping() bo'yicha aniq tartiblang: ORDER BY grouping(ustuvorlik), ustuvorlik.

2.3 CUBE — hamma kombinatsiyalar

CUBE (a, b) = GROUPING SETS ((a, b), (a), (b), ()) — hamma mumkin bo'lgan kombinatsiya:

SELECT ustuvorlik, bajarildi, count(*)
FROM vazifalar GROUP BY CUBE (ustuvorlik, bajarildi)
ORDER BY 1 NULLS LAST, 2 NULLS LAST;

 ustuvorlik | bajarildi | count
------------+-----------+-------
 past       | f         |     4
 past       |           |     4
 orta       | f         |    12
 orta       | t         |    21
 orta       |           |    33
 yuqori     | f         |     9
 yuqori     | t         |     1
 yuqori     |           |    10
            | f         |    25
            | t         |    22
            |           |    47
(11 rows)

Kanon tajribasi. Bir jadvalda: har ustuvorlik × holat, har ustuvorlikning jami, har holatning jami va umumiy jami. Qiziq fakt: 10 ta yuqori vazifadan faqat 1 tasi bajarilgan, orta larning esa 21/33 tasi. Muhim ishlar orqaga surilmoqda — bu hisobot savol tug'diradi.

ROLLUP n ustun uchun n + 1 guruh beradi, CUBE — 2ⁿ guruh. 4 ustunli CUBE — 16 ta guruhlash, natija tez kattalashadi.

Tekshirib ko'ring: GROUP BY ROLLUP (ustuvorlik, bajarildi) qaysi GROUPING SETS ga teng? CUBE dagi qaysi guruh unda yo'q?

3. Bo'sh kunlarni to'ldirish

3.1 Muammo: yo'q kun — yo'q qator

Azizning 12–18 oktyabr haftasidagi kunlik vazifalari, oddiy GROUP BY bilan (biz ishga tushirdik, PG 17.6):

SET TIME ZONE 'Asia/Tashkent';
SELECT yaratilgan_vaqt::date AS kun, count(*) AS yaratildi
FROM vazifalar
WHERE egasi_id = 1 AND yaratilgan_vaqt >= '2026-10-12'
  AND yaratilgan_vaqt < '2026-10-19'
GROUP BY 1 ORDER BY 1;

SET
    kun     | yaratildi
------------+-----------
 2026-10-14 |         1
 2026-10-18 |         1
(2 rows)

7 kunlik haftada 2 qator. Grafik chizuvchi kutubxona 14 va 18-oktyabrni yonma-yon qo'yadi — orada 3 kunlik "bo'shliq" ko'rinmaydi. GROUP BY faqat mavjud qatorlarni guruhlaydi: vazifa yo'q kun — guruh ham yo'q.

3.2 Yechim: kalendar + LEFT JOIN

generate_series(boshi, oxiri, qadam) — sonlar yoki sanalar ketma-ketligini yasaydigan funksiya. Rekursiv CTE darsida xuddi shunday ketma-ketlikni qo'lda yasagan edik. Kalendarni chap tomonga qo'yib, vazifalarni LEFT JOIN qilamiz:

SET TIME ZONE 'Asia/Tashkent';
SELECT k.kun::date, count(v.id) AS yaratildi
FROM generate_series('2026-10-01'::date, '2026-10-14',
                     interval '1 day') AS k(kun)
LEFT JOIN vazifalar v ON v.yaratilgan_vaqt::date = k.kun
GROUP BY k.kun ORDER BY k.kun;

SET
    kun     | yaratildi
------------+-----------
 2026-10-01 |         3
 2026-10-02 |         7
 2026-10-03 |         6
 2026-10-04 |         2
 2026-10-05 |         6
 2026-10-06 |         4
 2026-10-07 |         2
 2026-10-08 |         2
 2026-10-09 |         2
 2026-10-10 |         1
 2026-10-11 |         1
 2026-10-12 |         2
 2026-10-13 |         1
 2026-10-14 |         2
(14 rows)

Kanon tajribasi (hamma foydalanuvchilar). Bu davrda bo'sh kun bo'lmadi, lekin so'rov kafolat beradi: 14 kun — 14 qator, doim. Ikki muhim tafsilot:

• count(v.id), count(*) emas — bo'sh kunda LEFT JOIN bitta NULL qator beradi, count(*) uni 1 deb sanardi (JOIN'lar darsidagi tuzoq).
• generate_series sana va interval bilan timestamptz qaytaradi — har kun sessiya zonasidagi yarim tun. Shuning uchun blok SET TIME ZONE bilan boshlanadi, chiqishda esa ::date bilan sanaga o'giramiz.

Grafikda oktyabr boshidagi faollik va keyin pasayish ko'rinadi: 2-oktyabrda 7 ta, ikkinchi haftada kuniga 1–2 ta. Har kun nuqtasi bor — generate_series shuni kafolatladi.

Tekshirib ko'ring: LEFT JOIN vazifalar v ON v.yaratilgan_vaqt::date = k.kun o'rniga ... ON ... WHERE v.egasi_id = 1 deb Azizga filtrlasak, bo'sh kunlar saqlanadimi?

4. Pivot: qatorlarni ustunlarga

4.1 FILTER bilan

Qatorlar — odamlar, ustunlar — ustuvorliklar. Bu pivot (aylantirish): bir ustunning qiymatlari ustun nomlariga aylanadi. Postgres'da eng sodda yo'l — har ustun uchun count(*) FILTER (WHERE ...) (Agregatlar):

SELECT f.ism,
  count(*) FILTER (WHERE v.ustuvorlik = 'yuqori') AS yuqori,
  count(*) FILTER (WHERE v.ustuvorlik = 'orta') AS orta,
  count(*) FILTER (WHERE v.ustuvorlik = 'past') AS past
FROM foydalanuvchilar f LEFT JOIN vazifalar v ON v.egasi_id = f.id
GROUP BY f.id ORDER BY f.id;

        ism         | yuqori | orta | past
--------------------+--------+------+------
 Aziz Karimov       |      3 |    8 |    1
 Dilnoza Rahimova   |      1 |    4 |    1
 Sardor Tursunov    |      2 |    4 |    0
 Madina Yusupova    |      1 |    8 |    0
 Javohir Olimov     |      1 |    4 |    1
 Gulnora Saidova    |      1 |    2 |    1
 Bekzod Nazarov     |      1 |    3 |    0
 Shahnoza Ergasheva |      0 |    0 |    0
(8 rows)

Kanon tajribasi. Shahnozada uchala ustun 0. Diqqat: bu yerda count(*) LEFT JOIN bilan ham to'g'ri — FILTER sharti NULL qatorni o'tkazmaydi.

Cheklov: ustunlar so'rovda qat'iy yoziladi. To'rtinchi ustuvorlik qo'shilsa, so'rovni o'zgartirish kerak. SQL natijasining ustunlari so'rov yozilgan paytda ma'lum bo'lishi shart — bu tilning qoidasi.

4.2 crosstab (tablefunc)

Postgres'ning tablefunc kengaytmasida tayyor crosstab funksiyasi bor. Kengaytma — Postgres'ga qo'shimcha funksiyalar beradigan modul, CREATE EXTENSION bilan yoqiladi:

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
  $$SELECT f.ism, v.ustuvorlik::text, count(*)::int
    FROM vazifalar v JOIN foydalanuvchilar f ON f.id = v.egasi_id
    GROUP BY 1, 2 ORDER BY 1, 2$$,
  $$VALUES ('past'), ('orta'), ('yuqori')$$
) AS t(ism text, past int, orta int, yuqori int);

CREATE EXTENSION
       ism        | past | orta | yuqori
------------------+------+------+--------
 Aziz Karimov     |    1 |    8 |      3
 Bekzod Nazarov   |      |    3 |      1
 Dilnoza Rahimova |    1 |    4 |      1
 Gulnora Saidova  |    1 |    2 |      1
 Javohir Olimov   |    1 |    4 |      1
 Madina Yusupova  |      |    8 |      1
 Sardor Tursunov  |      |    4 |      2
(7 rows)

Kanon tajribasi; postgres:18 image'ida tablefunc bor (Windows'dagi PostgreSQL 17 da ham). $$...$$ — ichidagi apostroflarni ikkilantirmaslik uchun satr yozuvi. Birinchi argument — (qator, ustun, qiymat) uchligini qaytaruvchi so'rov, ikkinchisi — ustunlar ro'yxati. AS t(...) da natija turlarini o'zingiz yozasiz.

FILTER bilan farqi: bo'sh kataklar NULL (0 emas), Shahnoza yo'q (asosiy so'rov INNER JOIN). Amalda FILTER ko'proq ishlatiladi: kengaytma kerak emas, sodda va bir qarashda o'qiladi.

5. Gaps and islands: ketma-ket kunlar

5.1 Hiyla: sana − tartib raqami

Aziz vazifa yozgan kunlar: 1, 2, 3, 5, 7, 10, 14, 18-oktyabr. Savol: ketma-ket kunlarning "orollari" (islands) qaysilar? 1–3 oktyabr — 3 kunlik orol, qolganlari yolg'iz.

Hiyla shunday: kunlarni tartiblab, har biridan uning tartib raqamini ayiramiz. Ketma-ket kunlarda sana ham, raqam ham birga 1 ga o'sadi — ayirma o'zgarmaydi. Bo'shliq (gap) bo'lgan joyda sana raqamdan tezroq o'sadi va ayirma o'zgaradi. Orolni ochib ko'ramiz (biz ishga tushirdik, PG 17.6):

SET TIME ZONE 'Asia/Tashkent';
WITH kunlar AS (
  SELECT DISTINCT yaratilgan_vaqt::date AS kun
  FROM vazifalar WHERE egasi_id = 1
), orollar AS (
  SELECT kun, kun - (row_number() OVER (ORDER BY kun))::int AS orol
  FROM kunlar
)
SELECT kun, row_number() OVER (ORDER BY kun) AS n, orol
FROM orollar ORDER BY kun;

SET
    kun     | n |    orol
------------+---+------------
 2026-10-01 | 1 | 2026-09-30
 2026-10-02 | 2 | 2026-09-30
 2026-10-03 | 3 | 2026-09-30
 2026-10-05 | 4 | 2026-10-01
 2026-10-07 | 5 | 2026-10-02
 2026-10-10 | 6 | 2026-10-04
 2026-10-14 | 7 | 2026-10-07
 2026-10-18 | 8 | 2026-10-10
(8 rows)

Birinchi uch qatorda orol bir xil — 30-sentyabr. Bu sana o'zi ma'nosiz, u faqat orolning yorlig'i. 5-oktyabrdan keyin har kun o'z yorlig'ini oldi — ular yolg'iz.

row_number() Window funksiyalar 1 darsidan, date - int — sanadan kunlar ayirish (Ifodalar).

5.2 Orollarni yig'ish

Endi yorliq bo'yicha GROUP BY — har orolning boshi, oxiri va uzunligi:

SET TIME ZONE 'Asia/Tashkent';
WITH kunlar AS (
  SELECT DISTINCT yaratilgan_vaqt::date AS kun
  FROM vazifalar WHERE egasi_id = 1
), orollar AS (
  SELECT kun, kun - (row_number() OVER (ORDER BY kun))::int AS orol
  FROM kunlar
)
SELECT min(kun) AS boshi, max(kun) AS oxiri, count(*) AS ketma_ket_kun
FROM orollar GROUP BY orol ORDER BY boshi;

SET
   boshi    |   oxiri    | ketma_ket_kun
------------+------------+---------------
 2026-10-01 | 2026-10-03 |             3
 2026-10-05 | 2026-10-05 |             1
 2026-10-07 | 2026-10-07 |             1
 2026-10-10 | 2026-10-10 |             1
 2026-10-14 | 2026-10-14 |             1
 2026-10-18 | 2026-10-18 |             1
(6 rows)

Kanon tajribasi. Azizning eng uzun seriyasi — 1–3 oktyabr, 3 kun. Duolingo'dagi "streak" (ketma-ket kunlar), GitHub'dagi faollik seriyasi, "server necha soat uzluksiz ishladi" — hammasi shu naqsh. DISTINCT muhim: bir kunda ikki vazifa bo'lsa, kun ikki marta raqamlanib, hisob buzilardi.

Tekshirib ko'ring: shu naqsh bilan "Aziz qaysi kunlarda yozmagan" (gaps) ni qanday topish mumkin?

6. Kogorta (retention) so'rovi

6.1 Savol

Kogorta (cohort) — bir vaqtda kelgan foydalanuvchilar guruhi. Masalan "8-sentyabr haftasida ro'yxatdan o'tganlar". Retention (qaytish, saqlanish) — ularning qanchasi keyingi haftalarda ham ilovadan foydalangani. Startaplarda bu eng muhim raqamlardan biri: yangi foydalanuvchi kelib, qaytmasa — ilova ishlamayapti.

Bizda "foydalanish" belgisi — o'sha haftada vazifa yozish. Kanon tajribasi (SET TIME ZONE 'Asia/Tashkent' bilan):

SET TIME ZONE 'Asia/Tashkent';
WITH kogorta AS (
  SELECT id, date_trunc('week', yaratilgan_vaqt)::date AS hafta
  FROM foydalanuvchilar
), faollik AS (
  SELECT DISTINCT egasi_id,
         date_trunc('week', yaratilgan_vaqt)::date AS hafta
  FROM vazifalar
)
SELECT k.hafta AS kogorta, count(DISTINCT k.id) AS odam,
  count(DISTINCT fa.egasi_id)
    FILTER (WHERE fa.hafta = k.hafta) AS h0,
  count(DISTINCT fa.egasi_id)
    FILTER (WHERE fa.hafta = k.hafta + 7) AS h1,
  count(DISTINCT fa.egasi_id)
    FILTER (WHERE fa.hafta = k.hafta + 14) AS h2
FROM kogorta k LEFT JOIN faollik fa ON fa.egasi_id = k.id
GROUP BY k.hafta ORDER BY k.hafta;

SET
  kogorta   | odam | h0 | h1 | h2
------------+------+----+----+----
 2026-08-31 |    2 |  0 |  0 |  0
 2026-09-07 |    1 |  0 |  0 |  0
 2026-09-14 |    1 |  0 |  0 |  1
 2026-09-21 |    1 |  0 |  1 |  1
 2026-09-28 |    1 |  1 |  1 |  1
 2026-10-05 |    1 |  1 |  1 |  0
 2026-10-12 |    1 |  0 |  0 |  0
(7 rows)

6.2 O'qish

Bosqichlarni CTE darsidagi kabi yuqoridan pastga o'qiymiz:

Jadvalni qanday o'qish kerak: 21-sentyabr haftasida 1 kishi (Javohir) keldi. O'sha haftada vazifa yozmagan (h0 = 0), keyingi ikki haftada yozgan (h1 = 1, h2 = 1). 28-sentyabr kogortasi (Gulnora) — uchala haftada faol.

Halol eslatma: bizda 8 kishi, kogorta 1–2 kishidan iborat. Bunday kichik namunadan xulosa chiqarib bo'lmaydi — so'rovning shakli muhim, raqamlari emas. Bundan tashqari namunada vazifalar oktyabrdan boshlanadi, shuning uchun avgust-sentyabr kogortalari uchun h0–h2 nol. Haqiqiy ilovada minglab foydalanuvchi bilan jadval "pasayib boruvchi zinapoya" ko'rinishida bo'ladi va foizlarda ko'rsatiladi: round(100.0 * h1 / odam).

7. Ko'p uchraydigan xatolar

7.1 Bo'sh kunda 1 chiqdi

LEFT JOIN'dan keyin count(*). count(v.id) ishlating.

7.2 Kalendar qatorlari yo'qoldi

O'ng jadval sharti WHERE da. Shartni ON ga ko'chiring.

7.3 ROLLUP dagi NULL va haqiqiy NULL aralashdi

grouping(ustun) = 1 bilan "jami" qatorini ajrating, coalesce(ustun, 'jami') bilan emas — u haqiqiy NULL ni ham "jami" deb yozadi.

7.4 Kun chegarasi "siljigan"

UTC'da ::date — Toshkent kuni emas: 00:00–05:00 orasida yaratilgan vazifalar oldingi kunga tushadi. Hisobotdan oldin vaqt zonasini aniq belgilang — SQL'da sana va vaqt zonalari darsida batafsil.

7.5 crosstab: function crosstab(unknown, unknown) does not exist

tablefunc kengaytmasi yoqilmagan. CREATE EXTENSION tablefunc; (buning uchun bazada ruxsat kerak — boshqariladigan bulut bazalarida ro'yxatni tekshiring).

7.6 Gaps and islands noto'g'ri orollar berdi

Bir kunda bir nechta qator — DISTINCT unutilgan. Yoki tartib raqami PARTITION BY siz hisoblangan, holbuki har odam alohida kerak edi (3-mashq).

8. Mashqlar

Mashq fayllari kurs/mashqlar/26/31-tahliliy/ da. Hammasi vazifalar holat 23 da.

1-mashq (oson): ustuvorlik va jami

Har ustuvorlikdagi vazifalar soni va oxirida umumiy jami — bitta so'rovda. Yordam: GROUP BY ROLLUP (ustuvorlik), ORDER BY ustuvorlik NULLS LAST.

SELECT ustuvorlik, count(*) AS soni
FROM vazifalar
GROUP BY ROLLUP (ustuvorlik)
ORDER BY ustuvorlik NULLS LAST;

 ustuvorlik | soni
------------+------
 past       |    4
 orta       |   33
 yuqori     |   10
            |   47
(4 rows)

2-mashq (o'rta): har kuni bajarilganlar

1–7 oktyabr (Toshkent vaqti) har kuni nechta vazifa bajarilgan, bo'sh kunlar 0 bilan. Yordam: «Bo'sh kunlarni to'ldirish» bo'limidagi so'rov, yaratilgan_vaqt o'rniga bajarilgan_vaqt; blok boshida SET TIME ZONE.

SET TIME ZONE 'Asia/Tashkent';
SELECT k.kun::date, count(v.id) AS bajarildi
FROM generate_series('2026-10-01'::date, '2026-10-07',
                     interval '1 day') AS k(kun)
LEFT JOIN vazifalar v ON v.bajarilgan_vaqt::date = k.kun
GROUP BY k.kun ORDER BY k.kun;

SET
    kun     | bajarildi
------------+-----------
 2026-10-01 |         2
 2026-10-02 |         2
 2026-10-03 |         2
 2026-10-04 |         1
 2026-10-05 |         2
 2026-10-06 |         2
 2026-10-07 |         4
(7 rows)

3-mashq (qiyin): har kimning eng uzun seriyasi

Har foydalanuvchining eng uzun ketma-ket vazifa yozgan kunlar seriyasi: ism, eng_uzun. Yordam: gaps and islands — row_number() ga PARTITION BY egasi_id, keyin GROUP BY egasi_id, orol bilan orol uzunligi, oxirida har odam uchun max.

SET TIME ZONE 'Asia/Tashkent';
WITH kunlar AS (
  SELECT DISTINCT egasi_id, yaratilgan_vaqt::date AS kun
  FROM vazifalar
), orollar AS (
  SELECT egasi_id, kun,
         kun - (row_number() OVER (PARTITION BY egasi_id
                                   ORDER BY kun))::int AS orol
  FROM kunlar
), seriyalar AS (
  SELECT egasi_id, orol, count(*) AS uzunlik
  FROM orollar GROUP BY egasi_id, orol
)
SELECT f.ism, max(s.uzunlik) AS eng_uzun
FROM seriyalar s JOIN foydalanuvchilar f ON f.id = s.egasi_id
GROUP BY f.id ORDER BY f.id;

SET
       ism        | eng_uzun
------------------+----------
 Aziz Karimov     |        3
 Dilnoza Rahimova |        2
 Sardor Tursunov  |        1
 Madina Yusupova  |        3
 Javohir Olimov   |        2
 Gulnora Saidova  |        2
 Bekzod Nazarov   |        1
(7 rows)

4-mashq: SQL qadami — "oxirgi 7 kun" grafigi

Ilova profil sahifasida foydalanuvchining oxirgi 7 kunlik faollik grafigini ko'rsatadi. Aziz (1) uchun 12–18 oktyabr: kun (DD.MM ko'rinishida), yaratildi — bo'sh kunlar 0 bilan. Sxema o'zgarmaydi — commit yo'q. Yordam: generate_series + LEFT JOIN, Aziz sharti ON da; to_char(k.kun, 'DD.MM') (Ifodalar va funksiyalar).

SET TIME ZONE 'Asia/Tashkent';
SELECT to_char(k.kun, 'DD.MM') AS kun, count(v.id) AS yaratildi
FROM generate_series('2026-10-12'::date, '2026-10-18',
                     interval '1 day') AS k(kun)
LEFT JOIN vazifalar v
  ON v.egasi_id = 1 AND v.yaratilgan_vaqt::date = k.kun
GROUP BY k.kun ORDER BY k.kun;

SET
  kun  | yaratildi
-------+-----------
 12.10 |         0
 13.10 |         0
 14.10 |         1
 15.10 |         0
 16.10 |         0
 17.10 |         0
 18.10 |         1
(7 rows)

9. Real ishda

• Dashboard'lar (Metabase, Grafana, Superset) ichida aynan shu so'rovlar turadi: kunlik grafik — generate_series, jadval — pivot, jamli hisobot — ROLLUP.
• Mahsulot analitikasi: retention/kogorta jadvali, "streak", DAU/WAU (kunlik/haftalik faol foydalanuvchilar) — har startapning haftalik yig'ilishida.
• Moliya: oylik hisobot bo'lim → bo'lim ichida → jami (ROLLUP), "oy × mahsulot" matritsasi (pivot).
• Monitoring: "server qaysi oraliqlarda ishlamagan" — gaps and islands; bo'sh daqiqalarni 0 bilan to'ldirish.
• Katta hajmda bu so'rovlar og'ir: jamlangan natija materialized view yoki alohida hisobot jadvalida saqlanadi va davriy yangilanadi (Denormalizatsiya va hisoblangan maydonlar).

Xulosa

• ROLLUP — ierarxik oraliq jamlar, CUBE — hamma kombinatsiyalar, GROUPING SETS — tanlangan guruhlar; grouping() "jami" qatorini ajratadi.
• generate_series + LEFT JOIN + count(o'ng.id) — bo'sh kunlar 0 bilan, grafik uchun.
• Pivot: count(*) FILTER (WHERE ...) har ustun uchun; crosstab — tablefunc kengaytmasidan.
• Gaps and islands: kun - row_number() — ketma-ket kunlar uchun bir xil yorliq, keyin GROUP BY.
• Kogorta: ro'yxatdan o'tgan haftasi × keyingi haftalardagi faollik — FILTER va date_trunc('week', ...).

Keyingi dars: SQL'da sana va vaqt zonalari — timestamptz, AT TIME ZONE va Toshkent kunining chegaralari.

Manbalar

• PostgreSQL 18 hujjati — GROUPING SETS, CUBE, and ROLLUP: https://www.postgresql.org/docs/18/queries-table-expressions.html#QUERIES-GROUPING-SETS
• PostgreSQL 18 hujjati — Set Returning Functions (generate_series): https://www.postgresql.org/docs/18/functions-srf.html
• PostgreSQL 18 hujjati — tablefunc (crosstab): https://www.postgresql.org/docs/18/tablefunc.html