String, date va CASE WHEN

8-bob 15 kod bloki 3868 so'z

Real data iflos bo'ladi. Ismlarda ortiqcha bo'sh joylar bor, sanalar matn ko'rinishida keladi, kategoriyalarni raqamlardan chiqarish kerak. Bu bob — shularning hammasini query ichida, data pandas ga yetib borishidan oldin tuzatish uchun asboblar to'plami.

CASE WHEN birinchi keladi, chunki u bu yerdagi eng foydali konstruksiya — u SQL ning if / elif / else i.

CASE WHEN

CASE shartlarini yuqoridan pastga tekshiradi va TRUE bo'lgan birinchi shartning qiymatini qaytaradi. Hech biri mos kelmasa, ELSE qiymatini — ELSE yo'q bo'lsa NULL ni qaytaradi.

SELECT
    enrollment_id,
    grade,
    CASE
        WHEN grade IS NULL  THEN 'baholanmagan'
        WHEN grade >= 90    THEN 'A'
        WHEN grade >= 80    THEN 'B'
        WHEN grade >= 70    THEN 'C'
        WHEN grade >= 60    THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade DESC
LIMIT 6;

6 qator

1 ms

:::gotcha ⚠️ Tartib muhim — va shuning uchun yuqori chegara kerak emas Talabalar ko'pincha WHEN grade >= 80 AND grade < 90 THEN 'B' deb yozadi. Yuqori chegara ortiqcha: SQL o'sha qatorga yetganda 90 va undan yuqori barcha baholar allaqachon mos kelib chiqib ketgan bo'ladi. Shartlarni yuqoridan pastga yozish butun ishni soddalashtiradi.

Endi tartibni teskari qiling va buzilishini kuzating: WHEN grade >= 60 THEN 'D' ni birinchi qo'ysangiz, har bir o'tgan baho D bo'lib qoladi, chunki 100 ham >= 60 shartini qanoatlantiradi. Talabalarga bu xatoni bir marta ataylab qildiring. :::

CASE ning ikkita ko'rinishi

-- Searched CASE: har qanday shart. 95% holatda shuni ishlating.
CASE WHEN status = 'completed' THEN 'tugatildi'
     WHEN status = 'active'    THEN 'davom etmoqda'
     ELSE 'boshqa' END

-- Simple CASE: qisqaroq, lekin faqat bitta ifodaga tenglikni tekshiradi
CASE status
     WHEN 'completed' THEN 'tugatildi'
     WHEN 'active'    THEN 'davom etmoqda'
     ELSE 'boshqa' END

:::gotcha ⚠️ Simple CASE NULL ni topa olmaydi CASE status WHEN NULL THEN ... hech qachon ishlamaydi, chunki u status = NULL ga aylanadi — abadiy UNKNOWN. Faqat searched ko'rinish WHEN status IS NULL ni tekshira oladi. NULL bo'lishi mumkin bo'lganda searched ko'rinishni ishlating. :::

ORDER BY dagi CASE — o'z tartibingiz

Alifbo tartibi kamdan-kam odam xohlagan tartib bo'ladi. CASE o'zingizniki belgilash imkonini beradi:

SELECT enrollment_id, status
FROM enrollments
ORDER BY CASE status
             WHEN 'active'    THEN 1
             WHEN 'completed' THEN 2
             WHEN 'failed'    THEN 3
             WHEN 'dropped'   THEN 4
             ELSE 5
         END,
         enrollment_id;

… 2500 qatordan birinchi 15 tasi

2500 qator

2 ms

Yana bir ko'p ishlatiladigan usul — database ning standartidan qat'i nazar NULL larni oxiriga majburlash:

ORDER BY CASE WHEN city IS NULL THEN 1 ELSE 0 END, city;

Aggregate ichidagi CASE (pivot) Bu — butun bobdagi eng qimmatli pattern. CASE ni SUM yoki COUNT ichiga qo'yish row larni column larga aylantiradi — sof SQL da pivot table.

SELECT
    course_id,
    COUNT(*)                                                  AS total,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)     AS completed,
    SUM(CASE WHEN status = 'active'    THEN 1 ELSE 0 END)     AS active,
    SUM(CASE WHEN status = 'failed'    THEN 1 ELSE 0 END)     AS failed,
    SUM(CASE WHEN status = 'dropped'   THEN 1 ELSE 0 END)     AS dropped
