Elasticsearch学习-- 聚合查询

刺骨的言语ヽ痛彻心扉 2024-04-08 13:11 21阅读 0赞

### 一、聚合分类 ###

#### 1. 分桶聚合 bucket aggregations ####

按照每个标签进行分类 ,类似于group by

#### ![04e347b77a40422d803beb119a99a459.png][] ####

![acfc34db265a4ef6983bcd44c5e362f7.png][]

![9f71b4b3d9af4a39bcbee12cb5a02edd.png][]

#### 2. 指标聚合 metrics aggregations ####

![ad81de6bc7a34e199329271126ad9fa2.png][]

#### 3. 管道聚合 pipeline aggregations ####

![6b182882820e4764af05b4b358e8825b.png][]

先计算平均值，再计算最小值

![47c45c92e3e84ae087172aa130cbda2f.png][]

### 二、语法 ###

GET test_index/_search
    {
      "aggs": {
        "聚合后返回的字段": {
          "avg": {
            "聚合的字段": ""
          }
        },
        "聚合后返回的字段2": {
          "avg": {
            "聚合的字段": ""
          }
        }
      }
    }

默认查询返回结果是10条，可以通过设置size来看返回值数量

GET test_index/_search?size=20

GET test_index/_search
    {
      "size":20
    }

### 三、桶聚合 ###

#### 1. 统计不同标签的商品数量 ####

PUT product/_doc/1
    {
      "name":"prodect1 aa",
      "price": 1000,
      "tags":["tag1","tag2","tag3"]
    }
    
    PUT product/_doc/2
    {
      "name":"prodect2 bb",
      "price": 2000,
      "tags":["tag2","tag3"]
    }
    
    PUT product/_doc/3
    {
      "name":"prodect3 bb",
      "price": 2000,
      "tags":["tag4"]
    }
    
    PUT product/_doc/4
    {
      "name":"prodect4 bb",
      "price": 3000,
      "tags":["tag1","tag4"]
    }
    
    PUT product/_doc/5
    {
      "name":"prodect5 aa",
      "price": 3000,
      "tags":["tag1","tag5"]
    }
    
    PUT product/_doc/6
    {
      "name":"prodect6 aa",
      "price": 4000,
      "tags":["tag1","tag2"]
    }
    
    GET product/_search
    {
      "size": 0, 
      "aggs": {
        "agg_tag": {
          "terms": {
            "field": "tags.keyword",
            "size": 10,
            "order": {
              "_count": "asc"
            }
          }
        }
      }
    }

![18501da470304d96b0d38c19b1584d69.png][]

#### 2. 为什么上面使用tags.keyword ####

因为聚合查询使用的是doc\_values的正排索引，tags.keyword有正排索引

也可以通过设置fileddata属性进行正排索引

#### 3. fileddata ####

修改mapping中tags字段的fileddata属性为true

POST product/_mapping
    {
      "properties":{
        "tags":{
          "type":"text",
          "fielddata":true
        }
      }
    }

这样就可以使用tags直接进行聚合操作

GET product/_search
    {
      "size": 0, 
      "aggs": {
        "agg_tag": {
          "terms": {
            "field": "tags",
            "size": 10,
            "order": {
              "_count": "asc"
            }
          }
        }
      }
    }

![31411dbf58ef4603812e5c511e1fa4c8.png][]

#### 4. doc\_values和field\_data的区别 ####

doc\_values和filed\_data都可以用于聚合查询，

doc\_vaules是基于磁盘的，filed\_data是基于内存的

数据量比较大时，尽量不要使用filed\_data

### 四、指标聚合 ###

#### 1. 统计商品最贵、最便宜、平均价格 ####

