ES Aggregation 如何实现 SQL 的group by having limit
Elasticsearch | 作者 feihui | 发布于2017年10月18日 | 阅读数:5981
实现下面sql类似的功能:
select flowInstId, max(updateTime) as max_updateTime from table
where eid = "6810881" and handler = "58e24f4f60b2980b5954a7a9" and status in ("DONE", "RETURNED")
group by flowInstId having max_updateTime < 1506679462663L limit 5
步骤是过滤->分组->过滤排序->取前五条
下面是我写的es aggregation:
{
"query": {
"bool": {
"filter": [
{ "term": { "eid.keyword": "6810881"}},
{ "term": { "handler.keyword": "58e24f4f60b2980b5954a7a9" }},
{"terms":{
"status.keyword":[
"DONE",
"RETURNED"
]
}}
]
}
},
"aggs": {
"by_flowInstId": {
"terms": {
"field": "flowInstId",
"size":5,
"order":{
"max_updateTime":"desc"
}
},
"aggs": {
"max_updateTime": {
"max": {
"field": "updateTime"
}
},
"max_updateTime_filter": {
"bucket_selector": {
"buckets_path": {
"maxUpdateTime": "max_updateTime"
},
"script": "params.maxUpdateTime < 1506679462663L"
}
}
}
}
}
}
最大的问题在于size,看结果貌似是过滤->分组->排序->取前五条->过滤
请问如何如何实现上述SQL类型的功能呢?thx!
select flowInstId, max(updateTime) as max_updateTime from table
where eid = "6810881" and handler = "58e24f4f60b2980b5954a7a9" and status in ("DONE", "RETURNED")
group by flowInstId having max_updateTime < 1506679462663L limit 5
步骤是过滤->分组->过滤排序->取前五条
下面是我写的es aggregation:
{
"query": {
"bool": {
"filter": [
{ "term": { "eid.keyword": "6810881"}},
{ "term": { "handler.keyword": "58e24f4f60b2980b5954a7a9" }},
{"terms":{
"status.keyword":[
"DONE",
"RETURNED"
]
}}
]
}
},
"aggs": {
"by_flowInstId": {
"terms": {
"field": "flowInstId",
"size":5,
"order":{
"max_updateTime":"desc"
}
},
"aggs": {
"max_updateTime": {
"max": {
"field": "updateTime"
}
},
"max_updateTime_filter": {
"bucket_selector": {
"buckets_path": {
"maxUpdateTime": "max_updateTime"
},
"script": "params.maxUpdateTime < 1506679462663L"
}
}
}
}
}
}
最大的问题在于size,看结果貌似是过滤->分组->排序->取前五条->过滤
请问如何如何实现上述SQL类型的功能呢?thx!
0 个回复