Elasticsearch SQL 类子查询聚合

Question

我正在尝试使用 ES 来了解它是否可以涵盖我的大部分场景。我正处于思考如何在 SQL 中非常简单地获得特定结果的地步。

这是例子

在 elastic 中我有一个包含这些文件的索引

{ "Id": 1,  "Fruit": "Banana", "BoughtInStore"="Jungle", "BoughtDate"=20160101,  "BestBeforeDate": 20160102, "BiteBy":"John"}
{ "Id": 2,  "Fruit": "Banana", "BoughtInStore"="Jungle", "BoughtDate"=20160102,  "BestBeforeDate": 20160104, "BiteBy":"Mat"}
{ "Id": 3,  "Fruit": "Banana", "BoughtInStore"="Jungle", "BoughtDate"=20160103,  "BestBeforeDate": 20160105, "BiteBy":"Mark"}
{ "Id": 4,  "Fruit": "Banana", "BoughtInStore"="Jungle", "BoughtDate"=20160104,  "BestBeforeDate": 20160201, "BiteBy":"Simon"}
{ "Id": 5,  "Fruit": "Orange", "BoughtInStore"="Jungle", "BoughtDate"=20160112,  "BestBeforeDate": 20160112, "BiteBy":"John"}
{ "Id": 6,  "Fruit": "Orange", "BoughtInStore"="Jungle", "BoughtDate"=20160114,  "BestBeforeDate": 20160116, "BiteBy":"Mark"}
{ "Id": 7,  "Fruit": "Orange", "BoughtInStore"="Jungle", "BoughtDate"=20160120,  "BestBeforeDate": 20160121, "BiteBy":"Simon"}
{ "Id": 8,  "Fruit": "Kiwi", "BoughtInStore"="Shop", "BoughtDate"=20160121,  "BestBeforeDate": 20160121, "BiteBy":"Mark"}
{ "Id": 8,  "Fruit": "Kiwi", "BoughtInStore"="Jungle", "BoughtDate"=20160121,  "BestBeforeDate": 20160121, "BiteBy":"Simon"}

如果我想知道在 SQL 的特定日期范围内，人们在不同商店购买了多少水果，我会写这样的东西

SELECT 
    COUNT(DISTINCT kpi.Fruit) as Fruits, 
    kpi.BoughtInStore,
    kpi.BiteBy 
FROM 
    (
        SELECT f1.Fruit, f1.BoughtInStore, f1.BiteBy
        FROM FruitsTable f1
        WHERE f1.BoughtDate = (
            SELECT MAX(f2.BoughtDate)
            FROM FruitsTable f2
            WHERE f1.Fruit = f2.Fruit
            and f2.BoughtDate between 20160101 and 20160131
            and (f2.BestBeforeDate between 20160101 and 20160131)
        )
    ) kpi   
GROUP BY kpi.BoughtInStore, kpi.ByteBy

结果是这样的

{ "Fruits": 1,  "BoughtInStore": "Jungle", "BiteBy"="Mark"}
{ "Fruits": 1,  "BoughtInStore": "Shop", "BiteBy"="Mark"}
{ "Fruits": 2,  "BoughtInStore": "Jungle", "BiteBy"="Simon"}

您知道我如何通过聚合在 Elastic 中达到相同的结果吗？

简而言之，我在 elastic 中面临的问题是:

1. 如何在聚合前准备子数据(如本例中每个水果范围内的最新行)
2. 如何按多个字段对结果进行分组

谢谢

Answer 1

据我所知，无法在同一查询的过滤器中引用聚合结果。因此，您只能通过单个查询解决部分难题:

GET /purchases/fruits/_search
{
  "query": {
    "filtered":{ 
      "filter": {
        "range": {
          "BoughtDate": {
            "gte": "2015-01-01", //assuming you have right mapping for dates
            "lte": "2016-03-01"
          }
        }
      }
    }
  },
  "sort": { "BoughtDate": { "order": "desc" }},
  "aggs": {
    "byBoughtDate": {
      "terms": {
        "field": "BoughtDate",
        "order" : { "_term" : "desc" }
      },
      "aggs": {
        "distinctCount": {
           "cardinality": {
             "field": "Fruit"
           }
         }
      }
    }
  }
}

所以您将拥有日期范围内的所有文档，并且您将拥有聚合的存储桶计数，按术语排序，因此最大日期将位于顶部。客户端可以解析第一个桶(包括计数和值)，然后获取该日期值的文档。对于不同的水果计数，您只需使用嵌套基数聚合。

是的，查询返回的信息比您需要的多得多，但这就是生活:)