MIN_EACH

Collects the minimum value provided in VALUE for each GROUP.

Syntax

MIN_EACH([MAX VALUES, ]GROUP, VALUE)

Arguments

GROUP: A grouping field. VALUE: An expression of a NUMERIC type that can be ordered. MAX VALUES: An expression of INTEGER type for how many values to return (Default 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. WhenMAX VALUES is omitted, the limit is simply set to the default value.

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. MIN_EACH only appears as an option in the ADD AGGREGATE dialog when the source data is hierarchical. Trying to aggregate MIN_EACH, when the data is tabular, is likely to result in an error message. MIN_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

[
   {
      "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
   }
]

Query

Find the minimum score for each key/level:

SELECT
   MIN_EACH(data.level, data.score) AS min_each_data_user__data_score,
   data.user AS user:STRING 
FROM
   "MAX_SAMPLE_DATA - json" 
GROUP BY
   data.user

Results

{
   "min_each_data_userdata_score":[
      {
         "key":1,
         "value":25
      },
      {
         "key":2,
         "value":48
      }
   ],
   "user":"A01"
}{
   "min_each_data_userdata_score":[
      {
         "key":1,
         "value":23
      }
   ],
   "user":"B02"
}