本文使用的elasticsearch为6.x版本,7.x版本不支持文档的类型type了。使用相对应版本的kibana进行对elasticsearch的操作。
创建索引:
PUT /user2
{
"settings": {
"index": {
"number_of_shards": "1",
"number_of_replicas": "0"
}
},
"mappings": {
"user":{
"properties": {
"name": {
"type": "text"
},
"age": {
"type": "integer"
},
"mail": {
"type": "keyword"
},
"hobby": {
"type": "text",
"analyzer":"ik_max_word"
}
}
}
}
}
批量添加数据:
POST user2/_bulk
{"index":{"_index":"user2","_type":"user"}}
{"name":"张三","age": 20,"mail": "111@qq.com","hobby":"羽毛球、乒乓球、足球"}
{"index":{"_index":"user2","_type":"user"}}
{"name":"李四","age": 21,"mail": "222@qq.com","hobby":"羽毛球、乒乓球、足球、篮球"}
{"index":{"_index":"user2","_type":"user"}}
{"name":"王五","age": 21,"mail": "333@qq.com","hobby":"羽毛球、篮球、游泳、听音乐"}
{"index":{"_index":"user2","_type":"user"}}
{"name":"赵六","age": 24,"mail": "444@qq.com","hobby":"跑步、游泳、篮球"}
{"index":{"_index":"user2","_type":"user"}}
{"name":"孙七","age": 24,"mail": "555@qq.com","hobby":"羽毛球、乒乓球、足球"}
聚合查询:
- 求和:
GET user2/user/_search
{
"size": 0,
"aggs":{
"age_of_sum":{
"sum": {
"field": "age"
}
}
}
}
size
不需要返回文档,可以直接设置为0.可以提高查询速度
aggs
aggregations的缩写
age_of_sum
聚合的名字,随意写
sum
用来求和
返回结果:
{
"took": 0,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 5,
"max_score": 0,
"hits": []
},
"aggregations": {
"age_of_sum": {
"value": 110
}
}
}
- 求最小值:
GET user2/user/_search
{
"size": 0,
"aggs":{
"age_of_min":{
"min": {
"field": "age"
}
}
}
}
...
"aggregations": {
"age_of_min": {
"value": 20
}
}
- 求最大值:
GET user2/user/_search
{
"size": 0,
"aggs":{
"age_of_max":{
"max": {
"field": "age"
}
}
}
}
...
"aggregations": {
"age_of_max": {
"value": 24
}
}
- 求平均值:
...
GET user2/user/_search
{
"size": 0,
"aggs":{
"age_of_avg":{
"avg": {
"field": "age"
}
}
}
}
"aggregations": {
"age_of_avg": {
"value": 22
}
}
- 求互不相同的个数
GET user2/user/_search
{
"size": 0,
"aggs":{
"age_of_cardi":{
"cardinality": {
"field": "age"
}
}
}
}
"aggregations": {
"age_of_cardi": {
"value": 3
}
}
- 分组:
GET user2/user/_search
{
"size": 0,
"aggs":{
"age_of_group":{
"terms": {
"field": "age"
}
}
}
}
"aggregations": {
"age_of_group": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 21,
"doc_count": 2
},
{
"key": 24,
"doc_count": 2
},
{
"key": 20,
"doc_count": 1
}
]
}
}
- 先查询再分组再排序
按照爱好中包含篮球的进行查询,把查询结果按照年龄进行分组,然后按平均年龄进行排序
GET user2/user/_search
{
"size": 0,
"query": {
"match": {
"hobby": "篮球"
}
},
"aggs":{
"age_of_group":{
"terms": {
"field": "age",
"order": {
"age_of_avg": "desc"
}
},
"aggs": {
"age_of_avg": {
"avg": {
"field": "age"
}
}
}
}
}
}
查询结果:
"aggregations": {
"age_of_group": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 24,
"doc_count": 1,
"age_of_avg": {
"value": 24
}
},
{
"key": 21,
"doc_count": 2,
"age_of_avg": {
"value": 21
}
}
]
}
}
- extended_stats:同时得出多条聚合信息,包括计数,最大值,最小值,平均数,求和等等
GET user2/user/_search
{
"size": 0,
"aggs":{
"age_of_extended":{
"extended_stats": {
"field": "age"
}
}
}
}
"aggregations": {
"age_of_extended": {
"count": 5,
"min": 20,
"max": 24,
"avg": 22,
"sum": 110,
"sum_of_squares": 2434,
"variance": 2.8,
"std_deviation": 1.6733200530681511,
"std_deviation_bounds": {
"upper": 25.346640106136302,
"lower": 18.653359893863698
}
}
}
嵌套聚合
- 构造数据:
PUT /cars
{
"settings": {
"index": {
"number_of_shards": "1",
"number_of_replicas": "0"
}
},
"mappings": {
"transactions":{
"properties": {
"factory": {
"type": "keyword"
},
"price": {
"type": "integer"
},
"color": {
"type": "keyword"
},
"sold": {
"type": "date"
}
}
}
}
}
POST cars/_bulk
{ "index": {"_index":"cars","_type":"transactions"}}
{ "price" : 10000, "color" : "red", "factory" : "honda", "sold" : "2014-10-28" }
{ "index": {"_index":"cars","_type":"transactions"}}
{ "price" : 20000, "color" : "red", "factory" : "honda", "sold" : "2014-11-05" }
{ "index": {"_index":"cars","_type":"transactions"}}
{ "price" : 30000, "color" : "green", "factory" : "ford", "sold" : "2014-05-18" }
{ "index": {"_index":"cars","_type":"transactions"}}
{ "price" : 15000, "color" : "blue", "factory" : "toyota", "sold" : "2014-07-02" }
{ "index": {"_index":"cars","_type":"transactions"}}
{ "price" : 12000, "color" : "green", "factory" : "toyota", "sold" : "2014-08-19" }
{ "index": {"_index":"cars","_type":"transactions"}}
{ "price" : 20000, "color" : "red", "factory" : "honda", "sold" : "2014-11-05" }
{ "index": {"_index":"cars","_type":"transactions"}}
{ "price" : 80000, "color" : "red", "factory" : "bmw", "sold" : "2014-01-01" }
{ "index": {"_index":"cars","_type":"transactions"}}
{ "price" : 25000, "color" : "blue", "factory" : "ford", "sold" : "2014-02-12" }
- 根据颜色分组:
GET cars/transactions/_search
{
"size" : 0,
"aggs" : {
"popular_colors" : {
"terms" : {
"field" : "color"
}
}
}
}
"buckets": [
{
"key": "red",
"doc_count": 4
},
{
"key": "blue",
"doc_count": 2
},
{
"key": "green",
"doc_count": 2
}
]
java api为
public void aggsTermsQuery(){
SearchResponse response = transportClient.prepareSearch("cars")
.setTypes("transactions")
.addAggregation(
AggregationBuilders.terms("popular_colors")
.field("color"))
.setSize(0)
.get();
Aggregation popular_colors = response.getAggregations().get("popular_colors");
}
- 根据颜色分组后,计算每一组的平均价格
GET /cars/transactions/_search
{
"size" : 0,
"aggs": {
"colors": {
"terms": {
"field": "color"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
结果:
"buckets": [
{
"key": "red",
"doc_count": 4,
"avg_price": {
"value": 32500
}
},
{
"key": "blue",
"doc_count": 2,
"avg_price": {
"value": 20000
}
},
{
"key": "green",
"doc_count": 2,
"avg_price": {
"value": 21000
}
}
]
java api实现:
public void setMertricsQuery(){
SearchResponse response = transportClient.prepareSearch("cars")
.setTypes("transactions")
.addAggregation(
AggregationBuilders.terms("colors")
.field("color")
//添加指标
.subAggregation(AggregationBuilders
.avg("avg_price")
.field("price")
)
)
.setSize(0)
.get();
Aggregation colors = response.getAggregations().get("colors");
}
- 先按颜色分组,计算平均价格,再按生产厂家分组:
GET /cars/transactions/_search
{
"size" : 0,
"aggs": {
"colors": {
"terms": {
"field": "color"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
},
"factorys": {
"terms": {
"field": "factory"
}
}
}
}
}
}
结果:
"buckets": [
{
"key": "red",
"doc_count": 4,
"factorys": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "honda",
"doc_count": 3
},
{
"key": "bmw",
"doc_count": 1
}
]
},
"avg_price": {
"value": 32500
}
},
{
"key": "blue",
"doc_count": 2,
"factorys": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "ford",
"doc_count": 1
},
{
"key": "toyota",
"doc_count": 1
}
]
},
"avg_price": {
"value": 20000
}
},
{
"key": "green",
"doc_count": 2,
"factorys": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "ford",
"doc_count": 1
},
{
"key": "toyota",
"doc_count": 1
}
]
},
"avg_price": {
"value": 21000
}
}
]
java api实现
public void subMertricsQuery(){
SearchResponse response = transportClient.prepareSearch("cars")
.setTypes("transactions")
.addAggregation(
AggregationBuilders.terms("colors")
.field("color")
.subAggregation(AggregationBuilders
.avg("avg_price")
.field("price")
)
.subAggregation(AggregationBuilders
.terms("factory")//子集合的名字
.field("factory")//分类的字段
)
)
.setSize(0)
.get();
Aggregation colors = response.getAggregations().get("colors");
}
- 在上面基础上,再查询每个生产厂家生产的价格最高的和最低的车子的价格
GET /cars/transactions/_search
{
"size" : 0,
"aggs": {
"colors": {
"terms": {
"field": "color"
},
"aggs": {
"avg_price": { "avg": { "field": "price" }
},
"factorys" : {
"terms" : {
"field" : "factory"
},
"aggs" : {
"min_price" : {
"min": {
"field": "price"
}
},
"max_price" : {
"max": {
"field": "price"
}
}
}
}
}
}
}
}
结果:
{
"key": "red",
"doc_count": 4,
"factorys": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "honda",
"doc_count": 3,
"max_price": {
"value": 20000
},
"min_price": {
"value": 10000
}
},
{
"key": "bmw",
"doc_count": 1,
"max_price": {
"value": 80000
},
"min_price": {
"value": 80000
}
}
]
},
"avg_price": {
"value": 32500
}
},
java api 实现:
public void subMertricsQuery(){
SearchResponse response = transportClient.prepareSearch("cars")
.setTypes("transactions")
.addAggregation(
AggregationBuilders.terms("colors")
.field("color")
.subAggregation(AggregationBuilders
.avg("avg_price")
.field("price")
)
.subAggregation(AggregationBuilders
.terms("factorys")
.field("factory")
.subAggregation(AggregationBuilders
.max("max_price")
.field("price")
)
.subAggregation(AggregationBuilders
.min("min_price")
.field("price")
)
)
)
.setSize(0)
.get();
Aggregation colors = response.getAggregations().get("colors");
}