1 elasticsearch分组,ES分组 es聚合查询 ES排序
如果需要精确查询的,必须加上 xxx.keyword;属性
1 求最大值 ,取第一条
POST /tms_site_index/_search
{
"size":1,
"query": {
"match_all": {}
},
"aggs": {
"max_price": {
"max": {
"field": "lat"
}
}
}
}
2 一次性求取最大值,最小值,平均值,和等, 例如,统计年级的所有值 POST请求
{
"aggs" : {
"grades_stats" : { "extended_stats" : { "field" : "lat" } }
}
}
"aggregations": {
"grade_stats": {
"count": 9,
"min": 72,
"max": 99,
"avg": 86,
"sum": 774,
"sum_of_squares": 67028,
"variance": 51.55555555555556,
"std_deviation": 7.180219742846005,
"std_deviation_bounds": {
"upper": 100.36043948569201,
"lower": 71.63956051430799
}
}
}
3 (常用) 分组统计,并且按最大值排序 这是分组排序(order) 如sql order by desc
并不是sort排序 sort排序,是指对ES字段排序
并且限制了可分组字段必须是精确数据 有些字段加 xxx.keyword也无法分组
格式太严谨 连 size:0 都限制 totaldistance 是 aggs定义的
POST /ims_tsp_completecondition/_search
{
"size": 0,
"aggs": {
"group_by_plate": {
"terms": {
"field": "platelicenseno.keyword",
"order": {
"totaldistance": "desc"
},
"size":20
},
"aggs": {
"totaldistance": {
"max": {
"field": "totaldistance"
}
}
}
}
}
}
这样就报错 "totaldistance": "desc" 不是索引字段而是分组定义的字段
1 再加段代码加深印象 注意 order_totaldistance 字段
POST /ims_tsp_completecondition/_search
{
"size": 0,
"aggs": {
"group_by_plate": {
"terms": {
"field": "platelicenseno.keyword",
"order": {
"order_totaldistance": "desc"
},
"size":10
},
"aggs": {
"order_totaldistance": {
"max": {
"field": "totaldistance"
}
}
}
}
}
}
4 按索引字段查询排序, 关键字是在最外层,sort 与query 同等级
代码如下 两个字段排序
POST /ims_tsp_completecondition/_search
{
"size":2,
"query": {
"match": {
"platelicenseno": {
"query": "浙AU13A5"
}
}
},
"sort": [
{
"totaldistance":{"order": "desc" },
"_score": { "order": "desc" }
}
]
}
6 终极语句 query+agg+bool 查询
查询当天速度大于0 并且分组车牌号且按总里程排序,格式化时间后再查询
POST /ims_tsp_completecondition/_search
{
"size": 0,
"query": {
"bool" : {
"must_not" : {
"term" : { "vehiclespeed" : "0" }
},
"must" : {
"range" : {
"acquisitiontime": {
"gte": "2019-01-08 01:00:00",
"lt": "2019-01-09 08:00:00",
"format": "yyyy-MM-dd HH:mm:ss"
}
}
}
}
},
"aggs": {
"group_by_plate": {
"terms": {
"field": "platelicenseno.keyword",
"order": {
"order_totaldistance": "desc"
},
"size":10
},
"aggs": {
"order_totaldistance": {
"max": {
"field": "totaldistance"
}
}
}
}
}
}
最关键还是要掌握嵌套,查询分级等,今天被分组排序弄了很长时间,找各种例子实验,可能是之前没有明白分组排序的意义,导致后面查询一直失败, 更复杂的嵌套查询还在慢慢熟悉中