Hi, I have a query like this in MySQL that I am thinking to migrate to ElasticSearch for scalability:
SELELCT userID,sum(countA) as sumA,sum(countB) as sumB FROM data_table WHERE groupID=? AND date BETWEEN ? AND ? GROUP BY userID ORDER BY sumA LIMIT ?,?
For a table of
id, groupID, date, userID, countA, countB with an index of
Is it possible to do it in ES? The closest featuer I can find is composite aggregation: Composite aggregation | Elasticsearch Reference [7.11] | Elastic
It seems supporting aggregation(group by), pagination(limit).
sum(countA) can be done by sub-aggregation?
But it doesn't seem to support filtering by date/groupID.