既然你使用 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.

快乐编码:)