FIRST

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

Syntax

FIRST(VALUE)

Arguments

VALUE: An expression of any type.

Returns

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

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

Find the first connection_time for each GROUP_ID and USER_ID:

SELECT
   data.GROUP_ID AS group_id:STRING,
   data.USER_ID AS user_id:STRING,
   FIRST(data.CONNECTION_TIME) AS first_data_connection_time:STRING 
FROM
   "SAMPLE_DATA_G1U1 - json" 
GROUP BY
   data.GROUP_ID,
   data.USER_ID"

Results

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

FIRST_ARRAY FIRST_EACH FIRST_TIME_SERIES LAST

Dialog

Last updated