elk统计两个字段的和

zhao_rd 回复了问题 • 3 人关注 • 2 个回复 • 458 次浏览 • 2 天前 • 来自相关话题

long类型的时间字段date_histogram,extended_bounds,min无效?

medcl 回复了问题 • 3 人关注 • 1 个回复 • 89 次浏览 • 2 天前 • 来自相关话题

ES权威指南-取回一个文档疑问,求大神解答

bill 回复了问题 • 3 人关注 • 2 个回复 • 53 次浏览 • 3 天前 • 来自相关话题

elasticsearch5.6.6,java,RestHighLevelClient,使用路由(routing)后更慢了

xys2333 回复了问题 • 2 人关注 • 2 个回复 • 107 次浏览 • 3 天前 • 来自相关话题

6.x使用 reindex失敗

gentrice 回复了问题 • 3 人关注 • 2 个回复 • 73 次浏览 • 3 天前 • 来自相关话题

es 数据在被修改之后 再发起查询还是会查到未修改前的数据

code4j 回复了问题 • 5 人关注 • 4 个回复 • 140 次浏览 • 3 天前 • 来自相关话题

【求解】elasticsearch5 在90个并发查询耗时过长在4-5秒

yayg2008 回复了问题 • 5 人关注 • 3 个回复 • 142 次浏览 • 3 天前 • 来自相关话题

Elasticsearch如何实现 SQL语句中 Group By 和 Limit 的功能

rockybean 发表了文章 • 1 个评论 • 122 次浏览 • 3 天前 • 来自相关话题

有 SQL 背景的同学在学习 Elasticsearch 时,面对一个查询需求,不由自主地会先思考如何用 SQL 来实现,然后再去想 Elasticsearch 的 Query DSL 如何实现。那么本篇就给大家讲一条常见的 SQL 语句如何用 Elasticsearch 的查询语言实现。



1. SQL语句


假设我们有一个汽车的数据集,每个汽车都有车型、颜色等字段,我希望获取颜色种类大于1个的前2车型。假设汽车的数据模型如下:

json<br /> {<br /> "model":"modelA",<br /> "color":"red"<br /> }<br />

假设我们有一个 cars 表,通过如下语句创建测试数据。

sql<br /> INSERT INTO cars (model,color) VALUES ('A','red'); <br /> INSERT INTO cars (model,color) VALUES ('A','white'); <br /> INSERT INTO cars (model,color) VALUES ('A','black'); <br /> INSERT INTO cars (model,color) VALUES ('A','yellow'); <br /> INSERT INTO cars (model,color) VALUES ('B','red'); <br /> INSERT INTO cars (model,color) VALUES ('B','white'); <br /> INSERT INTO cars (model,color) VALUES ('C','black'); <br /> INSERT INTO cars (model,color) VALUES ('C','red'); <br /> INSERT INTO cars (model,color) VALUES ('C','white'); <br /> INSERT INTO cars (model,color) VALUES ('C','yellow'); <br /> INSERT INTO cars (model,color) VALUES ('C','blue'); <br /> INSERT INTO cars (model,color) VALUES ('D','red');<br /> INSERT INTO cars (model,color) VALUES ('A','red'); <br />

那么实现我们需求的 SQL 语句也比较简单,实现如下:

sql<br /> SELECT model,COUNT(DISTINCT color) color_count FROM cars GROUP BY model HAVING color_count > 1 ORDER BY color_count desc LIMIT 2;<br />

这条查询语句中 Group By 是按照 model 做分组, Having color_count>1 限定了车型颜色种类大于1,ORDER BY color_count desc 限定结果按照颜色种类倒序排列,而 LIMIT 2 限定只返回前3条数据。

那么在 Elasticsearch 中如何实现这个需求呢?

2. 在 Elasticsearch 模拟测试数据


首先我们需要先在 elasticsearch 中插入测试的数据,这里我们使用 bulk 接口 ,如下所示:

<br /> POST _bulk<br /> {"index":{"_index":"cars","_type":"doc","_id":"1"}}<br /> {"model":"A","color":"red"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"2"}}<br /> {"model":"A","color":"white"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"3"}}<br /> {"model":"A","color":"black"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"4"}}<br /> {"model":"A","color":"yellow"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"5"}}<br /> {"model":"B","color":"red"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"6"}}<br /> {"model":"B","color":"white"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"7"}}<br /> {"model":"C","color":"black"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"8"}}<br /> {"model":"C","color":"red"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"9"}}<br /> {"model":"C","color":"white"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"10"}}<br /> {"model":"C","color":"yellow"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"11"}}<br /> {"model":"C","color":"blue"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"12"}}<br /> {"model":"D","color":"red"}<br /> {"index":{"_index":"cars","_type":"doc","_id":"13"}}<br /> {"model":"A","color":"red"}<br />

