使用Django queryset获取每个组的前n条记录

本文介绍了使用Django queryset获取每个组的前n条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个类似下表的模型，

I have a model like the following Table,

create table `mytable` ( `person` varchar(10), `groupname` int, `age` int );

我想从每个小组中选出2个最年长的人.原始SQL问题和答案在此处 StackOverflow ，并且可行的解决方案之一是

And I want to get the 2 oldest people from each group. The original SQL question and answers are here StackOverflow and One of the solutions that work is

SELECT person, groupname, age FROM ( SELECT person, groupname, age, @rn := IF(@prev = groupname, @rn + 1, 1) AS rn, @prev := groupname FROM mytable JOIN (SELECT @prev := NULL, @rn := 0) AS vars ORDER BY groupname, age DESC, person ) AS T1 WHERE rn <= 2

您也可以在此处检查SQL输出 SQLFIDLE

You can check the SQL output here as well SQLFIDLE

我只想知道如何在Django的视图中以queryset的形式实现此查询.

I just want to know how can I implement this query in Django's views as queryset.

推荐答案

另一个具有类似输出的SQL将具有window函数，该函数用特定组名内的行号注释每一行，然后在 HAVING 子句.

Another SQL with similar output would have window function that annotates each row with row number within particular group name and then you would filter row numbers lower or equal 2 in HAVING clause.

在编写django时，不支持基于窗口函数结果的过滤需要在第一个查询中计算行并在第二个查询中过滤 People .

At the moment of writing django does not support filtering based on window function result so you need to calculate row in the first query and filter People in the second query.

以下代码基于类似问题，但它实现了每个 group_name .

Following code is based on similar question but it implements limiting number of rows to be returned per group_name.

from django.db.models import F, When, Window from django.db.models.functions import RowNumber person_ids = { pk for pk, row_no_in_group in Person.objects.annotate( row_no_in_group=Window( expression=RowNumber(), partition_by=[F('group_name')], order_by=['group_name', F('age').desc(), 'person'] ) ).values_list('id', 'row_no_in_group') if row_no_in_group <= 2 } filtered_persons = Person.objects.filter(id__in=person_ids)

用于 Person 表的跟踪状态

>>> Person.objects.order_by('group_name', '-age', 'person').values_list('group_name', 'age', 'person') <QuerySet [(1, 19, 'Brian'), (1, 17, 'Brett'), (1, 14, 'Teresa'), (1, 13, 'Sydney'), (2, 20, 'Daniel'), (2, 18, 'Maureen'), (2, 14, 'Vincent'), (2, 12, 'Carlos'), (2, 11, 'Kathleen'), (2, 11, 'Sandra')]>

返回上方的查询

>>> filtered_persons.order_by('group_name', '-age', 'person').values_list('group_name', 'age', 'person') <QuerySet [(1, 19, 'Brian'), (1, 17, 'Brett'), (2, 20, 'Daniel'), (2, 18, 'Maureen')]>