Es 相比关系型数据库在数据检索方面有着极大的优势, 在处理亿级数据时, 可谓是毫秒级响应, 我们在使用 Es 时不仅仅进行简单的查询, 有时候会做一些数据统计与分析, 如果你以前是使用的关系型数据库, 那么 Es 的数据统计跟关系型数据库还是有很大的区别的, 所以, 这篇内容, 为了更好的理解, 我简单对比了 Es 中统计的写法与关系型数据库的写法.
首先, 先了解一下 Es 中关于聚合的概念:
1: 桶 (Buckets) 满足特定条件的文档的集合;
2: 指标 (Metrics) 对桶内的文档进行统计计算
这两个概念是什么意思? 先看下面一段 T-SQL 统计代码:
- SELECT Color,SUM(1) as Nums[2]
- FROM #Cars
- GROUP BY Color [1]
桶: 满足特定条件的集合, 这个很好理解, 比如可以把蓝色的放到蓝色的桶里, 绿色的放到绿色的桶里, 桶是用来存放不同类型的集合. SQL 代码中[1] 就可以理解对桶进行分组, 有多少种颜色, 就会有几种不同的桶. 桶类似于 SQL 中 GROUP BY;
指标: 对桶内的数据进行统计计算. SQL 代码中[2] 就可以理解为指标, 每个桶里有多少条记录. 指标类似于 SQL 中各种汇总, 如 Count(),Sum(),Max(),Min();
概念了解之后, 对比来了, 我们来做一组数据:
1. 创建表结构并填充数据
1.1 创建 SQLSERVER 结构与数据
- CREATE TABLE #Cars
- (
ID int IDENTITY(1,1) NOT NULL, -- 创建自增序列
Price int, -- 价格
Color varchar(50), -- 颜色
Make varchar(50), -- 品牌
Sold datetime, -- 销售日期
Primary key(ID) -- 定义 ID 为临时表 #Cars 的主键
- );
- INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (10000,'红色','汉兰达','2014-10-28');
- INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (20000,'红色','汉兰达','2014-11-05');
- INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (30000,'绿色','福特','2014-05-18');
- INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (15000,'蓝色','丰田','2014-11-05');
- INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (12000,'绿色','丰田','2014-07-02');
- INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (20000,'红色','汉兰达','2014-11-05');
- INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (80000,'红色','宝马','2014-01-01');
- INSERT INTO #Cars(Price,Color,Make,Sold) VALUES (25000,'蓝色','福特','2014-02-12');
1.2 创建 Elastsearch 结构与数据
- POST /testindex/cars/_bulk
- {
- "index": {
- }
- }
- {
- "price" : 10000, "color" : "红色", "make" : "汉兰达", "sold" : "2014-10-28"
- }
- {
- "index": {
- }
- }
- {
- "price" : 20000, "color" : "红色", "make" : "汉兰达", "sold" : "2014-11-05"
- }
- {
- "index": {
- }
- }
- {
- "price" : 30000, "color" : "绿色", "make" : "福特", "sold" : "2014-05-18"
- }
- {
- "index": {
- }
- }
- {
- "price" : 15000, "color" : "蓝色", "make" : "丰田", "sold" : "2014-07-02"
- }
- {
- "index": {
- }
- }
- {
- "price" : 12000, "color" : "绿色", "make" : "丰田", "sold" : "2014-08-19"
- }
- {
- "index": {
- }
- }
- {
- "price" : 20000, "color" : "红色", "make" : "汉兰达", "sold" : "2014-11-05"
- }
- {
- "index": {
- }
- }
- {
- "price" : 80000, "color" : "红色", "make" : "宝马", "sold" : "2014-01-01"
- }
- {
- "index": {
- }
- }
- {
- "price" : 25000, "color" : "蓝色", "make" : "福特", "sold" : "2014-02-12"
- }
2. 统计查询对比
上面的代码中, 分别创建了 Es 与 SQLSERVER 的数据结构, 并且填充了一些数据. 接下来, 我们来举几个统计的例子, 来看看他们两个之间的统计代码分别怎么写.
2.1 统计哪个颜色的销量最好?
[SQLSERVER 实现]
- SELECT Color,SUM(1) as SalesNum
- FROM #Cars
- GROUP BY Color
结果如下图:
[Elasticsearch 实现]
- GET testindex/cars/_search
- {
- "size": 0, [3]
- "aggs": {[1]
- "SalesNum": { [2]
- "terms": {[4]
- "field": "color.keyword",
- "size": 10
- }
- }
- }
- }
结果如下图:
[1] : 如果想要进行统计分析, 统计代码需要写在 aggs 中, aggs 是 aggregations 的简称, 也可以写作 aggregations.
[2] : 是指定的列的名称, 作用同 SQLSERVER 统计中 as 重命名.
[3] : 这里设置了返回值为 0, 因为这个查询不仅仅返回了我们的统计的内容, 还返回了搜索结果的内容, 这里我们并不需要搜索结果的内容, 所以设置为 0.
[4] : 这里定义了桶的类型, 如果需要不同的统计内容, 这些需要使用不同的统计类型.
2.2 按颜色统计出平均价格?
[SQLSERVER 实现]
- SELECT Color,AVG(Price) as '平均价格'
- FROM #Cars
- GROUP BY Color
[Elasticsearch 实现]
- GET testindex/cars/_search
- {
- "size": 0,
- "aggs": {
- "s": {
- "terms": {
- "field": "color.keyword",
- "size": 10
- },
- "aggs": {
- "avg_price": {
- "avg": {
- "field": "price"
- }
- }
- }
- }
- }
- }
2.3 按照颜色统计出平均价格, 最高价格, 最低价格?
[SQLSERVER 实现]
- SELECT Color,AVG(Price) as '平均价格',MIN(Price) as MinPrice,MAX(Price) as MaxPrice
- FROM #Cars
- GROUP BY Color
[Elasticsearch 实现]
参考: https://elasticsearch.cn/question/4799
2.4 统计每一个企业品牌的最低价格和最高价格?
[SQLSERVER 实现]
- SELECT Make,MIN(Price) as MinPrice,MAX(Price) as MaxPrice
- FROM #Cars
- GROUP BY Make
[Elasticsearch 实现]
- GET testindex/cars/_search
- {
- "size": 0
- ,"aggs": {
- "make": {
- "terms": {
- "field": "make.keyword"
- }
- ,"aggs": {
- "price_age": {
- "avg": {
- "field": "price"
- }
- },
- "min_price": {
- "min": {
- "field": "price"
- }
- }
- ,"max_price":{
- "max": {
- "field": "price"
- }
- }
- }
- }
- }
- }
通过上面的几个示例, 我简单总结了几个 SQLSever 中汇总函数与 Es 的对比, 看下面的表格:
SQLSERVER 函数 | Agg_Type | 功能说明 |
GROUP BY 字段名称 | Terms (避免使用分词字段用来分组) | 分组、Es 划分桶 |
Max()函数 | Max | 求最大值 |
Min()函数 | Min | 求最小值 |
Avg()函数 | Avg | 求平均值 |
今天就先对比下简单的聚合汇总, 求平均值统计, 明天再对比下其他的, 比如日期的聚合以及聚合的排序等.
来源: https://www.cnblogs.com/Rawls/p/10316689.html