我有一个带有用户位置的索引,我想为每个用户选择最新的位置,然后对最新的位置应用一些复杂的过滤器。我设法用聚合和排序来获取最新的位置,但是我找不到一种方法在之后对其应用过滤器。
筛选或后筛选不会产生预期结果,因为它们应用于命中而不是聚合位置。我看到一些关于bucket脚本聚合的评论,但可用的示例非常简单。ElasticSearch有什么办法吗?用sql编写脚本非常简单。感谢您的帮助!
示例es查询聚合位置:
GET locations/_search
{
"query": {
"bool": {
"must": {
"match_all": {}
}
}
},
"aggs": {
"users": {
"terms": {
"field": "user_id"
},
"aggs": {
"userLocations": {
"top_hits": {
"sort": [{
"created": {
"order": "desc"
}
}],
"size": 1
}
}
}
}
}
}
示例sql查询(过于简化,不基于实际数据):
SELECT subquery.* FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY Created DESC) AS "RowNumber",
UserId,
Latitute,
Longitude,
Active,
OrganizationId
FROM Locations
) subquery
WHERE subquery.RowNumber = 1 AND Latitude < 10 AND Longitude > 0 AND Active = 1 AND OrganizationId = 123
样本Map:
{
"mappings": {
"properties": {
"user_id": {
"type": "integer"
},
"location": {
"type": "geo_point"
},
"location_source_type": {
"type": "integer"
},
"created": {
"type": "date"
}
"active": {
"type": "integer"
},
"org_id": {
"type": "integer"
}
}
}
}
样本数据:
用户标识位置来源类型已创建活动组织标识
1 (0,0) 1 2020-01-01 1 123
1 (0,0) 1 2020-02-01 1 123
1 (9,9) 1 2020-03-01 1 123
2 (8,8) 1 2020-04-01 1 123
预期结果:
1 (9,9) 1 2020-03-01 1 123
2 (8,8) 1 2020-04-01 1 123
这是我要应用于聚合数据的筛选器示例:
"post_filter": {
"bool": {
"should": [{
"bool": {
"must": [
{
{ "term": { "location_source_type": 1 }},
{ "term" : { "org_id" : "123" }}
},
{
"range": { "created": { "lte": "2020-01-01T00:00:00" } }
}
],
"filter": [{
"geo_distance": {
"distance": "1000km",
"location": {
"lat": 9,
"lon": 9
}
}
}]
}
}]
}
}
暂无答案!
目前还没有任何答案,快来回答吧!