将mySQL聚合查询转换为alasticsearch

Question

我有一个评论表，在过去的一年中，我将其移至Elasticsearch。

问题在于，我需要调整我当前在MySQL中的查询，该查询可以返回过去7天内每天的评论总数。

这是我现在拥有的MySQL查询：

SELECT count(*) AS number, DATE(created_at) AS date 
FROM `comments` 
WHERE `post_id` = ? 
GROUP BY `date` 
ORDER BY `date` DESC 
LIMIT 7

我的索引看起来像：

{
  "mappings": {
    "_doc": {
      "properties": {
        "id": {
          "type": "keyword"
        },
        "post_id": {
          "type": "integer"
        },
        "subject": {
          "analyzer": "custom_html_strip",
          "type": "text"
        },
        "body": {
          "analyzer": "custom_html_strip",
          "type": "text"
        },
        "created_at": {
          "format": "yyyy-MM-dd HH:mm:ss",
          "type": "date"
        }
      }
    }
  }
}

是否可以重现Elasticsearch的查询？如果是这样，那会怎样？

我的Elasticsearch知识有限，我知道它提供了聚合，但我真的不知道如何将所有内容放在一起。

Answer 1

Elasticsearch也支持SQL查询（尽管有限）
因此，有了一点更改，您可以使用此类内容

GET _sql
{
  "query": """
  SELECT count(*) AS number, created_at AS date 
FROM comments
WHERE post_id = 123 
GROUP BY date
ORDER BY date DESC 
LIMIT 7
  """
}

，可以使用_SQL/Translate看到相应的查询，这将返回

{
  "size" : 0,
  "query" : {
    "term" : {
      "post_id" : {
        "value" : 123,
        "boost" : 1.0
      }
    }
  },
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 7,
        "sources" : [
          {
            "31239" : {
              "terms" : {
                "field" : "created_at",
                "missing_bucket" : true,
                "order" : "desc"
              }
            }
          }
        ]
      }
    }
  }
}

说，不需要翻译查询中使用的某些内容，因此这将是一个更好的本地查询

{
  "query": {
    "term": {
      "post_id": {
        "value": 123
      }
    }
  },
  "aggs": {
    "unq_dates": {
      "terms": {
        "field": "created_at",
        "size": 7,
        "order": {
          "_term": "desc"
        }
      }
    }
  }
}

Answer 2

首先，建议定期汇总您的时间字段，因此使用直接写入“ Yyyy-MM-DD”格式的字段将提高您的性能并减少资源的消费

{
    "query":
    {
        "filter":
        [
            {
                "term":
                {
                    "post_id":
                    {
                        "value": 1
                    }
                }
            }
        ]
    },
    "_source": false,
    "aggs":
    {
        "created_at":
        {
            "auto_date_histogram":
            {
                "field": "created_at",
                "buckets": 7,
                "format": "yyyy-MM-dd"
            }
        }
    }
}

Answer 3

在过去的7天中，使用以下查询在给定的“ post_id”上获取所有评论。

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "id": {
              "value": "the_post_id"
            }
          }
        },
        /*** only include this clause if you want the recent most 7 days ***/
        {
          "range": {
            "created_at": {
              "gte": "now-7d/d",
              "lt": "now/d"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "posts_per_day": {
      "date_histogram": {
        "field": "created_at",
        "calendar_interval": "day",
        "order" : {"_key" : "desc"}
      }
    }
  }
}

从聚合中，在客户端应用程序中获取前7个存储桶。