GET product/_search
    {
      "size": 0,
      "aggs": {
        "max_price": {
          "max": {
            "field": "price"
          }
        },
        "min_price": {
          "min": {
            "field": "price"
          }
        },
        "avg_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    }

![a626798b16a941c68c9aa6d1be034013.png][]

#### 2. stats 查看所有指标 ####

# 查看price的所有指标
    GET product/_search
    {
      "size": 0,
      "aggs": {
        "stats_price": {
          "stats": {
            "field": "price"
          }
        }
      }
    }

![dcbfe2e92a2c4f1c8b1be8449f01e54d.png][]

#### 3. cardinality去重后的数量 ####

# 按照price字段进行去重后的数量
    GET product/_search
    {
      "_source": false, 
      "aggs": {
        "price_count": {
          "cardinality": {
            "field": "price"
          }
        }
      }
    }

![a6dd4757785749aab38b9502794bc6d0.png][]

### 五、管道聚合（二次聚合） ###

统计平均价格最低的商品分类

1）先对tags进行分桶

2）对分桶后的tags取平均price（在哪个的基础上进行操作，就放在同一级）

3）对上面的平均价格取最小平均价格的标签

# 统计平均价格最低的商品分类
    GET product/_search
    {
      "size": 0,
      "aggs": {
        "tags_bucket": {
          "terms": {
            "field": "tags.keyword"
          },
          "aggs":{
            "avg_price_bucket":{
              "avg": {
                "field": "price"
              }
            }
          }
        },
        "min_price_bucket":{
          "min_bucket": {
            "buckets_path": "tags_bucket>avg_price_bucket"
          }
        }
      }
    }

![78f21ac2027e41738281b91495251ab8.png][]

### 六、基于聚合结果的聚合--案例 ###

#### 1. 准备数据 ####

11条商品信息

PUT /goods/_doc/1
    {
        "name" : "小米手机",
        "desc" :  "手机中的战斗机",
        "price" :  3999,
        "lv":"旗舰机",
        "type":"手机",
        "createtime":"2020-10-01T08:00:00Z",
        "tags": [ "性价比", "发烧", "不卡顿" ]
    }
    PUT /goods/_doc/2
    {
        "name" : "小米NFC手机",
        "desc" :  "支持全功能NFC，手机中的滑翔机",
        "price" :  4999,
            "lv":"旗舰机",
        "type":"手机",
        "createtime":"2020-05-21T08:00:00Z",
        "tags": [ "性价比", "发烧", "公交卡" ]
    }
    PUT /goods/_doc/3
    {
        "name" : "NFC手机",
        "desc" :  "手机中的轰炸机",
        "price" :  2999,
            "lv":"高端机",
        "type":"手机",
        "createtime":"2020-06-20",
        "tags": [ "性价比", "快充", "门禁卡" ]
    }
    PUT /goods/_doc/4
    {
        "name" : "小米耳机",
        "desc" :  "耳机中的黄焖鸡",
        "price" :  999,
            "lv":"百元机",
        "type":"耳机",
        "createtime":"2020-06-23",
        "tags": [ "降噪", "防水", "蓝牙" ]
    }
    PUT /goods/_doc/5
    {
        "name" : "红米耳机",
        "desc" :  "耳机中的肯德基",
        "price" :  399,
        "type":"耳机",
            "lv":"百元机",
        "createtime":"2020-07-20",
        "tags": [ "防火", "低音炮", "听声辨位" ]
    }
    PUT /goods/_doc/6
    {
        "name" : "小米手机10",
        "desc" :  "充电贼快掉电更快，超级无敌望远镜，高刷电竞屏",
        "price" :  "",
            "lv":"旗舰机",
        "type":"手机",
        "createtime":"2020-07-27",
        "tags": [ "120HZ刷新率", "120W快充", "120倍变焦" ]
    }
    PUT /goods/_doc/7
    {
        "name" : "挨炮 SE2",
        "desc" :  "除了CPU，一无是处",
        "price" :  "3299",
            "lv":"旗舰机",
        "type":"手机",
        "createtime":"2020-07-21",
        "tags": [ "割韭菜", "割韭菜", "割新韭菜" ]
    }
    PUT /goods/_doc/8
    {
        "name" : "XS Max",
        "desc" :  "听说要出新款12手机了，终于可以换掉手中的4S了",
        "price" :  4399,
            "lv":"旗舰机",
        "type":"手机",
        "createtime":"2020-08-19",
        "tags": [ "5V1A", "4G全网通", "大" ]
    }
    PUT /goods/_doc/9
    {
        "name" : "小米电视",
        "desc" :  "70寸性价比只选，不要一万八，要不要八千八，只要两千九百九十八",
        "price" :  2998,
            "lv":"高端机",
        "type":"耳机",
        "createtime":"2020-08-16",
        "tags": [ "巨馍", "家庭影院", "游戏" ]
    }
    PUT /goods/_doc/10
    {
        "name" : "红米电视",
        "desc" :  "我比上边那个更划算，我也2998，我也70寸，但是我更好看",
        "price" :  2999,
        "type":"电视",
            "lv":"高端机",
        "createtime":"2020-08-28",
        "tags": [ "大片", "蓝光8K", "超薄" ]
    }
    PUT /goods/_doc/11
    {
      "name": "红米电视",
      "desc": "我比上边那个更划算，我也2998，我也70寸，但是我更好看",
      "price": 2998,
      "type": "电视",
      "lv": "高端机",
      "createtime": "2020-08-28",
      "tags": [
        "大片",
        "蓝光8K",
        "超薄"
      ]
    }

#### 2. 统计不同lv下的不同type的数量 ####

# 统计不同lv下的不同type的数量
    GET goods/_search
    {
      "size": 0,
      "aggs": {
        "lv_type_agg": {
          "terms": {
            "field": "lv.keyword"
          },
          "aggs": {
            "type_agg": {
              "terms": {
                "field": "type.keyword"
              }
            }
          }
        }
      }
    }

![94c5f839df304521b073fb650a81b79f.png][]

#### 3. 统计不同lv的价格信息 ####

# 统计不同lv下价格信息
    GET goods/_search
    {
      "size": 0,
      "aggs": {
        "lv_type_agg": {
          "terms": {
            "field": "lv.keyword"
          },
          "aggs": {
            "price_agg": {
              "stats": {
                "field": "price"
              }
            }
          }
        }
      }
    }

![e5c3efde37984eafb6bfc1f13d88ee99.png][]

#### 4. 统计不同type下的，不同lv的，price信息 ####

#统计不同type下的，不同lv的，price信息
    GET goods/_search
    {
      "size": 0,
      "aggs": {
        "type_agg": {
          "terms": {
            "field": "type.keyword"
          },
          "aggs": {
            "lv_agg": {
              "terms": {
                "field": "lv.keyword"
              },
              "aggs": {
                "price_agg": {
                  "stats": {
                    "field": "price"
                  }
                }
              }
            }
          }
        }
      }
    }

![487331a419ff4816b9ae7effdaeaaf8b.png][]

**统计不同type下的，不同lv的，price信息和tag信息**

#统计不同type下的，不同lv的，price信息和tag信息
    GET goods/_search
    {
      "size": 0,
      "aggs": {
        "type_agg": {
          "terms": {
            "field": "type.keyword"
          },
          "aggs": {
            "lv_agg": {
              "terms": {
                "field": "lv.keyword"
              },
              "aggs": {
                "price_agg": {
                  "stats": {
                    "field": "price"
                  }
                },
                "tag_agg": {
                  "terms": {
                    "field": "tags.keyword"
                  }
                }
              }
            }
          }
        }
      }
    }

![565928e1fd6a49738f75b95207d7bb34.png][]

#### 5. 统计每个商品type中，不同的lv商品中，平均price最低的lv ####

# 统计每个商品type中，不同的lv商品中，平均price最低的lv
    GET goods/_search
    {
      "size": 0,
      "aggs": {
        "type_agg": {
          "terms": {
            "field": "type.keyword"
          },
          "aggs": {
            "lv_agg": {
              "terms": {
                "field": "lv.keyword"
              },
              "aggs": {
                "price_agg": {
                  "avg": {
                    "field": "price"
                  }
                }
              }
            },
            "min_price_bucket": {
              "min_bucket": {
                "buckets_path": "lv_agg>price_agg"
              }
            }
          }
        }
      }
    }

![4334f697dcc443c4a809027bcba6675a.png][]

### 七、基于查询结果的聚合和基于聚合结果的查询--案例 ###

#### 1. 基于查询结果的聚合 ####

1）查询price>4000的tags信息

GET goods/_search
    {
      "size": 10,
      "query": {
        "range": {
          "price": {
            "gte": 4000
          }
        }
      },
      "aggs": {
        "tags_bucket": {
          "terms": {
            "field": "tags.keyword"
          }
        }
      }
    }

![a5027853bc0c4b56935878ce25e14ce0.png][]

2) 基于filter

与上面的结果一样

GET goods/_search
    {
      "size": 10,
      "query": {
        "constant_score": {
          "filter": {
            "range": {
              "price": {
                "gte": 4000
              }
            }
          }
        }
      },
      "aggs": {
        "tags_bucket": {
          "terms": {
            "field": "tags.keyword"
          }
        }
      }
    }

#### 2. 基于聚合结果的查询 ####

使用post\_filter

GET goods/_search
    {
      "size": 10,
      "aggs": {
        "tags_bucket": {
          "terms": {
            "field": "tags.keyword"
          }
        }
      },
      "post_filter": {
        "term": {
          "tags.keyword": "性价比"
        }
      }
    }

GET goods/_search
    {
      "size": 10,
      "query": {
        "term": {
          "tags.keyword": {
            "value": "性价比"
          }
        }
      }, 
      "aggs": {
        "tags_bucket": {
          "terms": {
            "field": "tags.keyword"
          }
        }
      }
    }

以上两个语句，查询结果相同

1）查询price>4000的平均price和所有商品的平均price

#查询price>4000的平均price和所有商品的平均price
    GET goods/_search
    {
      "size": 0,
      "query": {
        "range": {
          "price": {
            "gte": 4000
          }
        }
      }, 
      "aggs": {
        "avg_price": {
          "avg": {
            "field": "price"
          }
        },
        "all_avg_price":{
          "global": {},
          "aggs":{
            "avg_price2":{
              "avg": {
                "field": "price"
              }
            }
          }
        }
      }
    }

![e2db909c470d4a3fb7132eaae5b5f72c.png][]

2) 将上面的global换成filter的话，是与最上面的查询条件产生交集

