SQL в Data Science проектах

SQL есть в требованиях любой вакансии по запросу “Data Science”. На курсах по SQL в основном учат писать сложные запросы, чтобы доставать нужные данные или считать статистики. Но непонятно, как применять эти навыки в портфолио, где каждый проект начинается с готовых файлов train.csv и test.csv.

Понять задачу

На курсах и соревнованиях по машинному обучению нам дают четкие задачи и все необходимые данные для их решения. Реальные проекты обычно начинаются с расплывчатой идеи. Мы должны сами сформулировать задачу и подготовить данные. Все данные хранятся в базах данных, там много лишнего и часто нет того, что нам нужно.

Разберем пример: сеть супермаркетов хочет оптимизировать планирование закупок. Сейчас им занимаются вручную: план с прошлого года переносят на следующий и вносят небольшие изменения. Если магазин в центре Тулы продал 200 килограммов мандаринов в декабре 24-го года, менеджер магазина скорее всего закажет 200 или 220 килограммов к декабрю 25-го.

Менеджеры часто ошибаются, а их ошибки дорого стоят. Лишний товар придется утилизировать, а если товара не хватит, покупатели уйдут за ним к конкуренту. Директор считает, что машинное обучение позволит снизить ошибки при планировании. Другой информации о задаче у нас нет: нужно изучить данные и понять, что мы можем сделать.

Доступ к полной бухгалтерии магазинов закрыт. Мы можем обращаться только к одной базе данных: в нее ежедневно загружается часть данных о продажах. Отработаем все примеры на SQLite. Если хотите повторять все действия вместе со мной, скачайте и распакуйте файл ↓ data.db.zip.

Откроем терминал и перейдем в папку с файлом data.db. Для подключения к базе выполним команду:

sqlite3 data.db

SQLite устанавливается вместе с любой версией Python, поэтому команда должна выполниться без проблем.

Сейчас мы внутри SQL терминала. Здесь можно выполнять SQL запросы и встроенные команды SQLite. Выведем на экран доступные таблицы с помощью команды .tables:

.tables

sales   stores

Видим две таблицы: sales с данными продаж и stores с данными магазинов.

Посмотрим на данные в каждой таблице. Выведем первые 5 строк таблицы sales с помощью запроса SELECT * FROM sales LIMIT 5:

SELECT * FROM sales LIMIT 5;

┌─────────┬──────────┬────────────┬───────┬─────────────┬────────────┬──────────┬────────┐
│ sale_id │ store_id │    date    │ month │ day_of_week │ is_holiday │ is_promo │ amount │
├─────────┼──────────┼────────────┼───────┼─────────────┼────────────┼──────────┼────────┤
│ 0       │ 1        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 315780 │
│ 1       │ 2        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 363840 │
│ 2       │ 3        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 498840 │
│ 3       │ 4        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 839700 │
│ 4       │ 5        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 289320 │
└─────────┴──────────┴────────────┴───────┴─────────────┴────────────┴──────────┴────────┘

Здесь * означает “все колонки”.

В этой таблице содержится полная выручка каждого магазина за каждый день. Видим ID строки, ID магазина и дату продажи. Для удобства из даты выделены месяц и день недели: 1 означает понедельник, а 7 — воскресенье. Если в этот день был государственный праздник, флаг is_holiday равен 1. Флаг is_promo означает, что в этот день в магазине проводили акцию. Последняя колонка — полная выручка магазина в этот день в рублях.

Посмотрим на данные в таблице stores:

SELECT * FROM stores LIMIT 5;

┌──────────┬─────────────┬─────────────┬──────────────────────┐
│ store_id │ store_type  │ assortment  │ competition_distance │
├──────────┼─────────────┼─────────────┼──────────────────────┤
│ 1        │ супермаркет │ расширенный │ 1270                 │
│ 2        │ стандарт    │ расширенный │ 570                  │
│ 3        │ стандарт    │ расширенный │ 14130                │
│ 4        │ супермаркет │ базовый     │ 620                  │
│ 5        │ стандарт    │ расширенный │ 29910                │
└──────────┴─────────────┴─────────────┴──────────────────────┘

