elasticsearch - 如何在ElasticSearch中排序和限制聚合

Question

例如，我有以下记录，其列为:(国家，城市，日期，收入)

USA SF 2015-08 50
USA SF 2015-05 30
USA SF 2015-01 20
USA NY 2015-05 70
USA NY 2015-02 10
U.K LD 2015-05 90

我的SQL为: select country,city,max(date) as maxDate,sum(income) as sumIncome from testTable group by country,city order by maxDate desc,sumIncome desc limit 3。
因此结果应为:

USA SF 2015-08 100
U.K LD 2015-05 90
USA NY 2015-05 80

我写的ES聚合如下，但这是错误的:

"aggs":{"sub1": {"terms":{"field":"contry"},
   "aggs":{"sub2":{"terms":{"field":"city",
       "order":[{"submax":"DESC"},{"subsum":"DESC"}]},
     "aggs":{"submax":{"max":{"field":"date"}},"subsum":{"sum":{"field":"income"}}}}}}}

通过我上面的脚本，它得到以下错误结果:

USA SF 2015-08 100
USA NY 2015-05 80
U.K LD 2015-05 90

Answer 1

现在，您已经有了两个选择，现在我已经了解了要求。

选项1

使用script来“连接” country字段和city字段。在Elasticsearch中无法使用每个字段的常规聚合来执行所需的操作。

相反，您需要执行以下操作:

GET /test/test/_search?search_type=count
{
  "aggs": {
    "sub1": {
      "terms": {
        "script": "doc['country'].value + ' ' + doc['city'].value",
        "size": 3,  
        "order": [
          {
            "submax": "DESC"
          },
          {
            "subsum": "DESC"
          }
        ]
      },
      "aggs": {
        "submax": {
          "max": {
            "field": "date"
          }
        },
        "subsum": {
          "sum": {
            "field": "income"
          }
        }
      }
    }
  }
}

用 curl:

curl -XPOST "http://localhost:9200/livebox/type1/_search?search_type=count" -d'
{
  "aggs": {
    "sub1": {
      "terms": {
        "script": "doc[\"boxname\"].value + \" \" + doc[\"app\"].value",
        "size": 3,
        "order": [
          {
            "submax": "DESC"
          },
          {
            "subsum": "DESC"
          }
        ]
      },
      "aggs": {
        "submax": {
          "max": {
            "field": "date"
          }
        },
        "subsum": {
          "sum": {
            "field": "count"
          }
        }
      }
    }
  }
}'

聚合的结果将生成以下形式的项: country + + city。

     "buckets": [
        {
           "key": "usa sf",
           "doc_count": 3,
           "subsum": {
              "value": 100
           },
           "submax": {
              "value": 1438387200000,
              "value_as_string": "2015-08"
           }
        },
        {
           "key": "uk ld",
           "doc_count": 1,
           "subsum": {
              "value": 90
           },
           "submax": {
              "value": 1430438400000,
              "value_as_string": "2015-05"
           }
        },
        {
           "key": "usa ny",
           "doc_count": 2,
           "subsum": {
              "value": 80
           },
           "submax": {
              "value": 1430438400000,
              "value_as_string": "2015-05"
           }
        }
     ]

选项2

使用 _source transformation将在建立索引时建立一个新字段，这将“移动”在聚集时运行脚本的性能影响。

索引的映射，因为它需要进行一些更改，所以无论您现在拥有什么:

PUT /test
{
  "mappings": {
    "test": {
      "transform": {
        "script": "ctx._source['country_and_city'] = ctx._source['country'] + ' ' + ctx._source['city']"
      },
      "properties": {
        "country": {
          "type": "string"
        },
        "city": {
          "type": "string"
        },
        "income": {
          "type": "integer"
        },
        "date": {
          "type": "date",
          "format": "yyyy-MM"
        },
        "country_and_city": {
          "type": "string",
          "index": "not_analyzed"
        }
      }
    }
  }
}

查询:

GET /test/test/_search?search_type=count
{
  "aggs": {
    "sub1": {
      "terms": {
        "field": "country_and_city",
        "order": [
          {
            "submax": "DESC"
          },
          {
            "subsum": "DESC"
          }
        ]
      },
      "aggs": {
        "submax": {
          "max": {
            "field": "date"
          }
        },
        "subsum": {
          "sum": {
            "field": "income"
          }
        }
      }
    }
  }
}

结果:

     "buckets": [
        {
           "key": "usa sf",
           "doc_count": 3,
           "subsum": {
              "value": 100
           },
           "submax": {
              "value": 1438387200000,
              "value_as_string": "2015-08"
           }
        },
        {
           "key": "uk ld",
           "doc_count": 1,
           "subsum": {
              "value": 90
           },
           "submax": {
              "value": 1430438400000,
              "value_as_string": "2015-05"
           }
        },
        {
           "key": "usa ny",
           "doc_count": 2,
           "subsum": {
              "value": 80
           },
           "submax": {
              "value": 1430438400000,
              "value_as_string": "2015-05"
           }
        }
     ]