group_by() в SQLAlchemy ORM: группировка результатов

• 30.09.2023
• 310
• Библиотеки Python
• SQLAlchemy

Когда вы работаете с большим объемом данных, иногда необходимо сгруппировать эти данные по определенному признаку и произвести некоторые агрегированные операции над ними. Запросы с группировкой данных часто используются для создания отчетов или для получения статистической информации. Именно для таких случаев в SQLAlchemy предоставляется метод group_by().

Основы group_by()

В SQLAlchemy метод group_by() используется для группировки строк результата запроса. В простейшем виде, вы можете просто указать одно или несколько полей, по которым вы хотите сгруппировать данные:

from sqlalchemy import create_engine, Column, Integer, String, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

engine = create_engine('sqlite:///mydatabase.db')
Session = sessionmaker(bind=engine)
session = Session()

# Группировка пользователей по возрасту и подсчет количества пользователей каждого возраста
for age, count in session.query(User.age, func.count(User.id)).group_by(User.age):
    print(f"Age: {age}, Count: {count}")

Использование group_by() с агрегирующими функциями

Один из наиболее частых сценариев использования group_by() — это комбинация его с агрегирующими функциями, такими как func.sum(), func.avg(), func.min(), func.max() и т. д.:

# Подсчет среднего возраста пользователей для каждого имени
for name, avg_age in session.query(User.name, func.avg(User.age)).group_by(User.name):
    print(f"Name: {name}, Average Age: {avg_age}")

Множественная группировка

Иногда может потребоваться группировка по нескольким полям. SQLAlchemy ORM позволяет это делать, передавая несколько атрибутов в group_by():

# Группировка по имени и возрасту
for name, age, count in session.query(User.name, User.age, func.count(User.id)).group_by(User.name, User.age):
    print(f"Name: {name}, Age: {age}, Count: {count}")

Комбинирование group_by() с order_by()

Часто после группировки данных нужно их отсортировать. Это можно сделать, комбинируя group_by() и order_by():

# Группировка по имени и сортировка по количеству в порядке убывания
for name, count in session.query(User.name, func.count(User.id)).group_by(User.name).order_by(func.count(User.id).desc()):
    print(f"Name: {name}, Count: {count}")

group_by() и связанные таблицы

Если у вас есть модели, связанные отношениями (например, один-ко-многим или многие-ко-многим), group_by() также может использоваться для группировки данных из связанных таблиц:

from sqlalchemy.orm import relationship

class Order(Base):
    __tablename__ = 'orders'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    total = Column(Integer)
    
    user = relationship('User', back_populates='orders')

User.orders = relationship('Order', back_populates='user')

# Группировка пользователей по общей сумме их заказов
for user, total_sum in session.query(User, func.sum(Order.total)).join(Order).group_by(User.id):
    print(f"User: {user.name}, Total Sum: {total_sum}")

Заключение

Метод group_by() в SQLAlchemy ORM предоставляет мощные возможности для группировки данных, которые, в сочетании с различными агрегирующими функциями, позволяют проводить глубокий анализ данных в вашей базе данных. Правильное применение этого метода может помочь оптимизировать запросы и извлечь максимальное количество полезной информации из ваших данных.