MAX_EACH

Collects the maximum value provided in VALUE for each GROUP.
Syntax
MAX_EACH([MAX VALUES,  ]GROUP, VALUE)
Arguments
GROUP:               A grouping field
VALUE:               An expression of a NUMERIC type that can be ordered
MAX VALUES:    The maximum number of groups. Groups beyond this amount will be discarded 
                              (Defaults to 2147483647).
                              In some cases, depending on the data distribution, discarding groups via MAX
                             VALUES  may cause some data to be discarded from groups that would
                              otherwise have been returned. 
                             MAX VALUES is optional. If it is omitted there is, in effect, no limit.
Returns
An array of key-value pairs where the key type matches the GROUP type and the value corresponds to the VALUE type (numeric).
Notes
"EACH" data must be hierarchical.
MAX_EACH only appears as an option in the ADD AGGREGATE dialog when the source data is hierarchical.
Trying to aggregate MAX_EACH, when the data is tabular, is likely to result in an error message:
MAX_EACH can produce output in an array.
AVAILABLE IN
Output Type                     Availablity
Aggregated Outputs      NO
Explicit Lookup Table    YES
Inline Joins\Lookups     NO
Example
Data
1
[
2
   {
3
      "user":"A01",
4
      "level":1,
5
      "score":25
6
   },
7
   {
8
      "user":"A01",
9
      "level":2,
10
      "score":48
11
   },
12
   {
13
      "user":"A01",
14
      "level":2,
15
      "score":81
16
   },
17
   {
18
      "user":"B02",
19
      "level":1,
20
      "score":23
21
   },
22
   {
23
      "user":"B02",
24
      "level":1,
25
      "score":29
26
   }
27
]
Copied!
Query
Find the highest score for each level, and sort the results by user:
1
SELECT
2
   data.user AS user:STRING,
3
   MAX_EACH(data.level, data.score) AS max_each_data_level__data_score 
4
FROM
5
   "MAX_SAMPLE_DATA - json" 
6
GROUP BY
7
   data.user
Copied!
Results
1
{
2
   "max_each_data_leveldata_score":[
3
      {
4
         "key":1,
5
         "value":25
6
      },
7
      {
8
         "key":2,
9
         "value":81
10
      }
11
   ],
12
   "user":"A01"
13
}{
14
   "max_each_data_leveldata_score":[
15
      {
16
         "key":1,
17
         "value":29
18
      }
19
   ],
20
   "user":"B02"
21
}
Copied!
Related Functions
​MAX
MAX_BY
MAX_TIME_SERIES​
Dialog

MAX_BY

MAX_TIME_SERIES

Last modified 8mo ago

Copy link

Contents