How to aggregate by the max agg result?

Elastic Stack Elasticsearch
1

I save the player levelup logs in es like following:

{uid: 1, lv: 1, time: 1470119008, type: "leveup"}
{uid: 1, lv: 2, time: 1470120008, type: "leveup"}
{uid: 2, lv: 1, time: 1470119108, type: "leveup"}
{uid: 2, lv: 2, time: 1470120008, type: "leveup"}
{uid: 2, lv: 3, time: 1470121008, type: "leveup"}
...

Given a range of time, I want to know the lv distribution. For example:

in 1470119008 - 1470120008, the results will be:

{lv: 1, player_count: 2}
{lv: 2, player_count: 2}

My query:

GET player_logs/_search
{
  "size": 0, 
  "query": {
    "match": {
      "type": "levelup"
    }
  },
  "aggs": {
    "group_by_uid": {
      "terms": {
        "field": "uid"
      },
      "aggs": {
        "max_lv": {
          "max": {
            "field": "lv"
          }
        }
      }
    }
  }
}

Now I should aggregate by above results to generate the final results, in sql, it should be select a.lv, count(a.*) from (select uid, max(lv) from players_log where type='levelup' group by uid) a.

But I don't know how to do that in es.

Please help, thanks

2

Pipeline aggregations should be able to do it but won't be that efficient because they have to pull the results back to the coordinating node rather than run near the data.

Keep in mind that a terms aggregation can make a lot of buckets too - in your case one per player. If you have many players that isn't going to be efficient or quick. It might be enough buckets to cause the query to fail outright. So this question is a fairly inefficient one to answer.