Window functions в Django ORM | Курс Django ORM урок 3.6

Цель урока

Разобрать оконные функции в Django ORM: Window, Rank, RowNumber, Lead, Lag, накопительные суммы. Понять чем оконные функции отличаются от агрегатных и когда они незаменимы.

Необходимые знания

Урок 3.3: annotate(), aggregate()
Базовое понимание OVER() в SQL

Чем оконные функции отличаются от агрегатных

Агрегатные функции с GROUP BY сворачивают строки, вместо N строк получаете M групп:

-- GROUP BY category_id: 15 продуктов → 4 категории
SELECT category_id, AVG(price) FROM shop_product GROUP BY category_id;

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

-- OVER(): 15 продуктов остаются 15 строками, каждая с рангом в своей категории
SELECT name, price, category_id,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank
FROM shop_product;

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

Основные понятия

Window - обёртка Django ORM для SQL конструкции OVER(...). Принимает оконную функцию и описывает окно, по каким строкам считать (partition_by) и в каком порядке (order_by). Используется внутри annotate().

Rank - ранг строки внутри окна по заданному порядку. При одинаковых значениях строки получают одинаковый ранг, следующий ранг пропускается: 1, 2, 2, 4. DenseRank делает то же самое, но без пропусков: 1, 2, 2, 3.

RowNumber - порядковый номер строки внутри окна. Всегда уникален, одинаковые значения не влияют на нумерацию: 1, 2, 3, 4, 5.

Lag - значение поля из предыдущей строки окна. Используется чтобы сравнить текущую строку с предыдущей, например найти изменение суммы между соседними заказами.

Lead - значение поля из следующей строки окна. Зеркальная противоположность Lag.

Синтаксис Window

from django.db.models import Window, F
from django.db.models.functions import Rank

Product.objects.annotate(
    price_rank=Window(
        expression=Rank(),
        partition_by=[F("category_id")],  # PARTITION BY - группировка окна
        order_by=F("price").desc(),        # ORDER BY внутри окна
    )
)

SELECT shop_product.*,
       RANK() OVER (
           PARTITION BY category_id
           ORDER BY price DESC
       ) AS price_rank
FROM shop_product
ORDER BY shop_product.name;

Компоненты Window:

expression, оконная функция (Rank, RowNumber, Sum, Avg и др.)
partition_by, разбивка на окна, аналог GROUP BY, но без сворачивания
order_by, порядок строк внутри окна

Ранжирующие функции

Rank - ранг с пропусками

from django.db.models.functions import Rank

# Ранг продукта по цене внутри категории
# При одинаковых ценах - одинаковый ранг, следующий пропускается (1,2,2,4)
Product.objects.annotate(
    price_rank=Window(
        expression=Rank(),
        partition_by=[F("category_id")],
        order_by=F("price").desc(),
    )
)

RANK() OVER (PARTITION BY category_id ORDER BY price DESC)
-- результат: 1, 2, 2, 4, 5 (2 повторяется, 3 пропускается)

DenseRank - ранг без пропусков

from django.db.models.functions import DenseRank

Product.objects.annotate(
    dense_rank=Window(
        expression=DenseRank(),
        partition_by=[F("category_id")],
        order_by=F("price").desc(),
    )
)

DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC)
-- результат: 1, 2, 2, 3, 4 (нет пропусков)

RowNumber - порядковый номер строки

from django.db.models.functions import RowNumber

# Порядковый номер без учета одинаковых значений
Product.objects.annotate(
    row_num=Window(
        expression=RowNumber(),
        partition_by=[F("category_id")],
        order_by=F("price").desc(),
    )
)

ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC)
-- результат: 1, 2, 3, 4, 5 (всегда уникальный)

Практический пример: топ-3 продукта в каждой категории

from django.db.models import Window, F
from django.db.models.functions import Rank

# Аннотируем каждый продукт рангом внутри категории
ranked = Product.objects.filter(is_active=True).annotate(
    rank_in_category=Window(
        expression=Rank(),
        partition_by=[F("category_id")],
        order_by=F("price").desc(),
    )
)

# Фильтрация по рангу, нужен подзапрос или обработка в Python
# Django не позволяет filter() по window функции напрямую
top3 = [p for p in ranked if p.rank_in_category <= 3]

