forked from StarRocks/starrocks
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathtest_array_unique_agg
More file actions
150 lines (150 loc) · 6.66 KB
/
Copy pathtest_array_unique_agg
File metadata and controls
150 lines (150 loc) · 6.66 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
-- name: testArrayUniqueAgg
CREATE TABLE `array_unique_agg_test` (
id int,
s_1 Array<String>,
i_1 Array<BigInt>,
f_1 Array<Double>,
d_1 Array<DECIMAL(26, 2)>,
ai_1 Array<Array<BigInt>>
) ENGINE=OLAP
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 4
PROPERTIES (
"replication_num" = "1",
"enable_persistent_index" = "true",
"replicated_storage" = "true",
"compression" = "LZ4"
);
-- result:
-- !result
insert into array_unique_agg_test values
(1, ['a', 'a'], [1.0], [1.2, 1.2], [1.3], [[1]]),
(2, ['1'], [2.0], [2.1], [100.0], [[2]]),
(1, ['a'], [1.0], [1.2, 1.2], [1.3], [[1]]),
(2, ['1'], [2.0], [2.1, 2.1], [100.0], [[2]]);
-- result:
-- !result
select array_unique_agg(s_1) from array_unique_agg_test group by id order by id;
-- result:
["a"]
["1"]
-- !result
select array_unique_agg(i_1) from array_unique_agg_test group by id order by id;
-- result:
[1]
[2]
-- !result
select array_unique_agg(f_1) from array_unique_agg_test group by id order by id;
-- result:
[1.2]
[2.1]
-- !result
select array_unique_agg(d_1) from array_unique_agg_test group by id order by id;
-- result:
[1.30]
[100.00]
-- !result
-- name: test_array_unique_agg_different_types
create table test_array_agg (
id INT,
col_boolean ARRAY<BOOLEAN>,
col_tinyint ARRAY<TINYINT>,
col_smallint ARRAY<SMALLINT>,
col_int ARRAY<INT>,
col_bigint ARRAY<BIGINT>,
col_largeint ARRAY<LARGEINT>,
col_float ARRAY<FLOAT>,
col_double ARRAY<DOUBLE>,
col_varchar ARRAY<VARCHAR(100)>,
col_char ARRAY<CHAR(10)>,
col_datetime ARRAY<DATETIME>,
col_date ARRAY<DATE>,
col_array ARRAY<ARRAY<INT>>,
col_map ARRAY<MAP<STRING, INT>>,
col_struct ARRAY<STRUCT<f1 INT, f2 STRING>>
) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 3
PROPERTIES ("replication_num" = "1");
-- result:
-- !result
insert into test_array_agg values
(1, [true, false, true], [10, 20, 10], [100, 200, 100], [1000, 2000, 1000], [10000, 20000, 10000], [100000, 200000, 100000], [1.1, 2.2, 1.1], [2.2, 3.3, 2.2], ['hello', 'world', 'hello'], ['char1', 'char2', 'char1'], ['2024-01-01 12:00:00', '2024-02-02 13:00:00', '2024-01-01 12:00:00'], ['2024-01-01', '2024-02-02', '2024-01-01'], [[1,2,3,1]], [map{"key1": 1, "key2": 2}], [row(1, "test1")]),
(2, [false, true, false], [20, 30, 20], [200, 300, 200], [2000, 3000, 2000], [20000, 30000, 20000], [200000, 300000, 200000], [3.3, 4.4, 3.3], [4.4, 5.5, 4.4], ['world', 'hello', 'world'], ['char2', 'char3', 'char2'], ['2024-02-02 13:00:00', '2024-03-03 14:00:00', '2024-02-02 13:00:00'], ['2024-02-02', '2024-03-03', '2024-02-02'], [[4,5,6,4]], [map{"key3": 3, "key4": 4}], [row(2, "test2")]),
(3, [false, true, false], [20, 30, 20], [200, 300, 200], [2000, 3000, 2000], [20000, 30000, 20000], [200000, 300000, 200000], [3.3, 4.4, 3.3], [4.4, 5.5, 4.4], ['world', 'hello', 'world'], ['char2', 'char3', 'char2'], ['2024-02-02 13:00:00', '2024-03-03 14:00:00', '2024-02-02 13:00:00'], ['2024-02-02', '2024-03-03', '2024-02-02'], [[4,5,6,4]], [map{"key3": 3, "key4": 4}], [row(2, "test2")]),
(1, [false, true, false], [20, NULL, 20], [200, 300, 200], [2000, 3000, 2000], [20000, 30000, 20000], [200000, 300000, 200000], [3.3, 4.4, 3.3], [4.4, 5.5, 4.4], ['world', 'hello', 'world', NULL], ['char2', 'char3', 'char2'], ['2024-02-02 13:00:00', '2024-03-03 14:00:00', '2024-02-02 13:00:00'], ['2024-02-02', '2024-03-03', '2024-02-02', NULL], [[4,5,6,4]], [map{"key3": 3, "key4": 4}], [row(2, "test2")]),
(2, [false, NULL, false], [20, 30, 20], [200, NULL, 200], [2000, 3000, NULL], [20000, 30000, 20000], [200000, 300000, NULL], [3.3, 4.4, 3.3, NULL], [4.4, 5.5, 4.4], ['world', 'hello', 'world'], [NULL, 'char2', 'char3', 'char2'], ['2024-02-02 13:00:00', '2024-03-03 14:00:00', '2024-02-02 13:00:00', NULL], ['2024-02-02', '2024-03-03', '2024-02-02'], [[4,5,6,4]], [map{"key3": 3, "key4": 4}], [row(2, "test2")]),
(3, [false, true, false], [20, 30, 20], [200, 300, 200], [2000, 3000, 2000], [20000, 30000, NULL], [200000, 300000, 200000], [3.3, 4.4, 3.3], [4.4, 5.5, 4.4, NULL], ['world', 'hello', 'world'], ['char2', 'char3', 'char2'], ['2024-02-02 13:00:00', '2024-03-03 14:00:00', '2024-02-02 13:00:00'], ['2024-02-02', '2024-03-03', '2024-02-02'], [[4,5,6,4]], [map{"key3": 3, "key4": 4}], [row(2, "test2")]),
(3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
-- result:
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_boolean)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_boolean)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 0 1
2 0 1
3 0 1
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_tinyint)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_tinyint)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 10 20
2 20 30
3 20 30
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_smallint)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_smallint)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 100 300
2 200 300
3 200 300
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_int)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_int)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 1000 3000
2 2000 3000
3 2000 3000
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_bigint)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_bigint)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 10000 30000
2 20000 30000
3 20000 30000
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_largeint)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_largeint)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 100000 300000
2 200000 300000
3 200000 300000
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_float)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_float)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 1.1 4.4
2 3.3 4.4
3 3.3 4.4
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_double)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_double)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 2.2 5.5
2 4.4 5.5
3 4.4 5.5
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_varchar)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_varchar)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 hello world
2 hello world
3 hello world
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_char)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_char)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 char1 char3
2 char2 char3
3 char2 char3
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_datetime)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_datetime)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 2024-01-01 12:00:00 2024-03-03 14:00:00
2 2024-02-02 13:00:00 2024-03-03 14:00:00
3 2024-02-02 13:00:00 2024-03-03 14:00:00
-- !result
SELECT id, ARRAY_MIN(ARRAY_UNIQUE_AGG(col_date)), ARRAY_MAX(ARRAY_UNIQUE_AGG(col_date)) FROM test_array_agg GROUP BY id ORDER BY id;
-- result:
1 2024-01-01 2024-03-03
2 2024-02-02 2024-03-03
3 2024-02-02 2024-03-03
-- !result