Upsolver
Search…
FIRST_EACH
Returns an array of first values in the time window, grouped by a sub-key. The statement is limited to a maximum number of values.

Syntax

FIRST_EACH([MAX VALUES, ]GROUP, VALUE)

Arguments

MAX VALUES: Limit of key\value elements in the output (Default: 2,147,483,647). When MAX VALUES is omitted, the limit is simply set to the default value. 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 FIRST values of key\value pairs.

Notes

This function is non-deterministic.

AVAILABLE IN

Output Type Availablity Aggregated Outputs 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, find when the each USER first appeared.
1
SELECT
2
data.GROUP_ID AS group_id:STRING,
3
FIRST_EACH(data.USER_ID, data.CONNECTION_TIME) AS first_each_data_user_id__data_connection_time
4
FROM
5
"SAMPLE_DATA_G1U1 - json"
6
GROUP BY
7
data.GROUP_ID
Copied!

Results:

1
{
2
"group_id":"G2",
3
"first_each_data_user_id__data_connection_time":[
4
{
5
"value":"2020-06-26 23:54:27,687",
6
"key":"Z1"
7
}
8
]
9
}{
10
"group_id":"G1",
11
"first_each_data_user_id__data_connection_time":[
12
{
13
"value":"2020-06-26 02:31:29,573",
14
"key":"U1"
15
},
16
{
17
"value":"2020-06-26 18:11:45,783",
18
"key":"U2"
19
}
20
]
21
}
Copied!

Dialog