SQL转ES：如何实现分组聚合后的分页与排序？

我来帮你调整这个聚合查询，完美匹配你想要的SQL效果，同时搞定排序和分页的问题，顺便纠正一下你当前DSL里的命名小偏差~

完整的Elasticsearch DSL #

{
  "size": 0,
  "aggregations": {
    "src_ip": {
      "terms": {
        "field": "src_ip.keyword",
        "size": 10, // 对应SQL LIMIT中的10，控制返回的分组数量
        "offset": 0, // 对应SQL LIMIT中的0，支持分页（比如取第11-20条就设为10）
        "order": {
          "threat_target>first_time": "desc" // 按first_time（即max(timestamp)）降序排序
        }
      },
      "aggregations": {
        "threat_target": {
          "terms": {
            "field": "threat_target.keyword"
          },
          "aggregations": {
            "first_time": { // 对应SQL里的max(timestamp) AS first_time
              "max": {
                "field": "timestamp"
              }
            },
            "last_time": { // 对应SQL里的min(timestamp) AS last_time
              "min": {
                "field": "timestamp"
              }
            }
          }
        }
      }
    }
  }
}

关键调整说明 #

• 排序设置：
  你需要在最外层的src_ip terms聚合里添加order参数，用threat_target>first_time指定排序依据——这里的>表示嵌套聚合的路径，意思是按src_ip分组下的threat_target子聚合里的first_time值降序排列，完全对应SQL的ORDER BY first_time desc。

• 分页/数量限制：

  • size:10直接对应SQL的LIMIT ...10，控制返回的顶级分组数量；
  • 如果需要分页（比如从第10条开始取），加上offset:10即可，对应SQL的LIMIT 10,10。

• count(*)的处理：
  Elasticsearch的terms聚合默认会返回doc_count字段，这个值就是每个分组的记录数，完全等价于SQL里的count(*)。如果你一定要把字段名改成count，可以在threat_target的聚合里加一个bucket_script来重命名：

  "count": {
    "bucket_script": {
      "buckets_path": {
        "cnt": "_count"
      },
      "script": "params.cnt"
    }
  }
  

• 命名修正：
  你之前的DSL里把max(timestamp)命名为last_time，min(timestamp)命名为first_time，和你的SQL命名刚好相反，我已经在上面的DSL里修正过来了，确保字段名和SQL一致。

返回结果示例结构 #

{
  "aggregations": {
    "src_ip": {
      "buckets": [
        {
          "key": "192.168.1.1", // src_ip值
          "doc_count": 5, // 对应count(*)
          "threat_target": {
            "buckets": [
              {
                "key": "targetA", // threat_target值
                "doc_count": 5,
                "first_time": {
                  "value": 1699999999000 // max(timestamp)
                },
                "last_time": {
                  "value": 1699999000000 // min(timestamp)
                }
              }
            ]
          }
        }
      ]
    }
  }
}

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