使用Django queryset获取每个组的前n条记录 [英] Get top n records for each group with Django queryset

问题描述

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

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')]>

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