LAST_EACH

Returns an array of last values in the time window, grouped by a sub-key. 
The statement is limited to a maximum number of values.
Syntax
LAST_EACH([MAX VALUES, ]GROUP, VALUE)
Arguments
MAX VALUES:   limit of number of key\value elements in the output 
GROUP:             field to aggregate by 
VALUE:             field to sub-aggregate by
‌ Returns
For each key aggregated in the query the function returns an array or key\value pairs of LAST values. 
Notes
This function is non-deterministic.
AVAILABLE IN
Output Type                     Availablity
Aggregate                        No
Explicit Lookup Table    Yes
Inline Joins\Lookups     No
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
For each group get a list of users and their last connection time in the time window: 
1
SET
2
   DATE_TIME = TO_DATE(data.CONNECTION_TIME);
3
SELECT
4
   data.GROUP_ID AS group_id:STRING,
5
   LAST_EACH(data.USER_ID, data.CONNECTION_TIME) AS last_each_data_user_id__data_connection_time 
6
FROM
7
   "SAMPLE_DATA_G1U1 - json" 
8
GROUP BY
9
   data.GROUP_ID
Copied!
Results
1
{
2
   "last_each_data_user_iddata_connection_time":[
3
      {
4
         "key":"Z1",
5
         "value":"2021-07-01 23:54:27,687"
6
      }
7
   ],
8
   "group_id":"G2"
9
}{
10
   "last_each_data_user_iddata_connection_time":[
11
      {
12
         "key":"U1",
13
         "value":"2021-07-01 18:11:45,783"
14
      },
15
      {
16
         "key":"U2",
17
         "value":"2021-07-01 02:31:29,573"
18
      }
19
   ],
20
   "group_id":"G1"
21
}
Copied!
Related Functions
​LAST 
LAST_K 
LAST_K_EACH 
LAST_TIME_SERIES 
FIRST 
MAX_EACH​
Dialog

LAST_ARRAY

LAST_K

Last modified 8mo ago

Copy link

Contents