Subquery, CTE va CASE WHEN

Murakkab savolni bosqichlarga bo'lib yechamiz

10-sinfBI & ML4-hafta · 12-dars

Takrorlash

GROUP BY / HAVING

Guruhlab hisoblash va filtrlash

JOIN

INNER, LEFT, RIGHT, FULL

Bugun

So'rovni qismlarga bo'lish

Subquery

Savol ichida savol

Ichkaridan tashqariga

Skalyar subquery


SELECT hudud_id, oquvchilar_soni
FROM yillik_kpi
WHERE yil = 2024
  AND oquvchilar_soni >
      (SELECT AVG(oquvchilar_soni) FROM yillik_kpi WHERE yil = 2024);

Natija: hudud 2, 3, 4 (Samarqand, Farg'ona, Andijon)

IN bilan ro'yxat


SELECT nomi FROM hududlar
WHERE hudud_id IN (
  SELECT hudud_id FROM yillik_kpi
  WHERE yil = 2024 AND oquvchilar_soni > 500000);

NOT IN haqida pastga

NOT IN ehtiyotkorligi

Ichki ro'yxatda NULL bo'lsa, NOT IN kutilmagan (bo'sh) natija berishi mumkin.

Xavfsiz yo'l: LEFT JOIN ... WHERE ... IS NULL (anti-join).

FROM dagi subquery


SELECT t.hudud_id, t.ortacha
FROM (SELECT hudud_id, AVG(oquvchilar_soni) AS ortacha
      FROM yillik_kpi GROUP BY hudud_id) AS t
WHERE t.ortacha > 500000;

FROM dagi subquery ga taxallus (AS t) majburiy.

Mini-test

Skalyar subquery nechta qiymat qaytarishi kerak?

Skalyar qiymat taqqoslash belgisi (>, =) bilan ishlatiladi, shuning uchun bitta bo'lishi shart.

CTE: WITH

Nomlangan vaqtinchalik natija

Qadam-baqadam yozuv

Qutilarni bosing. CTE: tepadan pastga o'qiladi.

Subquery bilan


SELECT yil, jami
FROM (SELECT yil, SUM(oquvchilar_soni) AS jami
      FROM yillik_kpi GROUP BY yil) AS t;

Ichma-ich: ichkaridan o'qiladi.

CTE bilan


WITH t AS (
  SELECT yil, SUM(oquvchilar_soni) AS jami
  FROM yillik_kpi GROUP BY yil
)
SELECT yil, jami FROM t;

Bir xil natija, lekin tepadan pastga o'qiladi.

Yillik o'sish (YoY)


WITH t AS (
  SELECT yil, SUM(oquvchilar_soni) AS jami
  FROM yillik_kpi GROUP BY yil
)
SELECT yil, jami,
  jami - LAG(jami) OVER (ORDER BY yil) AS yoy_change
FROM t
ORDER BY yil;

2022: NULL2023: +48 0002024: +48 000

Bir nechta CTE


WITH jami AS (
  SELECT hudud_id, SUM(oquvchilar_soni) AS s
  FROM yillik_kpi GROUP BY hudud_id
), ort AS (
  SELECT AVG(s) AS a FROM jami
)
SELECT j.hudud_id, j.s FROM jami j, ort
WHERE j.s > ort.a;

Vergul bilan ajratiladi; ikkinchi CTE birinchisini ishlatishi mumkin.

Subquery yoki CTE?

Subquery

bosing

Bir joyda bitta oddiy qiymat yoki ro'yxat kerak bo'lganda.

CTE

bosing

Ko'p bosqichli hisob, qayta ishlatish va o'qish osonligi kerak bo'lganda.

CASE WHEN

SQL dagi if / elif / else

Shartlar zinapoyasi

Yuqoridan pastga tekshiriladi. Birinchi to'g'ri shart ishlaydi.

CASE WHEN yozuvi


SELECT h.nomi, k.oquvchilar_soni,
  CASE
    WHEN k.oquvchilar_soni >= 600000 THEN 'Katta'
    WHEN k.oquvchilar_soni >= 400000 THEN 'O''rta'
    ELSE 'Kichik'
  END AS toifa
FROM yillik_kpi k
JOIN hududlar h ON h.hudud_id = k.hudud_id
WHERE k.yil = 2024;

Matn ichida apostrof ikkilanadi: 'O''rta'

Pivot: yillar ustunlarga

Pivot kodi


SELECT hudud_id,
  SUM(CASE WHEN yil = 2022 THEN oquvchilar_soni END) AS y2022,
  SUM(CASE WHEN yil = 2023 THEN oquvchilar_soni END) AS y2023,
  SUM(CASE WHEN yil = 2024 THEN oquvchilar_soni END) AS y2024
FROM yillik_kpi
GROUP BY hudud_id;

Shartga mos kelmasa CASE NULL qaytaradi, SUM esa NULL ni o'tkazib yuboradi.

CASE qanday tekshiradi?

Qiymat: 585 000

Farg'ona viloyati, 2024.

>= 600 000 ?

Yo'q, keyingi shartga o'tamiz.

>= 400 000 ?

Ha: natija "O'rta", qolgani tekshirilmaydi.

Odatiy xatolar

END yo'q

CASE ni END bilan yoping.

Shart tartibi

Kichik shart oldin tursa, kattasini "yutadi".

CTE vergullari

CTE lar orasida vergul bor, oxirida yo'q.

Mini-test: tartib

WHEN x > 0 THEN 'A' WHEN x >= 400000 THEN 'B'. x = 500000 uchun natija?

Birinchi to'g'ri shart (x > 0) ishlaydi, ikkinchisi tekshirilmaydi. Shartlar kattadan kichikka yoziladi.

Amaliyot 20 daqiqa

Oson2024-yilda eng ko'p o'quvchisi bo'lgan hududni MAX va subquery bilan toping.

O'rtaHar hududga o'quvchi/o'qituvchi nisbati bo'yicha toifa bering (CASE WHEN).

QiyinCTE + pivot: 2022 va 2024 ustunlari, 2 yillik o'sish bo'yicha tartiblang.

4-hafta yo'l xaritasi

10-dars. GROUP BY va HAVING: guruhlash

11-dars. JOIN: jadvallarni bog'lash

12-dars. Subquery, CTE, CASE WHEN: murakkab so'rovlar

5-hafta. Ma'lumotlarni modellashtirish: Star Schema

Xulosa

Bugun o'rgandik

Subquery: skalyar, IN, FROM
CTE: WITH, bir nechta CTE
CASE WHEN va pivot
YoY o'sish ko'rinishi

Uyga vazifa

CTE + CASE WHEN hisoboti: hududlarni nisbat bo'yicha toifalash.

Hafta oxiridagi test: 4-hafta testi (20 savol).