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
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 minimum score for each key/level:
1
SELECT
2
   MIN_EACH(data.level, data.score) AS min_each_data_user__data_score,
3
   data.user AS user:STRING 
4
FROM
5
   "MAX_SAMPLE_DATA - json" 
6
GROUP BY
7
   data.user
Copied!
Results
1
{
2
   "min_each_data_userdata_score":[
3
      {
4
         "key":1,
5
         "value":25
6
      },
7
      {
8
         "key":2,
9
         "value":48
10
      }
11
   ],
12
   "user":"A01"
13
}{
14
   "min_each_data_userdata_score":[
15
      {
16
         "key":1,
17
         "value":23
18
      }
19
   ],
20
   "user":"B02"
21
}
Copied!
Related Functions
​MAX_EACH
MIN
MIN_BY
MIN_TIME_SERIES
STRING_MIN_EACH​
Dialog

MIN_BY

MIN_TIME_SERIES

Last modified 8mo ago

Copy link

Contents