FROM enrollments
GROUP BY course_id
ORDER BY course_id;

14 qator

1 ms

course_idtotalcompletedactivefaileddropped

152120 255000 332001 432100 541300

Buni 4-bobdagi GROUP BY course_id, status bilan solishtiring: u har bir birikma uchun bitta row beradi va bo'shlarini tashlab ketadi. Bu esa haqiqiy to'r beradi, real nol lar bilan. Rahbariyat aynan shunisiga qarashni xohlaydi.

Xuddi shu pattern shartli foizni ham beradi — 4-bobdagi o'tish foizi idiomi:

SELECT
    course_id,
    COUNT(*) AS graded,
    ROUND(100.0 * SUM(CASE WHEN grade >= 60 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pass_pct
FROM enrollments WHERE grade IS NOT NULL
GROUP BY course_id;

14 qator

1 ms

String function lar

FunctionNima qiladiMisol → natija

UPPER(s) / LOWER(s)Harf holatini o'zgartiradiUPPER('aziza') → AZIZA LENGTH(s)Belgilar soniLENGTH('Aziza') → 5 SUBSTR(s, start, len)Qismini ajratadi (1 dan boshlanadi!)SUBSTR('Karimova',1,3) → Kar REPLACE(s, from, to)Barcha uchrashlarni almashtiradiREPLACE('a-b-c','-','.') → a.b.c TRIM(s) / LTRIM / RTRIMChetdagi bo'shliqlarni olib tashlaydiTRIM(' hi ') → hi INSTR(s, sub)Qism o'rni, yo'q bo'lsa 0INSTR('a@b.uz','@') → 2 a || bBirlashtiradi'A' || 'B' → AB

SELECT
    first_name || ' ' || last_name                          AS full_name,
    SUBSTR(first_name,1,1) || SUBSTR(last_name,1,1)         AS initials,
    LOWER(first_name) || '.' || LOWER(last_name) || '@school.uz' AS email,
    LENGTH(first_name || last_name)                         AS name_length
FROM students
LIMIT 4;

4 qator

0 ms

:::gotcha ⚠️ SQL da string 0 dan emas, 1 dan boshlanadi Har bir Python dasturchisi bunga bir marta qoqiladi. SUBSTR(s, 1, 3) — birinchi uchta belgi; SUBSTR(s, 0, 3) esa kutilmagan boshqa narsa qiladi. SQL da hech qayerda noldan boshlanuvchi indeks yo'q. :::

Dialect izohi: string function nomlari SQL ning eng kam universal qismlaridan biri.

Iflos matnni tozalash

Real hayotdagi eng ko'p uchraydigan ikkita matn muammosi va ikkalasini ham WHERE ichida tuzatish:

-- 1-muammo: ko'rinmas oldingi/keyingi bo'shliqlar.
-- 'Tashkent ' <> 'Tashkent', shuning uchun bu row hech qachon mos kelmaydi.
SELECT * FROM students WHERE TRIM(city) = 'Tashkent';

-- 2-muammo: har xil harf holati ('TASHKENT', 'tashkent', 'Tashkent')
SELECT * FROM students WHERE LOWER(TRIM(city)) = 'tashkent';

-- Ikkalasi birga, tozalangan chiqish column i sifatida
SELECT
    city                                   AS raw,
    UPPER(SUBSTR(TRIM(LOWER(city)),1,1))
      || SUBSTR(TRIM(LOWER(city)),2)       AS cleaned   -- 'Tashkent'
FROM students;

1-natija

… 82 qatordan birinchi 15 tasi

82 qator

2-natija

… 82 qatordan birinchi 15 tasi

82 qator

3-natija

… 300 qatordan birinchi 15 tasi

300 qator

1 ms

:::gotcha ⚠️ WHERE ichida tozalash index ni yo'qotadi WHERE LOWER(TRIM(city)) = 'tashkent' to'g'ri, lekin to'liq scan ga majbur qiladi (3-bobdagi non-sargable qoidasi). Bir martalik tahlil uchun bu yaxshi. Doim ishlaydigan query uchun esa haqiqiy yechim — data ni kirish paytida tozalash: bir marta UPDATE (10-bob), yoki iflos qiymatlarni umuman qabul qilmaydigan CHECK constraint. :::

Nima qilishni hal qilishdan oldin iflos qiymatlarni topishning tez yo'li:

SELECT city, COUNT(*) AS n,
       CASE WHEN city <> TRIM(city) THEN 'bo''shliqli' ELSE 'toza' END AS issue
FROM students
GROUP BY city
ORDER BY n DESC;

9 qator

0 ms

Date va time function lar SQLite sanalarni 'YYYY-MM-DD' matn sifatida saqlaydi va ular bilan ishlash uchun beshta function beradi.

SELECT
    enrolled_at,
    strftime('%Y',    enrolled_at) AS year,
    strftime('%Y-%m', enrolled_at) AS year_month,
    strftime('%w',    enrolled_at) AS weekday_num,
    CASE strftime('%w', enrolled_at)
        WHEN '0' THEN 'Yakshanba'  WHEN '1' THEN 'Dushanba'
        WHEN '2' THEN 'Seshanba'   WHEN '3' THEN 'Chorshanba'
        WHEN '4' THEN 'Payshanba'  WHEN '5' THEN 'Juma'
        ELSE 'Shanba'
    END AS weekday
FROM enrollments
WHERE enrollment_id IN (1, 13, 19);

3 qator

0 ms

SELECT
    DATE('2026-02-14', '+7 days')            AS week_later,      -- 2026-02-21
    DATE('2026-02-14', '-1 month')           AS month_earlier,   -- 2026-01-14
    DATE('2026-02-14', 'start of month')     AS month_start,     -- 2026-02-01
    DATE('2026-02-14', 'start of month', '+1 month', '-1 day') AS month_end,  -- 2026-02-28
    DATE('2026-02-14', 'start of year')      AS year_start;      -- 2026-01-01

1 qator

0 ms

O'sha month_end zanjiri — 28/30/31 ni qo'lda yozmasdan va kabisa yili haqida o'ylamasdan istalgan oyning oxirgi kunini olishning standart usuli.

Sana arifmetikasi — yosh va oraliqlar

Sanalarni ayirish uchun JULIANDAY kerak, u sanani oddiy kunlar soniga aylantiradi.

SELECT
    first_name,
    birth_date,
    CAST((JULIANDAY('2026-09-01') - JULIANDAY(birth_date)) / 365.25 AS INTEGER) AS age
FROM students
ORDER BY age DESC;

… 300 qatordan birinchi 15 tasi

300 qator

1 ms

:::gotcha ⚠️ Nega 365.25 va nega ROUND emas, CAST 365.25 kabisa yillarni hisobga oladi. CAST(... AS INTEGER) kesib tashlaydi, "yosh" degani ham shu — siz 22 ga to'lgan kuningizgacha 21 dasiz, olti oy oldin "22 ga yaxlitlangan" emas. Bu yerda ROUND ishlatish student laringizning yarmini bir yosh kattaroq qilib ko'rsatadi. Yana e'tibor bering: mos yozuvlar sanasi DATE('now') emas, aniq yozilgan — shunda jadvaldagi raqamlar darsingizda o'zgarmaydi. :::

Hodisalar orasidagi oraliqlar — JULIANDAY ni 7-bobdagi LAG bilan birga:

SELECT
    student_id,
    enrolled_at,
    LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at) AS previous,
    CAST(JULIANDAY(enrolled_at)
       - JULIANDAY(LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at))
      AS INTEGER) AS days_since_last
