按交替值排序查询集 [英] Order queryset by alternating value

问题描述

我有以下模型:

class Entry(models.Model):
    name = models.Charfield(max_length=255)
    client = models.Charfield(max_length=255)

client 是客户端的名称，它可以包含 facebook、google 等值.

client is the name of the client as could have values like facebook, google, and so on.

是否可以对查询集进行排序，以便结果交替 client 的值?我期望的是这样的:

Is it possible to order the queryset so that the result is alternating the values of client? What I expect is something like this:

Entry.objects.order_by('alternate client') --> 

| client   | name   |
| google   | robert |
| facebook | linda  |
| google   | kate   | 
| facebook | jack   |
| google   | nina   |
| facebook | pierre |    

如果有帮助，我正在使用 django2.x 和 postgres.

I am using django2.x and postgres if that helps.

一些额外的信息/要求.

Some additional info / requirements.

• 我有大约 10 到 20 个不同的客户
• Entry 也有一个 created 日期字段.如果可能，结果也应按日期排序
• 我想对 Entry 使用分页，所以解决方案应该使用 django 的 ORM

• I have about 10 to 20 differtent clients
• Entry does also have a created DateField. If possible result should also be ordered by date
• I want to use pagination for Entry so solution should be using django's ORM

推荐答案

既然你使用 Postgres，你就可以使用它的 窗口函数，它对与当前行有某种关联的一组表行执行计算.另一个很好的信息依赖于您使用支持窗口函数的 Django2.x(Django 文档)，它允许将 OVER 子句添加到 Querysets.

Since you use Postgres you can use its Window Functions which perform a calculation across a set of table rows that are somehow related to the current row. Another good information relies in the fact that you use Django2.x which supports Window Functions(Django docs) which allows adding an OVER clause to Querysets.

您的用例可以通过单个 ORM 查询来解决，例如:

Your use-case can be resolved with Single ORM query like:

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

results = Entry.objects.annotate(row_number=Window(
    expression=RowNumber(),
    partition_by=[F('client')],
    order_by=F('created').desc())
).order_by('row_number', 'client')

for result in results:
    print('Id: {} - client: {} - row_number {}'.format(result.id, result.client, result.row_number))

输出:

Id: 12 - client: facebook - row_number 1
Id: 13 - client: google - row_number 1
Id: 11 - client: facebook - row_number 2
Id: 8 - client: google - row_number 2
Id: 10 - client: facebook - row_number 3
Id: 5 - client: google - row_number 3
Id: 9 - client: facebook - row_number 4
Id: 3 - client: google - row_number 4
Id: 7 - client: facebook - row_number 5
Id: 2 - client: google - row_number 5
Id: 6 - client: facebook - row_number 6
Id: 1 - client: google - row_number 6
Id: 4 - client: facebook - row_number 7

原始 SQL 看起来像:

The raw SQL looks like:

SELECT 
"orm_entry"."id",
"orm_entry"."name",
"orm_entry"."client",
"orm_entry"."created",
ROW_NUMBER() OVER (PARTITION BY "orm_entry"."client" ORDER BY "orm_entry"."created" DESC) AS "row_number" 
FROM "orm_entry" 
ORDER BY "row_number" ASC, "orm_entry"."client" ASC

窗口函数被声明为一个聚合函数，后跟一个 OVER 子句，它准确地指示行是如何分组的.应用窗口函数的那组行称为分区".

Window functions are declared just as an aggregate function followed by an OVER clause, which indicates exactly how rows are being grouped. The group of rows onto which the window function is applied is called "partition".

您会注意到我们按 'client' 字段对行进行了分组，您可以得出结论，在我们的示例中，我们将有两个分区.第一个分区将包含所有 'facebook' 条目，第二个分区将包含所有 'google' 条目.在其基本形式中，分区与普通的聚合函数组没有什么不同:只是一组根据某些标准被认为相等"的行，并且该函数将应用于所有这些行以返回单个结果.

You can notice that we grouped the rows by 'client' field and you can conclude that in our example we will have the two partitions. First partition will contain all the 'facebook' entries and second partition will contain all the 'google' entries. In its basic form, a partition is no different than a normal aggregate function group: simply a set of rows considered "equal" by some criteria, and the function will be applied over all these rows to return a single result.

在您的示例中，我们可以使用 row_number 窗口函数它只是返回分区内当前行的索引，从 1 开始.这帮助我在 order_by('row_number', 'client') 中建立交替输出.

In your example we can use the row_number window function which simply returns the index of the current row within its partition starting from 1. That helped me to establish the alternating output in order_by('row_number', 'client').

其他信息:

如果要实现这样的命令:

If you want to achieve an order like this:

'facebook','facebook', 'google','google','facebook','facebook','google','google'

或

'facebook','facebook','facebook','google','google','google','facebook','facebook','facebook'

您需要对之前的查询进行一个小的数学相关修改，例如:

You will need to do one small math related modification of the previous query like:

GROUP_SIZE = 2
results = Entry.objects.annotate(row_number=Window(
    expression=RowNumber(),
    partition_by=[F('client')],
    order_by=F('created').desc())
).annotate(row_number=(F('row_number') - 1)/GROUP_SIZE + 1).order_by('row_number', 'client')

for result in results:
    print('Id: {} - client: {} - row_number {}'.format(result.id, result.client, result.row_number))

输出:

Id: 12 - client: facebook - row_number 1
Id: 11 - client: facebook - row_number 1
Id: 8 - client: google - row_number 1
Id: 13 - client: google - row_number 1
Id: 10 - client: facebook - row_number 2
Id: 9 - client: facebook - row_number 2
Id: 3 - client: google - row_number 2
Id: 5 - client: google - row_number 2
Id: 7 - client: facebook - row_number 3
Id: 6 - client: facebook - row_number 3
Id: 1 - client: google - row_number 3
Id: 2 - client: google - row_number 3
Id: 4 - client: facebook - row_number 4

您可以注意到 GROUP_SIZE 常量定义了每个交替组中的项目数.

You can notice that GROUP_SIZE constant defines how many items will be in the each alternating group.

附言

感谢您提出这个问题，因为它帮助我更好地理解了窗口函数.

Thank you for asking this question because it helped me to better understand the Window Functions.

快乐编码:)

这篇关于按交替值排序查询集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！