Python-нан SQL: `GROUP BY`, `JOIN` және нөл емес бос жауап

Не үшін керек

Өткен сабақта тарих кестеге жатты, ал SELECT ең қарапайым түрде жазылды: жолдарды таңдап, циклмен аралау.

Бірақ мұндағы цикл — қажеттілік емес, әдет. «Әр валюта бойынша орташа», «ең қымбат күн», «қаңтарда не болды» — бұл Python-ның жұмысы емес: дерекқор мұндай сұрақтарға сұраныстың бір жолымен жауап береді, әрі жылдамырақ жауап береді, себебі керек емес нәрсені сыртқа шығармайды.

Айырма көлемнен көрінеді: цикл тоқсан пайызын лақтырып тастау үшін барлық жолды жадқа тартады; сұраныс дайын жауапты береді.

Бірден тұтас

suranys.py файлы. Іске қосу: ортадан python suranys.py.

Міндеттісі — алғашқы үш блок: агрегаттар, GROUP BY және HAVING. Төртіншісі екі кестені біріктіруді, бесіншісі күннің бөлігінен жасалған топты, алтыншысы бос жауаптағы тұзақты көрсетеді.

"""22-сабақ: Python-нан SQL — тілімді циклмен емес, сұраныспен алу.

Өткен сабақта кесте мен ең қарапайым `SELECT` пайда болды. Бүгін дерекқордың
өзі санай алатынын білеміз: топтау, екі кестені біріктіру және бұрын шартты
циклмен алынатын дайын жауапты беру.
"""

import sqlite3
from pathlib import Path

HERE = Path(__file__).parent
FILE = HERE / "svodka.db"
FILE.unlink(missing_ok=True)

conn = sqlite3.connect(FILE)
conn.row_factory = sqlite3.Row
conn.executescript("""
    CREATE TABLE rates (
        day   TEXT NOT NULL,
        code  TEXT NOT NULL,
        value REAL NOT NULL,
        PRIMARY KEY (day, code)
    );
    CREATE TABLE currencies (
        code TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        quant INTEGER NOT NULL
    );
""")
conn.executemany("INSERT INTO rates VALUES (?, ?, ?)", [
    ("2026-01-15", "USD", 510.43), ("2026-01-15", "EUR", 594.86), ("2026-01-15", "UZS", 4.24),
    ("2026-01-16", "USD", 511.02), ("2026-01-16", "EUR", 596.10), ("2026-01-16", "UZS", 4.21),
    ("2026-02-02", "USD", 515.70), ("2026-02-02", "EUR", 601.44), ("2026-02-02", "UZS", 4.19),
])
conn.executemany("INSERT INTO currencies VALUES (?, ?, ?)", [
    ("USD", "АҚШ доллары", 1), ("EUR", "еуро", 1), ("UZS", "өзбек сомы", 100),
])
conn.commit()

print("== циклдің орнына жауаптың бір жолы")
row = conn.execute("""
    SELECT count(*) AS days, min(value) AS low, max(value) AS high, avg(value) AS mid
    FROM rates WHERE code = ?
""", ("USD",)).fetchone()
print(f"доллар: {row['days']} күн, {row['low']}-тан {row['high']}-ке дейін, орташа {row['mid']:.2f}")

print()
print("== топтау: әр валютаға бір жол")
for row in conn.execute("""
    SELECT code, count(*) AS days, round(avg(value), 2) AS mid
    FROM rates
    GROUP BY code
    ORDER BY mid DESC
"""):
    print(f"{row['code']}: {row['days']} күн, орташа {row['mid']}")

print()
print("== топ бойынша сүзгі — WHERE емес, HAVING")
for row in conn.execute("""
    SELECT code, round(avg(value), 2) AS mid
    FROM rates
    GROUP BY code
    HAVING avg(value) > ?
    ORDER BY code
""", (100,)):
    print(f"{row['code']}: {row['mid']}")

print()
print("== екі кесте: код бойынша біріктіру")
for row in conn.execute("""
    SELECT r.day, c.name, round(r.value / c.quant, 4) AS unit
    FROM rates AS r
    JOIN currencies AS c ON c.code = r.code
    WHERE r.day = ?
    ORDER BY unit DESC
""", ("2026-01-15",)):
    print(f"{row['day']} {row['name']}: бір бірлігі {row['unit']}")