GET goods/_search
    {
      "size": 0,
      "query": {
        "range": {
          "price": {
            "gte": 4000
          }
        }
      }, 
      "aggs": {
        "avg_price": {
          "avg": {
            "field": "price"
          }
        },
        "all_avg_price":{
          "filter": {
            "range": {
              "price": {
                "lte": 3000
              }
            }
          },
          "aggs":{
            "avg_price2":{
              "avg": {
                "field": "price"
              }
            }
          }
        }
      }
    }

![0cf57cbc27864e8896778c1351c2b69d.png][]

### 八、聚合排序 ###

#### 1. \_count ####

按照doc\_count排序

GET goods/_search
    {
      "size": 0,
      "aggs": {
        "tags_aggs": {
          "terms": {
            "field": "tags.keyword",
            "order": {
              "_count": "desc"
            }
          }
        }
      }
    }

![023e68c76ae04a6f854adf01ff2ad052.png][]

#### 2.\_term/\_key ####

\_term已经淘汰，按照key字典序排序

GET goods/_search
    {
      "size": 0,
      "aggs": {
        "tags_aggs": {
          "terms": {
            "field": "tags.keyword",
            "order": {
              "_key": "asc"
            }
          }
        }
      }
    }

![26f4bb0998654801961af8657657153a.png][]