Здесь хранятся параметры магазина: тип магазина, тип ассортимента и расстояние до ближайшего магазина-конкурента в метрах.

Посчитаем количество строк в каждой таблице. Для этого используем функцию COUNT:

SELECT COUNT(*) FROM sales;

┌──────────┐
│ COUNT(*) │
├──────────┤
│ 808454   │
└──────────┘

В таблице sales 808 тысяч записей о продажах.

Используем тот же запрос для таблицы stores:

SELECT COUNT(*) FROM stores;

┌──────────┐
│ COUNT(*) │
├──────────┤
│ 1112     │
└──────────┘

Здесь данные 1112 магазинов.

Проверим диапазон времени в таблице sales — для этого используем функции MIN и MAX:

SELECT MIN(date), MAX(date) FROM sales;

┌────────────┬────────────┐
│ MIN(date)  │ MAX(date)  │
├────────────┼────────────┤
│ 2023-01-01 │ 2025-07-31 │
└────────────┴────────────┘

Данные продаж есть с 1 января 23-го года по 31 июля 25-го.

Выполним еще несколько запросов для базовой аналитики. Посчитаем минимальную, среднюю и максимальную дневную выручку по всем магазинам. Для этого используем функции MIN, AVG и MAX:

SELECT MIN(amount), AVG(amount), MAX(amount) FROM sales;

┌─────────────┬──────────────────┬─────────────┐
│ MIN(amount) │   AVG(amount)    │ MAX(amount) │
├─────────────┼──────────────────┼─────────────┤
│ 153420      │ 409014.884755348 │ 930480      │
└─────────────┴──────────────────┴─────────────┘

Выручка находится в диапазоне от 150 до 930 тысяч рублей в день, 410 тысяч в среднем.

Посмотрим на среднюю выручку по дням недели. Выберем колонку day_of_week и среднюю выручку. Используем предложение GROUP BY с колонкой day_of_week для группировки по дням недели:

SELECT day_of_week, AVG(amount) FROM sales GROUP BY day_of_week;

┌─────────────┬──────────────────┐
│ day_of_week │   AVG(amount)    │
├─────────────┼──────────────────┤
│ 1           │ 414162.389284508 │
│ 2           │ 396384.768643111 │
│ 3           │ 398410.75425161  │
│ 4           │ 415123.541302665 │
│ 5           │ 362229.093652136 │
│ 6           │ 468445.066562255 │
│ 7           │ 467223.357873807 │
└─────────────┴──────────────────┘

Ожидаемо пик продаж приходится на субботу и воскресенье.

Более глубокая аналитика сейчас не нужна — мы узнали достаточно для того, чтобы сформулировать задачу. У нас нет данных о закупках и выручке по категориям товаров. Лучшее, что мы можем сделать в этой ситуации — обучить модель предсказывать полную выручку за сутки для любого магазина в конкретный день.

При планировании менеджеры будут использовать прогноз общей выручки и умножать его на среднюю долю продаж каждой категории товаров. Если модель предсказывает выручку 500 тысяч рублей в следующее воскресенье, а овощи в среднем составляют 15% общих продаж, значит нужно будет закупить овощей на 75 тысяч рублей.

Собрать данные

До сих пор у нас всегда были под рукой готовые файлы train.csv и test.csv. При работе с базой мы готовим их сами. Для этого нужно выбрать нужные строки и колонки и разобраться, как выгрузить их из базы.

Посмотрим на таблицу sales еще раз:

SELECT * FROM sales LIMIT 5;

