Conditional — IF, CASE WHEN, COALESCE, NULLIF, IFNULL

~20 daq · MySQL · Funksiyalar

Muammodan boshlaylik

Hisobotda bazadagi xom qiymatlar emas, tushunarli matn kerak:

  • holat = 'tolangan' → "To'langan ✓"
  • soni = 0 → "Tugagan", soni < 5 → "Kam qoldi", aks holda → "Bor"
  • telefon = NULL → "Ko'rsatilmagan"
  • Mijozlarni xarid summasiga qarab segmentlarga bo'lish

Ilovada if/else bilan qilish mumkin. Lekin ko'pincha bu mantiqni so'rovning o'zida bajarish qulayroq — ayniqsa guruhlash va hisobotlarda.

CASE — SQL ning if/else i

Qidiruv shakli (searched CASE)

SELECT
    nom,
    soni,
    CASE
        WHEN soni = 0 THEN 'Tugagan'
        WHEN soni < 5 THEN 'Kam qoldi'
        ELSE 'Bor'
    END AS holat
FROM mahsulotlar;

+--------------------+------+-----------+
| nom                | soni | holat     |
+--------------------+------+-----------+
| iPhone 15          |    4 | Kam qoldi |
| Samsung Galaxy A55 |   12 | Bor       |
| MacBook Air M3     |    2 | Kam qoldi |
...
  • Shartlar yuqoridan pastga tekshiriladi, birinchi TRUE ishlaydi
  • ELSE bo'lmasa va hech biri mos kelmasa — NULL
  • END bilan tugaydi

Qiymat shakli (simple CASE)

Bitta ustunni turli qiymatlarga solishtirish:

SELECT
    id,
    CASE holat
        WHEN 'yangi'      THEN 'Yangi'
        WHEN 'tolangan'   THEN 'To''langan'
        WHEN 'yolda'      THEN 'Yo''lda'
        WHEN 'yetkazildi' THEN 'Yetkazildi'
        WHEN 'bekor'      THEN 'Bekor qilingan'
    END AS holat_matni
FROM buyurtmalar;

CASE qayerda ishlatiladi

CASE — ifoda. U qiymat qaytaradi va istalgan joyda ishlatiladi.

ORDER BY da — o'z tartibingiz

SELECT id, holat FROM buyurtmalar
ORDER BY CASE holat
    WHEN 'yangi'    THEN 1
    WHEN 'tolangan' THEN 2
    WHEN 'yolda'    THEN 3
    ELSE 4
END, yaratilgan DESC;

Avval ishlov berilishi kerak bo'lganlar.

Aggregate ichida — pivot hisobot

Bitta so'rovda — har bir holat alohida ustunda:

SELECT
    m.shahar,
    COUNT(*)                                             AS jami,
    SUM(CASE WHEN b.holat = 'yetkazildi' THEN 1 ELSE 0 END) AS yetkazilgan,
    SUM(CASE WHEN b.holat = 'bekor'      THEN 1 ELSE 0 END) AS bekor,
    COUNT(CASE WHEN b.holat IN ('yangi','tolangan','yolda') THEN 1 END) AS jarayonda
FROM buyurtmalar b
JOIN mijozlar m ON m.id = b.mijoz_id
GROUP BY m.shahar;

+-----------+------+-------------+-------+-----------+
| shahar    | jami | yetkazilgan | bekor | jarayonda |
+-----------+------+-------------+-------+-----------+
| Toshkent  |    3 |           1 |     1 |         1 |
| Samarqand |    2 |           1 |     0 |         1 |
+-----------+------+-------------+-------+-----------+

Qatorlar → ustunlar. Bu pivot (yoki "conditional aggregation") — hisobotlarda eng ko'p ishlatiladigan naqshlardan biri.

COUNT(CASE WHEN ... THEN 1 END) — ELSE yo'q, mos kelmaganlar NULL, COUNT ularni sanamaydi.

Oyma-oy tushum — toifalar ustunlarda:

SELECT
    DATE_FORMAT(b.yaratilgan, '%Y-%m') AS oy,
    SUM(CASE WHEN p.toifa_id = 1 THEN q.soni * q.narx ELSE 0 END) AS telefonlar,
    SUM(CASE WHEN p.toifa_id = 2 THEN q.soni * q.narx ELSE 0 END) AS noutbuklar,
    SUM(CASE WHEN p.toifa_id = 3 THEN q.soni * q.narx ELSE 0 END) AS aksessuarlar
FROM buyurtmalar b
JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
JOIN mahsulotlar p        ON p.id = q.mahsulot_id
WHERE b.holat <> 'bekor'
GROUP BY oy
ORDER BY oy;

UPDATE da — turli qatorlarga turli qiymat

UPDATE mahsulotlar
SET narx = CASE
    WHEN toifa_id = 1 THEN ROUND(narx * 0.90)   -- telefonlar -10%
    WHEN toifa_id = 3 THEN ROUND(narx * 0.80)   -- aksessuarlar -20%
    ELSE narx
