MAX_EACH
Collects the maximum value provided in VALUE for each GROUP.
MAX_EACH([MAX VALUES, ]GROUP, VALUE)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.An array of key-value pairs where the key type matches the GROUP type and the value corresponds to the VALUE type (numeric).
"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.Output Type Availablity
Aggregated Outputs NO
Explicit Lookup Table YES
Inline Joins\Lookups NO
[
{
"user":"A01",
"level":1,
"score":25
},
{
"user":"A01",
"level":2,
"score":48
},
{
"user":"A01",
"level":2,
"score":81
},
{
"user":"B02",
"level":1,
"score":23
},
{
"user":"B02",
"level":1,
"score":29
}
]
Find the highest score for each level, and sort the results by user:
SELECT
data.user AS user:STRING,
MAX_EACH(data.level, data.score) AS max_each_data_level__data_score
FROM
"MAX_SAMPLE_DATA - json"
GROUP BY
data.user
{
"max_each_data_leveldata_score":[
{
"key":1,
"value":25
},
{
"key":2,
"value":81
}
],
"user":"A01"
}{
"max_each_data_leveldata_score":[
{
"key":1,
"value":29
}
],
"user":"B02"
}
Last modified 1yr ago