注: 本文学习自小d课堂Elasticsearch学习
以下的查询均采用kibana工具,写的比较初级,如果有错误的,或者有更方便的,请留言指正修改,谢谢
1、桶聚合查询,类似数据库查询的group by
- 例一: 火箭队根据年龄分组
使用aggs关键字–terms指定分组字段,size指定显示多少分组
GET /nba/_search
{
"query": {
"term": {
"teamNameEn": {
"value": "Rockets"
}
}
},
"aggs": {
"ageGroup": {
"terms": {
"field": "age",
"size": 10
}
}
},
"size": 0
}
{
"took" : 0,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 21,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"ageGroup" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 3,
"buckets" : [
{
"key" : 21,
"doc_count" : 4
},
{
"key" : 25,
"doc_count" : 3
},
{
"key" : 23,
"doc_count" : 2
},
{
"key" : 30,
"doc_count" : 2
},
{
"key" : 34,
"doc_count" : 2
},
{
"key" : 22,
"doc_count" : 1
},
{
"key" : 24,
"doc_count" : 1
},
{
"key" : 26,
"doc_count" : 1
},
{
"key" : 27,
"doc_count" : 1
},
{
"key" : 29,
"doc_count" : 1
}
]
}
}
}
- 例二: 查询nba每个队的平均年龄,并按照平均年龄降序
- 按照球队分组
- 计算每个球队的平均年龄
- 排序
GET /nba/_search
{
"aggs": {
"tearmGroup": {
"terms": {
"field": "teamNameEn",
"size": 10,
"order": {
"avgAge": "desc"
}
},
"aggs": {
"avgAge": {
"avg": {
"field": "age"
}
}
}
}
},
"size": 0
}
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 566,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"tearmGroup" : {
"doc_count_error_upper_bound" : -1,
"sum_other_doc_count" : 381,
"buckets" : [
{
"key" : "Bucks",
"doc_count" : 14,
"avgAge" : {
"value" : 28.142857142857142
}
},
{
"key" : "Mavericks",
"doc_count" : 20,
"avgAge" : {
"value" : 27.85
}
},
{
"key" : "Lakers",
"doc_count" : 21,
"avgAge" : {
"value" : 27.714285714285715
}
},
{
"key" : "Raptors",
"doc_count" : 17,
"avgAge" : {
"value" : 26.823529411764707
}
},
{
"key" : "Wizards",
"doc_count" : 17,
"avgAge" : {
"value" : 26.823529411764707
}
},
{
"key" : "Heat",
"doc_count" : 17,
"avgAge" : {
"value" : 26.764705882352942
}
},
{
"key" : "Rockets",
"doc_count" : 21,
"avgAge" : {
"value" : 26.761904761904763
}
},
{
"key" : "Spurs",
"doc_count" : 20,
"avgAge" : {
"value" : 26.75
}
},
{
"key" : "Jazz",
"doc_count" : 17,
"avgAge" : {
"value" : 26.647058823529413
}
},
{
"key" : "Pistons",
"doc_count" : 21,
"avgAge" : {
"value" : 26.476190476190474
}
}
]
}
}
}
排序时候也可以指定按照分组的和排序
GET /nba/_search
{
"query": {
"term": {
"teamNameEn": {
"value": "Rockets"
}
}
},
"aggs": {
"aggsAge": {
"terms": {
"field": "age",
"size": 10,
"order": {
"_count": "desc"
}
}
}
},
"size": 30
}
- 例三:分组筛选排序
我们可以指定包括哪几个分组,或者排除哪几个
include包含
exclude排除
GET /nba/_search
{
"aggs": {
"tearmGroup": {
"terms": {
"field": "teamNameEn",
"include": ["Hawks","Thunder"],
"size": 10,
"order": {
"avgAge": "desc"
}
},
"aggs": {
"avgAge": {
"avg": {
"field": "age"
}
}
}
}
},
"size": 0
}
也可以使用正则表达式
GET /nba/_search
{
"aggs": {
"tearmGroup": {
"terms": {
"field": "teamNameEn",
"include": "Lakers|Ro.*|Warriors",
"size": 10,
"order": {
"avgAge": "desc"
}
},
"aggs": {
"avgAge": {
"avg": {
"field": "age"
}
}
}
}
},
"size": 0
}
- 例四: 按照范围分组
使用range关键字
to:表示小于
form: 表示大于
下面的查询的意思是:
统计age字段分别在20以下、20至35之间、35以后的球员个数
GET /nba/_search
{
"aggs": {
"groupAge": {
"range": {
"field": "age",
"ranges": [
{
"to": 20
},
{
"to":20,
"from": 35
},
{
"from": 35
}
]
}
}
},
"size": 0
}
返回
{
"took" : 2,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 566,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"groupAge" : {
"buckets" : [
{
"key" : "*-20.0",
"to" : 20.0,
"doc_count" : 15
},
{
"key" : "35.0-20.0",
"from" : 35.0,
"to" : 20.0,
"doc_count" : 0
},
{
"key" : "35.0-*",
"from" : 35.0,
"doc_count" : 20
}
]
}
}
}
也可以指定别名,也就是说,如果你的返回值不想是 “key” : “35.0-*”,这样子的,我们就可以起一个别名
GET /nba/_search
{
"aggs": {
"groupAge": {
"range": {
"field": "age",
"ranges": [
{
"from": 35,
"key":"a"
}
]
}
}
},
"size": 0
}
- 例五:按照日期范围聚合
使用date_range
POST /nba/_search
{
"aggs": {
"birthDayRange": {
"date_range": {
"field": "birthDay",
"format": "MM-yyy",
"ranges": [
{
"to": "01-1989"
},
{
"from": "01-1989",
"to": "01-1999"
},
{
"from": "01-1999",
"to": "01-2009"
},
{
"from": "01-2009"
}
]
}
}
},
"size": 0
}
- 例六:Date Histogram Aggregation 时间柱状图聚合
按天、⽉、年等进⾏聚合统计。可按 year (1y), quarter (1q), month (1M), week (1w), day
(1d), hour (1h), minute (1m), second (1s) 间隔聚合
NBA球员按出⽣年分组
##查询每一年出生的球员有多少个
GET /nba/_search
{
"aggs": {
"yearList": {
"date_histogram": {
"field": "birthDay",
"format": "yyyy",
"interval": "year"
}
}
},"size": 5
}
#! Deprecation: [interval] on [date_histogram] is deprecated, use [fixed_interval] or [calendar_interval] in the future.
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 566,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"yearList" : {
"buckets" : [
{
"key_as_string" : "1977",
"key" : 220924800000,
"doc_count" : 1
},
{
"key_as_string" : "1978",
"key" : 252460800000,
"doc_count" : 1
},
{
"key_as_string" : "1979",
"key" : 283996800000,
"doc_count" : 0
},
{
"key_as_string" : "1980",
"key" : 315532800000,
"doc_count" : 3
},
{
"key_as_string" : "1981",
"key" : 347155200000,
"doc_count" : 2
},
{
"key_as_string" : "1982",
"key" : 378691200000,
"doc_count" : 3
},
{
"key_as_string" : "1983",
"key" : 410227200000,
"doc_count" : 2
},
{
"key_as_string" : "1984",
"key" : 441763200000,
"doc_count" : 8
},
{
"key_as_string" : "1985",
"key" : 473385600000,
"doc_count" : 15
},
{
"key_as_string" : "1986",
"key" : 504921600000,
"doc_count" : 19
},
{
"key_as_string" : "1987",
"key" : 536457600000,
"doc_count" : 16
},
{
"key_as_string" : "1988",
"key" : 567993600000,
"doc_count" : 27
},
{
"key_as_string" : "1989",
"key" : 599616000000,
"doc_count" : 24
},
{
"key_as_string" : "1990",
"key" : 631152000000,
"doc_count" : 35
},
{
"key_as_string" : "1991",
"key" : 662688000000,
"doc_count" : 31
},
{
"key_as_string" : "1992",
"key" : 694224000000,
"doc_count" : 36
},
{
"key_as_string" : "1993",
"key" : 725846400000,
"doc_count" : 46
},
{
"key_as_string" : "1994",
"key" : 757382400000,
"doc_count" : 45
},
{
"key_as_string" : "1995",
"key" : 788918400000,
"doc_count" : 57
},
{
"key_as_string" : "1996",
"key" : 820454400000,
"doc_count" : 56
},
{
"key_as_string" : "1997",
"key" : 852076800000,
"doc_count" : 57
},
{
"key_as_string" : "1998",
"key" : 883612800000,
"doc_count" : 39
},
{
"key_as_string" : "1999",
"key" : 915148800000,
"doc_count" : 28
},
{
"key_as_string" : "2000",
"key" : 946684800000,
"doc_count" : 15
}
]
}
}
}