LAST_K

The last "k" values per group.
Syntax
LAST_K(VALUE, [K])
Arguments
VALUE:   An expression of any type.
K:           An optional integer (default is 1). ‌
Returns
The result type matches VALUE for each key.
Notes
In Upsolver output, the LAST_K function needs no TYPE field.
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:
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
Results:
{
   "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"
}
Related Functions
​LAST
LAST_EACH
LAST_K_EACH​
Dialog

LAST_EACH

LAST_K_EACH

Last modified 1yr ago