SQL из Python: `GROUP BY`, `JOIN` и пустой ответ, который не ноль

Зачем это нужно

В прошлом уроке история легла в таблицу, а SELECT был написан в простейшем виде: выбрать строки и пройти их циклом.

Но цикл здесь — привычка, а не необходимость. «Среднее по каждой валюте», «самый дорогой день», «что было в январе» — это не работа для Python: база отвечает на такие вопросы одной строкой запроса и делает это быстрее, потому что не выносит наружу то, что не нужно.

Разница видна на размере: цикл тянет в память все строки, чтобы выбросить девяносто процентов; запрос отдаёт готовый ответ.

Сразу целиком

Файл suranys.py. Запуск: python suranys.py из окружения.

Обязательное — первые три блока: агрегаты, GROUP BY и HAVING. Четвёртый показывает соединение двух таблиц, пятый — группу из части даты, шестой — подвох с пустым ответом.

"""Урок 22: SQL из Python — срез запросом, а не циклом.

В прошлом уроке появилась таблица и `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("== фильтр по группе — это HAVING, а не WHERE")
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

== фильтр по группе — это HAVING, а не WHERE
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 и добавьте курс валюты, которой нет в справочнике.
• Попробуйте GROUP BY без ORDER 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 и доступ по имени