AI 助理
备案控制台
开发者社区 数据库 文章 正文

Elasticsearch聚合深入详解——对比Mysql实现

2019-07-04 419
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
检索分析服务 Elasticsearch 版，2核4GB开发者规格 1个月
推荐场景：
数据可视化分析航班信息
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
简介： Elasticsearch聚合深入详解——对比Mysql实现。

聚合认知前提

桶（Buckets）——满足特定条件的文档的集合
指标（Metrics）——对桶内的文档进行统计计算

SELECT COUNT(color)
FROM table
GROUP BY color

COUNT(color) 相当于指标。
GROUP BY color 相当于桶。

image.png

一、聚合起步

1、创建索引

1.1 创建索引DSL实现

put cars
POST /cars/transactions/_bulk
{ "index": {}}
{ "price" : 10000, "color" : "red", "make" : "honda", "sold" : "2014-10-28" }
{ "index": {}}
{ "price" : 20000, "color" : "red", "make" : "honda", "sold" : "2014-11-05" }
{ "index": {}}
{ "price" : 30000, "color" : "green", "make" : "ford", "sold" : "2014-05-18" }
{ "index": {}}
{ "price" : 15000, "color" : "blue", "make" : "toyota", "sold" : "2014-07-02" }
{ "index": {}}
{ "price" : 12000, "color" : "green", "make" : "toyota", "sold" : "2014-08-19" }
{ "index": {}}
{ "price" : 20000, "color" : "red", "make" : "honda", "sold" : "2014-11-05" }
{ "index": {}}
{ "price" : 80000, "color" : "red", "make" : "bmw", "sold" : "2014-01-01" }
{ "index": {}}
{ "price" : 25000, "color" : "blue", "make" : "ford", "sold" : "2014-02-12" }

1.2 创建mysql库表sql实现

CREATE TABLE `cars` (
  `id` int(11) NOT NULL,
  `price` int(11) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  `make` varchar(255) DEFAULT NULL,
  `sold` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、统计不同颜色车的数目

2.1 统计不同颜色车的DSL实现

GET /cars/transactions/_search
{
  "size":0,
  "aggs":{
  "popular_colors" : {
  "terms":{
  "field": "color.keyword"
  }
  }
  }
}

返回结果：

lve

2.2 统计不同颜色的mysql实现

select color, count(color) as cnt from cars group by color order by cnt desc;

返回结果：

red 4
green 2
blue 2

3、统计不同颜色车的平均价格

3.1 统计不同颜色车的平均价格DSL实现：

GET /cars/transactions/_search
{
  "size":0,
  "aggs":{
  "colors" : {
  "terms":{
  "field": "color.keyword"
  },
  "aggs":{
  "avg_price":{
  "avg": {
  "field": "price"
  }
  }
  }
  }
  }
}

返回聚合结果：

lve

3.2 统计不同颜色车的平均价格sql实现：

select color, count(color) as cnt, avg(price) as avg_price from cars group by color order by cnt desc;

color cnt avg_price
red 4 32500.0000
green 2 21000.0000
blue 2 20000.0000

4、每种颜色汽车制造商的分布

4.1 统计每种颜色汽车制造商的分布dsl实现

GET /cars/transactions/_search
{
  "size":0,
  "aggs":{
  "colors" : {
  "terms":{
  "field": "color.keyword"
  },
  "aggs":{
  "make":{
  "terms":{
  "field": "make.keyword"
  }
  }
  }
  }
  }
}

返回结果：

4.2 统计每种颜色汽车制造商的分布sql实现

说明：和dsl的实现不严格对应

select color, make from cars order by color;

color make
blue toyota
blue ford
green ford
green toyota
red bmw
red honda
red honda
red honda

5、统计每个制造商的最低价格、最高价格

5.1 统计每个制造商的最低、最高价格的DSL实现

GET /cars/transactions/_search
{
  "size":0,
  "aggs":{
  "make_class" : {
  "terms":{
  "field": "make.keyword"
  },
  "aggs":{
  "min_price":{
  "min":{
  "field": "price"
  }
  },
  "max_price":{
  "max":{
  "field": "price"
  }
  }
  }
  }
  }
}

聚合结果：

5.2 统计每个制造商的最低、最高价格的sql实现

select make, min(price) as min_price, max(price) as max_price from cars group by make;

make min_price max_price
bmw 80000 80000
ford 25000 30000
honda 10000 20000
toyota 12000 15000

二、聚合进阶

1、条形图聚合

1.1 分段统计每个区间的汽车销售价格总和

GET /cars/transactions/_search
{
  "size":0,
  "aggs":{
  "price" : {
  "histogram":{
  "field": "price",
  "interval": 20000
  },
  "aggs":{
  "revenue":{
  "sum":{
  "field": "price"
  }
  }
  }
  }
  }
}

汽车销售价格区间：定义为20000；
分段统计price和用sum统计。

1.2 多维度度量不同制造商的汽车指标

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs": {
  "makes": {
  "terms": {
  "field": "make.keyword",
  "size": 10
  },
  "aggs": {
  "stats": {
  "extended_stats": {
  "field": "price"
  }
  }
  }
  }
  }
}

