如何将MySQL按日统计评论数的聚合查询转换为ElasticSearch查询

完全可以在ElasticSearch中复现该MySQL查询的等效效果，基于你现有的索引mapping不需要做任何结构调整，直接组合过滤条件和日期聚合即可实现。

逻辑对应关系 #

原MySQL语句的逻辑和ES实现的映射如下：

• WHERE post_id = ?：用term精确查询过滤指定文章的评论
• DATE(created_at)分组 + count(*)计数：用date_histogram日期直方图聚合按天切分时间桶，每个桶的文档数就是当日评论量
• ORDER BY date DESC LIMIT 7：给聚合指定按日期键倒序排序，限制返回7个时间桶即可
• 额外增加最近7天的时间范围过滤，可以大幅减少聚合扫描的数据量，查询性能更高

可直接使用的ES查询DSL #

将代码中索引名替换为你实际存储评论的索引名，post_id的值替换为目标文章ID即可：

GET /你的评论索引名/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {"term": {"post_id": 替换为目标post_id整数值}},
        {"range": {"created_at": {"gte": "now-7d/d", "time_zone": "+08:00"}}}
      ]
    }
  },
  "aggs": {
    "daily_count": {
      "date_histogram": {
        "field": "created_at",
        "calendar_interval": "day",
        "format": "yyyy-MM-dd",
        "order": {"_key": "desc"},
        "min_doc_count": 0,
        "size": 7
      }
    }
  }
}

参数说明 #

• 最外层"size":0：表示不需要返回匹配的原始评论文档，仅返回聚合结果，能显著降低查询开销
• time_zone：请替换为你业务实际使用的时区，国内东八区填+08:00即可，不填会默认用UTC时区，导致按天分组时出现日期偏移
• format: "yyyy-MM-dd"：返回的日期格式和MySQLDATE()函数输出格式完全一致
• min_doc_count: 0：表示即使某天没有评论，也会返回该日期的统计结果，值为0；如果不需要补全空日期，直接删除该配置项即可

结果读取 #

查询返回结果中，aggregations.daily_count.buckets数组就是最终统计数据，每个元素的：

• key_as_string字段对应SQL查询里的date
• doc_count字段对应SQL查询里的number

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