Upsolver
Search…
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!

Dialog