sql语句是这个:
SELECT name
FROM xxxx
where type=1
and created_at BETWEEN ‘2022-04-07 13:55:30’ and ‘2022-04-07 13:56:30’
GROUP BY name
HAVING COUNT(DISTINCT(user_id)) >= 5;
转化为es查询:
{
“query”: {
“bool”: {
“must”: [
{
“term”: {
“type”: 1
}
},
{
“range”: {
“login_at”: {
“gte”: “2023-01-01 00:00:00”
}
}
}
]
}
},
“size”:0,
“aggs”: {
“group_by_name”: {
“terms”: {
“field”: “name”,
“min_doc_count”: 5,
“size”: 10000000,
“missing”: “”
},
“aggs”: {
“distinct_user_id”: {
“cardinality”: {
“field”: “user_id”
}
},
“user_id_filter”: {
“bucket_selector”: {
“buckets_path”: {
“count”: “distinct_user_id.value”
},
“script”: “params.count > 30”
}
}
}
}
},
“sort”: []
}