┌─────────┬──────────┬────────────┬───────┬─────────────┬────────────┬──────────┬────────┐
│ sale_id │ store_id │    date    │ month │ day_of_week │ is_holiday │ is_promo │ amount │
├─────────┼──────────┼────────────┼───────┼─────────────┼────────────┼──────────┼────────┤
│ 0       │ 1        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 315780 │
│ 1       │ 2        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 363840 │
│ 2       │ 3        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 498840 │
│ 3       │ 4        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 839700 │
│ 4       │ 5        │ 2025-07-31 │ 7     │ 4           │ 0          │ 1        │ 289320 │
└─────────┴──────────┴────────────┴───────┴─────────────┴────────────┴──────────┴────────┘

В ней достаточно данных для обучения модели. Возьмем месяц, день недели, информацию о праздниках, акциях и выручке:

SELECT
    month,
    day_of_week,
    is_promo,
    is_holiday,
    amount
FROM sales
LIMIT 5;

┌───────┬─────────────┬──────────┬────────────┬────────┐
│ month │ day_of_week │ is_promo │ is_holiday │ amount │
├───────┼─────────────┼──────────┼────────────┼────────┤
│ 7     │ 4           │ 1        │ 0          │ 315780 │
│ 7     │ 4           │ 1        │ 0          │ 363840 │
│ 7     │ 4           │ 1        │ 0          │ 498840 │
│ 7     │ 4           │ 1        │ 0          │ 839700 │
│ 7     │ 4           │ 1        │ 0          │ 289320 │
└───────┴─────────────┴──────────┴────────────┴────────┘

Если убрать ограничение на количество строк, этот запрос выгрузит все данные из таблицы. Сохраним их в файл с помощью Python. Перейдем в редактор кода и установим зависимости: Pandas для работы с данными, Scikit-learn и CatBoost для работы с ML моделями:

uv add pandas scikit-learn catboost

Создадим модуль load.py для загрузки данных. Импортируем встроенную библиотеку sqlite3 и pandas. Перепишем SQL запрос и добавим фильтр по дате. Мы будем обучать модель на более старых данных, а проверять — на новых. Заберем все данные до 1 января 25 года для обучения:

import sqlite3
import pandas as pd

query = """
SELECT
    month,
    day_of_week,
    is_promo,
    is_holiday,
    amount
FROM sales
WHERE date < '2025-01-01';
"""

Подключимся к базе данных с помощью контекстного менеджера sqlite3.connect и передадим ему путь к файлу. Выполним запрос с помощью функции read_sql. Выведем на экран первые 3 строки таблицы и ее размерность для проверки. Запишем результат в файл train.csv:

import sqlite3
import pandas as pd

query = """
SELECT
    month,
    day_of_week,
    is_promo,
    is_holiday,
    amount
FROM sales
WHERE date < '2025-01-01';
"""

with sqlite3.connect("data.db") as connection:
    data = pd.read_sql(query, connection)

print(data.head())
print(data.shape)
data.to_csv("train.csv", index=False)

   month  day_of_week  is_promo  is_holiday  amount
0     12            2         0           0  156300
1     12            2         0           0  228240
2     12            2         0           0  609120
3     12            2         0           0  156240
4     12            2         0           0  313140
(619945, 5)

Получаем выборку обучения из 619 тысяч строк.

Теперь сохраним тестовую выборку — для нее возьмем данные с 1 января 25 года. Меняем знак < на >= и имя файла на test.csv:

import sqlite3
import pandas as pd

query = """
SELECT
    month,
    day_of_week,
    is_promo,
    is_holiday,
    amount
FROM sales
WHERE date >= '2025-01-01';
"""

with sqlite3.connect("data.db") as connection:
    data = pd.read_sql(query, connection)

print(data.head())
print(data.shape)
data.to_csv("test.csv", index=False)

   month  day_of_week  is_promo  is_holiday  amount
0      7            4         1           0  315780
1      7            4         1           0  363840
2      7            4         1           0  498840
3      7            4         1           0  839700
4      7            4         1           0  289320
(188509, 5)

В тестовой выборке будет 188 тысяч строк.

