Aggregations 聚合总结

发表于 | 分类于

聚合分类

聚合，可以简单的理解为数据库操作中的 Distinct、 Group By ，count、sum、Having 等操作，当然，实际的功能还是有差别的。目前 Elasticsearch 中的聚合操作包含以下几种分类：

  • Bucket ，分桶
  • Metric ，⼀些系列的统计⽅法，包括 sum、max、min、avg 、stats 等
  • Matrix（弃用）
  • Pipeline，针对 “聚合” 的聚合。基于其他聚合的结果集

之前一直觉得聚合好难，然而真的读完，敲了两遍之后发现也就那样。。。主要还是因为之前看到的都是 bucket_selector 那种复杂操作，没能理解到。

1.Bucket & Metric 聚合分析及嵌套聚合

Aggregation 属于 Search 的 一部分。一般情况下，建议将其 Size 指定为 0，创建测试索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
DELETE /employees
PUT /employees/
{
  "mappings" : {
      "properties" : {
        "age" : {
          "type" : "integer"
        },
        "gender" : {
          "type" : "keyword"
        },
        "job" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 50
            }
          }
        },
        "name" : {
          "type" : "keyword"
        },
        "salary" : {
          "type" : "integer"
        }
      }
    }
}
# 添加测试数据
PUT /employees/_bulk
{ "index" : {  "_id" : "1" } }
{ "name" : "Emma","age":32,"job":"Product Manager","gender":"female","salary":35000 }
{ "index" : {  "_id" : "2" } }
{ "name" : "Underwood","age":41,"job":"Dev Manager","gender":"male","salary": 50000}
{ "index" : {  "_id" : "3" } }
{ "name" : "Tran","age":25,"job":"Web Designer","gender":"male","salary":18000 }
{ "index" : {  "_id" : "4" } }
{ "name" : "Rivera","age":26,"job":"Web Designer","gender":"female","salary": 22000}
{ "index" : {  "_id" : "5" } }
{ "name" : "Rose","age":25,"job":"QA","gender":"female","salary":18000 }
{ "index" : {  "_id" : "6" } }
{ "name" : "Lucy","age":31,"job":"QA","gender":"female","salary": 25000}
{ "index" : {  "_id" : "7" } }
{ "name" : "Byrd","age":27,"job":"QA","gender":"male","salary":20000 }
{ "index" : {  "_id" : "8" } }
{ "name" : "Foster","age":27,"job":"Java Programmer","gender":"male","salary": 20000}
{ "index" : {  "_id" : "9" } }
{ "name" : "Gregory","age":32,"job":"Java Programmer","gender":"male","salary":22000 }
{ "index" : {  "_id" : "10" } }
{ "name" : "Bryant","age":20,"job":"Java Programmer","gender":"male","salary": 9000}
{ "index" : {  "_id" : "11" } }
{ "name" : "Jenny","age":36,"job":"Java Programmer","gender":"female","salary":38000 }
{ "index" : {  "_id" : "12" } }
{ "name" : "Mcdonald","age":31,"job":"Java Programmer","gender":"male","salary": 32000}
{ "index" : {  "_id" : "13" } }
{ "name" : "Jonthna","age":30,"job":"Java Programmer","gender":"female","salary":30000 }
{ "index" : {  "_id" : "14" } }
{ "name" : "Marshall","age":32,"job":"Javascript Programmer","gender":"male","salary": 25000}
{ "index" : {  "_id" : "15" } }
{ "name" : "King","age":33,"job":"Java Programmer","gender":"male","salary":28000 }
{ "index" : {  "_id" : "16" } }
{ "name" : "Mccarthy","age":21,"job":"Javascript Programmer","gender":"male","salary": 16000}
{ "index" : {  "_id" : "17" } }
{ "name" : "Goodwin","age":25,"job":"Javascript Programmer","gender":"male","salary": 16000}
{ "index" : {  "_id" : "18" } }
{ "name" : "Catherine","age":29,"job":"Javascript Programmer","gender":"female","salary": 20000}
{ "index" : {  "_id" : "19" } }
{ "name" : "Boone","age":30,"job":"DBA","gender":"male","salary": 30000}
{ "index" : {  "_id" : "20" } }
{ "name" : "Kathy","age":29,"job":"DBA","gender":"female","salary": 20000}

Metric

指标统计，使用 Metric 统计工资的最大值、最小值、平均值示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GET employees/_search
{
  "size": 0,
  "aggs": {
    "max_salary": {
      "max": {"field": "salary"}
    },
    "min_salary": {
      "min": {"field": "salary"}
    },
    "avg_salary": {
      "avg": {"field": "salary"}
    }
  }
}

cardinality

