SQL: ma'lumotni bazadan olish

import pandas as pd
import sqlite3

df = pd.read_csv(DATA / "uy_ijara.csv")

# 1-yo'l: pandas — avval HAMMASINI xotiraga yuklaymiz
natija_pandas = df.groupby("tuman").narx_usd.mean().round(1).sort_values(ascending=False)
print("pandas:")
print(natija_pandas.to_string())

show(pd.DataFrame([
    {"": "pandas", "Qanday ishlaydi": "Butun jadvalni XOTIRAGA yuklaydi, keyin hisoblaydi",
     "40 mln qatorda": "Xotira yetmaydi yoki kompyuter muzlaydi"},
    {"": "SQL", "Qanday ishlaydi": "Hisobni BAZANING O'ZIDA bajaradi, faqat natija keladi",
     "40 mln qatorda": "Bir necha sekund, 8 qator natija qaytadi"},
]))

baza = sqlite3.connect(":memory:")            # xotirada baza (fayl kerak emas)
df.to_sql("ijara", baza, index=False, if_exists="replace")

# ikkinchi jadval — JOIN uchun kerak bo'ladi
tumanlar = pd.DataFrame([
    {"tuman": "Yunusobod", "metro_liniya": "Yunusobod", "aholi_ming": 265},
    {"tuman": "Chilonzor", "metro_liniya": "Chilonzor", "aholi_ming": 245},
    {"tuman": "Mirzo Ulug'bek", "metro_liniya": "Chilonzor", "aholi_ming": 215},
    {"tuman": "Shayxontohur", "metro_liniya": "O'zbekiston", "aholi_ming": 180},
    {"tuman": "Yakkasaroy", "metro_liniya": "O'zbekiston", "aholi_ming": 115},
    {"tuman": "Olmazor", "metro_liniya": "Chilonzor", "aholi_ming": 290},
    {"tuman": "Sergeli", "metro_liniya": "Sergeli", "aholi_ming": 195},
    # DIQQAT: Bektemir YO'Q — LEFT JOIN darsida kerak bo'ladi
])
tumanlar.to_sql("tumanlar", baza, index=False, if_exists="replace")

