最大分组的 Django TruncDay 限制

Question

我正在尝试绘制日系列图，它将显示日系列中部门的员 worker 数。这里的问题是我想找到一种方法来限制员工最多的部门，这样我就不需要画太多线并给数据库施加压力。 (例如将其限制为员工出勤率最高的前 3 个部门)

我正在使用: Django 1.11.xPostgres 9.4

目标是创建此类日系列图。有部门分组和员 worker 数。

我已经设法用下面的代码实现了它:

from datetime import date, datetime
from django.db.models import Count
from django.db.models.functions import (
    TruncDate, TruncDay, TruncHour, TruncMinute, TruncSecond,
    )

emp_by_day = Attendance.objects.annotate(day=TruncDay('created_at')).values('day', 'division_id').annotate(cnt=Count('employee_id', distinct = True)).order_by('day')

for exp in emp_by_day:
    print(exp['day'], exp['division_id'], exp['cnt'])

然而，它目前输出显示如下(我通常很高兴但想限制它):

              employee count<->
              division_id<->
<---day----------------->
2019-10-22 00:00:00+00:00 15 6
2019-10-22 00:00:00+00:00 16 6
2019-10-22 00:00:00+00:00 18 5
2019-10-22 00:00:00+00:00 20 4
2019-10-22 00:00:00+00:00 21 12 <-- largest 3
2019-10-22 00:00:00+00:00 25 14 <-- largest 3
2019-10-22 00:00:00+00:00 28 12 <-- largest 3
2019-10-23 00:00:00+00:00 15 6
2019-10-23 00:00:00+00:00 16 5
2019-10-23 00:00:00+00:00 18 2
2019-10-23 00:00:00+00:00 20 3
2019-10-23 00:00:00+00:00 21 14 <-- largest 3
2019-10-23 00:00:00+00:00 25 17 <-- largest 3
2019-10-23 00:00:00+00:00 28 13 <-- largest 3
2019-10-24 00:00:00+00:00 15 2
2019-10-24 00:00:00+00:00 16 6
2019-10-24 00:00:00+00:00 18 5
2019-10-24 00:00:00+00:00 20 4
2019-10-24 00:00:00+00:00 21 13 <-- largest 3
2019-10-24 00:00:00+00:00 25 12 <-- largest 3
2019-10-24 00:00:00+00:00 28 10 <-- largest 3

我的目标是生成这个(将其限制为最大的 3 个分区):

2019-10-22 00:00:00+00:00 21 12 <-- largest 3
2019-10-22 00:00:00+00:00 25 14 <-- largest 3
2019-10-22 00:00:00+00:00 28 12 <-- largest 3
2019-10-23 00:00:00+00:00 21 14 <-- largest 3
2019-10-23 00:00:00+00:00 25 17 <-- largest 3
2019-10-23 00:00:00+00:00 28 13 <-- largest 3
2019-10-24 00:00:00+00:00 21 13 <-- largest 3
2019-10-24 00:00:00+00:00 25 12 <-- largest 3
2019-10-24 00:00:00+00:00 28 10 <-- largest 3

请告诉我如何产生这样的预期输出(将其限制为最大的 3 格)

Answer 1

你应该使用 Rank()过滤结果的窗口函数。

逻辑:

假设您希望按天分组

您应该根据一天内划分的 cnt 计数值对每一行进行排序。最高的将获得第一名，依此类推。现在你应该过滤掉排名在 1 到 3 之间的结果。

继续查询

emp_by_day.annotate(rank=Window(
expression=Rank(),
order_by=F('cnt').desc(),
partition_by=[F('day')])).filter(rank__range=(1,3))

注意:如果超过一行的cnt值相同，则两行或更多行的排名相同。因此你可以获得超过 3 行。如果您只想要前 3 行，请使用 RowNumber()而不是 Rank()。

Postgres 示例查询:

select * from (
 select *, rank() over (partition by day order by cnt desc) as rank from 
 (
  select emp_id,day,count(emp_id) as cnt from attendance group by emp_id,day  
  order by day
 ) as T
) as Temp where rank between 1 and 3;

替换rank()通过 row_number()只获取前 3 行。

更新

Django 1.11 不支持 window()。不过你可以引用following gist它将此功能从 Django 2 反向移植到 1.11。

注意:我没有测试过。但是，OP 创建者对其进行了测试并且它有效。