FIRST_EACH

Returns an array of first values in the time window, grouped by a sub-key. The statement is limited to a maximum number of values.

Syntax

FIRST_EACH([MAX VALUES, ]GROUP, VALUE)

Arguments

MAX VALUES: Limit of key\value elements in the output (Default: 2,147,483,647). When MAX VALUES is omitted, the limit is simply set to the default value. GROUP: Field to aggregate by VALUE: Field to sub-aggregate by

Returns

For each key aggregated in the query, the function returns an array or FIRST values of key\value pairs.

Notes

This function is non-deterministic.

AVAILABLE IN

Output Type Availablity Aggregated Outputs NO Explicit Lookup Table YES Inline Joins\Lookups NO

Example

Data

[
   {
      "GROUP_ID":"G1",
      "USER_ID":"U1",
      "CONNECTION_TIME":"2020-06-26 02:31:29,573"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U2",
      "CONNECTION_TIME":"2020-06-26 18:11:45,783"
   },
   {
      "GROUP_ID":"G2",
      "USER_ID":"Z1",
      "CONNECTION_TIME":"2020-06-26 23:54:27,687"
   },
   {
      "GROUP_ID":"G2",
      "CONNECTION_TIME":"2020-07-26 23:54:27,687"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U2",
      "CONNECTION_TIME":"2021-07-01 02:31:29,573"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U1",
      "CONNECTION_TIME":"2021-07-01 18:11:45,783"
   },
   {
      "GROUP_ID":"G2",
      "USER_ID":"Z1",
      "CONNECTION_TIME":"2021-07-01 23:54:27,687"
   }
]

Query:

For each GROUP, find when the each USER first appeared.

SELECT
   data.GROUP_ID AS group_id:STRING,
   FIRST_EACH(data.USER_ID, data.CONNECTION_TIME) AS first_each_data_user_id__data_connection_time 
FROM
   "SAMPLE_DATA_G1U1 - json" 
GROUP BY
   data.GROUP_ID

Results:

{
   "group_id":"G2",
   "first_each_data_user_id__data_connection_time":[
      {
         "value":"2020-06-26 23:54:27,687",
         "key":"Z1"
      }
   ]
}{
   "group_id":"G1",
   "first_each_data_user_id__data_connection_time":[
      {
         "value":"2020-06-26 02:31:29,573",
         "key":"U1"
      },
      {
         "value":"2020-06-26 18:11:45,783",
         "key":"U2"
      }
   ]
}