如何在Elasticsearch中基于聚合数据按计数器总和排序搜索词

数据集批量写入语句 #

PUT my-data-stream/_bulk
{"create":{}}
{"timestamp":"2022-05-06T18:25:42","search_term":"hello", "counter": 10}
{"create":{}}
{"timestamp":"2022-05-06T18:25:42","search_term":"bye", "counter": 5}
{"create":{}}
{"timestamp":"2022-05-06T17:25:42","search_term":"hello", "counter": 9}
{"create":{}}
{"timestamp":"2022-05-06T17:25:42","search_term":"bye", "counter": 7}
{"create":{}}
{"timestamp":"2022-05-06T16:25:42","search_term":"hello", "counter": 5}
{"create":{}}
{"timestamp":"2022-05-06T16:25:42","search_term":"bye", "counter": 2}

需求说明 #

筛选出时间戳大于指定值的记录，按search_term分组计算counter字段的总和，最终按总和降序输出结果。示例中指定时间戳为2022-05-06T16:35:42，对应前4条记录，预期输出：

hello, 19 (10 + 9)
bye, 12 (5 + 7)

实现查询语句 #

使用Elasticsearch的聚合查询即可实现，DSL如下：

GET my-data-stream/_search
{
  "size": 0, // 不返回原始文档，仅返回聚合结果
  "query": {
    "range": {
      "timestamp": {
        "gt": "2022-05-06T16:35:42" // 过滤时间戳大于指定值的记录
      }
    }
  },
  "aggs": {
    "group_by_search_term": {
      "terms": {
        "field": "search_term.keyword", // 需用keyword类型字段分组，避免分词干扰
        "order": {
          "total_counter": "desc" // 按counter总和降序排序
        }
      },
      "aggs": {
        "total_counter": {
          "sum": {
            "field": "counter" // 计算每组counter的总和
          }
        }
      }
    }
  }
}

查询结果解析 #

执行上述查询后，聚合结果会返回每个search_term对应的counter总和，按降序排列：

• hello组总和：10 + 9 = 19
• bye组总和：5 + 7 = 12

如果需要格式化为示例中的文本样式，可在应用层对聚合结果进行遍历拼接即可。

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