LAST_EACH
Returns an array of last values in the time window, grouped by a sub-key. The statement is limited to a maximum number of values.
Syntax
LAST_EACH([MAX VALUES, ]GROUP, VALUE)
Arguments
MAX VALUES:
limit of number of key\value elements in the output
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 key\value pairs of LAST values.
Notes
This function is non-deterministic.
AVAILABLE IN
Output Type Availablity Aggregate 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 get a list of users and their last connection time in the time window:
SET
DATE_TIME = TO_DATE(data.CONNECTION_TIME);
SELECT
data.GROUP_ID AS group_id:STRING,
LAST_EACH(data.USER_ID, data.CONNECTION_TIME) AS last_each_data_user_id__data_connection_time
FROM
"SAMPLE_DATA_G1U1 - json"
GROUP BY
data.GROUP_ID
Results
{
"last_each_data_user_iddata_connection_time":[
{
"key":"Z1",
"value":"2021-07-01 23:54:27,687"
}
],
"group_id":"G2"
}{
"last_each_data_user_iddata_connection_time":[
{
"key":"U1",
"value":"2021-07-01 18:11:45,783"
},
{
"key":"U2",
"value":"2021-07-01 02:31:29,573"
}
],
"group_id":"G1"
}
Related Functions
LAST LAST_K LAST_K_EACH LAST_TIME_SERIES FIRST MAX_EACH
Dialog
Last updated
Was this helpful?