Використання агрегувань та підзапитів у SQLAlchemy

Агрегація — це процес обробки даних для отримання зведеної інформації. Її можна порівняти з підведенням підсумків на зборі: ти збираєш багато даних (наприклад, зарплати співробітників) і зводиш їх в одне значення (наприклад, середню зарплату).

У SQL для цього використовуються агрегатні функції:

• SUM — сума значень.
• AVG — середнє значення.
• COUNT — кількість записів.
• MIN і MAX — мінімальне і максимальне значення.

Приклад SQL-запиту з використанням агрегатних функцій:

SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Цей запит покаже тобі, скільки працівників працює в кожному відділі і яка їхня середня зарплата.

Реалізація агрегацій у SQLAlchemy

SQLAlchemy надає зручний інтерфейс для виконання агрегацій. Для цього використовуються методи і функції модуля sqlalchemy.sql.functions.

Почнемо з простого, порахуємо кількість користувачів у таблиці User:

from sqlalchemy import func

# Припустимо, ви вже створили сесію SQLAlchemy
# і таблицю User з полями id, name та age.

user_count = session.query(func.count(User.id)).scalar()
print(f"Кількість користувачів: {user_count}")

Функція func дозволяє викликати SQL-функції, такі як COUNT, SUM, AVG, та інші.

Тепер дізнаємось, який середній вік наших користувачів:

average_age = session.query(func.avg(User.age)).scalar()
print(f"Середній вік користувачів: {average_age}")

Зверни увагу на метод .scalar(), який повертає перше значення результату.

Використаємо метод group_by, щоб згрупувати користувачів за іменем і отримати кількість записів для кожного імені:

results = session.query(User.name, func.count(User.id).label('count')) \
    .group_by(User.name).all()

for name, count in results:
    print(f"Ім'я: {name}, Кількість: {count}")

Метод label дозволяє зручно називати результат функції, щоб простіше було працювати з ним надалі.

---

Робота з підзапитами

Підзапити (або субзапити) — це запити, які використовуються всередині інших запитів. Вони дозволяють витягувати дані, що залежать від результатів іншого запиту.

Приклад підзапиту на звичайному SQL:

SELECT name, age
FROM employees
WHERE age > (SELECT AVG(age) FROM employees);

Цей запит вибирає всіх працівників, вік яких перевищує середній вік працівників.

SQLAlchemy дозволяє реалізовувати підзапити через об'єкт subquery.

Створимо підзапит для обчислення середнього віку і використаємо його для фільтрації:

from sqlalchemy.orm import aliased

# Підзапит для обчислення середнього віку
subq = session.query(func.avg(User.age).label('avg_age')).subquery()

# Основний запит використовує підзапит
results = session.query(User).filter(User.age > subq.c.avg_age).all()

for user in results:
    print(f"Ім'я: {user.name}, Вік: {user.age}")

Тут:

• subquery() робить підзапит доступним для використання в основному запиті.
• subquery().c.<ім'я> дозволяє звертатися до результату як до колонки.

Аліаси дозволяють перейменовувати таблиці або запити для спрощення роботи з підзапитами. Це корисно, коли в запиті беруть участь кілька таблиць або один і той самий запит використовується двічі.

user_alias = aliased(User)

results = session.query(User.name, func.count(user_alias.id)) \
    .join(user_alias, User.id == user_alias.id) \
    .group_by(User.name).all()

---

Складні агрегації та підзапити в реальному прикладі

Створимо невелику базу даних і реалізуємо запити різної складності.

from sqlalchemy import Column, Integer, String, ForeignKey, func
from sqlalchemy.orm import relationship, declarative_base, sessionmaker
from sqlalchemy import create_engine

Base = declarative_base()

# Визначимо моделі
class Department(Base):
    __tablename__ = 'departments'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    salary = Column(Integer)
    department_id = Column(Integer, ForeignKey('departments.id'))

    department = relationship('Department')

# Створення бази даних SQLite
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Додавання даних
dept1 = Department(name='IT')
dept2 = Department(name='HR')
session.add_all([dept1, dept2])
session.commit()

employees = [
    Employee(name='Alice', age=30, salary=80000, department=dept1),
    Employee(name='Bob', age=25, salary=50000, department=dept1),
    Employee(name='Charlie', age=35, salary=60000, department=dept2),
]
session.add_all(employees)
session.commit()

Завдання 1: середня зарплата у відділах

Використаємо group_by для підрахунку середньої зарплати в кожному відділі:

results = session.query(Department.name, func.avg(Employee.salary).label('avg_salary')) \
    .join(Employee, Department.id == Employee.department_id) \
    .group_by(Department.name).all()

for dept, avg_salary in results:
    print(f"Відділ: {dept}, Середня зарплата: {avg_salary}")

Завдання 2: співробітники з зарплатою вищою за середню по їхньому відділу

Створимо підзапит для обчислення середньої зарплати і використаємо його:

subq = session.query(Employee.department_id, func.avg(Employee.salary).label('avg_salary')) \
    .group_by(Employee.department_id).subquery()

results = session.query(Employee.name, Employee.salary, Department.name) \
    .join(Department, Employee.department_id == Department.id) \
    .join(subq, Employee.department_id == subq.c.department_id) \
    .filter(Employee.salary > subq.c.avg_salary).all()

for name, salary, dept_name in results:
    print(f"Співробітник: {name}, Зарплата: {salary}, Відділ: {dept_name}")

---

Поради щодо оптимізації агрегувань та підзапитів

1. Уникай зайвих підзапитів. Деякі підзапити можна перетворити на JOIN для підвищення продуктивності.
2. Використовуй індекси. Додавання індексів на колонки, які часто беруть участь в агрегаціях або фільтрах, значно прискорить запити.
3. Старайся групувати дані на рівні бази. Виконуй агрегації ближче до бази даних, щоб мінімізувати обсяг витягнутих даних.

from sqlalchemy import Index

Index('idx_salary', Employee.salary)

4. Профілюй запити. SQLAlchemy дає інструменти для аналізу часу виконання запитів і їх оптимізації.

import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

INFO sqlalchemy.engine.Engine SELECT ...

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