如何在Elasticsearch中实现按价格及交替公司ID排序？

原始数据 #

name                       | price | company_id

product 1 of the company 1 | 10    | 1
product 2 of the company 1 | 10    | 1
product 3 of the company 1 | 20    | 1
product 4 of the company 2 | 10    | 2
product 5 of the company 2 | 10    | 2
product 6 of the company 2 | 20    | 2
product 7 of the company 3 | 10    | 3
product 8 of the company 3 | 10    | 3
product 9 of the company 3 | 20    | 3

需求说明 #

需要按price升序排序，同时让不同company_id的记录交替出现，最终期望结果如下：

name                       | price | company_id

product 1 of the company 1 | 10    | 1
product 4 of the company 2 | 10    | 2
product 7 of the company 3 | 10    | 3
product 2 of the company 1 | 10    | 1
product 5 of the company 2 | 10    | 2
product 8 of the company 3 | 10    | 3
product 3 of the company 1 | 20    | 1
product 6 of the company 2 | 20    | 2
product 9 of the company 3 | 20    | 3

实现思路与方法 #

这类排序属于分组交替排序，核心是先按价格分组，再给每个公司同价格下的产品分配"组内序号"，最后按「价格+组内序号+公司ID」的优先级排序。

SQL 实现示例 #

1. 用窗口函数生成组内序号：

SELECT 
    name,
    price,
    company_id,
    ROW_NUMBER() OVER (PARTITION BY company_id, price ORDER BY name) AS row_num
FROM products;

row_num会给每个公司同价格的产品标记第1、第2...个序号。

2. 按规则排序得到最终结果：

SELECT name, price, company_id
FROM (
    SELECT 
        name,
        price,
        company_id,
        ROW_NUMBER() OVER (PARTITION BY company_id, price ORDER BY name) AS row_num
    FROM products
) ranked
ORDER BY price, row_num, company_id;

编程语言（以Python为例）实现思路 #

• 先按价格分组，每个价格组内再按公司分组，给每个公司的产品添加组内序号
• 按「价格→组内序号→公司ID」的顺序对所有产品排序
• 展开排序后的结果即可得到交替显示的效果

搜索关键词 #

如果需要查找更多相关方法，可以使用这些关键词：

• 分组交替排序
• 轮询排序（round-robin sort）
• 按组交替输出数据
• SQL 分组交错排序

内容的提问来源于stack exchange，提问作者nicolaa