如何在ElasticSearch中的聚合字段上执行复杂查询

认为以下内容应有所帮助。我的理解是，您希望首先基于进行分组ColumnA, ColumnB, ColumnC，计算clicks, views, earned1 and earned2字段的总和，然后应用所需的自定义聚合逻辑。

我已经能够提出以下查询，其中我使用了Bucket Selector Aggregation。

POST <your_index_name>/_search
{
  "size": 0, 
  "aggs": {
    "3": {
      "terms": {
        "field": "ColumnA",
        "order": {
          "_key": "desc"
        },
        "size": 50
      },
      "aggs": {
        "4": {
          "terms": {
            "field": "ColumnB",
            "order": {
              "_key": "desc"
            },
            "size": 50
          },
          "aggs": {
            "5": {
              "terms": {
                "field": "ColumnC",
                "order": {
                  "_key": "desc"
                },
                "size": 50
              },
              "aggs": {
                "sum_views": {
                  "sum": {
                    "field": "views"
                  }
                },
                "sum_clicks": {
                  "sum": {
                    "field": "clicks"
                  }
                },
                "sum_earned1": {
                  "sum": {
                    "field": "earned1"
                  }
                },
                "sum_earned2": {
                  "sum": {
                    "field": "earned2"
                  }
                },
                "custom_sum_bucket_filter": {
                  "bucket_selector": {
                    "buckets_path": {
                      "sum_of_views": "sum_views",
                      "sum_of_clicks": "sum_clicks",
                      "sum_of_earned1": "sum_earned1",
                      "sum_of_earned2": "sum_earned2"
                    },
                    "script": "(params.sum_of_views/params.sum_of_clicks) * (params.sum_of_earned1/params.sum_of_earned2) < 0.5"
                  }
                }
              }
            },
            "min_bucket_selector": {
              "bucket_selector": {
                "buckets_path": {
                  "valid_docs_count": "5._bucket_count"
                },
                "script": {
                  "source": "params.valid_docs_count >= 1"
                }
              }
            }
          }
        },
        "min_bucket_selector": {
          "bucket_selector": {
            "buckets_path": {
              "valid_docs_count": "4._bucket_count"
            },
            "script": {
              "source": "params.valid_docs_count >= 1"
            }
          }
        }
      }
    }
  }
}

请注意，要得到确切的结果，您正在寻找，我不得不在增加桶的过滤条件4和5。

我使用的聚合是

• 铲斗选择器以计算您提到的条件
• 再次选择存储桶选择器，以便在聚合5时不显示空存储桶
• 再次选择一个存储桶选择器，以便现在显示第4级的空存储桶聚合。

为了测试为什么我添加了其他空桶式过滤器，您可以将其删除并查看观察到的结果。

请注意，为简单起见，我忽略了query零件和cost字段。请随时添加它们并进行测试。