输出截取片段：

{
  "key": "ford",
  "doc_count": 2,
  "stats": {
  "count": 2,
  "min": 25000,
  "max": 30000,
  "avg": 27500,
  "sum": 55000,
  "sum_of_squares": 1525000000,
  "variance": 6250000,
  "std_deviation": 2500,
  "std_deviation_bounds": {
  "upper": 32500,
  "lower": 22500
  }
  }
  }

2、按时间统计聚合

2.1 按月份统计制造商汽车销量dsl实现

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs": {
  "sales":{
  "date_histogram":{
  "field":"sold",
  "interval":"month",
  "format":"yyyy-MM-dd"
  }
  }
  }
}

返回结果：

2.2 按月份统计制造商汽车销量sql实现

SELECT make, count(make) as cnt, CONCAT(YEAR(sold),',',MONTH(sold)) AS data_time
FROM `cars`
GROUP BY YEAR(sold) DESC,MONTH(sold)

查询结果如下：
make cnt data_time
bmw 1 2014,1
ford 1 2014,2
ford 1 2014,5
toyota 1 2014,7
toyota 1 2014,8
honda 1 2014,10
honda 2 2014,11

2.3 包含12月份的处理DSL实现

以上2.1 中没有12月份的统计结果显示。

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs": {
  "sales":{
  "date_histogram":{
  "field":"sold",
  "interval":"month",
  "format":"yyyy-MM-dd",
  "min_doc_count": 0,
  "extended_bounds":{
  "min":"2014-01-01",
  "max":"2014-12-31"
  }
  }
  }
  }
}

2.4 以季度为单位统计DSL实现

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs": {
  "sales":{
  "date_histogram":{
  "field":"sold",
  "interval":"quarter",
  "format":"yyyy-MM-dd",
  "min_doc_count": 0,
  "extended_bounds":{
  "min":"2014-01-01",
  "max":"2014-12-31"
  }
  },
  "aggs":{
  "per_make_sum":{
  "terms":{
  "field": "make.keyword"
  },
  "aggs":{
  "sum_price":{
  "sum":{ "field": "price"}
  }
  }
  },
  "top_sum": {
  "sum": {"field":"price"}
  }
  }
  }
  }
  }

2.5 基于搜索的（范围限定）聚合操作

2.5.1 基础查询聚合

GET /cars/transactions/_search
{
  "query" : {
  "match" : {
  "make.keyword" : "ford"
  }
  },
  "aggs" : {
  "colors" : {
  "terms" : {
  "field" : "color.keyword"
  }
  }
  }
}

对应的sql实现：

select make, color from cars
where make = "ford";

结果返回如下：

make color
ford green
ford blue

三、过滤聚合

1. 过滤操作

统计全部汽车的平均价钱以及单品平均价钱；

GET /cars/transactions/_search
{
  "size" : 0,
  "query" : {
  "match" : {
  "make.keyword" : "ford"
  }
  },
  "aggs" : {
  "single_avg_price": {
  "avg" : { "field" : "price" }
  },
  "all": {
  "global" : {},
  "aggs" : {
  "avg_price": {
  "avg" : { "field" : "price" }
  }

  }
  }
  }
}

等价于：

select make, color, avg(price) from cars
where make = "ford" ;
select avg(price) from cars;

2、范围限定过滤（过滤桶）

我们可以指定一个过滤桶，当文档满足过滤桶的条件时，我们将其加入到桶内。

GET /cars/transactions/_search
{
  "size" : 0,
  "query":{
  "match": {
  "make": "ford"
  }
  },
  "aggs":{
  "recent_sales": {
  "filter": {
  "range": {
  "sold": {
  "from": "now-100M"
  }
  }
  },
  "aggs": {
  "average_price":{
  "avg": {
  "field": "price"
  }
  }
  }
  }
  }
}

mysql的实现如下：

select *, avg(price) from cars where period_diff(date_format(now() , '%Y%m') , date_format(sold, '%Y%m')) > 30
and make = "ford";

mysql查询结果如下：

id price color make sold avg
3 30000 green ford 2014-05-18 27500.0000

3、后过滤器

只过滤搜索结果，不过滤聚合结果——post_filter实现