FROM enrollments
WHERE student_id IN (1, 3)
ORDER BY student_id, enrolled_at;

… 16 qatordan birinchi 15 tasi

16 qator

0 ms

student_idenrolled_atpreviousdays_since_last

12025-09-05NULLNULL 12025-09-052025-09-050 12026-02-032025-09-05151 32025-09-18NULLNULL 32026-02-052025-09-18140 32026-02-052026-02-050

CAST va type o'zgartirish

SELECT
    CAST('42'   AS INTEGER)   AS text_to_int,      -- 42
    CAST(3.99   AS INTEGER)   AS float_to_int,     -- 3   (kesildi, yaxlitlanmadi!)
    CAST(7      AS REAL)      AS int_to_real,      -- 7.0
    CAST(42     AS TEXT)      AS int_to_text,      -- '42'
    CAST('abc'  AS INTEGER)   AS bad_cast;         -- SQLite da 0, PostgreSQL da XATO

1 qator

0 ms

:::gotcha ⚠️ INTEGER ga CAST kesadi; muvaffaqiyatsiz cast database ga qarab farq qiladi CAST(3.99 AS INTEGER) — bu 4 emas, 3. Yaxlitlash kerak bo'lsa ROUND ishlating. Noto'g'ri cast esa SQLite da indamay 0 qaytaradi, PostgreSQL da xato beradi — ya'ni lokalda "ishlaydigan" data tozalash query si production da yiqilishi yoki battari, har bir o'qib bo'lmaydigan qiymatni jimgina nolga aylantirishi mumkin. :::