基数统计（是近似值！！！），cardinality 聚合基于 HyperLogLog ++ 算法。获取不同的 job 的数量示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
GET employees/_search
{
  "size": 0,
  "aggs": {
    "distinct_job":{
      "cardinality": {
        "field": "job.keyword"
      }
    },
    "distinct_job2":{
      "cardinality": {
        "script":{
          "lang":"painless",
          "source":"doc.salary.value/doc.salary.value"
        }
      }
    }
  }
}
  • stats 返回 max、min、avg、sum、count ，extended_stats 会扩展出方差、标准偏差等更专业的统计信息；
  • percentiles、percentile_ranks 返回百分比（是近似值！！！）；
  • top_hits 需作为字聚合与 bucket aggs 结合使用；
  • value_count，统计某字段有值的数量，与 cardinality 的区别是不去重，默认忽略空值字段

Terms Aggregation

默认只能对 keyword 类型进行聚合，text 类型的字段需要打开 fielddata，才能进⾏ Terms Aggregation（也是近似值！！！）

Bucket Size & Top Hits 使用案例：（先根据工作分组，然后查询每种工作年龄前三的人员）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
GET employees/_search
{
  "size": 0,
  "aggs": {
    "job_bucket": {
      "terms": {
        "field":"job.keyword"
      },
      "aggs":{
        "old_employees":{
          "top_hits":{
            "size":3,
            "sort": {"age":"desc"}
          }
        }
      }
    }
  }
}
# terms 聚合，使用字聚合的结果 agg_name.xxx 进行排序
GET employees/_search
{
  "size": 0,
  "aggs": {
    "job_bucket": {
      "terms": {
        "field":"job.keyword",
        "order":{"salary_stats.max": "desc"}
      },
      "aggs":{
        "salary_stats":{
          "stats":{"field":"salary"}
        }
      }
    }
  }
}

优化 term 聚合的性能：在新建索引时，为字段 mapping 添加 eager_global_ordinals: true 参数

Range& Histogram Aggs

  • 按照数字的范围进行分桶
  • 在 range aggs 中，可以自定义 key

按照工资范围（Range）分桶示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
GET employees/_search
{
  "size": 0,
  "aggs": {
    "salary_range":{
      "range":{
        "field":"salary",
        "ranges":[
          {
            "to":10000
          },
          {
            "from":10000,
            "to":20000
          },
          {
            "key":">20000",
            "from":20000
          }
        ]
      }
    }
  }
}
# 多次嵌套。根据工作类型分桶，然后按照性别分桶，计算工资的统计信息
POST employees/_search
{
  "size": 0,
  "aggs": {
    "Job_gender_stats": {
      "terms": {
        "field": "job.keyword"
      },
      "aggs": {
        "gender_stats": {
          "terms": {
            "field": "gender"
          },
          "aggs": {
            "salary_stats": {
              "stats": {
                "field": "salary"
              }
            }
          }
        }
      }
    }
  }
}

按照工资的间隔（Histogram）分桶示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#Salary Histogram,工资0到10万，以 5000一个区间进行分桶
POST employees/_search
{
  "size": 0,
  "aggs": {
    "salary_histrogram": {
      "histogram": {
        "field":"salary",
        "interval":5000,
        "min_doc_count":1,
        "extended_bounds":{
          "min":0,
          "max":100000
        }
      }
    }
  }
}
# 按照月进行 histogram 聚合 
"date_histogram" : {
    "field" : "date",
    "calendar_interval" : "month"
}

filter

数据过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
GET shirts/_search
{
  "size": 0,
  "aggs": {
    "color_filter": {
      "filter": {
        "term":{
          "color":"red"
        }
      }
    }
  }
}
# 多 filter 过滤
PUT /logs/_bulk?refresh
{ "index" : { "_id" : 1 } }
{ "body" : "warning: page could not be rendered" }
{ "index" : { "_id" : 2 } }
{ "body" : "authentication error" }
{ "index" : { "_id" : 3 } }
{ "body" : "warning: connection timed out" }

GET logs/_search
{
  "size": 0,
  "aggs" : {
    "messages" : {
      "filters" : {
        "filters" : {
          "errors" :   { "match" : { "body" : "error"   }},
          "warnings" : { "match" : { "body" : "warning" }}
        }
      }
    }
  }
}

2. pipeline聚合

  • 指标型：avg_bucket、max_bucket、min_bucket、sum_bucket、stats_bucket、extended_stats_bucket。
  • 百分比：percentiles_bucket
  • 特殊场景，需要在 hostogram 的子聚合下才能执行：derivative 导数聚合，moving_fn 、cumulative_sum
  • 特殊操作：bucket_script、bucket_selector

min_bucket 和 stats_bucket 实例

按照工作类型分组，找出平均工资最低的工种

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 通过 . 或者 > 符号指定 buckets_path 来进行 pipeline 聚合
GET employees/_search
{
  "size":0,
  "aggs":{
    "job_bucket":{
      "terms": {
        "field": "job.keyword"
      },
      "aggs":{
        "avg_salary":{
          "avg": {
            "field": "salary"
          }
        }
      }
    },
    "salary_min_bucket":{
      "min_bucket": {
        "buckets_path": "job_bucket.avg_salary"
      }
    },
    "stats_salary":{
      "stats_bucket": {
        "buckets_path": "job_bucket>avg_salary"
      }
    }
  }
}

bucket_script 实例