#### 3. 多层聚合 ####

外层按照type的数量倒序排，里层按照lv的数量正序排

GET goods/_search
    {
      "size": 0,
      "aggs": {
        "type_aggs": {
          "terms": {
            "field": "type.keyword",
            "order": {
              "_count": "desc"
            }
          },
          "aggs": {
            "lv_aggs": {
              "terms": {
                "field": "lv.keyword",
                "order": {
                  "_count": "asc"
                }
              }
            }
          }
        }
      }
    }

![12da8badd97e401db35a1e86375c6fcf.png][]

**根据price\_stats中的最小值进行排序**

# 根据price_stats中的最小值进行排序
    GET goods/_search
    {
      "size": 0,
      "aggs": {
        "type_aggs": {
          "terms": {
            "field": "type.keyword",
            "order": {
              "filter_aggs>price_stats.min": "asc"
            }
          },
          "aggs": {
            "filter_aggs": {
              "filter": {
                "terms": {
                  "type.keyword": [
                    "耳机",
                    "手机",
                    "电视"
                  ]
                }
              },
              "aggs": {
                "price_stats": {
                  "stats": {
                    "field": "price"
                  }
                }
              }
            }
          }
        }
      }
    }

![f11503c3887b40c3a5576450fb058b00.png][]

### 九、图形化 ###

#### 1. 根据价格range分桶 ####

GET goods/_search
    {
      "size": 0,
      "aggs": {
        "price_range": {
          "range": {
            "field": "price",
            "ranges": [
              {
                "from": 0,
                "to": 1000
              },
              {
                "from": 1000,
                "to": 2000
              },
              {
                "from": 2000,
                "to": 3000
              },
              {
                "from": 3000,
                "to": 4000
              },
              {
                "from": 4000,
                "to": 5000
              }
            ]
          }
        }
      }
    }

![5b944ef1591c485c8b36a79388c18323.png][]

#### 2. histogram（直方图、柱状图） ####

根据price，间隔1000，跟上面的基本相同

GET goods/_search
    {
      "size": 0,
      "aggs": {
        "price_range": {
          "histogram": {
            "field": "price",
            "interval": 1000
          }
        }
      }
    }

![eec5b59ca113461da696896845f9b58f.png][]

跟field、interval同级的其他参数

keyed：设置true，表示将结果输出为key、value形式

min\_doc\_count：设置成1，表示输出结果doc\_value大于等于1的数据（过滤0）

missing：对空值赋默认值（设置成500，上图中表示，将price缺失的数据的price设置成500）

#### 3. date-histogram ####

专门针对日期类型的直方图

根据月份进行分桶，可以用format进行指定格式输出

