LAST

Returns the last value of the aggregated key in the stream.

Syntax

LAST(VALUE)

Arguments

VALUE: An expression/field of any type.

Returns

Returns a value of last expression for each each key in the aggregation statement. The result type matches the VALUE type.

AVAILABLE IN

Output Type Availablity Aggregate YES Explicit Lookup Table YES Inline Joins\Lookups YES

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 connection time by user group and user id:

SET 
   DATE_TIME = TO_DATE(data.CONNECTION_TIME); 
SELECT
   data.GROUP_ID AS group_id:STRING, 
   data.USER_ID AS user_id:STRING, 
   LAST(data.CONNECTION_TIME) AS last_data_connection_time:STRING 
FROM 
   "SAMPLE_DATA_G1U1-json"
GROUP BY 
   data.GROUP_ID, 
   data.USER_ID

Results

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

Related Functions

FIRST

FIRST LAST_ARRAY LAST_EACH LAST_K LAST_TIME_SERIES

Dialog