如何在ElasticSearch中实现类似分组row_number的排序逻辑

需求说明 #

现有产品数据，包含ProductId、Name、ManufacturerId、CategoryId、UserScore字段，需实现：

• 按指定CategoryId过滤产品
• 按UserScore降序排序，但避免同一厂商的产品大量连续出现
• 每个厂商最多连续展示2个产品

原T-SQL的问题与解决方案 #

直接按UserScore排序会导致同厂商产品扎堆，对应的T-SQL代码：

SELECT
P.ProductId, P.Name, P.ManufacturerId, P.UserScore
FROM Products P
WHERE P.CategoryId = 1
ORDER BY P.UserScore

用户已通过T-SQL实现需求：先按厂商分组，给每个厂商内的产品按UserScore降序编号（RN），再通过UserScore / CEILING(RN/2.0)的计算值排序，实现每个厂商最多连续展示2个产品。代码如下：

SELECT T.*
FROM (
       SELECT
             P.ProductId, P.Name, P.ManufacturerId, P.UserScore,
             ROW_NUMBER() OVER (PARTITION BY P.ManufacturerId ORDER BY P.UserScore DESC) RN
       FROM Products P
       WHERE P.CategoryId = 1
) T
ORDER BY T.UserScore / CEILING(RN/2.0) DESC

Elasticsearch实现方案 #

Elasticsearch中可以通过窗口函数（Window Functions） + 运行时脚本实现相同逻辑，具体DSL如下：

注意事项 #

• 示例中UserScore为字符串类型，需先转换为数值类型参与计算
• 需确保Elasticsearch版本在7.10及以上（支持窗口函数）

查询DSL #

{
  "query": {
    "term": {
      "CategoryId": "1"
    }
  },
  "fields": ["ProductId", "Name", "ManufacturerId", "UserScore"],
  "window": {
    "rn_window": {
      "partition_by": "ManufacturerId",
      "order": {
        "UserScore.keyword": "desc"
      },
      "script": "emit(ctx._index + 1)"
    }
  },
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "source": """
            double score = Double.parseDouble(doc['UserScore'].value);
            int rn = params.rn;
            double ceiling = Math.ceil(rn / 2.0);
            return score / ceiling;
          """,
          "params": {
            "rn": "{{window.rn_window}}"
          }
        },
        "order": "desc"
      }
    }
  ]
}

逻辑说明 #

1. 过滤数据：通过term查询筛选CategoryId=1的产品
2. 计算行号RN：使用window按ManufacturerId分组，每组内按UserScore降序生成行号rn
3. 自定义排序逻辑：通过脚本将UserScore转为数值，计算score / CEILING(rn/2.0)，按该值降序排序，实现同厂商产品最多连续展示2个的效果

若你的Elasticsearch版本不支持窗口函数，可通过聚合+脚本的方式实现，但复杂度较高，上述方案是最贴近T-SQL逻辑的实现方式。

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