Eng ko'p uchraydigan amaliy foydasi — 2-bobdagi haqiqiy bo'lishga majburlash:

SELECT 7 / 2                    AS integer_division,   -- 3
       7 * 1.0 / 2              AS trick_multiply,     -- 3.5
       CAST(7 AS REAL) / 2      AS explicit_cast;      -- 3.5

1 qator

0 ms

Matematika va yaxlitlash

FunctionNima qiladiMisol → natija

ROUND(x, n)n kasrgacha yaxlitlaydiROUND(79.428, 2) → 79.43 ABS(x)ModulABS(-15) → 15 x % yQoldiq17 % 5 → 2 MIN(a,b) / MAX(a,b)Argumentlarning kichigi / kattasiMAX(10, 20) → 20 ABS, POWER, SQRTStandart matematikaSQRT(16) → 4.0

:::gotcha ⚠️ MIN ikki xil narsani anglatadi Bitta argumentli MIN(grade) — aggregate, ya'ni ko'p row bo'ylab eng past baho. Ikkita argumentli MIN(a, b) esa bitta row ichidagi ikkita qiymatni solishtiradigan scalar function. Bir xil nom, butunlay boshqa xatti-harakat. (PostgreSQL chalkashlikni scalar variantni LEAST/GREATEST deb atash bilan hal qilgan.) :::

Mashqlar to'plami

-- 1. Har bir baholangan enrollment uchun harfli baho
SELECT enrollment_id, grade,
       CASE WHEN grade >= 90 THEN 'A' WHEN grade >= 80 THEN 'B'
            WHEN grade >= 70 THEN 'C' WHEN grade >= 60 THEN 'D'
            ELSE 'F' END AS letter
FROM enrollments WHERE grade IS NOT NULL ORDER BY grade DESC;

-- 2. Har bir harfli bahodan nechtadan?
SELECT CASE WHEN grade >= 90 THEN 'A' WHEN grade >= 80 THEN 'B'
            WHEN grade >= 70 THEN 'C' WHEN grade >= 60 THEN 'D'
            ELSE 'F' END AS letter,
       COUNT(*) AS n
FROM enrollments WHERE grade IS NOT NULL GROUP BY letter ORDER BY letter;

-- 3. Course bo'yicha status pivot
SELECT course_id,
       SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status='active'    THEN 1 ELSE 0 END) AS active,
       SUM(CASE WHEN status='failed'    THEN 1 ELSE 0 END) AS failed
FROM enrollments GROUP BY course_id;

-- 4. Yaratilgan email manzillar
SELECT LOWER(first_name) || '.' || LOWER(last_name) || '@school.uz' AS email
FROM students;

-- 5. Har bir student ning yoshi
SELECT first_name,
       CAST((JULIANDAY('2026-09-01') - JULIANDAY(birth_date))/365.25 AS INTEGER) AS age
FROM students ORDER BY age DESC;

-- 6. Course lar bo'yicha narx darajasi
SELECT title, price,
       CASE WHEN price >= 2500000 THEN 'premium'
            WHEN price >= 1400000 THEN 'standart'
            ELSE 'oddiy' END AS price_band
FROM courses ORDER BY price DESC;

-- 7. Hafta kunlari bo'yicha enrollment lar
SELECT strftime('%w', enrolled_at) AS weekday_num, COUNT(*) AS n
FROM enrollments GROUP BY weekday_num ORDER BY weekday_num;

-- 8. Student larni yosh guruhlariga ajratish
SELECT CASE WHEN CAST((JULIANDAY('2026-09-01')-JULIANDAY(birth_date))/365.25 AS INTEGER) <= 21
            THEN '21 va undan kichik' ELSE '22 va undan katta' END AS age_band,
       COUNT(*) AS n