其中 index 为 cars,type 为 doc,所有数据与mysql 数据保持一致。大家可以在 Kibana 的 Dev Tools 中执行上面的命令,然后执行下面的查询语句验证数据是否已经成功存入。

<br /> GET cars/_search<br />

3. Group By VS Terms/Metric Aggregation


SQL 中 Group By 语句在 Elasticsearch 中对应的是 Terms Aggregation,即分桶聚合,对应 Group By color 的语句如下所示:

json<br /> GET cars/_search<br /> {<br /> "size":0,<br /> "aggs":{<br /> "models":{<br /> "terms":{<br /> "field":"model.keyword"<br /> }<br /> }<br /> }<br /> }<br />

结果如下:

json<br /> {<br /> "took": 161,<br /> "timed_out": false,<br /> "_shards": {<br /> "total": 5,<br /> "successful": 5,<br /> "skipped": 0,<br /> "failed": 0<br /> },<br /> "hits": {<br /> "total": 13,<br /> "max_score": 0,<br /> "hits": []<br /> },<br /> "aggregations": {<br /> "models": {<br /> "doc_count_error_upper_bound": 0,<br /> "sum_other_doc_count": 0,<br /> "buckets": [<br /> {<br /> "key": "A",<br /> "doc_count": 5<br /> },<br /> {<br /> "key": "C",<br /> "doc_count": 5<br /> },<br /> {<br /> "key": "B",<br /> "doc_count": 2<br /> },<br /> {<br /> "key": "D",<br /> "doc_count": 1<br /> }<br /> ]<br /> }<br /> }<br /> }<br />

我们看 aggregations 这个 key 下面的即为返回结果。

SQL 语句中还有一项是 COUNT(DISTINCT color) color_count 用于计算每个 model 的颜色数,在 Elasticsearch 中我们需要使用一个指标类聚合 Cardinality ,进行不同值计数。语句如下:

sql<br /> GET cars/_search<br /> {<br /> "size": 0,<br /> "aggs": {<br /> "models": {<br /> "terms": {<br /> "field": "model.keyword"<br /> },<br /> "aggs": {<br /> "color_count": {<br /> "cardinality": {<br /> "field": "color.keyword"<br /> }<br /> }<br /> }<br /> }<br /> }<br /> }<br />

其返回结果如下:

json<br /> {<br /> "took": 74,<br /> "timed_out": false,<br /> "_shards": {<br /> "total": 5,<br /> "successful": 5,<br /> "skipped": 0,<br /> "failed": 0<br /> },<br /> "hits": {<br /> "total": 13,<br /> "max_score": 0,<br /> "hits": []<br /> },<br /> "aggregations": {<br /> "models": {<br /> "doc_count_error_upper_bound": 0,<br /> "sum_other_doc_count": 0,<br /> "buckets": [<br /> {<br /> "key": "A",<br /> "doc_count": 5,<br /> "color_count": {<br /> "value": 4<br /> }<br /> },<br /> {<br /> "key": "C",<br /> "doc_count": 5,<br /> "color_count": {<br /> "value": 5<br /> }<br /> },<br /> {<br /> "key": "B",<br /> "doc_count": 2,<br /> "color_count": {<br /> "value": 2<br /> }<br /> },<br /> {<br /> "key": "D",<br /> "doc_count": 1,<br /> "color_count": {<br /> "value": 1<br /> }<br /> }<br /> ]<br /> }<br /> }<br /> }<br />

结果中 color_count 即为每个 model 的颜色数,但这里所有的模型都返回了,我们只想要颜色数大于1的模型,因此这里还要加一个过滤条件。





4. Having Condition VS Bucket Filter Aggregation


Having color_count > 1 在 Elasticsearch 中对应的是 Bucket Filter 聚合,语句如下所示:

json<br /> GET cars/_search<br /> {<br /> "size": 0,<br /> "aggs": {<br /> "models": {<br /> "terms": {<br /> "field": "model.keyword"<br /> },<br /> "aggs": {<br /> "color_count": {<br /> "cardinality": {<br /> "field": "color.keyword"<br /> }<br /> },<br /> "color_count_filter": {<br /> "bucket_selector": {<br /> "buckets_path": {<br /> "colorCount": "color_count"<br /> },<br /> "script": "params.colorCount>1"<br /> }<br /> }<br /> }<br /> }<br /> }<br /> }<br />

