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
|
# name: test/sql/window/test_list_window.test
# description: Test aggregate list
# group: [window]
statement ok
SET default_null_order='nulls_first';
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE list_extract_test(i INTEGER, g INTEGER);
statement ok
INSERT INTO list_extract_test VALUES (1, 1), (2, 1), (3, 2), (NULL, 3), (42, 3);
statement ok
CREATE VIEW list_window AS
SELECT g, LIST(i) OVER (PARTITION BY g ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as l
FROM list_extract_test;
query II
SELECT * FROM list_window ORDER BY g
----
1 [1, 2]
1 [1, 2]
2 [3]
3 [NULL, 42]
3 [NULL, 42]
query I
SELECT FIRST(LIST_EXTRACT(l, 1)) FROM list_window GROUP BY g ORDER BY g;
----
1
3
NULL
query I
SELECT FIRST(LIST_EXTRACT(l, 2)) FROM list_window GROUP BY g ORDER BY g;
----
2
NULL
42
query I
SELECT FIRST(LIST_EXTRACT(l, 3)) FROM list_window GROUP BY g ORDER BY g;
----
NULL
NULL
NULL
statement ok
create table list_combine_test as
select range%3 j,
range::varchar AS s,
case when range%3=0 then '-' else '|' end sep
from range(1, 65)
query III
select j, s, list(s) over (partition by j order by s)
from list_combine_test
order by j, s;
----
0 12 [12]
0 15 [12, 15]
0 18 [12, 15, 18]
0 21 [12, 15, 18, 21]
0 24 [12, 15, 18, 21, 24]
0 27 [12, 15, 18, 21, 24, 27]
0 3 [12, 15, 18, 21, 24, 27, 3]
0 30 [12, 15, 18, 21, 24, 27, 3, 30]
0 33 [12, 15, 18, 21, 24, 27, 3, 30, 33]
0 36 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36]
0 39 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39]
0 42 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42]
0 45 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45]
0 48 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45, 48]
0 51 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45, 48, 51]
0 54 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45, 48, 51, 54]
0 57 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45, 48, 51, 54, 57]
0 6 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45, 48, 51, 54, 57, 6]
0 60 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45, 48, 51, 54, 57, 6, 60]
0 63 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45, 48, 51, 54, 57, 6, 60, 63]
0 9 [12, 15, 18, 21, 24, 27, 3, 30, 33, 36, 39, 42, 45, 48, 51, 54, 57, 6, 60, 63, 9]
1 1 [1]
1 10 [1, 10]
1 13 [1, 10, 13]
1 16 [1, 10, 13, 16]
1 19 [1, 10, 13, 16, 19]
1 22 [1, 10, 13, 16, 19, 22]
1 25 [1, 10, 13, 16, 19, 22, 25]
1 28 [1, 10, 13, 16, 19, 22, 25, 28]
1 31 [1, 10, 13, 16, 19, 22, 25, 28, 31]
1 34 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34]
1 37 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37]
1 4 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4]
1 40 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40]
1 43 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43]
1 46 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43, 46]
1 49 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43, 46, 49]
1 52 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43, 46, 49, 52]
1 55 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43, 46, 49, 52, 55]
1 58 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43, 46, 49, 52, 55, 58]
1 61 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43, 46, 49, 52, 55, 58, 61]
1 64 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43, 46, 49, 52, 55, 58, 61, 64]
1 7 [1, 10, 13, 16, 19, 22, 25, 28, 31, 34, 37, 4, 40, 43, 46, 49, 52, 55, 58, 61, 64, 7]
2 11 [11]
2 14 [11, 14]
2 17 [11, 14, 17]
2 2 [11, 14, 17, 2]
2 20 [11, 14, 17, 2, 20]
2 23 [11, 14, 17, 2, 20, 23]
2 26 [11, 14, 17, 2, 20, 23, 26]
2 29 [11, 14, 17, 2, 20, 23, 26, 29]
2 32 [11, 14, 17, 2, 20, 23, 26, 29, 32]
2 35 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35]
2 38 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38]
2 41 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41]
2 44 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44]
2 47 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47]
2 5 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 5]
2 50 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 5, 50]
2 53 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 5, 50, 53]
2 56 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 5, 50, 53, 56]
2 59 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 5, 50, 53, 56, 59]
2 62 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 5, 50, 53, 56, 59, 62]
2 8 [11, 14, 17, 2, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 5, 50, 53, 56, 59, 62, 8]
|
