COLLECT_SET

A set of all values related to a field.
Syntax
COLLECT_SET([MAX VALUES,] VALUE)
Arguments
MAX VALUES: 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.
VALUE:            The field that can be counted. 
Returns
An ARRAY of the argument type.
Notes
The order of elements in the array is non-deterministic.
NULL values are excluded.
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 distinct entries for each unique group.
1
SET
2
   DATE_TIME = TO_DATE(data.CONNECTION_TIME);
3
SELECT
4
   COLLECT_SET(data.USER_ID) AS collect_set_data_group_id:STRING,
5
   data.GROUP_ID AS group_id:STRING 
6
FROM
7
   "SAMPLE_DATA_G1U1 - json" 
8
GROUP BY
9
   data.GROUP_ID
Copied!
Results
1
{
2
   "collect_set_data_group_id":[
3
      "Z1"
4
   ],
5
   "group_id":"G2"
6
}{
7
   "collect_set_data_group_id":[
8
      "U1",
9
      "U2"
10
   ],
11
   "group_id":"G1"
12
}
Copied!
Related Functions
​COLLECT_SET_EACH​
Dialog

AVG_TIME_SERIES

COLLECT_SET_EACH

Last modified 8mo ago

Copy link

Contents