Ограничение: Django не поддерживает filter() по оконным функциям напрямую. PostgreSQL тоже запрещает WHERE по window функции, она вычисляется после WHERE. Решение, обернуть в подзапрос:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT id FROM (
            SELECT id, RANK() OVER (
                PARTITION BY category_id
                ORDER BY price DESC
            ) AS rank_in_category
            FROM shop_product
            WHERE is_active = true
        ) ranked
        WHERE rank_in_category <= 3
    """)
    top3_ids = [row[0] for row in cursor.fetchall()]

top3_products = Product.objects.filter(id__in=top3_ids)

-- PostgreSQL выполняет:
SELECT id FROM (
    SELECT id, RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank_in_category
    FROM shop_product
    WHERE is_active = true
) ranked
WHERE rank_in_category <= 3;

Для небольших наборов (до нескольких тысяч строк) проще фильтровать в Python:

ranked = Product.objects.filter(is_active=True).annotate(
    rank_in_category=Window(
        expression=Rank(),
        partition_by=[F("category_id")],
        order_by=F("price").desc(),
    )
)
# Все строки уже в памяти, фильтрация без доп. запроса
top3 = [p for p in ranked if p.rank_in_category <= 3]

Выбор подхода:

Маленькая выборка (< 5000 строк), фильтрация в Python
Большая выборка, raw SQL с подзапросом

Смещение: Lead и Lag

Lead и Lag дают доступ к значению следующей или предыдущей строки в окне.

Lag - предыдущая строка

from django.db.models.functions import Lag
from django.db.models import FloatField

# Для каждого заказа пользователя, сумма предыдущего заказа
Order.objects.annotate(
    prev_total=Window(
        expression=Lag("total_price", offset=1),
        partition_by=[F("user_id")],
        order_by=F("created_at").asc(),
    )
)

SELECT shop_order.*,
       LAG(total_price, 1) OVER (
           PARTITION BY user_id
           ORDER BY created_at ASC
       ) AS prev_total
FROM shop_order;

Для первой строки в окне (нет предыдущей), значение NULL. Можно задать дефолт:

Lag("total_price", offset=1, default=0)

Lead - следующая строка

from django.db.models.functions import Lead

# Следующий заказ пользователя
Order.objects.annotate(
    next_total=Window(
        expression=Lead("total_price", offset=1),
        partition_by=[F("user_id")],
        order_by=F("created_at").asc(),
    )
)

LEAD(total_price, 1) OVER (PARTITION BY user_id ORDER BY created_at ASC)

Агрегатные функции как оконные

Обычные агрегаты (Sum, Avg, Count) тоже работают как оконные функции:

Накопительная сумма (running total)

from django.db.models import Sum

# Накопительная сумма заказов пользователя по времени
Order.objects.annotate(
    running_total=Window(
        expression=Sum("total_price"),
        partition_by=[F("user_id")],
        order_by=F("created_at").asc(),
    )
)

SELECT shop_order.*,
       SUM(total_price) OVER (
           PARTITION BY user_id
           ORDER BY created_at ASC
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM shop_order;

Доля от общего

from django.db.models import Sum, FloatField
from django.db.models.functions import Cast

# Какой процент от общей выручки составляет каждый заказ пользователя
Order.objects.annotate(
    user_total=Window(
        expression=Sum("total_price"),
        partition_by=[F("user_id")],
    )
)
# Затем в Python: order.total_price / order.user_total * 100

Frame specification - управление рамкой окна

По умолчанию оконные функции с ORDER BY используют рамку от начала до текущей строки ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Без ORDER BY, вся партиция.

Django позволяет задать рамку явно:

from django.db.models.expressions import RowRange, ValueRange

# Скользящее среднее за последние 3 строки
Window(
    expression=Avg("total_price"),
    partition_by=[F("user_id")],
    order_by=F("created_at").asc(),
    frame=RowRange(start=-2, end=0),  # текущая + 2 предыдущие
)

AVG(total_price) OVER (
    PARTITION BY user_id
    ORDER BY created_at ASC
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Несколько оконных функций

Можно добавить несколько аннотаций с разными окнами:

products = Product.objects.annotate(
    rank_by_price=Window(
        expression=Rank(),
        partition_by=[F("category_id")],
        order_by=F("price").desc(),
    ),
    rank_by_stock=Window(
        expression=Rank(),
        partition_by=[F("category_id")],
        order_by=F("stock").desc(),
    ),
    category_avg_price=Window(
        expression=Avg("price"),
        partition_by=[F("category_id")],
    ),
)

SELECT shop_product.*,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank_by_price,
       RANK() OVER (PARTITION BY category_id ORDER BY stock DESC) AS rank_by_stock,
       AVG(price) OVER (PARTITION BY category_id) AS category_avg_price
FROM shop_product;

Все три окна, в одном запросе.

Практическое задание

Аннотируйте продукты рангом по цене внутри их категории (самый дорогой = ранг 1). Выведите топ-1 продукт каждой категории через фильтрацию в Python.
Аннотируйте заказы каждого пользователя накопительной суммой total_price по дате создания. Для пользователя с id=1 выведите список заказов с накопленной суммой.
Для каждого заказа найдите сумму предыдущего заказа того же пользователя через Lag. Для первого заказа должен быть NULL или 0 через default.
Аннотируйте продукты средней ценой по их категории через оконную функцию (без GROUP BY). Сравните с подходом через annotate(avg=Avg(...)), в чем разница в результате?

Возможные ошибки

filter() по оконной функции

# FieldError, нельзя фильтровать по window аннотации напрямую
Product.objects.annotate(
    rank=Window(expression=Rank(), partition_by=[F("category_id")], order_by=F("price").desc())
).filter(rank=1)  # ошибка

Решение, фильтрация в Python (для малых наборов) или подзапрос через raw SQL. Примеры показаны выше в разделе "Фильтрация по результатам оконной функции".

Window без order_by там где он нужен

# Lag без order_by - неопределенный порядок строк, результат непредсказуем
Window(expression=Lag("total_price"), partition_by=[F("user_id")])
# Нужен order_by для Lag/Lead/Rank/RowNumber

Смешивание оконных и агрегатных функций в одном annotate

# Нельзя смешивать Window и обычные агрегаты в одном QuerySet напрямую
Product.objects.annotate(
    rank=Window(expression=Rank(), ...),
    total=Sum("price"),  # конфликт - Sum требует GROUP BY, Window - нет
)
# django.db.utils.ProgrammingError

Решение, разбить на два отдельных QuerySet или использовать subquery.

Связь со следующим уроком

Блок 3 завершен. В уроке 4.1 начинаем блок связей и оптимизации JOIN'ов, разберем select_related, как он работает под капотом, в каких случаях делает один JOIN вместо N запросов и когда его использование контрпродуктивно.

<< Урок 3.5

Урок 4.1 >>

Подписывайтесь на мой Telegram канал

Если вам нужен ментор и вы хотите научиться веб-разработке, узнать подробнее