1聚霜、背景
我們知道在sql中是可以實(shí)現(xiàn) group by 字段a,字段b屈呕，那么這種效果在elasticsearch中該如何實(shí)現(xiàn)呢微宝？此處我們記錄在elasticsearch中的3種方式來實(shí)現(xiàn)這個(gè)效果。
2虎眨、實(shí)現(xiàn)多字段聚合的思路
實(shí)現(xiàn)多字段聚合的思路
從上圖中芥吟，我們可以知道侦铜，可以通過3種方式來實(shí)現(xiàn) 多字段的聚合操作。
3钟鸵、需求
根據(jù)省(province)和性別(sex)來進(jìn)行聚合钉稍，然后根據(jù)聚合后的每個(gè)桶的數(shù)據(jù)，在根據(jù)每個(gè)桶中的最大年齡(age)來進(jìn)行倒序排序棺耍。
4贡未、數(shù)據(jù)準(zhǔn)備
4.1 創(chuàng)建索引
PUT /index_person
{
"settings": {
"number_of_shards": 1
},
"mappings": {
"properties": {
"id": {
"type": "long"
},
"name": {
"type": "keyword"
},
"province": {
"type": "keyword"
},
"sex": {
"type": "keyword"
},
"age": {
"type": "integer"
},
"address": {
"type": "text",
"analyzer": "ik_max_word",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
4.2 準(zhǔn)備數(shù)據(jù)
PUT /_bulk
{"create":{"_index":"index_person","_id":1}}
{"id":1,"name":"張三","sex":"男","age":20,"province":"湖北","address":"湖北省黃岡市羅田縣匡河鎮(zhèn)"}
{"create":{"_index":"index_person","_id":2}}
{"id":2,"name":"李四","sex":"男","age":19,"province":"江蘇","address":"江蘇省南京市"}
{"create":{"_index":"index_person","_id":3}}
{"id":3,"name":"王武","sex":"女","age":25,"province":"湖北","address":"湖北省武漢市江漢區(qū)"}
{"create":{"_index":"index_person","_id":4}}
{"id":4,"name":"趙六","sex":"女","age":30,"province":"北京","address":"北京市東城區(qū)"}
{"create":{"_index":"index_person","_id":5}}
{"id":5,"name":"錢七","sex":"女","age":16,"province":"北京","address":"北京市西城區(qū)"}
{"create":{"_index":"index_person","_id":6}}
{"id":6,"name":"王八","sex":"女","age":45,"province":"北京","address":"北京市朝陽區(qū)"}
5、實(shí)現(xiàn)方式
5.1 multi_terms實(shí)現(xiàn)
5.1.1 dsl
GET /index_person/_search
{
"size": 0,
"aggs": {
"agg_province_sex": {
"multi_terms": {
"size": 10,
"shard_size": 25,
"order":{
"max_age": "desc"
},
"terms": [
{
"field": "province",
"missing": "defaultProvince"
},
{
"field": "sex"
}
]
},
"aggs": {
"max_age": {
"max": {
"field": "age"
}
}
}
}
}
}
5.1.2 java 代碼
@Test
@DisplayName("多term聚合-根據(jù)省和性別聚合蒙袍，然后根據(jù)最大年齡倒序")
public void agg01() throws IOException {
SearchRequest searchRequest = new SearchRequest.Builder()
.size(0)
.index("index_person")
.aggregations("agg_province_sex", agg ->
agg.multiTerms(multiTerms ->
multiTerms.terms(term -> term.field("province"))
.terms(term -> term.field("sex"))
.order(new NamedValue<>("max_age", SortOrder.Desc))
)
.aggregations("max_age", ageAgg ->
ageAgg.max(max -> max.field("age")))
)
.build();
System.out.println(searchRequest);
SearchResponse<Object> response = client.search(searchRequest, Object.class);
System.out.println(response);
}
5.1.3 運(yùn)行結(jié)果
運(yùn)行結(jié)果
5.2 script實(shí)現(xiàn)
5.2.1 dsl
GET /index_person/_search
{
"size": 0,
"runtime_mappings": {
"runtime_province_sex": {
"type": "keyword",
"script": """
String province = doc['province'].value;
String sex = doc['sex'].value;
emit(province + '|' + sex);
"""
}
},
"aggs": {
"agg_province_sex": {
"terms": {
"field": "runtime_province_sex",
"size": 10,
"shard_size": 25,
"order": {
"max_age": "desc"
}
},
"aggs": {
"max_age": {
"max": {
"field": "age"
}
}
}
}
}
}
5.2.2 java代碼
@Test
@DisplayName("多term聚合-根據(jù)省和性別聚合俊卤，然后根據(jù)最大年齡倒序")
public void agg02() throws IOException {
SearchRequest searchRequest = new SearchRequest.Builder()
.size(0)
.index("index_person")
.runtimeMappings("runtime_province_sex", field -> {
field.type(RuntimeFieldType.Keyword);
field.script(script -> script.inline(new InlineScript.Builder()
.lang(ScriptLanguage.Painless)
.source("String province = doc['province'].value;\n" +
" String sex = doc['sex'].value;\n" +
" emit(province + '|' + sex);")
.build()));
return field;
})
.aggregations("agg_province_sex", agg ->
agg.terms(terms ->
terms.field("runtime_province_sex")
.size(10)
.shardSize(25)
.order(new NamedValue<>("max_age", SortOrder.Desc))
)
.aggregations("max_age", minAgg ->
minAgg.max(max -> max.field("age")))
)
.build();
System.out.println(searchRequest);
SearchResponse<Object> response = client.search(searchRequest, Object.class);
System.out.println(response);
}
5.2.3 運(yùn)行結(jié)果
運(yùn)行結(jié)果
5.3 通過copyto實(shí)現(xiàn)
我本地測試過，通過copyto沒實(shí)現(xiàn)害幅，此處故先不考慮
5.4 通過pipeline來實(shí)現(xiàn)
實(shí)現(xiàn)思路：
創(chuàng)建mapping時(shí)消恍，多創(chuàng)建一個(gè)字段pipeline_province_sex，該字段的值由創(chuàng)建數(shù)據(jù)時(shí)指定pipeline來生產(chǎn)以现。
5.4.1 創(chuàng)建mapping
PUT /index_person
{
"settings": {
"number_of_shards": 1
},
"mappings": {
"properties": {
"id": {
"type": "long"
},
"name": {
"type": "keyword"
},
"province": {
"type": "keyword"
},
"sex": {
"type": "keyword"
},
"age": {
"type": "integer"
},
"pipeline_province_sex":{
"type": "keyword"
},
"address": {
"type": "text",
"analyzer": "ik_max_word",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
此處指定了一個(gè)字段pipeline_province_sex狠怨，該字段的值會(huì)由pipeline來處理。
5.4.2 創(chuàng)建pipeline
PUT _ingest/pipeline/pipeline_index_person_provice_sex
{
"description": "將provice和sex的值拼接起來",
"processors": [
{
"set": {
"field": "pipeline_province_sex",
"value": ["{{province}}", "{{sex}}"]
},
"join": {
"field": "pipeline_province_sex",
"separator": "|"
}
}
]
}
5.4.3 插入數(shù)據(jù)
PUT /_bulk?pipeline=pipeline_index_person_provice_sex
{"create":{"_index":"index_person","_id":1}}
{"id":1,"name":"張三","sex":"男","age":20,"province":"湖北","address":"湖北省黃岡市羅田縣匡河鎮(zhèn)"}
{"create":{"_index":"index_person","_id":2}}
{"id":2,"name":"李四","sex":"男","age":19,"province":"江蘇","address":"江蘇省南京市"}
{"create":{"_index":"index_person","_id":3}}
{"id":3,"name":"王武","sex":"女","age":25,"province":"湖北","address":"湖北省武漢市江漢區(qū)"}
{"create":{"_index":"index_person","_id":4}}
{"id":4,"name":"趙六","sex":"女","age":30,"province":"北京","address":"北京市東城區(qū)"}
{"create":{"_index":"index_person","_id":5}}
{"id":5,"name":"錢七","sex":"女","age":16,"province":"北京","address":"北京市西城區(qū)"}
{"create":{"_index":"index_person","_id":6}}
{"id":6,"name":"王八","sex":"女","age":45,"province":"北京","address":"北京市朝陽區(qū)"}
注意： 此處的插入需要指定上一步的pipeline
PUT /_bulk?pipeline=pipeline_index_person_provice_sex
5.4.4 聚合dsl
GET /index_person/_search
{
"size": 0,
"aggs": {
"agg_province_sex": {
"terms": {
"field": "pipeline_province_sex",
"size": 10,
"shard_size": 25,
"order": {
"max_age": "desc"
}
},
"aggs": {
"max_age": {
"max": {
"field": "age"
}
}
}
}
}
}
5.4.5 運(yùn)行結(jié)果
運(yùn)行結(jié)果