END;

ELSE narx — muhim. Usiz boshqa toifalardagi narxlar NULL bo'lib qoladi (yoki NOT NULL ustunda xato).

IF — qisqa variant

MySQL ga xos, ikki tarmoqli CASE:

SELECT nom, IF(soni > 0, 'Bor', 'Tugagan') AS holat FROM mahsulotlar;

IF(shart, true_bo'lsa, false_bo'lsa). Oddiy holatlar uchun qulay. Lekin bu standart SQL emas — PostgreSQL da ishlamaydi. Ko'chirib bo'ladigan kod kerak bo'lsa — CASE.

NULL bilan ishlash funksiyalari

COALESCE — birinchi NULL bo'lmagani

SELECT ism, COALESCE(telefon, 'Ko''rsatilmagan') AS telefon FROM mijozlar;

SELECT COALESCE(NULL, NULL, 'uchinchi', 'to''rtinchi');   -- 'uchinchi'

Zaxira zanjiri:

-- Ko'rsatiladigan ism: taxallus, bo'lmasa to'liq ism, bo'lmasa email
SELECT COALESCE(taxallus, toliq_ism, email) AS korinadigan FROM foydalanuvchilar;

Aggregate natijasini nolga aylantirish (13-dars):

SELECT COALESCE(SUM(narx), 0) FROM mahsulotlar WHERE toifa_id = 99;   -- 0

IFNULL — ikki argumentli COALESCE

SELECT IFNULL(telefon, '-') FROM mijozlar;

MySQL ga xos. COALESCE — standart va ko'p argument oladi; uni afzal ko'ring.

NULLIF — qiymatni NULL ga aylantirish

NULLIF(a, b) — a = b bo'lsa NULL, aks holda a:

SELECT NULLIF(5, 5), NULLIF(5, 3);   -- NULL, 5

Ikki asosiy ishlatilishi:

1. Nolga bo'lishdan himoya:

SELECT sotilgan / NULLIF(korilgan, 0) * 100 AS konversiya FROM statistika;
-- korilgan = 0 bo'lsa → NULL, xato emas

2. Bo'sh matnni NULL deb hisoblash:

SELECT COALESCE(NULLIF(TRIM(telefon), ''), 'Ko''rsatilmagan') FROM mijozlar;
-- NULL ham, '' ham, '   ' ham → 'Ko'rsatilmagan'

Segmentatsiya — to'liq misol

WITH mijoz_xaridlar AS (
    SELECT m.id, m.ism, COALESCE(SUM(q.soni * q.narx), 0) AS jami,
           MAX(b.yaratilgan) AS oxirgi
    FROM mijozlar m
    LEFT JOIN buyurtmalar b        ON b.mijoz_id = m.id AND b.holat <> 'bekor'
    LEFT JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    GROUP BY m.id, m.ism
)
SELECT
    ism,
    jami,
    CASE
        WHEN jami = 0                              THEN 'Xarid qilmagan'
        WHEN jami >= 20000000                      THEN 'VIP'
        WHEN oxirgi < NOW() - INTERVAL 60 DAY      THEN 'Uxlab qolgan'
        ELSE 'Faol'
    END AS segment
FROM mijoz_xaridlar
ORDER BY jami DESC;

Marketing har bir segmentga alohida xabar yuboradi.

Nima uchun shunday?

Nima uchun bunday mantiqni bazada yozish kerak — ilovada if/else bilan qilish oddiyroq emasmi?

Ko'rsatish matnlari ("To'langan ✓") uchun — ha, ilovada qilgan ma'qul: tarjima, dizayn o'zgarishlari ilovaning ishi. Lekin hisob-kitob mantiqi uchun — pivot hisobotlar, segmentlar, shartli yig'indilar — bazada qilish ancha samarali. Aks holda millionlab qatorni ilovaga tashib, u yerda guruhlash kerak bo'ladi. Qoida: ma'lumotni qisqartiradigan mantiq — bazada, ko'rinishni o'zgartiradigan mantiq — ilovada.

Xulosa

Eslab qoling:

  • CASE WHEN shart THEN ... ELSE ... END — birinchi mos kelgani; ELSE siz → NULL
  • CASE ustun WHEN qiymat THEN ... — qiymatga solishtirish
  • SUM(CASE WHEN ... THEN 1 ELSE 0 END) — pivot hisobot
  • UPDATE ... SET x = CASE ... ELSE x END — ELSE ni unutmang
  • IF(shart, a, b) — MySQL ga xos qisqa variant
  • COALESCE(a, b, c) — birinchi NULL bo'lmagani
  • NULLIF(x, 0) — nolga bo'lishdan himoya; NULLIF(x, '') — bo'sh matn → NULL

Keyingi dars

29-dars: Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD