Фильтрация набора запросов Django после использования оконной функции

Для простой модели:

from django.db.models import CharField, Model, PositiveIntegerField

class Example(Model):
    category = CharField(max_length=20, null=False, blank=False)
    version = PositiveIntegerField(null=False, blank=False)

    class Meta:
        unique_together = ["category", "version"]

И некоторые примеры данных:

Example.objects.update_or_create(id=1, category="Thing 1", version=1)
Example.objects.update_or_create(id=2, category="Thing 1", version=2)
Example.objects.update_or_create(id=3, category="Thing 1", version=4)
Example.objects.update_or_create(id=4, category="Thing 2", version=1)
Example.objects.update_or_create(id=5, category="Thing 2", version=2)
Example.objects.update_or_create(id=6, category="Thing 3", version=3)

Я хотел бы использовать оконные функции, чтобы получить:

  • Примеры, которые только из подмножества с последней версией в каждой категории, имеют идентификаторы либо 1, 4, 5, либо 6.

Я пытаюсь сделать это с помощью RowNumber оконной функции:

from django.db.models import F, Window
from django.db.models.functions.window import RowNumber

results = Example.objects.alias(
    rn=Window(
        expression=RowNumber(),
        partition_by=[F("category")],
        order_by="-version"
    ),
).filter(rn=1).filter(id__in=[1,4,5,6])

print(results.query)
print(results)

При этом генерируется запрос:

SELECT "col1",
       "col2",
       "col3"
FROM   (
  SELECT *
  FROM   (
    SELECT "greatest_n_example"."id" AS "col1",
           "greatest_n_example"."category" AS "col2",
           "greatest_n_example"."version" AS "col3",
           ROW_NUMBER() OVER (PARTITION BY "greatest_n_example"."category" ORDER BY "greatest_n_example"."version" DESC) AS "qual0"
    FROM   "greatest_n_example"
    WHERE  "greatest_n_example"."id" IN (1, 4, 5, 6)
  ) "qualify"
  WHERE  "qual0" = 1
) "qualify_mask"

И возвращает примеры 1, 5 и 6; это не то, что требуется.

Последним для category="Thing 1" является version=4, и фильтр на rn должен быть применен перед фильтром на id (это не так, в настоящее время это другой фильтр). наоборот), поэтому ожидается, что все элементы категории Thing 1 должны быть исключены из результирующего набора.

Ожидаемый запрос эквивалентен:

SELECT "col1",
       "col2",
       "col3"
FROM   (
  SELECT *
  FROM   (
    SELECT "greatest_n_example"."id" AS "col1",
           "greatest_n_example"."category" AS "col2",
           "greatest_n_example"."version" AS "col3",
           ROW_NUMBER() OVER (PARTITION BY "greatest_n_example"."category" ORDER BY "greatest_n_example"."version" DESC) AS "qual0"
    FROM   "greatest_n_example"
  ) "qualify"
  WHERE  "qual0" = 1
  AND    "col1" IN (1, 4, 5, 6)
) "qualify_mask"

, который выводил бы только 5 и 6.

Как, используя оконные функции, я могу обеспечить, чтобы фильтр по номеру строки выполнялся, по крайней мере, в том же встроенном представлении SQL, что и фильтр id, если не раньше?

Примечание: Я знаю, что могу решить эту проблему, используя Subquery, а не Window, для выполнения фильтрации с использованием коррелированных подзапросов; этот вопрос конкретно о том, как используйте оконные функции и получите WHERE фильтра в правильном встроенном представлении SQL-запроса.

Вы можете решить эту проблему, используя Case и When:

from django.db.models import Case, F, When, Window
from django.db.models.functions.window import RowNumber
from greatest_n.models import Example

query = Example.objects.alias(
    rn=Window(
        expression=RowNumber(),
        partition_by=[F("category")],
        order_by="-version"
    ),
    first_id=Case(When(rn=1, then=F("id")), default=None),
).filter(first_id__in=[1,4,5,6])

print(query.query)
print(query)

Который генерирует запрос:

SELECT "col1",
       "col2",
       "col3"
FROM   (
  SELECT *
  FROM   (
    SELECT "greatest_n_example"."id" AS "col1",
           "greatest_n_example"."category" AS "col2",
           "greatest_n_example"."version" AS "col3",
           CASE
           WHEN (ROW_NUMBER() OVER (
                   PARTITION BY "greatest_n_example"."category"
                   ORDER BY "greatest_n_example"."version" DESC
                 ) = 1)
           THEN "greatest_n_example"."id"
           ELSE NULL
           END AS "qual0"
    FROM   "greatest_n_example"
  ) "qualify"
  WHERE "qual0" IN (1, 4, 5, 6)
) "qualify_mask"

И выводит только примеры 5 и 6.

Вы можете использовать необработанный запрос:

params = [1,4,5,6]
query = Example.objects.raw(
    f"""
    SELECT  "id",
            "category",
            "version"
    FROM   (
        SELECT  g."id",
                g."category",
                g."version",
                ROW_NUMBER() OVER (
                    PARTITION BY g."category"
                    ORDER BY g."version" DESC
                ) AS rn
        FROM    "{Example._meta.db_table}" g
    )
    WHERE rn = 1
    AND   "id" IN ({",".join("%s" for _ in params)})
    """,
    params
)
print(query.query)
print([r for r in query])

Примечание: Возможно, потребуется обработать особый случай, когда params пуст.

Который генерирует запрос:

    SELECT  "id",
            "category",
            "version"
    FROM   (
        SELECT  g."id",
                g."category",
                g."version",
                ROW_NUMBER() OVER (
                    PARTITION BY g."category"
                    ORDER BY g."version" DESC
                ) AS rn
        FROM    "greatest_n_example" g
    )
    WHERE rn = 1
    AND   "id" IN (1,4,5,6)

И выводит только примеры 5 и 6.

Вернуться на верх