LAST_K_EACH

The last "k" values per group.

Syntax

LAST_K_EACH([MAX VALUES, ]2, GROUP, VALUE)

Arguments

MAX VALUES: The maximum VALUE that is allowed. The maximum number of entries that can be counted (default: 2,147,483,647) ‌ MAX VALUES is optional. If it is omitted there is, in effect, no limit. K: A number (of last entries) GROUP: A group of any type. VALUE: A value of any type.

‌Returns

The result type is an array of VALUE for each type of group.

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
   LAST_K_EACH(2, data.GROUP_ID, data.CONNECTION_TIME) AS last_k_each_data_connection_time__data_group_id 
FROM
   "SAMPLE_DATA_G1U1 - json"

Results:

[
    {
        "last_k_each_data_connection_time__data_group_id":
        [
            {
                "key": "G1",
                "value":
                [
                    "2021-07-01 02:31:29,573",
                    "2021-07-01 18:11:45,783"
                ]
            },
            {
                "key": "G2",
                "value":
                [
                    "2020-07-26 23:54:27,687",
                    "2021-07-01 23:54:27,687"
                ]
            }
        ],
    }
]

Related Function

LAST LAST_EACH LAST_K

Dialog