一、功能场景
在mysql数据库中查询数据时，我们可以采用dinstinct关键字去重。那么，在ES中如何实现查询结果去重呢？
二、原理分析
DISTINCT关键字去重的sql语句等价于对需要去重的字段进行GROUP BY。
以下2个sql都能实现对name,age字段查询结果的去重。
SELECT DISTINCT name,age FROM distinct_index SELECT name,age FROM distinct_index GROUP BY name,age
三、方案汇总
根据上面的分析，总结出以下2种实现方案：
1、使用GROUP BY的SQL查询
2、使用Aggregation(聚合)查询
说明：
ES6.3之后的版本以及支持SQL查询
四、数据准备
## 删除索引 ## DELETE distinct_index ## 新建索引 PUT distinct_index { "mappings": { "properties": { "name": { "type": "keyword" }, "age": { "type": "integer" } } } } ## 添加数据 POST distinct_index/_bulk?refresh { "create": { } } { "name": "小天", "age": 25} { "create": { } } { "name": "小天", "age": 25} { "create": { } } { "name": "老万", "age": 35} { "create": { } } { "name": "老王", "age": 45} { "create": { } } { "name": "小明", "age": 15} { "create": { } } { "name": "小明", "age": 15} { "create": { } } { "name": "小红", "age": 12} { "create": { } } { "name": "乐乐", "age": 18}
五、方案实战
1、SQL查询方案
##可以通过format参数控制返回结果的格式，txt表示文本格式，看起来更直观点，默认为json格式。 POST _sql?format=txt { "query": "SELECT name,age FROM distinct_index group by name,age" }
查询结果：
和预期相符，查询结果达到去重的效果。
SQL语句转DSL：
POST /_sql/translate { "query": "SELECT name,age FROM distinct_index group by name,age" }
结果：
{ "size" : 0, "_source" : false, "stored_fields" : "_none_", "aggregations" : { "groupby" : { "composite" : { "size" : 1000, "sources" : [ { "f5b401c4" : { "terms" : { "field" : "name", "missing_bucket" : true, "order" : "asc" } } }, { "f557e07b" : { "terms" : { "field" : "age", "missing_bucket" : true, "order" : "asc" } } } ] } } } }
说明：
通过将sql语句转为dsl语句可以发现，sql语句中的group by查询底层原理是转化成了dsl中的aggregations聚合查询。
2、aggregations聚合查询
POST distinct_index/_search { "from": 0, "size": 0, "aggregations": { "name": { "terms": { "field": "name", "size": 2147483647 }, "aggregations": { "age": { "terms": { "field": "age", "size": 2147483647 } } } } } }
查询结果：
{ "took" : 3, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 8, "relation" : "eq" }, "max_score" : null, "hits" : [ ] }, "aggregations" : { "name" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : "小天", "doc_count" : 2, "age" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 25, "doc_count" : 2 } ] } }, { "key" : "小明", "doc_count" : 2, "age" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 15, "doc_count" : 2 } ] } }, { "key" : "乐乐", "doc_count" : 1, "age" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 18, "doc_count" : 1 } ] } }, { "key" : "小红", "doc_count" : 1, "age" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 12, "doc_count" : 1 } ] } }, { "key" : "老万", "doc_count" : 1, "age" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 35, "doc_count" : 1 } ] } }, { "key" : "老王", "doc_count" : 1, "age" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 45, "doc_count" : 1 } ] } } ] } } }
六、异常记录
1、ES中的sql查询不支持DISTINCT关键字
POST _sql?format=txt { "query": "SELECT DISTINCT name,age FROM distinct_index" }
报错：
{ "error" : { "root_cause" : [ { "type" : "verification_exception", "reason" : "Found 1 problem\nline 1:8: SELECT DISTINCT is not yet supported" } ], "type" : "verification_exception", "reason" : "Found 1 problem\nline 1:8: SELECT DISTINCT is not yet supported" }, "status" : 400 }
2、ES的sql查询中表名中不能有中划线，比如my-index-000001
PUT my-index-000001 { "mappings": { "properties": { "name": { "type": "keyword" }, "age": { "type": "integer" } } } } POST _sql?format=txt { "query": "SELECT name FROM my-index-000001" }
报错：
{ "error" : { "root_cause" : [ { "type" : "parsing_exception", "reason" : "line 1:20: extraneous input '-' expecting {<EOF>, ',', 'ANALYZE', 'ANALYZED', 'AS', 'CATALOGS', ……
遇到这种情况，最简单方法是给索引添加别名。
POST /_aliases { "actions" : [ { "add" : { "index" : "my-index-000001", "alias" : "my_index" } } ] } POST _sql?format=txt { "query": "SELECT name FROM my_index" }
总结
本文主要介绍了ES中如何实现类似dinstinct的数据去重功能。
1、首先通过通过dinstinct和group by的等价sql语句，说明可以通过分组函数实现数据去重。
2、分别介绍了ES中通过sql语句查询和aggregations聚合查询实现对查询结果的去重。
3、ES中的sql查询不支持DISTINCT关键字
4、ES中的sql查询中表名不能包含中划线，比如my-index-000001
![Elasticsearch Relevance Engine---为AI变革提供高级搜索能力[ES向量搜索、常用配置参数、聚合功能等详解]](https://ucc.alicdn.com/fnj5anauszhew_20231019_05f219d6edca4595abc931668fc0e37f.png?x-oss-process=image/format,webp/resize,h_160,m_lfit)
