【es】聚合查询

Fat春

已于 2022-04-19 19:55:23 修改

阅读量4.5k

点赞数 3

文章标签： elasticsearch

于 2022-04-19 15:43:17 首次发布

本文链接：https://blog.csdn.net/qq_28011629/article/details/124274228

版权

聚合查询

三种聚合分类：Bucket、Metrics、Pipeline

分桶聚合Bucket agregations

把具有相同属性的放到一起，类似SQL的group by；
例子：每个手机品牌看作一个桶。
在这里插入图片描述
品牌下的系列在分成一个桶，就是agregations嵌套查询

指标聚合Metrics agregations

一系列的指标
在这里插入图片描述
例子：按班级分桶，在按学科查询指标

管道聚合Pipeline agregations

概念：对聚合的结果二次聚合（）
分类：父级和兄弟级（嵌套关系还是同级关系）
语法：buckets_path
例子1：按商品分类进行聚合，在计算平均价格。在这个基础之上在次进行计算最低价格；
在这里插入图片描述

测试数据

mysql数据（供参考，没有实际用处）

在这里插入图片描述

/*
 Navicat Premium Data Transfer

 Source Server         : MyOwn
 Source Server Type    : MySQL
 Source Server Version : 50731
 Source Host           : 39.96.23.94:3306
 Source Schema         : msb_db

 Target Server Type    : MySQL
 Target Server Version : 50731
 File Encoding         : 65001

 Date: 29/07/2021 15:14:52
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `desc` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  `lv` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  `tags` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

SET FOREIGN_KEY_CHECKS = 1;

es数据

在这里插入图片描述

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

语法

基本语法

{
  "aggs": {// 当前是聚合查询，可以包含多个
    "<aggs_name>": {// 给当前聚合起个名字
      "<aggs_type>": {// 聚合的类型：嵌套、桶、指标
        "field": "<field_name>",// 聚合的字段名称
        "size": 10// (可选)返回前10个，不指定默认10
      }
    }
  },
  "size": 0,// (可选)his列表数据返回0条，不指定默认10
  "query" {}// (可选)查询条件
}

桶聚合：

按标签统计

{
  "aggs": {
    "tag_bucket": {
      "terms": {
        "field": "tags.keyword"// .keyword不被分词，和正排索引doc_values和field_data类型有关系
      }
    }
  }
}

降序排序，返回前3条

{
  "size": 0, 
  "aggs": {
    "tag_bucket": {// 给当前聚合起个名
      "terms": {
        "field": "tags.keyword",
        "size": 3,// 返回前3条
        "order": {// 增加排序
          "_count": "desc"// 降序
        }
      }
    }
  }
}

指标聚合：

查看所有指标

{
  "size": 0, 
  "aggs": {
    "price_stats": {
      "stats": {// 查看所有指标
        "field": "price"// price字段
      }
    }
  }
}

最贵、最便宜和平均价格…5个指标

{
  "size": 0, 
  "aggs": {
    "max_price": {// 给聚合起名：价格最大
      "max": {// max最大函数
        "field": "price"// 统计price字段
      }
    },
    "min_price": {// 给聚合起名：价格最小
      "min": {// min最小函数
        "field": "price"
      }
    },
    "avg_price": {// 给聚合起名：价格平均
      "avg": {// 平均值函数
        "field": "price"
      }
    },
    "count_price":{
      "value_count": {// 多少条
        "field": "price"
      }
    },
    "sum_price":{// 给聚合起名：价格求和
      "sum": {// 求和
        "field": "price"
      }
    }
  }
}

按名字去重统计

{
  "size": 0, 
  "aggs": {
    "name_count": {// 起名：按名字统计个数
      "cardinality": {// 去重关键字，相当于mysql中distinct
        "field": "name.keyword"
      }
    }
  }
}

管道聚合（二次聚合）：

平均价格最低的商品分类，先计算每个分类平均价格，在计算最低的价格的商品。
1、按类型分桶

{
  "size": 0, 
  "aggs": {
    "type_bucket": {// 起名：按类型分桶
      "terms": {// 分桶
        "field": "type.keyword"// 按类型字段
      }
    }
  }
}

2、按类型分桶，并计算平均价格

{
  "size": 0, 
  "aggs": {
    "type_bucket": {// 起名：按类型分桶
      "terms": {// 分桶
        "field": "type.keyword"// 按类型字段
      },
      "aggs": {// 桶内嵌套聚合，在type_bucket这级继续聚合
        "price_bucket": {// 起名：统计平均价格
          "avg": {// 平均
            "field": "price"// 价格字段
          }
        }
      }
    }
  }
}

3、按类型分桶，并计算平均价格，并且计算最低价格的商品

{
  "size": 0, 
  "aggs": {
    "type_bucket": {// 起名：按类型分桶
      "terms": {// 分桶
        "field": "type.keyword"// 按类型字段
      },
      "aggs": {// 桶内嵌套聚合
        "price_bucket": {// 起名：统计平均价格
          "avg": {// 平均
            "field": "price"// 价格字段
          }
        }
      }
    },
    "min_bucket":{// 起名：取最小的桶
      "min_bucket": {// es关键字，取最小的桶
        "buckets_path": "type_bucket>price_bucket"// es关键字buckets_path引用，取同级的type_bucket聚合下的price_bucket桶，里面最小的桶。
      }
    }
  }
}

嵌套聚合

语法

{
  "size": 0,
  "aggs": {
    "<agg_name>": {// 1级名
      "<agg_type>": {// 聚合类型
        "field": "<field_name>"// 聚合字段
      },
      "aggs": {
        "<agg_name_child>": {// 2级名
          "<agg_type>": {// 聚合类型
            "field": "<field_name>"// 聚合字段
          }
        }
      }
    }
  }
}

例子：

按类型分桶，在按级别分桶

{
  "size": 0, 
  "aggs": {
    "type_lv": {// 1级名
      "terms": {
        "field": "type.keyword"// 聚合字段
      },
      "aggs": {
        "lv": {// 2级名
          "terms": {
            "field": "lv.keyword"// 聚合字段
          }
        }
      }
    }
  }
}

按照lv分桶，输出每个桶的具体价格信息

{
  "size": 0, 
  "aggs": {
    "lv_price": {// 聚合名
      "terms": {
        "field": "lv.keyword"// 字段
      },
      "aggs": {// 上面聚合进行二次聚合
        "price": {// 聚合名
          "stats": {// 获取所有指标
            "field": "price"// 字段
          }
        }
      }
    }
  }
}

统计：不同类型商品>不同级别档次>价格信息、标签信息

{
  "size": 0, 
  "aggs": {
    "type_agg": {// 起名：按类型分桶
      "terms": {
        "field": "type.keyword"// 分桶字段
      },
      "aggs": {// 上面type.keyword结果进行二次聚合
        "lv_agg": {// 起名：按级别分桶
          "terms": {
            "field": "lv.keyword"// 分桶字段
          },
          "aggs": {// 上面lv.keyword结果进行二次聚合
            "price_stats": {// 起名：价钱指标
              "stats": {// 所有指标关键字
                "field": "price"// 价钱字段
              }
            },
            "tags_buckets": {// 起名：按标签分桶
              "terms": {
                "field": "tags.keyword"// 分桶字段
              }
            }
          }
        }
      }
    }
  }
}

统计：每个商品类型中>不同档次分类商品中>平均价格最低的档次

{
  "size": 0,
  "aggs": {
    "type_bucket": {// 按类型分桶
      "terms": {
        "field": "type.keyword"
      },
      "aggs": {// 二次聚合
        "lv_bucket": {// 按级别分桶
          "terms": {
            "field": "lv.keyword"
          },
          "aggs": {// 二次聚合
            "price_avg": {// 平均价格
              "avg": {
                "field": "price"
              }
            }
          }
        },
        "min_bucket": {// 当前lv_bucket级聚合下的price_avg级别，找最小桶
          "min_bucket": {
            "buckets_path": "lv_bucket>price_avg"
          }
        }
      }
    }
  }
}

例子：基于查询结果的聚合和基于聚合结果的查询

基于查询结果的聚合

基于query，价格大于2000的商品类型

{
  "size": 0, 
  "query": {
    "range": {
      "price": {
        "gte": 2000
      }
    }
  }, 
  "aggs": {
    "type_bucket": {
      "terms": {
        "field": "type.keyword"
      }
    }
  }
}

基于filter的比query效率高

{
  "size": 0,
  "query": {
    "bool": {
      "filter": {// 基于filter效率高
        "range": {
          "price": {
            "gte": 2000
          }
        }
      }
    }
  },
  "aggs": {
    "type_bucket": {
      "terms": {
        "field": "type.keyword"
      }
    }
  }
}

基于聚合结果的查询

根据标签分桶后，过滤标签=性价比的数据

{
  "aggs": {
    "tags_bucket": {
      "terms": {
        "field": "tags.keyword"
      }
    }
  },
  "post_filter": {
    "term": {
      "tags.keyword": "性价比"
    }
  }
}

统计价格>4000的平均价格 && 统计所有的平均价格

{
  "size": 0,
  "query": {// 价格>4000
    "range": {
      "price": {
        "gte": 4000
      }
    }
  },
  "aggs": {
    "avg_price": {// 价格>4000的平均价格
      "avg": {
        "field": "price"
      }
    },
    "all_avg_price": {// 所有平均价格
      "global": {},// 当前聚合中，清楚query代码块，查询所有的数据
      "aggs": {
        "avg_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    }
  }
}

聚合排序

排序

{
  "size": 0,
  "aggs": {
    "type_aggs": {
      "terms": {
        "field": "tags.keyword",// tags字段分桶
        "order": {// 类似sql的order by
          "_count": "desc"// 降序，按value排序
          //"_term": "desc"// 降序，按key排序
          //"_key": "desc"// 降序，代替_term
        },
        "size": 3// 前3条
      }
    }
  }
}

多级聚合排序，第1桶聚合按type降序第2桶聚合按lv升序

{
  "aggs": {
    "first_sort": {
      "terms": {
        "field": "type.keyword",
        "order": {
          "_count": "desc"
        }
      },
      "aggs": {
        "second_sort": {
          "terms": {
            "field": "lv.keyword",
            "order": {
              "_count": "asc"
            }
          }
        }
      }
    }
  }
}

多层排序，第1桶聚合按第2桶stats聚合的sum排序

{
  "size": 0,
  "aggs": {
    "type_avg_price": {// 类型分桶
      "terms": {
        "field": "type.keyword",
        "order": {// 排序
          "agg_stats>stats.avg": "desc"// 按下一级agg_stats里面的stats.avg字段排序
        }
      },
      "aggs": {
        "agg_stats": {// 二次分桶按指标
          "filter": {
            "terms": {// 过滤 type是"耳机","手机","电视"
              "type.keyword": [
                "耳机","手机","电视"
              ]
            }
          },
          "aggs": {
            "stats": {// 全部聚合
              "extended_stats": {
                "field": "price"
              }
            }
          }
        }
      }
    }
  }
}

常用聚合函数

在这里插入图片描述

histogram：直方图或柱状图统计

按价格范围统计

{
  "size": 0, 
  "aggs": {
    "price_range": {// 聚合起名
      "range": {// 区间
        "field": "price",// 按price字段
        "ranges": [// 范围值
          {
            "from": 0,// 可省略
            "to": 1000
          },
          {
            "from": 1000,
            "to": 2000
          },
          {
            "from": 3000,
            "to": 4000
          },
          {
            "from": 4000,
            "to": 5000// 可省略
          }
        ]
      }
    }
  }
}

按日期区间统计

{
  "aggs": {
    "price_range": {
      "range": {
        "field": "createtime",
        "ranges": [
          {
            "from": "2020-05-01", 
            "to": "2020-05-31"
          },
          {
            "from": "2020-06-01",
            "to": "2020-06-30"
          },
          {
            "from": "2020-07-01",
            "to": "2020-07-31"
          },
          {
            "from": "2020-08-01"
          }
        ]
      }
    }
  }
}

高级点

按price字段，每隔1000为区间进行分段

{
  "aggs": {
    "price_histogram": {
      "histogram": {
        "field": "price",// 统计字段
        "interval": 1000,// 间隔1000
        "keyed": true,// 为true，桶就由数组变成对象，false就是显示数组。
        "min_doc_count": 0,// 显示大于多少的数据的桶
        "missing": 1999// 对字段的空值""赋值默认值
      }
    }
  }
}

date-histogram：基于日期的直方图，比如统计一年每个月的销售额

统计每个月的数据，没有数据的默认填充。
date_histogram

{
  "aggs": {
    "my_date_histogram": {// 起名
      "date_histogram": {// 按日期聚合关键字
        "field": "createtime",// 分桶字段
        "calendar_interval": "month",// 按月分桶hour、day、month、year
        "min_doc_count": 0,// 展示数据>0的桶
        "format": "yyyy-MM", // 日期格式化
        "extended_bounds": {// 扩展，展示没有数据的数据，前提："min_doc_count": 0或者去掉"min_doc_count"
          "min": "2020-01",// 时间最小值
          "max": "2020-12"// 时间最大值
        },
        "order": {// 排序
          "_count": "desc"
        }
      }
    }
  }
}

（了解即可，灵活度不高）自动interval时间统计 auto_date_histogram
buckets设置的桶的数量，会自动变统计单位，可以是月、天等…

{
  "aggs": {
    "my_auto_histogram": {
      "auto_date_histogram": {// 自动时间统计
        "field": "createtime",// 统计字段
        "format": "yyyy-MM-dd",// 日期格式化
        "buckets": 180// 分几个桶，12就是按月，365就是按天
      }
    }
  }
}

buckets=12就是按月
在这里插入图片描述
buckets=180就是按天

percentile 百分位统计或者饼状图（管道聚合、累加聚合）

在这里插入图片描述

累加函数cumulative_sum
求每个月的销售额，每个月累加销售额。

{
  "aggs": {
    "my_date_histogram": {// 起名
      "date_histogram": {// 日期分桶
        "field": "createtime",// 分桶字段
        "calendar_interval": "month",// 按月分桶
        "min_doc_count": 0,// >0的桶展示
        "format": "yyyy-MM", // 日期格式化
        "extended_bounds": {// 扩展，补充展示没有数据的桶
          "min": "2020-01",// 补充区间
          "max": "2020-12"// 补充区间
        }
      },
      "aggs": {// 二次聚合
        "sum_agg": {// 起名
          "sum": {// 求和
            "field": "price"// 字段
          }
        },
        "my_cumulative_sum":{// 起名
          "cumulative_sum": {// 累计
            "buckets_path": "sum_agg"// 指定字段进行累加，小计
          }
        }
      }
    }
  }
}

百分比函数percentile_ranks
统计数据分布情况，当前价格分布百分比

{
  "aggs": {
    "price_percentiles": {// 起名
      "percentile_ranks": {// 百分比
        "field": "price",// 字段
        "values": [
          1000,// <=1000以内占百分之几
          2000,
          3000,// <=3000以内占百分之几
          4000,
          5000,
          6000// <=6000以内占百分之几
        ]
      }
    }
  }
}

百分比percentiles
价格分布百分比，

{
  "aggs": {
    "price_percentiles": {
      "percentiles": {// 
        "field": "price",// 字段
        "percents": [
          1,// 百分之1的价格在多少以内
          5,
          25,// 百分之25的价格在多少以内
          50,
          75,
          95,
          99// 百分之99的价格在多少以内
        ]
      }
    }
  }
}