GET /cars/transactions/_search
{
  "query": {
  "match": {
  "make": "ford"
  }
  },
  "post_filter": {
  "term" : {
  "color.keyword" : "green"
  }
  },
  "aggs" : {
  "all_colors": {
  "terms" : { "field" : "color.keyword" }
  }
  }
}

post_filter 会过滤搜索结果，只展示绿色 ford 汽车。这在查询执行过 后 发生，所以聚合不受影响。

小结

选择合适类型的过滤（如：搜索命中、聚合或两者兼有）通常和我们期望如何表现用户交互有关。选择合适的过滤器（或组合）取决于我们期望如何将结果呈现给用户。

  • 在 filter 过滤中的 non-scoring 查询，同时影响搜索结果和聚合结果。
  • filter 桶影响聚合。
  • post_filter 只影响搜索结果。

四、多桶排序

4.1 内置排序

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs" : {
  "colors" : {
  "terms" : {
  "field" : "color.keyword",
  "order": {
  "_count" : "asc"
  }
  }
  }
  }
}

4.2 按照度量排序

以下是按照汽车平均售价的升序进行排序。
过滤条件：汽车颜色；
聚合条件：平均价格；
排序条件：汽车的平均价格升序。

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs" : {
  "colors" : {
  "terms" : {
  "field" : "color.keyword",
  "order": {
  "avg_price" : "asc"
  }
  },
  "aggs": {
  "avg_price": {
  "avg": {"field": "price"}
  }
  }
  }
}
}

多条件聚合后排序如下所示：

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs" : {
  "colors" : {
  "terms" : {
  "field" : "color.keyword",
  "order": {
  "stats.variance" : "asc"
  }
  },
  "aggs": {
  "stats": {
  "extended_stats": {"field": "price"}
  }
  }
  }
  }
}

4.3 基于“深度”的度量排序

太复杂，不推荐！

五、近似聚合

cardinality的含义是“基数”；

5.1 统计去重后的数量

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs" : {
  "distinct_colors" : {
  "cardinality" : {
  "field" : "color.keyword"
  }
  }
  }
}

类似于：

SELECT COUNT(DISTINCT color)  FROM cars;

以下：
以月为周期统计；

GET /cars/transactions/_search
{
  "size" : 0,
  "aggs" : {
  "months" : {
  "date_histogram": {
  "field": "sold",
  "interval": "month"
  },
  "aggs": {
  "distinct_colors" : {
  "cardinality" : {
  "field" : "color.keyword"
  }
  }
  }
  }
  }
}

六、doc values解读

在 Elasticsearch 中，doc values 就是一种列式存储结构，默认情况下每个字段的 doc values 都是激活的，doc values 是在索引时创建的，当字段索引时，Elasticsearch 为了能够快速检索，会把字段的值加入倒排索引中，同时它也会存储该字段的 doc values。
Elasticsearch 中的 doc vaules 常被应用到以下场景：

- 1）对一个字段进行排序
- 2）对一个字段进行聚合
- 3）某些过滤，比如地理位置过滤
- 4） 某些与字段相关的脚本计算

因为文档值被序列化到磁盘，我们可以依靠操作系统的帮助来快速访问。当 working set 远小于节点的可用内存，系统会自动将所有的文档值保存在内存中，使得其读写十分高速；

当其远大于可用内存，操作系统会自动把 doc values 加载到系统的页缓存中，从而避免了 jvm 堆内存溢出异常。

作者：铭毅天下
转载请标明出处，原文地址：
http://blog.csdn.net/laoyang360/article/details/79048455