print()
print("== ай — топ ретінде: күн жолының бөлігі бойынша тілім")
for row in conn.execute("""
    SELECT substr(day, 1, 7) AS month, code, round(avg(value), 2) AS mid
    FROM rates
    WHERE code = ?
    GROUP BY month, code
    ORDER BY month
""", ("USD",)):
    print(f"{row['month']}: {row['mid']}")

print()
print("== SQL-де болмайтын нәрсе: бос жауап — қате емес")
missing = conn.execute("SELECT avg(value) AS mid FROM rates WHERE code = ?", ("GBP",)).fetchone()
print("фунт: жол жоқ, avg қайтарғаны —", missing["mid"])
print("бос бойынша count:", conn.execute(
    "SELECT count(*) AS n FROM rates WHERE code = ?", ("GBP",)).fetchone()["n"])

conn.close()
FILE.unlink()

Шығатыны:

== циклдің орнына жауаптың бір жолы
доллар: 3 күн, 510.43-тан 515.7-ке дейін, орташа 512.38

== топтау: әр валютаға бір жол
EUR: 3 күн, орташа 597.47
USD: 3 күн, орташа 512.38
UZS: 3 күн, орташа 4.21

== топ бойынша сүзгі — WHERE емес, HAVING
EUR: 597.47
USD: 512.38

== екі кесте: код бойынша біріктіру
2026-01-15 еуро: бір бірлігі 594.86
2026-01-15 АҚШ доллары: бір бірлігі 510.43
2026-01-15 өзбек сомы: бір бірлігі 0.0424

== ай — топ ретінде: күн жолының бөлігі бойынша тілім
2026-01: 510.73
2026-02: 515.7

== SQL-де болмайтын нәрсе: бос жауап — қате емес
фунт: жол жоқ, avg қайтарғаны — None
бос бойынша count: 0

Талдау

Python емес, дерекқор санайды

SELECT count(*), min(value), max(value), avg(value) FROM rates WHERE code = ?

count, min, max, avg, sum — бұл агрегаттар: олар көп жолды алып, бір мән қайтарады. «Күн саны, қайдан қайда, орташа» деген бүкіл блок — жауаптың бір жолы, әрі Python-ға ол есептеліп қойған күйде келеді.

Сол нәрсе циклмен — бұл барлық жол бойынша for, төрт айнымалы және бос-емесіне тексеру. Айырма әдемілікте емес: цикл дерекқордың өзі жинай алатынды алдымен жадқа сүйрейді.

Елестетіп көріңіз. Асханадағы тапсырыс. Сіз бір порция сұрайсыз, өзіңіз өлшеу үшін қазанды үстелге алып келмейсіз.

GROUP BY: әр топқа бір жол

EUR: 3 күн, орташа 597.47
USD: 3 күн, орташа 512.38
UZS: 3 күн, орташа 4.21

GROUP BY code жолдарды баған мәні бойынша шоғырға бөледі, ал агрегат әрқайсысының ішінде саналады. Валютаға жауаптың бір жолы, ал ретті ORDER BY белгілейді — әдепкіде ол жоқ, әрі «қалай шықса, солай» дегенге сүйенуге болмайды.

Есте сақтауға тұрарлық ереже: топтау кезінде SELECT ішінде тек не бойынша топтағаның және агрегаттар бола алады. code — болады, avg(value) — болады, ал олардың қасындағы day — жауабы жоқ сұрақ: үш күннің қайсысын айтып тұрсыз.

HAVING — топтарға арналған WHERE

GROUP BY code
HAVING avg(value) > ?

WHERE топтауға дейін жолдарды сүзеді, HAVING — кейін топтарды. WHERE avg(value) > 100 деп жазуға болмайды: WHERE жұмыс істеп тұрған сәтте орташа әлі жоқ.

Осыдан сұранысты дауыстап оқудың реті шығады: алдымен қайдан (FROM), содан кейін қандай жолдар (WHERE), сосын шоғырға қалай бөлу (GROUP BY), сосын қай шоғырды қалдыру (HAVING), тек содан кейін — қалай көрсету (ORDER BY).

JOIN: атау басқа кестеде жатыр

FROM rates AS r
JOIN currencies AS c ON c.code = r.code

