高级查询
范围查询
1
2
3
4
5
6
7
8
9
10
| {
"query": {
"range": {
"amount": {
"gte" :1,
"lte":100
}
}
}
}
|
相当于 amount>=1 and amount<=100
短语查询
1
2
3
4
5
6
7
| {
"query": {
"match_phrase": {
"desc": "收入"
}
}
}
|
script查询
script查询 可以对查询出的字段进行再次计算。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| GET order/_search
{
"query": {
"match_all": {}
},
"script_fields": {
"test1": {
"script": {
"lang": "painless",
"source": "doc['amount'].value * 2"
}
},
"test2": {
"script": {
"lang": "painless",
"params": {
"factor": 2
},
"source": "doc['amount'].value + params.factor"
}
}
}
}
|
过滤和查询
区别:
Filter：在查询过程中，Filter只判断该文档是否满足条件，只有YES或者NO。
ES会对它的结果进行缓存，所以相较于Query而言Filter的速度会更快一些。
Query： 除了问YES或NO，还会问匹配的程度。
过滤查询已被弃用，并在ES 5.0中删除。现在使用bool查询代替。
bool 查询是一个组合查询，返回一个bool值。 包含must,should,filter等查询
must:相当于and,必须满足
should:相当于or,代表或者意思
filter：是一个bool表达式。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| {
"query": {
"bool": {
"must": {
"match":{"desc": "收入"}
},
"should": {
"match":{"name": "user"}
},
"filter":{
"range":{
"amount":{
"gte" :10,
"lte":50
}
}
}
}
}
}
|
相当于mysql中的 1=1 and ((desc like ‘%收入%’ and amount>=10 and amount<=50>) or name =’user’)
聚合
在mysql中，聚合用group by，对于聚合后的计算用sum，avg等聚合函数计算，在es中，groupby 后面的字段称为桶，sum等聚合函数称为指标。
如：
1
| select sex,sum(age) from user group by sex
|
上面的sql中,sex和sum都是查询的指标，sex是桶。
聚合的写法：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| {
"size": 0,
"aggs": {
"buckets": {
"terms": {
"field": "orderId",
"order":{"sumAmount" : "asc" }
},
"aggs": {
"sumAmount": {
"sum": {
"field": "amount"
}
}
}
}
}
}
|
对于聚合来说，es中的聚合函数有，sum,avg,stats,max,min等，聚合方式可以归为以下几类：
单字段单指标聚合单字段多指标聚合 多字段单指标聚合聚合后筛选
单字段单指标聚合
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| {
"size": 0,
"aggs": {
"buckets": {
"terms": {
"field": "orderId",
"order":{"sumAmount.avg" : "asc" }
},
"aggs": {
"sumAmount" : {
"stats" : { "field" : "amount" }
}
}
}
}
}
|
单字段多指标聚合
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| {
"size": 0,
"aggs": {
"bucket1": {
"terms": {
"field": "orderId",
"order":{"sumAmount" : "asc" }
},
"aggs": {
"sumAmount": {
"sum": {
"field": "amount"
}
},
"avgAmount":{
"avg": {
"field": "amount"
}
}
}
}
}
}
|
多字段单指标聚合
对索引中的两个字段一起聚合,相当于group by xxx,yyy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| {
"size": 0,
"aggs": {
"bulket1": {
"terms": {
"field": "orderId"
},
"aggs": {
"bulket2": {
"terms": {
"field": "name"
},
"aggs": {
"sumAmount": {
"sum": {
"field": "amount"
}
}
}
}
}
}
}
}
|
聚合后的筛选：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| {
"size": 0,
"aggs": {
"groupUserId": {
"terms": {
"field": "shortTime"
},
"aggs": {
"sumAmount": {
"sum": {
"field": "amount"
}
},
"having": {
"bucket_selector": {
"buckets_path": {
"orderCount": "_count",
"sumAmount": "sumAmount"
},
"script": {
"source": "params.sumAmount >= 100 && params.orderCount >=2"
}
}
}
}
}
}
}
|
ES中有一个区别于传统DB的聚合方式，对索引中的两个字段分别聚合，相当于mysql中group by 'xxx', group by 'yyy'，统计后的结果分布在各个桶里面
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| {
"size": 0,
"aggs": {
"bulket1": {
"terms": {
"field": "shortTime"
}
},
"bulket2": {
"terms": {
"field": "name"
}
}
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| "aggregations" : {
"bulket2" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "user",
"doc_count" : 4
},
{
"key" : "user1",
"doc_count" : 2
}
]
},
"bulket1" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 1546905600000,
"key_as_string" : "2019-01-08T00:00:00.000Z",
"doc_count" : 3
},
{
"key" : 1546646400000,
"key_as_string" : "2019-01-05T00:00:00.000Z",
"doc_count" : 2
},
{
"key" : 1546992000000,
"key_as_string" : "2019-01-09T00:00:00.000Z",
"doc_count" : 1
}
]
}
}
|
也可以多个字段各自统计
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| {
"size": 0,
"aggs": {
"bucket1": {
"terms": {
"field": "orderId",
"order":{"sumAmount" : "asc" }
},
"aggs": {
"sumAmount": {
"sum": {
"field": "amount"
}
}
}
},
"bucket2": {
"terms": {
"field": "name",
"order":{"avgAmount" : "asc" }
},
"aggs": {
"avgAmount": {
"sum": {
"field": "amount"
}
}
}
}
}
}
|
聚合后结果如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
| "aggregations" : {
"bucket2" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "user1",
"doc_count" : 2,
"avgAmount" : {
"value" : 137.07
}
},
{
"key" : "user",
"doc_count" : 4,
"avgAmount" : {
"value" : 246.18
}
}
]
},
"bucket1" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "10000",
"doc_count" : 3,
"sumAmount" : {
"value" : 51.16
}
},
{
"key" : "10001",
"doc_count" : 3,
"sumAmount" : {
"value" : 332.09000000000003
}
}
]
}
}
|