FROM students GROUP BY age_band;

1-natija

… 1826 qatordan birinchi 15 tasi

1826 qator

2-natija

5 qator

3-natija

14 qator

4-natija

… 300 qatordan birinchi 15 tasi

300 qator

5-natija

… 300 qatordan birinchi 15 tasi

300 qator

6-natija

15 qator

7-natija

7 qator

8-natija

2 qator

5 ms

Intervyu savollari

S: CASE qaysi shoxni tanlashini qanday hal qiladi?

Shartlarni yuqoridan pastga tekshiradi va TRUE bo'lgan birinchisini qaytaradi, shuning uchun tartib muhim. Hech biri mos kelmasa va ELSE bo'lmasa, NULL qaytaradi.

S: Sof SQL da pivot table ni qanday quriladi?

Har bir chiqish column i uchun bittadan SUM(CASE WHEN col = 'qiymat' THEN 1 ELSE 0 END) AS qiymat yozib, row o'lchovi bo'yicha GROUP BY qilinadi.

S: Nega AVG(CASE ...) ichida ELSE 0 noto'g'ri?

Chunki u mos kelmagan row larni chiqarib tashlash o'rniga o'rtachaga kiradigan nol larga aylantiradi. ELSE ni yozmang — shunda ular NULL bo'ladi va AVG ularni tashlab ketadi.

S: Simple va searched CASE farqi?

Simple CASE expr WHEN v THEN ... faqat bitta ifodaga tenglikni tekshiradi va NULL ni topa olmaydi. Searched CASE WHEN shart THEN ... esa har qanday shartga, jumladan IS NULL ga ruxsat beradi.

S: SUBSTR 0 dan boshlanadimi yoki 1 dan?

1 dan. Birinchi belgi — 1-o'rin.

S: Oyning oxirgi kunini qanday olasiz?

Oy boshiga o'ting, bir oy qo'shing, bir kun ayiring: DATE(d,'start of month','+1 month','-1 day'). Hech qachon 30 yoki 31 ni qo'lda yozmang.

S: Katta table da WHERE UPPER(name) = 'ALI' nimasi bilan yomon?

Column ustidagi function uni non-sargable qiladi, ya'ni hech qanday index ishlatilmaydi. Query paytida column ni o'rash o'rniga data ni tuzating yoki functional index qo'shing.

S: CAST(3.99 AS INTEGER) — 3 mi, 4 mi?

1. CAST kesadi. 4 kerak bo'lsa ROUND ishlating.

Xulosa

• CASE WHEN — SQL ning if/else i. Shartlar tartib bilan tekshiriladi, shuning uchun ularni yuqoridan pastga yozing va NULL tekshiruvini birinchi qo'ying — aks holda baholanmagan row lar indamay F bo'lib qoladi.
• Simple CASE NULL ni topa olmaydi; searched ko'rinish topa oladi.
• ORDER BY dagi CASE o'z saralash tartibingizni va NULL ning o'rnini belgilaydi.
• SUM(CASE WHEN ... THEN 1 ELSE 0 END) — pivot idiomi: row lar column ga aylanadi, real nol lar bilan. Aggregate AVG bo'lsa ELSE 0 ni tashlang.
• String ishlari: ||, UPPER/LOWER, SUBSTR (1 dan), TRIM, REPLACE, LENGTH, INSTR. Nomlar database lar orasida ancha farq qiladi.
• TRIM + LOWER iflos matnning ko'pini tuzatadi, lekin WHERE ichida tozalash index ni yo'qotadi — saqlangan data ni tozalang.
• Sanalar: ajratish uchun strftime, siljitish uchun DATE(d, modifikator), ayirish uchun JULIANDAY. 'start of month','+1 month','-1 day' oy oxirini xavfsiz beradi.
• Yosh = CAST(kunlar / 365.25 AS INTEGER) — kesing, hech qachon yaxlitlamang.
• INTEGER ga CAST kesadi; noto'g'ri cast SQLite da indamay 0, PostgreSQL da esa xato.

Keyingi: 9-bob — UNION, INTERSECT va EXCEPT bilan query natijalarini bir-birining ustiga qo'yish.