Бағамдар кестесінде валютаның коды жатыр, ал оның атауы мен quant-ы — анықтамалықта. Солай болуы да керек: доллардың атауы күн сайын өзгермейді, әрі оны әр бағам жолында сақтау бір нәрсені мың рет қайталау деген сөз.

JOIN ... ON екі кестенің жолдарын сәйкестік бойынша біріктіреді: әр бағам жолына сол коды бар анықтамалық жолы қосылады. r мен c бүркеншік аттары — әшекей емес: онсыз code-ты толық жазуға тура келер еді, ал r.value / c.quant бірден оқылады.

Бөлуге назар аударыңыз: бір бірлікке бағам — бұл value / quant, әрі оны енді он тоғызыншы сабақтағыдай біз емес, дерекқор санайды.

Ай — баған емес, топ

SELECT substr(day, 1, 7) AS month ... GROUP BY month

Күн ISO-мен мәтін болып жатыр, сондықтан алғашқы жеті таңба — дәл жыл мен ай. Оларды substr арқылы алып, солар бойынша топтауға болады: кестеде «ай» деген баған жоқ, ал айлар бойынша тілім бар.

Бұл пішім дұрыс таңдалғандықтан жұмыс істейді. «15.01.2026»-мен сол әдіс күндер бойынша топ берер еді — күндерді ISO-да ұстаудың тағы бір себебі, ол туралы он төртінші сабақ айтқан.

Бос жауап — қате де емес, нөл де емес

фунт: жол жоқ, avg қайтарғаны — None
бос бойынша count: 0

Міне, сабақты соңына дейін оқуға тұрарлық тұзақ. Кестеде жоқ валюта туралы сұраныс құламайды: ол avg-і None болатын жол қайтарады.

count бұл кезде адал түрде 0 дейді — өйткені «неше жол» деген сұрақтың бос жиында жауабы бар, ал «орташа» дегеннің жоқ. Әрі қарай бағдарламада f"{mid:.2f}" None-ға тап болып, кінәсіз көрінетін жерде құлайды — бұл жетінші сабақтағы сол жетіспейтін мән, енді дерекқордан.

Анық тексереді: if mid is None. Немесе дерекқордан мәнді өзі қоюын сұрайды — COALESCE(avg(value), 0), — бірақ мұндағы нөл шынымен «нөл» дегенді білдірсе ғана, «білмейміз» дегенді емес.

Сабақ картасы

Өз сөзіңізбен айтыңыз

Қарамай, дауыстап немесе қағазға жауап беріңіз. Жауаптары — сабақтың соңында.

1. HAVING WHERE-ден немен өзгеше, әрі оларды неге орнымен ауыстыруға болмайды?
2. Валютаның атауы неге бағамның қасында емес, бөлек кестеде жатыр?
3. Бос жолдар жиынында avg не қайтарады, әрі мұның қаупі неде?

Жаттығу

Тапсырмаға дейінгі үш қысқа қадам: болжау, толықтыру, жөндеу. Жауаптары — сабақтың соңында, бірақ алдымен өзіңіз жауап беріңіз.

1. Болжаңыз. Бұл бағдарлама не басып шығарады?

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.executemany("INSERT INTO t VALUES (?, ?)",
                 [("USD", 510.0), ("USD", 512.0), ("EUR", 595.0)])
for row in conn.execute("SELECT code, count(*), avg(value) FROM t GROUP BY code ORDER BY code"):
    print(row)

2. Бос орынды толтырыңыз. ... орнына жолдарды емес, топтарды сүзетін сөзді қойыңыз.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.executemany("INSERT INTO t VALUES (?, ?)",
                 [("USD", 510.0), ("EUR", 595.0), ("UZS", 4.2)])
for row in conn.execute("""
    SELECT code, avg(value) AS mid
    FROM t
    GROUP BY code
    ... avg(value) > 100
    ORDER BY code
"""):
    print(row)

3. Жөндеңіз. Кестеде мұндай валюта жоқ, әрі бағдарлама пішімдеуде құлайды. Ол бұл туралы сөзбен айтатындай етіңіз.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.execute("INSERT INTO t VALUES (?, ?)", ("USD", 510.0))
mid = conn.execute("SELECT avg(value) FROM t WHERE code = ?", ("GBP",)).fetchone()[0]
print(f"орташа: {mid:.2f}")

Тапсырма

Міндетті. Есепті сұраныстармен жинаңыз. Берілгені:

