Upsolver
Search…
Welcome to Upsolver
Getting Started
Start using Upsolver for free
Get started as a Upsolver user
Upsolver concepts
Upsolver Amazon AWS deployment guide
Tutorials and FAQ
Glossary
Language guide
SQL syntax reference
Functions
Aggregation Functions
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT_EACH
AVG
AVG_EACH
AVG_TIME_SERIES
COLLECT_SET
COLLECT_SET_EACH
COUNT
COUNT(*)
COUNT_DISTINCT
COUNT_EACH
COUNT_IF
DECAYED_SUM
DYNAMIC_SESSIONS
FIRST
FIRST_ARRAY
FIRST_EACH
FIRST_TIME_SERIES
LAST
LAST_ARRAY
LAST_EACH
LAST_K
LAST_K_EACH
LAST_TIME_SERIES
MAX
MAX_BY
MAX_EACH
MAX_TIME_SERIES
MIN
MIN_BY
MIN_EACH
MIN_TIME_SERIES
SESSION_COUNT
STD_DEV
STD_DEV_EACH
STRING_MAX_EACH
STRING_MIN_EACH
SUM
SUM_EACH
SUM_TIME_SERIES
WEIGHTED_AVERAGE
Calculated functions
Data formats
Data types and features
Database output options
Permissions list
Index
Troubleshooting
Connecting data sources
Amazon AWS data sources
Microsoft Azure data sources
Kafka data source
Google Cloud Storage data source
File upload data source
CDC data sources (Debezium)
JDBC data source
HDFS data source
Data source UI
Data source properties
Data outputs and data transformation
Data outputs
Data transformation
Guide for developers
Upsolver REST API
CI/CD on Upsolver
Administration
Connections
Clusters
Python UDF
Reference data
Workspaces
Monitoring
Security
Release notes
Change log
Support
Upsolver support portal
Powered By
GitBook
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
Previous
LAST_ARRAY
Next
LAST_K
Last modified
8mo ago
Copy link
Contents
Syntax
Arguments
‌ Returns
Notes
AVAILABLE IN
Example
Related Functions