Перейдем к обучению. Создадим модуль train.py, импортируем pandas и прочитаем таблицы train.csv и test.csv. Уберем из таблицы колонку с выручкой — ее будет предсказывать модель. Повторим все действия для выборки обучения и тестовой выборки:

import pandas as pd

train_data = pd.read_csv("train.csv")
test_data = pd.read_csv("test.csv")

X_train = train_data.drop(columns=["amount"])
y_train = train_data["amount"]
X_test = test_data.drop(columns=["amount"])
y_test = test_data["amount"]

Импортируем модель CatBoostRegressor из библиотеки catboost. В качестве метрики используем средний процент отклонения из пакета sklearn.metrics. Инициализируем модель, вызовем метод fit и передадим ему данные выборки обучения. После обучения вычислим средний процент отклонения модели на тестовой выборке и выведем его значение на экран:

import pandas as pd
from catboost import CatBoostRegressor
from sklearn.metrics import mean_absolute_percentage_error

train_data = pd.read_csv("train.csv")
test_data = pd.read_csv("test.csv")

X_train = train_data.drop(columns=["amount"])
y_train = train_data["amount"]
X_test = test_data.drop(columns=["amount"])
y_test = test_data["amount"]

model = CatBoostRegressor()
model.fit(X_train, y_train)

score = mean_absolute_percentage_error(y_test, model.predict(X_test))
print(f"Ошибка на тестовой выборке: {100 * score:.1f}%")

Ошибка на тестовой выборке: 27.0%

Получаем 27%. Это значит, что если реальная выручка магазина за сутки составила 600 тысяч рублей, модель в среднем может предсказать выручку от 400 до 800 тысяч.

Объединить источники

Мы можем улучшить результат если используем все доступные данные. Сейчас модель знает только общие закономерности — как на продажи влияет сезон, день недели, акции и праздники. Но модель не учитывает данные магазинов. Она предсказывает что-то среднее между выручкой супермаркета в центре города и маленького магазина в спальном районе.

Для создания новой выборки обучения нужно объединить таблицы. Посмотрим на обе таблицы: каждая строка в таблице sales ссылается на идентификатор магазина store_id, а данные магазина с этим значением store_id находятся в таблице stores. То есть для каждой строки в таблице sales нужно подобрать строку в таблице stores с таким же значением store_id. Так работает операция объединения — предложение JOIN.

Для примера возьмем колонки date и amount из таблицы sales и колонку store_type из таблицы stores. Для объединения с таблицей stores используем JOIN по колонке store_id:

SELECT
    date,
    amount,
    store_type
FROM sales
JOIN stores USING(store_id)
LIMIT 5;

┌────────────┬────────┬─────────────┐
│    date    │ amount │ store_type  │
├────────────┼────────┼─────────────┤
│ 2025-07-31 │ 315780 │ супермаркет │
│ 2025-07-31 │ 363840 │ стандарт    │
│ 2025-07-31 │ 498840 │ стандарт    │
│ 2025-07-31 │ 839700 │ супермаркет │
│ 2025-07-31 │ 289320 │ стандарт    │
└────────────┴────────┴─────────────┘

Результатом будут данные продаж, дополненные данными магазинов.

После объединения можно получить более интересную статистику. Посмотрим на распределение выручки по типу магазина. Для этого выберем тип магазина и среднюю выручку из объединенной таблицы и сгруппируем результат по типу магазина:

SELECT
    store_type,
    AVG(amount)
FROM sales
JOIN stores USING(store_id)
GROUP BY store_type;

┌─────────────┬──────────────────┐
│ store_type  │   AVG(amount)    │
├─────────────┼──────────────────┤
│ мини        │ 406426.804514395 │
│ стандарт    │ 407167.354313378 │
│ супермаркет │ 410051.590890435 │
│ универсам   │ 512573.170656371 │
└─────────────┴──────────────────┘

Тут выделяются универсамы — их выручка на четверть больше, чем у других магазинов.

