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
1
[
2
   {
3
      "GROUP_ID":"G1",
4
      "USER_ID":"U1",
5
      "CONNECTION_TIME":"2020-06-26 02:31:29,573"
6
   },
7
   {
8
      "GROUP_ID":"G1",
9
      "USER_ID":"U2",
10
      "CONNECTION_TIME":"2020-06-26 18:11:45,783"
11
   },
12
   {
13
      "GROUP_ID":"G2",
14
      "USER_ID":"Z1",
15
      "CONNECTION_TIME":"2020-06-26 23:54:27,687"
16
   },
17
   {
18
      "GROUP_ID":"G2",
19
      "CONNECTION_TIME":"2020-07-26 23:54:27,687"
20
   },
21
   {
22
      "GROUP_ID":"G1",
23
      "USER_ID":"U2",
24
      "CONNECTION_TIME":"2021-07-01 02:31:29,573"
25
   },
26
   {
27
      "GROUP_ID":"G1",
28
      "USER_ID":"U1",
29
      "CONNECTION_TIME":"2021-07-01 18:11:45,783"
30
   },
31
   {
32
      "GROUP_ID":"G2",
33
      "USER_ID":"Z1",
34
      "CONNECTION_TIME":"2021-07-01 23:54:27,687"
35
   }
36
]
Copied!
Query
List the last two connection times by user group:
1
SET
2
   DATE_TIME = TO_DATE(data.CONNECTION_TIME);
3
SELECT
4
   LAST_K_EACH(2, data.GROUP_ID, data.CONNECTION_TIME) AS last_k_each_data_connection_time__data_group_id 
5
FROM
6
   "SAMPLE_DATA_G1U1 - json"
Copied!
Results:
1
[
2
    {
3
        "last_k_each_data_connection_time__data_group_id":
4
        [
5
            {
6
                "key": "G1",
7
                "value":
8
                [
9
                    "2021-07-01 02:31:29,573",
10
                    "2021-07-01 18:11:45,783"
11
                ]
12
            },
13
            {
14
                "key": "G2",
15
                "value":
16
                [
17
                    "2020-07-26 23:54:27,687",
18
                    "2021-07-01 23:54:27,687"
19
                ]
20
            }
21
        ],
22
    }
23
]
Copied!
Related Function
​LAST
LAST_EACH
LAST_K​
Dialog

LAST_K

LAST_TIME_SERIES

Last modified 8mo ago

Copy link

Contents