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"
}
]
}
Related Functions
FIRST FIRST_ARRAY FIRST_TIME_SERIES
Dialog
Last updated
Was this helpful?