Можно посмотреть выручку в разрезе ассортимента, для этого меняем колонку store_type на колонку assortment:

SELECT
    assortment,
    AVG(amount)
FROM sales
JOIN stores USING(store_id)
GROUP BY assortment;

┌─────────────┬──────────────────┐
│ assortment  │   AVG(amount)    │
├─────────────┼──────────────────┤
│ базовый     │ 424229.478572588 │
│ расширенный │ 394016.517298669 │
│ широкий     │ 493714.423945297 │
└─────────────┴──────────────────┘

Товары первой необходимости из базового ассортимента приносят больше выручки, чем товары из расширенного ассортимента, но меньше, чем более дорогие товары из широкого ассортимента.

Вернемся к обучению. Обновим файлы train.csv и test.csv. Добавим к ним колонки из таблицы stores: тип магазина, ассортимент и расстояние до ближайшего магазина-конкурента. Чтобы получить доступ к этим колонкам добавим JOIN по колонке store_id:

import sqlite3
import pandas as pd

query = """
SELECT
    month,
    day_of_week,
    is_promo,
    is_holiday,
    amount,
    store_type,
    assortment,
    competition_distance
FROM sales
JOIN stores USING(store_id)
WHERE date < '2025-01-01';
"""

with sqlite3.connect("data.db") as connection:
    data = pd.read_sql(query, connection)

print(data.head())
print(data.shape)
data.to_csv("train.csv", index=False)

   month  day_of_week  is_promo  is_holiday  amount   store_type   assortment  competition_distance
0     12            2         0           0  156300  супермаркет  расширенный                  1270
1     12            2         0           0  228240     стандарт  расширенный                 14130
2     12            2         0           0  609120  супермаркет      базовый                   620
3     12            2         0           0  156240     стандарт  расширенный                   310
4     12            2         0           0  313140     стандарт      базовый                 24000
(619945, 8)

Поменяем знак в условии и сохраним данные тестовой выборки:

import sqlite3
import pandas as pd

query = """
SELECT
    month,
    day_of_week,
    is_promo,
    is_holiday,
    amount,
    store_type,
    assortment,
    competition_distance
FROM sales
JOIN stores USING(store_id)
WHERE date >= '2025-01-01';
"""

with sqlite3.connect("data.db") as connection:
    data = pd.read_sql(query, connection)

print(data.head())
print(data.shape)
data.to_csv("test.csv", index=False)

   month  day_of_week  is_promo  is_holiday  amount   store_type   assortment  competition_distance
0      7            4         1           0  315780  супермаркет  расширенный                  1270
1      7            4         1           0  363840     стандарт  расширенный                   570
2      7            4         1           0  498840     стандарт  расширенный                 14130
3      7            4         1           0  839700  супермаркет      базовый                   620
4      7            4         1           0  289320     стандарт  расширенный                 29910
(188509, 8)

Обучим модель на новых данных. Теперь в них есть два категориальных признака: store_type и assortment. Передадим их конструктору CatBoostRegressor и перезапустим обучение:

import pandas as pd
from catboost import CatBoostRegressor
from sklearn.metrics import mean_absolute_percentage_error

train_data = pd.read_csv("train.csv")
test_data = pd.read_csv("test.csv")

X_train = train_data.drop(columns=["amount"])
y_train = train_data["amount"]
X_test = test_data.drop(columns=["amount"])
y_test = test_data["amount"]

model = CatBoostRegressor(cat_features=["store_type", "assortment"])
model.fit(X_train, y_train)

score = mean_absolute_percentage_error(y_test, model.predict(X_test))
print(f"Ошибка на тестовой выборке: {100 * score:.1f}%")

Ошибка на тестовой выборке: 18.4%

Ошибка снизилась на треть — с 27% до 18%.

Если этот результат всех устроит, модель запустят в нескольких тестовых магазинах. Менеджеры этих магазинов будут корректировать закупки исходя из прогнозов модели. Если модель покажет себя хорошо, к ней подключат остальные магазины, а нас попросят снижать ошибки и развивать проект.