返回结果如下:

json<br /> {<br /> "took": 39,<br /> "timed_out": false,<br /> "_shards": {<br /> "total": 5,<br /> "successful": 5,<br /> "skipped": 0,<br /> "failed": 0<br /> },<br /> "hits": {<br /> "total": 13,<br /> "max_score": 0,<br /> "hits": []<br /> },<br /> "aggregations": {<br /> "models": {<br /> "doc_count_error_upper_bound": 0,<br /> "sum_other_doc_count": 0,<br /> "buckets": [<br /> {<br /> "key": "A",<br /> "doc_count": 5,<br /> "color_count": {<br /> "value": 4<br /> }<br /> },<br /> {<br /> "key": "C",<br /> "doc_count": 5,<br /> "color_count": {<br /> "value": 5<br /> }<br /> },<br /> {<br /> "key": "B",<br /> "doc_count": 2,<br /> "color_count": {<br /> "value": 2<br /> }<br /> }<br /> ]<br /> }<br /> }<br /> }<br />

此时返回结果只包含颜色数大于1的模型,但大家会发现颜色数多的 C 不是在第一个位置,我们还需要做排序处理。

5. Order By Limit VS Bucket Sort Aggregation


ORDER BY color_count desc LIMIT 3 在 Elasticsearch 中可以使用 Bucket Sort 聚合实现,语句如下所示:

sql<br /> GET cars/_search<br /> {<br /> "size": 0,<br /> "aggs": {<br /> "models": {<br /> "terms": {<br /> "field": "model.keyword"<br /> },<br /> "aggs": {<br /> "color_count": {<br /> "cardinality": {<br /> "field": "color.keyword"<br /> }<br /> },<br /> "color_count_filter": {<br /> "bucket_selector": {<br /> "buckets_path": {<br /> "colorCount": "color_count"<br /> },<br /> "script": "params.colorCount>1"<br /> }<br /> },<br /> "color_count_sort": {<br /> "bucket_sort": {<br /> "sort": {<br /> "color_count": "desc"<br /> },<br /> "size": 2<br /> }<br /> }<br /> }<br /> }<br /> }<br /> }<br />

返回结果如下:

json<br /> {<br /> "took": 32,<br /> "timed_out": false,<br /> "_shards": {<br /> "total": 5,<br /> "successful": 5,<br /> "skipped": 0,<br /> "failed": 0<br /> },<br /> "hits": {<br /> "total": 13,<br /> "max_score": 0,<br /> "hits": []<br /> },<br /> "aggregations": {<br /> "models": {<br /> "doc_count_error_upper_bound": 0,<br /> "sum_other_doc_count": 0,<br /> "buckets": [<br /> {<br /> "key": "C",<br /> "doc_count": 5,<br /> "color_count": {<br /> "value": 5<br /> }<br /> },<br /> {<br /> "key": "A",<br /> "doc_count": 5,<br /> "color_count": {<br /> "value": 4<br /> }<br /> }<br /> ]<br /> }<br /> }<br /> }<br />

至此我们便将 SQL 语句实现的功能用 Elasticsearch 查询语句实现了。对比 SQL 语句与 Elasticsearch 的查询语句,大家会发现后者复杂了很多,但并非无章可循,随着大家对常见语法越来越熟悉,相信一定会越写越得心应手!

elasticsearch之空间数据、地理数据

回复

匿名用户 回复了问题 • 1 人关注 • 1 个回复 • 65 次浏览 • 3 天前 • 来自相关话题

elasticsearch如何查询所有index

laoyang360 回复了问题 • 5 人关注 • 6 个回复 • 170 次浏览 • 4 天前 • 来自相关话题

elastic-cross经常性的链接超时

回复

sjk7984 发起了问题 • 2 人关注 • 0 个回复 • 78 次浏览 • 5 天前 • 来自相关话题

es2.4.5 dsl聚合优化

laoyang360 回复了问题 • 2 人关注 • 1 个回复 • 109 次浏览 • 5 天前 • 来自相关话题

elasticsearch 怎么查询Title字段包含“手机”词频为3的文档

laoyang360 回复了问题 • 4 人关注 • 2 个回复 • 172 次浏览 • 5 天前 • 来自相关话题

centos7怎么安全的启动关闭重启es集群

bill 回复了问题 • 2 人关注 • 1 个回复 • 103 次浏览 • 5 天前 • 来自相关话题