Django使用ORM选择每个组的前n条记录 [英] Django selecting top n records per group using ORM

问题描述

遵循此问题，我试图获取每个group_by critera的前10条记录，但是Django返回此错误：

Following this question, I'm trying to get top 10 records per each group_by critera, but Django return this error:

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

Purchases.objects.annotate(row_number=Window(
        expression=RowNumber(),
        partition_by=F('customer'),
        order_by=F('field_of_interest').desc()
        )
    ).filter(row_number=10)

raise NotSupportedError(
django.db.utils.NotSupportedError: Window is disallowed in the filter clause.

当我删除.desc（）时，错误消息更改为：

When I remove .desc(), error message changes to:

ValueError: order_by must be either an Expression or a sequence of expressions.

我正在使用Postgr eSql。

I'm using PostgreSql. Is it a bug or am I wrong somewhere in my query?

推荐答案

我们可以在子查询。

首先，让我们获得每位客户的前n个购物

Firstly, let's get top n Purchases per customer

top_n_purchases_per_customer = Purchases.objects.filter(
    customer=OuterRef('customer')
).order_by('-field_of_interest')[:10]

接下来，我们可以从每个客户的前10个中选择具有匹配ID的购买。

Next, we can select Purchases with matching ids from the top 10 per each customer.

top_n_purchases = Purchases.objects.filter(
    id__in=Subquery(top_n_purchases_per_customer.values('id'))
)

生成的查询使用相关子查询，因此会变慢。确保对 field_of_interest 和 customer 使用索引（最好同时使用两个字段的索引（请参见 index_together 在Django文档中））

The generated query makes use of correlated subquery, so can become slow. Make sure to use index for field_of_interest and customer (preferably combined index by both fields (see index_together in Django docs))

这篇关于Django使用ORM选择每个组的前n条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！