rates = [
    ("2026-01-15", "USD", 510.43), ("2026-01-15", "UZS", 4.24),
    ("2026-01-16", "USD", 511.02), ("2026-01-16", "UZS", 4.21),
    ("2026-02-02", "USD", 515.70), ("2026-02-02", "UZS", 4.19),
]
currencies = [("USD", "АҚШ доллары", 1), ("UZS", "өзбек сомы", 100)]

Екі кесте жасап, мыналарды басып шығарыңыз: әр валюта бойынша — күн саны, ең азы, ең көбі және орташасы (жүздікке дейін дөңгелектелген); 16 қаңтарға — анықтамалықтағы валютаның атауы және бір бірлікке бағам (value / quant, төрт таңба), кему ретімен; доллардың айлар бойынша орташа бағамы; ал соңғы жолмен — кестеде жоқ фунттың орташа бағамы. Шартты бірде-бір цикл болмасын: дерекқор санайды.

Күтілетін шығу:

== валюталар бойынша
USD: 3 күн, 510.43-тан 515.7-ке дейін, орташа 512.38
UZS: 3 күн, 4.19-тан 4.24-ке дейін, орташа 4.21
== бір күнде, атауларымен
АҚШ доллары: бір бірлігі 511.02
өзбек сомы: бір бірлігі 0.0421
== доллар айлар бойынша
2026-01: 510.73
2026-02: 515.7
фунт: дерек жоқ

Дайын болғаны: шығу жол-жолмен сәйкес келеді; Python-да санайтын бірде-бір цикл жоқ — тек жауап жолдарын басып шығаратын цикл; атаулар екінші кестеден JOIN арқылы келген; ай қасына жазылмай, күннен алынған; жоқ валюта бағдарламаны құлатпаған.

Өз деректеріңізде. Өткен сабақтағы өз кестеңізді алып, оған сұраныстармен үш сұрақ қойыңыз: әр кілт бойынша неше жазба, айлар бойынша орташа және ең үлкен күн. Олардың әрқайсысы циклмен неге айналарын дауыстап айтыңыз.

Қалауыңызша.

• JOIN-ды LEFT JOIN-ға ауыстырып, анықтамалықта жоқ валютаның бағамын қосыңыз.
• ORDER BY-сыз GROUP BY-ды бірнеше рет орындап көріңіз де, ретке сүйенуге бола ма, соны шешіңіз.
• avg-ті COALESCE(avg(value), 0)-ге орап, олай істеуге қашан болмайтынын түсіндіріңіз.

Жобада бұл қайда тұрады

Шолу деректі әрі-бері тасуын қояды. «Нешеу», «орташа», «айлар бойынша» деген сұрақтар дерекқорға кетеді, ал Python дайын жолдарды алып, есепті басып шығарады. Бұл келесі қадамды да дайындайды: шолу кесте бойынша жұмыс істей бастағанда, оған бір жылдық тарихты жадта ұстаудың қажеті болмайды.

Қарыздар. Біз индекстерді қозғаған жоқпыз — тоғыз жолда олар керек емес, ал миллионда бәрін шешеді. Әрі JOIN-ымыз біреу әрі қарапайым; кесте үшеу болғанда, ал сәйкестік болмағанда не болатыны — бөлек әңгіме.

Жауаптар

('EUR', 1, 595.0)
('USD', 2, 511.0)

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.executemany("INSERT INTO t VALUES (?, ?)",
                 [("USD", 510.0), ("EUR", 595.0), ("UZS", 4.2)])
for row in conn.execute("""
    SELECT code, avg(value) AS mid
    FROM t
    GROUP BY code
    HAVING avg(value) > 100
    ORDER BY code
"""):
    print(row)

('EUR', 595.0)
('USD', 510.0)

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.execute("INSERT INTO t VALUES (?, ?)", ("USD", 510.0))
mid = conn.execute("SELECT avg(value) FROM t WHERE code = ?", ("GBP",)).fetchone()[0]
if mid is None:
    print("орташа: дерек жоқ")
else:
    print(f"орташа: {mid:.2f}")

орташа: дерек жоқ

Дереккөздер

• SQLite: агрегат функциялары
• SQLite: SELECT, GROUP BY және HAVING
• SQLite: кестелерді біріктіру
• Python: sqlite3.Row және атау бойынша қатынау