文章标签：
云数据库 RDS MySQL 版
检索分析服务 Elasticsearch版
SQL
关系型数据库
索引
MySQL
数据库
关键词：
检索分析服务 Elasticsearch版聚合
云数据库 RDS MySQL 版聚合
云数据库 RDS MySQL 版elasticsearch
检索分析服务 Elasticsearch版mysql
elasticsearch云数据库 RDS MySQL 版
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
开发者社区
目录
相关文章
warmhearted
|
2月前
|
存储 SQL 监控
Elasticsearch 在日志分析中的应用聚合分析
【10月更文挑战第23天】
warmhearted
66 8
肾透侧视攻城狮
|
2月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（5）作者——LJS[含MySQL DQL基本查询:select；简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
肾透侧视攻城狮
156 5
糜终
|
3月前
|
关系型数据库 MySQL API
MySQL 历史数据迁移到 Elasticsearch
MySQL 历史数据迁移到 Elasticsearch
糜终
126 4
肾透侧视攻城狮
|
2月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（7）作者——LJS[含MySQL 聚合、数学、字符创、日期、控制流函数等使用详解；注意点及常见报错问题所对应的解决方法]
MySQL 聚合、数学、字符创、日期、控制流函数等使用详解；注意点及常见报错问题所对应的解决方法
肾透侧视攻城狮
39 0
qhhf_地道酬勤
|
4月前
|
NoSQL 关系型数据库 Redis
mall在linux环境下的部署（基于Docker容器），Docker安装mysql、redis、nginx、rabbitmq、elasticsearch、logstash、kibana、mongo
mall在linux环境下的部署（基于Docker容器），docker安装mysql、redis、nginx、rabbitmq、elasticsearch、logstash、kibana、mongodb、minio详细教程，拉取镜像、运行容器
qhhf_地道酬勤
660 3
mall在linux环境下的部署（基于Docker容器），Docker安装mysql、redis、nginx、rabbitmq、elasticsearch、logstash、kibana、mongo
最好zzz
|
3月前
|
消息中间件 监控 关系型数据库
MySQL数据实时同步到Elasticsearch：技术深度解析与实践分享
在当今的数据驱动时代，实时数据同步成为许多应用系统的核心需求之一。MySQL作为关系型数据库的代表，以其强大的事务处理能力和数据完整性保障，广泛应用于各种业务场景中。然而，随着数据量的增长和查询复杂度的提升，单一依赖MySQL进行高效的数据检索和分析变得日益困难。这时，Elasticsearch（简称ES）以其卓越的搜索性能、灵活的数据模式以及强大的可扩展性，成为处理复杂查询需求的理想选择。本文将深入探讨MySQL数据实时同步到Elasticsearch的技术实现与最佳实践。
最好zzz
228 0
程序员小海绵
|
4月前
|
存储 自然语言处理 关系型数据库
ElasticSearch基础3——聚合、补全、集群。黑马旅游检索高亮+自定义分词器+自动补全+前后端消息同步
聚合、补全、RabbitMQ消息同步、集群、脑裂问题、集群分布式存储、黑马旅游实现过滤和搜索补全功能
程序员小海绵
166 0
VipSoft
|
5月前
|
自然语言处理 Java 关系型数据库
ElasticSearch 实现分词全文检索 - 聚合查询 cardinality
ElasticSearch 实现分词全文检索 - 聚合查询 cardinality
VipSoft
179 1
G佳伟
|
6月前
|
存储 关系型数据库 MySQL
【Elasticsearch】在es中实现mysql中的FIND_IN_SET查询条件
【Elasticsearch】在es中实现mysql中的FIND_IN_SET查询条件
G佳伟
152 0
公众号:码到三十五
|
7月前
|
存储 缓存 自然语言处理
elasticsearch 聚合 : 指标聚合、桶聚合、管道聚合解析使用总结
elasticsearch 聚合 : 指标聚合、桶聚合、管道聚合解析使用总结
公众号:码到三十五
184 0

热门文章

最新文章

  • 1
    推荐一款轻量级且强大的 Elasticsearch GUI ： elasticvue
  • 2
    阿里云技术公开课直播预告：基于阿里云 Elasticsearch 构建 AI 搜索和可观测 Chatbot
  • 3
    超值选择：阿里云Elasticsearch Serverless在企业数据检索与分析中的高性能与灵活性
  • 4
    （Elasticsearch）使用阿里云 infererence API 及 semantic text 进行向量搜索
  • 5
    一文看懂Elasticsearch的技术架构：高效、精准的搜索神器
  • 6
    MySQL数据库下载安装教程（Windows&Linux）
  • 7
    极致 ElasticSearch 调优，让你的ES 狂飙100倍！
  • 8
    揭秘 Elasticsearch 集群架构，解锁大数据处理神器
  • 9
    MySQL常用函数：IF、SUM等用法
  • 10
    Python处理数据库：MySQL与SQLite详解 | python小知识
  • 1
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    88
  • 2
    Linux对MySQL语法校验
    100
  • 3
    【MySQL系列笔记】分库分表
    240
  • 4
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    173
  • 5
    服务器Linux系统配置mysql数据库主从自动备份
    234
  • 6
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    429
  • 7
    golang使用gorm操作mysql3，数据查询
    105
  • 8
    golang使用gorm操作mysql2
    72
  • 9
    golang使用gorm操作mysql1
    130
  • 10
    Hbase与MySQL对比，区别是什么？
    338

    • 相关课程

    更多
  • MySQL完全自学手册
  • MySQL数据库入门学习
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶

    • 相关电子书

    更多
  • 阿里云Elasticsearch体系架构与特性解析
  • 开源与云：Elasticsearch应用剖析
  • 《Elasticsearch全观测解决方案》

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • 通过Lindorm-cli连接并使用宽表引擎
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务

    • 推荐镜像

    更多
  • mysql
  • clickhouse
  • mongodb
    • 下一篇
    DataWorks智能交互式数据开发与分析之旅