print("Jadvallar:", pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", baza).name.tolist())
print(f"ijara    : {pd.read_sql('SELECT COUNT(*) n FROM ijara', baza).n[0]} qator")
print(f"tumanlar : {pd.read_sql('SELECT COUNT(*) n FROM tumanlar', baza).n[0]} qator")

def sorov(sql):
    """SQL ni bajaradi va natijani DataFrame qilib qaytaradi."""
    return pd.read_sql(sql, baza)

show(sorov("SELECT tuman, xonalar, maydon_m2, narx_usd FROM ijara LIMIT 5"))

show(pd.DataFrame([
    {"SQL": "SELECT ustun1, ustun2", "O'zbekcha": "Shu ustunlarni OLIB BER"},
    {"SQL": "FROM jadval", "O'zbekcha": "Shu jadvaldan"},
    {"SQL": "WHERE shart", "O'zbekcha": "Faqat shu shartga mos qatorlarni"},
    {"SQL": "ORDER BY ustun DESC", "O'zbekcha": "Shu ustun bo'yicha kamayish tartibida saralab"},
    {"SQL": "LIMIT 10", "O'zbekcha": "Faqat birinchi 10 tasini"},
]))

show(sorov("""
    SELECT tuman, xonalar, maydon_m2, metro_km, narx_usd
    FROM ijara
    WHERE tuman = 'Yunusobod'
      AND xonalar = 2
      AND mebel = 1
    ORDER BY narx_usd DESC
    LIMIT 6
"""))

show(sorov("""
    SELECT
        COUNT(*)                                        AS jami,
        SUM(CASE WHEN narx_usd BETWEEN 400 AND 700 THEN 1 ELSE 0 END) AS orta_narx,
        SUM(CASE WHEN tuman IN ('Sergeli', 'Bektemir') THEN 1 ELSE 0 END) AS chekka_tuman,
        SUM(CASE WHEN qurilgan_yil IS NULL THEN 1 ELSE 0 END)          AS yil_yoq
    FROM ijara
"""))

print("Isbot:")
print("  = NULL  natijasi:", sorov("SELECT COUNT(*) n FROM ijara WHERE qurilgan_yil = NULL").n[0], "qator")
print("  IS NULL natijasi:", sorov("SELECT COUNT(*) n FROM ijara WHERE qurilgan_yil IS NULL").n[0], "qator")

show(sorov("""
    SELECT
        tuman,
        COUNT(*)              AS elonlar,
        ROUND(AVG(narx_usd))  AS ortacha_narx,
        MIN(narx_usd)         AS eng_arzon,
        MAX(narx_usd)         AS eng_qimmat,
        ROUND(AVG(ijaraga_berildi_30k) * 100, 1) AS tez_ketish_foizi
    FROM ijara
    GROUP BY tuman
    ORDER BY ortacha_narx DESC
"""))

show(sorov("""
    SELECT xonalar, COUNT(*) AS soni, ROUND(AVG(narx_usd)) AS ortacha
    FROM ijara
    GROUP BY xonalar
    HAVING COUNT(*) > 100
    ORDER BY xonalar
"""))

show(sorov("""
    SELECT
        i.tuman,
        t.metro_liniya,
        t.aholi_ming,
        COUNT(*)             AS elonlar,
        ROUND(AVG(i.narx_usd)) AS ortacha_narx
    FROM ijara i
    INNER JOIN tumanlar t ON i.tuman = t.tuman
    GROUP BY i.tuman, t.metro_liniya, t.aholi_ming
    ORDER BY ortacha_narx DESC
"""))

print("ijara dagi tumanlar  :", sorov("SELECT COUNT(DISTINCT tuman) n FROM ijara").n[0])
print("tumanlar jadvalida   :", sorov("SELECT COUNT(*) n FROM tumanlar").n[0])
print("INNER JOIN dan keyin :", len(sorov("SELECT DISTINCT i.tuman FROM ijara i INNER JOIN tumanlar t ON i.tuman=t.tuman")))
print()
print("Yo'qolgan tuman:")
show(sorov("""
    SELECT i.tuman, COUNT(*) AS elonlar
    FROM ijara i
    LEFT JOIN tumanlar t ON i.tuman = t.tuman
    WHERE t.tuman IS NULL
    GROUP BY i.tuman
"""))

show(pd.DataFrame([
    {"JOIN turi": "INNER JOIN", "Nimani qoldiradi": "Faqat ikkisida ham mos keladigan qatorlar"},
    {"JOIN turi": "LEFT JOIN", "Nimani qoldiradi": "CHAPDAGI hamma qator + mos kelgani (mos kelmasa NULL)"},
    {"JOIN turi": "RIGHT JOIN", "Nimani qoldiradi": "O'NGDAGI hamma qator (SQLite da yo'q — jadvallarni almashtiring)"},
    {"JOIN turi": "FULL OUTER JOIN", "Nimani qoldiradi": "Ikkisidagi hamma qator"},
]))

print("LEFT JOIN bilan hamma tuman saqlanadi:")
show(sorov("""
    SELECT i.tuman,
           COALESCE(t.metro_liniya, 'metro YO`Q') AS metro_liniya,
           COUNT(*) AS elonlar
    FROM ijara i
    LEFT JOIN tumanlar t ON i.tuman = t.tuman
    GROUP BY i.tuman, t.metro_liniya
    ORDER BY elonlar DESC
"""))

show(sorov("""
    SELECT
        tuman,
        xonalar,
        narx_usd,
        ROUND(AVG(narx_usd) OVER (PARTITION BY tuman))            AS tuman_ortachasi,
        narx_usd - ROUND(AVG(narx_usd) OVER (PARTITION BY tuman)) AS farq,
        RANK() OVER (PARTITION BY tuman ORDER BY narx_usd DESC)   AS tumandagi_orni
    FROM ijara
    WHERE tuman IN ('Yunusobod', 'Sergeli')
    ORDER BY tuman, tumandagi_orni
    LIMIT 10
"""))

show(pd.DataFrame([
    {"Vazifa": "40 mln qatordan 10 ming tasini olish", "Qaysi biri": "SQL",
     "Nega": "Xotiraga hammasi sig'maydi"},
    {"Vazifa": "Guruhlab yig'ish, JOIN", "Qaysi biri": "SQL",
     "Nega": "Baza buni indeks bilan tez bajaradi"},
    {"Vazifa": "Grafik chizish", "Qaysi biri": "pandas",
     "Nega": "SQL grafik chizmaydi"},
    {"Vazifa": "Model o'qitish", "Qaysi biri": "pandas",
     "Nega": "sklearn DataFrame/massiv kutadi"},
    {"Vazifa": "Murakkab tozalash, apply", "Qaysi biri": "pandas",
     "Nega": "Python funksiyalarini ishlatish mumkin"},
]))

# Bazadan FAQAT kerakli qismni olamiz — bu haqiqiy loyihadagi naqsh
model_uchun = sorov("""
    SELECT i.xonalar, i.maydon_m2, i.metro_km, i.mebel, i.tuman,
           COALESCE(t.aholi_ming, 0) AS aholi_ming,
           i.ijaraga_berildi_30k
    FROM ijara i
    LEFT JOIN tumanlar t ON i.tuman = t.tuman
    WHERE i.narx_usd BETWEEN 200 AND 1500
""")
print(f"Bazadan keldi: {model_uchun.shape[0]} qator, {model_uchun.shape[1]} ustun")
print()
show(model_uchun.head(5))

show(pd.DataFrame([
    {"Buyruq": "SELECT / FROM / WHERE", "Nima qiladi": "Ustun va qator tanlash"},
    {"Buyruq": "IS NULL", "Nima qiladi": "Bo'sh qiymat tekshirish (= NULL ishlamaydi)"},
    {"Buyruq": "GROUP BY + AVG/COUNT", "Nima qiladi": "Guruhlab hisoblash"},
    {"Buyruq": "HAVING", "Nima qiladi": "Guruhlarni filtrlash (WHERE qatorlarni)"},
    {"Buyruq": "INNER JOIN", "Nima qiladi": "Mos kelganlarni bog'laydi — qator YO'QOTADI"},
    {"Buyruq": "LEFT JOIN", "Nima qiladi": "Chapdagi hammasini saqlaydi"},
    {"Buyruq": "OVER (PARTITION BY)", "Nima qiladi": "Qatorni saqlab, guruh hisobini qo'shadi"},
]))