ES高级查询

高级查询

范围查询  

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. 聚合后筛选

单字段单指标聚合

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
}
}
]
}
}