LAST_K
The last "k" values per group.
LAST_K(VALUE, [K])
VALUE:
An expression of any type.
K:
An optional integer (default is 1). The result type matches
VALUE
for each key.In Upsolver output, the LAST_K function needs no TYPE field.
[
{
"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"
}
]
List the last two connection times by user group:
SET
DATE_TIME = TO_DATE(data.CONNECTION_TIME);
SELECT
data.GROUP_ID AS group_id:STRING,
LAST_K(2, data.CONNECTION_TIME) AS last_k_data_connection_time:STRING
FROM
"SAMPLE_DATA_G1U1 - json"
GROUP BY
data.GROUP_ID
{
"last_k_data_connection_time":[
"2020-07-26 23:54:27,687",
"2021-07-01 23:54:27,687"
],
"group_id":"G2"
}{
"last_k_data_connection_time":[
"2021-07-01 02:31:29,573",
"2021-07-01 18:11:45,783"
],
"group_id":"G1"
}

Last modified 1yr ago