GET goods/_search
    {
      "size": 0,
      "aggs": {
        "date_range": {
          "date_histogram": {
            "field": "createtime",
            "interval": "month"
          }
        }
      }
    }

![c7b7bb76819540f9b896550fafb184fe.png][]

可以设置**extended\_bounds**，展示没有的数据0

![1a0fb07b771f4c649f961ca641ac9334.png][]

#### 4. auto\_date\_histogram ####

自动设置interval，根据buckets

![97e871f3058943899f898ce1ab5f7b43.png][]

#### 5. cumulative\_sum（累加聚合） ####

### 十、percentile （百分位统计、饼状图） ###

#### 1. percentiles ####

![7269cd281d624ad5b15fe7b0f917f4cc.png][]

#### 2. percentile\_ranks ####

![0622d66e6aa14d59ac450cbb786268bd.png][]

[04e347b77a40422d803beb119a99a459.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/5bc1f779b23349eca92102ebb001278a.png
[acfc34db265a4ef6983bcd44c5e362f7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/d78ad1bfc6d0415380f46ab41f44b48a.png
[9f71b4b3d9af4a39bcbee12cb5a02edd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/b40077df56a744b789e5783c8ce631a3.png
[ad81de6bc7a34e199329271126ad9fa2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/efbc8ad793f24c68afde1a193dd168da.png
[6b182882820e4764af05b4b358e8825b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/ad5c413655cb4479b7af5e58992c82e5.png
[47c45c92e3e84ae087172aa130cbda2f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/5804a04042d444908d0f511cfa6b472e.png
[18501da470304d96b0d38c19b1584d69.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/cddaf4dbd12349b4a4f6f4a758b54763.png
[31411dbf58ef4603812e5c511e1fa4c8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/37f15b1da2254c42bc397576ee6f900d.png
[a626798b16a941c68c9aa6d1be034013.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/0cee033f8d654f419fc52c497fbd87ad.png
[dcbfe2e92a2c4f1c8b1be8449f01e54d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/e544d955879c4ad28d8b0ef4f676aaac.png
[a6dd4757785749aab38b9502794bc6d0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/85dc2e4a084b4e74991960c2d25f1d39.png
[78f21ac2027e41738281b91495251ab8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/33046a0d0be54c93ad506b3153d543ed.png
[94c5f839df304521b073fb650a81b79f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/dc1dd0d5bdc34a5284eb49620fbdf9f2.png
[e5c3efde37984eafb6bfc1f13d88ee99.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/4e53e538e2944a0d8ac9e08a68df8883.png
[487331a419ff4816b9ae7effdaeaaf8b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/142f6e57acbb44a6a8efc1aa1f15ac55.png
[565928e1fd6a49738f75b95207d7bb34.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/eaf5541b0cf64bebb722ec949071309a.png
[4334f697dcc443c4a809027bcba6675a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/7041de74f3cb4570a2814ab7a50ae226.png
[a5027853bc0c4b56935878ce25e14ce0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/d7b71014f920437ea2e5710d0d580880.png
[e2db909c470d4a3fb7132eaae5b5f72c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/3989468a5b844ba787b22b9caaa75d56.png
[0cf57cbc27864e8896778c1351c2b69d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/f1d9a81fb66f40af89ac6f0d1393445a.png
[023e68c76ae04a6f854adf01ff2ad052.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/329b85b6893b442c8f2204652efab215.png
[26f4bb0998654801961af8657657153a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/6e2dd40bec3f4ad0ac1cb64185b5a8bf.png
[12da8badd97e401db35a1e86375c6fcf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/30c16a5262434ffb842415e8bae35e32.png
[f11503c3887b40c3a5576450fb058b00.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/ca4af469ac4e42d8b897f119f1601041.png
[5b944ef1591c485c8b36a79388c18323.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/fd3e5ed3ca1549709c5f196e3f321a19.png
[eec5b59ca113461da696896845f9b58f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/14c590976fde4aa284d34c9f861b1524.png
[c7b7bb76819540f9b896550fafb184fe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/12185134c8aa49d4be157df9fc15ef06.png
[1a0fb07b771f4c649f961ca641ac9334.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/3565aabd3c0945539a32888b1022c934.png
[97e871f3058943899f898ce1ab5f7b43.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/eee15b29d7274830a146d32343f02603.png
[7269cd281d624ad5b15fe7b0f917f4cc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/37dc7c5ba8dc4f92beedf9170ebfaeee.png
[0622d66e6aa14d59ac450cbb786268bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/8821c78396b745588656fb909d467e39.png