计算每月的 T 桖销售额占总销售额的百分比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# total_sales 
POST /sales/_search
{
    "size": 0,
    "aggs" : {
        "sales_per_month" : {
            "date_histogram" : {
                "field" : "date",
                "calendar_interval" : "month"
            },
            "aggs": {
                "total_sales": {
                    "sum": {
                        "field": "price"
                    }
                },
                "t-shirts": {
                  "filter": {
                    "term": {
                      "type": "t-shirt"
                    }
                  },
                  "aggs": {
                    "sales": {
                      "sum": {
                        "field": "price"
                      }
                    }
                  }
                },
                "t-shirt-percentage": {
                    "bucket_script": {
                        "buckets_path": {
                          "tShirtSales": "t-shirts>sales",
                          "totalSales": "total_sales"
                        },
                        "script": "params.tShirtSales / params.totalSales * 100"
                    }
                }
            }
        }
    }
}

bucket_selector 实例

筛选每月的销售额大于 200 的月份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
POST /sales/_search
{
    "size": 0,
    "aggs" : {
        "sales_per_month" : {
            "date_histogram" : {
                "field" : "date",
                "calendar_interval" : "month"
            },
            "aggs": {
                "total_sales": {
                    "sum": {
                        "field": "price"
                    }
                },
                "sales_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                          "totalSales": "total_sales"
                        },
                        "script": "params.totalSales > 200"#这里只能使用 bucket_selector 使用 bucket_script 会报错
                    }
                }
            }
        }
    }
}

与其他的 pipeline 管道聚合一样，bucket_selector 聚合在所有其他同级聚合之后执行。这意味着使用 bucket_selector 聚合来过滤响应中返回的存储桶不会节省运行聚合的执行时间。

bucket_sort

使用子聚合结果：当月的总销售额，降序排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
POST /sales/_search
{
    "size": 0,
    "aggs" : {
        "sales_per_month" : {
            "date_histogram" : {
                "field" : "date",
                "calendar_interval" : "month"
            },
            "aggs": {
                "total_sales": {
                    "sum": {
                        "field": "price"
                    }
                },
                "sales_bucket_sort": {
                    "bucket_sort": {
                        "sort": [
                          {"total_sales": {"order": "desc"}}
                        ],
                        "size": 3
                    }
                }
            }
        }
    }
}

3. 聚合的作用范围

  • ES 聚合分析的默认作⽤范围是 query 的查询结果集
  • 同时 ES 还⽀持以下⽅式改变聚合的作⽤范围：filter、post_filter、global

filter 作用范围

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
GET employees/_search
{
  "size": 0,
  "aggs": {
    "old_person": {
      // filter只对当前的子聚合生效
      "filter": {
        "range": {
          "age": {
            "from": 35
          }
        }
      },
      "aggs": {
        "job_bucket": {
          "terms": {
            "field": "job.keyword"
          }
        }
      }
    },
    // all_jobs 还是针对所有数据
    "all_jobs":{
      "terms": {
        "field": "job.keyword"
      }
    }
  }
}

post_filter作用范围：

post_filter 用两种应用场景：

  • 结果中再次搜索（不推荐，会导致 filter_query 的缓存效果失效）
  • 聚合后！！！，再过滤返回的 hits 的结果集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 正常情况下，hits会返回所有的文档，post_filter 将 hits 结果集也限制为大于 35 岁的员工
GET employees/_search
{
  "size": 10,
  "aggs": {
    "old_person": {
      "filter": {
        "range": {
          "age": {
            "from": 35
          }
        }
      },
      "aggs": {
        "job_bucket": {
          "terms": {
            "field": "job.keyword"
          }
        }
      }
    },
    "all_jobs":{
      "terms": {
        "field": "job.keyword"
      }
    }
  },
  "post_filter": {
    "bool": {
        "filter": {
          "range": {
            "age": {
              "gte": 35
            }
          }
        }
      }
  }
}

global作用范围

尽管 query 已经限制了范围为 35 岁以上的员工，global_test 通过 global 将作用范围重新改为所有数据。（仅对当前子聚合生效）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
GET employees/_search
{
  "size": 10,
  "query": {
    "bool": {
      "filter": {
        "range": {
          "age": {
            "gte": 35
          }
        }
      }
    }
  },
  "aggs": {
    "old_person": {
      "terms": {
        "field": "job.keyword"
      }
    },
    "global_test": {
      "global": {},
      "aggs": {
        "all_jobs": {
          "terms": {
            "field": "job.keyword"
          }
        }
      }
    }
  }
}

4. tips

  • 经常使用到的聚合，Elasticsearch 会缓存下来；
  • 只返回聚合结果需要设置 size:0
  • 可以通过 meta 为聚合结果指定自定义的属性

返回结果将会带上 "meta": { "color": "blue" } 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
GET /twitter/_search
{
  "size": 0,
  "aggs": {
    "titles": {
      "terms": {
        "field": "title"
      },
      "meta": {
        "color": "blue"
      }
    }
  }
}

あなたが生きる、この世界に

あなたが生きる、この世界に-玛修.jpg

0%