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
|
# name: test/sql/window/test_no_default_window_spec.test
# description: Non-default window specs
# group: [window]
statement ok
PRAGMA enable_verification
statement ok
create table tenk1d(ten int4, four int4)
statement ok
insert into tenk1d values (0,0), (1,1), (3,3), (2,2), (4,2), (9,1), (4,0), (7,3), (0,2), (2,0), (5,1), (1,3), (3,1), (6,0), (8,0), (9,3), (8,2), (6,2), (7,1), (5,3)
# BASIC
query IIRI
SELECT four, ten, sum(ten) over (partition by four order by ten) st, last_value(ten) over (partition by four order by ten) lt FROM tenk1d ORDER BY four, ten
----
0 0 0.000000 0
0 2 2.000000 2
0 4 6.000000 4
0 6 12.000000 6
0 8 20.000000 8
1 1 1.000000 1
1 3 4.000000 3
1 5 9.000000 5
1 7 16.000000 7
1 9 25.000000 9
2 0 0.000000 0
2 2 2.000000 2
2 4 6.000000 4
2 6 12.000000 6
2 8 20.000000 8
3 1 1.000000 1
3 3 4.000000 3
3 5 9.000000 5
3 7 16.000000 7
3 9 25.000000 9
# same but with explicit window def
query IIRI
SELECT four, ten, sum(ten) over (partition by four order by ten range between unbounded preceding and current row) st, last_value(ten) over (partition by four order by ten range between unbounded preceding and current row) lt FROM tenk1d order by four, ten
----
0 0 0.000000 0
0 2 2.000000 2
0 4 6.000000 4
0 6 12.000000 6
0 8 20.000000 8
1 1 1.000000 1
1 3 4.000000 3
1 5 9.000000 5
1 7 16.000000 7
1 9 25.000000 9
2 0 0.000000 0
2 2 2.000000 2
2 4 6.000000 4
2 6 12.000000 6
2 8 20.000000 8
3 1 1.000000 1
3 3 4.000000 3
3 5 9.000000 5
3 7 16.000000 7
3 9 25.000000 9
# unbounded following
query IIRI
SELECT four, ten, sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following) st, last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following) lt FROM tenk1d order by four, ten
----
0 0 20.000000 8
0 2 20.000000 8
0 4 20.000000 8
0 6 20.000000 8
0 8 20.000000 8
1 1 25.000000 9
1 3 25.000000 9
1 5 25.000000 9
1 7 25.000000 9
1 9 25.000000 9
2 0 20.000000 8
2 2 20.000000 8
2 4 20.000000 8
2 6 20.000000 8
2 8 20.000000 8
3 1 25.000000 9
3 3 25.000000 9
3 5 25.000000 9
3 7 25.000000 9
3 9 25.000000 9
# unbounded following with expressions
query IIRI
SELECT four, ten//4 as two, sum(ten//4) over (partition by four order by ten//4 range between unbounded preceding and current row) st, last_value(ten//4) over (partition by four order by ten//4 range between unbounded preceding and current row) lt FROM tenk1d order by four, ten//4
----
0 0 0.000000 0
0 0 0.000000 0
0 1 2.000000 1
0 1 2.000000 1
0 2 4.000000 2
1 0 0.000000 0
1 0 0.000000 0
1 1 2.000000 1
1 1 2.000000 1
1 2 4.000000 2
2 0 0.000000 0
2 0 0.000000 0
2 1 2.000000 1
2 1 2.000000 1
2 2 4.000000 2
3 0 0.000000 0
3 0 0.000000 0
3 1 2.000000 1
3 1 2.000000 1
3 2 4.000000 2
# unbounded following with named windows
query IIRI
SELECT four, ten//4 as two, sum(ten//4) OVER w st, last_value(ten//4) OVER w lt FROM tenk1d WINDOW w AS (partition by four order by ten//4 range between unbounded preceding and current row) order by four, ten//4
----
0 0 0.000000 0
0 0 0.000000 0
0 1 2.000000 1
0 1 2.000000 1
0 2 4.000000 2
1 0 0.000000 0
1 0 0.000000 0
1 1 2.000000 1
1 1 2.000000 1
1 2 4.000000 2
2 0 0.000000 0
2 0 0.000000 0
2 1 2.000000 1
2 1 2.000000 1
2 2 4.000000 2
3 0 0.000000 0
3 0 0.000000 0
3 1 2.000000 1
3 1 2.000000 1
3 2 4.000000 2
|