Облачные базы

SQLite обычно используют для хранения данных на одном устройстве. Мобильная игра может сохранять в SQLite состояние мира, ваш инвентарь и настройки. Браузеры используют SQLite для хранения закладок и истории. Но если данные читает и обновляет сразу несколько человек, база должна находиться в облаке и выдерживать большую нагрузку.

Для работы с облачной базой данных нужно установить несколько библиотек и обновить код подключения к базе. Для примера посмотрим на работу с Postgres. Я запустил Postgres в облаке и загрузил в нее данные продаж. Чтобы к ней подключиться установим две библиотеки: sqlalchemy и psycopg2:

uv add sqlalchemy 'psycopg2-binary'

SQLAlchemy — это универсальная библиотека для работы с SQL, а psycopg2 — драйвер для подключения к Postgres.

Вместо sqlite3 импортируем из sqlalchemy функцию create_engine и вызовем ее, чтобы подключиться к базе данных. На работе вам дадут строку для подключения к базе: в ней будет адрес базы, ваш логин и пароль. Свою строку я скопирую из панели управления. Больше ничего менять не нужно — при перезапуске кода мы получим тот же результат, но в этот раз данные загрузятся из облака:

import pandas as pd
from sqlalchemy import create_engine

query = """
SELECT
    month,
    day_of_week,
    is_promo,
    is_holiday,
    amount,
    store_type,
    assortment,
    competition_distance
FROM sales
JOIN stores USING(store_id)
WHERE date >= '2025-01-01';
"""

engine = create_engine("postgresql://ivanov:ivanov123@5.129.250.215:5432/supermarket")
data = pd.read_sql(query, engine)

print(data.head())
print(data.shape)
data.to_csv("test.csv", index=False)

   month  day_of_week  is_promo  is_holiday  amount   store_type   assortment  competition_distance
0      7            4         1           0  315780  супермаркет  расширенный                  1270
1      7            4         1           0  363840     стандарт  расширенный                   570
2      7            4         1           0  498840     стандарт  расширенный                 14130
3      7            4         1           0  839700  супермаркет      базовый                   620
4      7            4         1           0  289320     стандарт  расширенный                 29910
(188509, 8)

Для работы с другой базой данных нужно будет изменить только строку подключения. Например, мы можем переключиться обратно на SQLite. Для этого изменим строку подключения на sqlite:///data.db:

import pandas as pd
from sqlalchemy import create_engine

query = """
SELECT
    month,
    day_of_week,
    is_promo,
    is_holiday,
    amount,
    store_type,
    assortment,
    competition_distance
FROM sales
JOIN stores USING(store_id)
WHERE date >= '2025-01-01';
"""

engine = create_engine("sqlite:///data.db")
data = pd.read_sql(query, engine)

print(data.head())
print(data.shape)
data.to_csv("test.csv", index=False)

   month  day_of_week  is_promo  is_holiday  amount   store_type   assortment  competition_distance
0      7            4         1           0  315780  супермаркет  расширенный                  1270
1      7            4         1           0  363840     стандарт  расширенный                   570
2      7            4         1           0  498840     стандарт  расширенный                 14130
3      7            4         1           0  839700  супермаркет      базовый                   620
4      7            4         1           0  289320     стандарт  расширенный                 29910
(188509, 8)

Результат при этом не изменится.

На работе вы скорее всего увидите Postgres, ClickHouse, Microsoft SQL или Oracle. Между ними есть небольшие отличия: ClickHouse эффективнее всего работает с тяжелыми аналитическими запросами, а Postgres и остальные — с частыми запросами на добваление и обновление строк. Microsoft SQL и Oracle платные, Postgres и ClickHouse бесплатные.

Выбор и поддержка облачных баз данных — не наша ответственность, но мы должны уметь с ними работать. А для этого осталось только написать правильный запрос.