Функция over

Функция over создает оконную функцию в SQLAlchemy. Оконные функции позволяют выполнять вычисления по набору строк, связанных с текущей строкой, не сворачивая результат в одну строку, в отличие от обычных агрегатных функций. Первым параметром передается агрегатная или оконная функция, например func.sum или func.row_number. Именованные параметры partition_by, order_by и rows задают способ разбиения и сортировки строк.

Синтаксис

over(func, partition_by=None, order_by=None, rows=None)

Пример

Давайте создадим таблицу articles и вычислим накопительную сумму по столбцу num:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy import func, select, over

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()

articles = Table(
	'articles', metadata,
	Column('id', Integer, primary_key=True),
	Column('title', String),
	Column('num', Integer),
)
metadata.create_all(engine)

with engine.begin() as conn:
	conn.execute(articles.insert(), [
		{'id': 1, 'title': 'article 1', 'num': 10},
		{'id': 2, 'title': 'article 2', 'num': 20},
		{'id': 3, 'title': 'article 3', 'num': 30},
	])

stmt = select(
	articles.c.title,
	articles.c.num,
	over(func.sum(articles.c.num), order_by=articles.c.id).label('running_total'),
)

with engine.connect() as conn:
	res = conn.execute(stmt).all()
	for row in res:
		print(row)

Результат выполнения кода:

('article 1', 10, 10)
('article 2', 20, 30)
('article 3', 30, 60)

Итоговый SQL запрос:

SELECT articles.title, articles.num,
SUM(articles.num) OVER (ORDER BY articles.id) AS running_total
FROM articles

Пример

Давайте разобьем строки на группы параметром partition_by и посчитаем сумму num внутри каждой группы по столбцу status:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy import func, select, over

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()

articles = Table(
	'articles', metadata,
	Column('id', Integer, primary_key=True),
	Column('title', String),
	Column('status', String),
	Column('num', Integer),
)
metadata.create_all(engine)

with engine.begin() as conn:
	conn.execute(articles.insert(), [
		{'id': 1, 'title': 'article 1', 'status': 'published', 'num': 10},
		{'id': 2, 'title': 'article 2', 'status': 'published', 'num': 20},
		{'id': 3, 'title': 'article 3', 'status': 'draft', 'num': 30},
		{'id': 4, 'title': 'article 4', 'status': 'draft', 'num': 40},
	])

stmt = select(
	articles.c.title,
	articles.c.status,
	articles.c.num,
	over(
		func.sum(articles.c.num),
		partition_by=articles.c.status,
	).label('status_total'),
)

with engine.connect() as conn:
	res = conn.execute(stmt).all()
	for row in res:
		print(row)

Результат выполнения кода:

('article 1', 'published', 10, 30)
('article 2', 'published', 20, 30)
('article 3', 'draft', 30, 70)
('article 4', 'draft', 40, 70)

Итоговый SQL запрос:

SELECT articles.title, articles.status, articles.num,
SUM(articles.num) OVER (PARTITION BY articles.status) AS status_total
FROM articles

Пример

Давайте пронумеруем строки внутри каждой группы с помощью func.row_number и параметров partition_by и order_by:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy import func, select, over

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()

articles = Table(
	'articles', metadata,
	Column('id', Integer, primary_key=True),
	Column('title', String),
	Column('status', String),
	Column('num', Integer),
)
metadata.create_all(engine)

with engine.begin() as conn:
	conn.execute(articles.insert(), [
		{'id': 1, 'title': 'article 1', 'status': 'published', 'num': 10},
		{'id': 2, 'title': 'article 2', 'status': 'published', 'num': 20},
		{'id': 3, 'title': 'article 3', 'status': 'draft', 'num': 30},
		{'id': 4, 'title': 'article 4', 'status': 'draft', 'num': 40},
	])

stmt = select(
	articles.c.title,
	articles.c.status,
	over(
		func.row_number(),
		partition_by=articles.c.status,
		order_by=articles.c.num,
	).label('row_num'),
)

with engine.connect() as conn:
	res = conn.execute(stmt).all()
	for row in res:
		print(row)

Результат выполнения кода:

('article 1', 'published', 1)
('article 2', 'published', 2)
('article 3', 'draft', 1)
('article 4', 'draft', 2)

Итоговый SQL запрос:

SELECT articles.title, articles.status,
ROW_NUMBER() OVER (PARTITION BY articles.status ORDER BY articles.num) AS row_num
FROM articles

Смотрите также

функцию within_group,
которая задает сортировку внутри агрегатной функции
функцию func,
которая создает вызов произвольной SQL-функции
функцию func.sum,
которая считает сумму значений
функцию desc,
которая задает сортировку по убыванию