blob: f2c596c6fe3f58867f4a79edcd49d7a9c9df2bc9 [file] [log] [blame]
danc9a86682018-05-30 20:44:58 +00001# 2018 May 19
2#
3# The author disclaims copyright to this source code. In place of
4# a legal notice, here is a blessing:
5#
6# May you do good and not evil.
7# May you find forgiveness for yourself and forgive others.
8# May you share freely, never taking more than you give.
9#
10#***********************************************************************
11#
12
13source [file join [file dirname $argv0] pg_common.tcl]
14
15#=========================================================================
16
17start_test window3 "2018 May 31"
dan67a9b8e2018-06-22 20:51:35 +000018ifcapable !windowfunc
danc9a86682018-05-30 20:44:58 +000019
20execsql_test 1.0 {
21 DROP TABLE IF EXISTS t2;
22 CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
23 INSERT INTO t2(a, b) VALUES
danc9a86682018-05-30 20:44:58 +000024 (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62),
25 (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78),
26 (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77),
27 (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7),
28 (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43),
29 (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90),
30 (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56),
31 (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98),
32 (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33),
33 (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84),
34 (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13),
35 (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35),
36 (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8),
37 (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73),
38 (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34),
39 (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77),
40 (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70),
41 (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80),
42 (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66),
43 (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37),
44 (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91),
45 (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69),
46 (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84),
47 (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38),
48 (195,34), (196,83), (197,27), (198,82), (199,17), (200,7);
49}
50
51execsql_test 1.1 {
52 SELECT max(b) OVER (
53 ORDER BY a
54 ) FROM t2
55}
56
57foreach {tn window} {
58 1 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
59 2 "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
60 3 "RANGE BETWEEN CURRENT ROW AND CURRENT ROW"
61 4 "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING"
62 5 "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING"
63 6 "ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING"
64 7 "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
65 8 "ROWS BETWEEN 4 PRECEDING AND CURRENT ROW"
66 9 "ROWS BETWEEN CURRENT ROW AND CURRENT ROW"
67 10 "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING"
68 11 "ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING"
69 12 "ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING"
70 13 "ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING"
71 14 "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
72 15 "ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING"
73 16 "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING"
74 17 "ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING"
danc782a812019-03-15 20:46:19 +000075
76 18 "ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW"
77 19 "ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES"
78 20 "ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP"
79
danc9a86682018-05-30 20:44:58 +000080} {
dandfa552f2018-06-02 21:04:28 +000081 execsql_test 1.$tn.2.1 "SELECT max(b) OVER ( ORDER BY a $window ) FROM t2"
82 execsql_test 1.$tn.2.2 "SELECT min(b) OVER ( ORDER BY a $window ) FROM t2"
83
84 execsql_test 1.$tn.3.1 "
85 SELECT row_number() OVER ( ORDER BY a $window ) FROM t2
86 "
87 execsql_test 1.$tn.3.2 "
88 SELECT row_number() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
89 "
dan9c277582018-06-20 09:23:49 +000090 execsql_test 1.$tn.3.3 "
91 SELECT row_number() OVER ( $window ) FROM t2
92 "
dandfa552f2018-06-02 21:04:28 +000093
94 execsql_test 1.$tn.4.1 "
95 SELECT dense_rank() OVER ( ORDER BY a $window ) FROM t2
96 "
97 execsql_test 1.$tn.4.2 "
98 SELECT dense_rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
99 "
100 execsql_test 1.$tn.4.3 "
101 SELECT dense_rank() OVER ( ORDER BY b $window ) FROM t2
102 "
103 execsql_test 1.$tn.4.4 "
104 SELECT dense_rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
105 "
106 execsql_test 1.$tn.4.5 "
107 SELECT dense_rank() OVER ( ORDER BY b%10 $window ) FROM t2
108 "
109 execsql_test 1.$tn.4.6 "
110 SELECT dense_rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2
111 "
112
113 execsql_test 1.$tn.5.1 "
114 SELECT rank() OVER ( ORDER BY a $window ) FROM t2
115 "
116 execsql_test 1.$tn.5.2 "
117 SELECT rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
118 "
119 execsql_test 1.$tn.5.3 "
120 SELECT rank() OVER ( ORDER BY b $window ) FROM t2
121 "
122 execsql_test 1.$tn.5.4 "
123 SELECT rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
124 "
125 execsql_test 1.$tn.5.5 "
126 SELECT rank() OVER ( ORDER BY b%10 $window ) FROM t2
127 "
128 execsql_test 1.$tn.5.6 "
129 SELECT rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2
130 "
131
132 execsql_test 1.$tn.6.1 "
133 SELECT
134 row_number() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ),
135 rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ),
136 dense_rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window )
137 FROM t2
138 "
139
danf1abe362018-06-04 08:22:09 +0000140 execsql_float_test 1.$tn.7.1 "
141 SELECT percent_rank() OVER ( ORDER BY a $window ) FROM t2
142 "
143 execsql_float_test 1.$tn.7.2 "
144 SELECT percent_rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
145 "
146 execsql_float_test 1.$tn.7.3 "
147 SELECT percent_rank() OVER ( ORDER BY b $window ) FROM t2
148 "
149 execsql_float_test 1.$tn.7.4 "
150 SELECT percent_rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
151 "
152 execsql_float_test 1.$tn.7.5 "
153 SELECT percent_rank() OVER ( ORDER BY b%10 $window ) FROM t2
154 "
155 execsql_float_test 1.$tn.7.6 "
danec891fd2018-06-06 20:51:02 +0000156 SELECT percent_rank() OVER (PARTITION BY b%2 ORDER BY b%10 $window) FROM t2
danf1abe362018-06-04 08:22:09 +0000157 "
dandfa552f2018-06-02 21:04:28 +0000158
danf1abe362018-06-04 08:22:09 +0000159 execsql_float_test 1.$tn.8.1 "
160 SELECT cume_dist() OVER ( ORDER BY a $window ) FROM t2
161 "
162 execsql_float_test 1.$tn.8.2 "
163 SELECT cume_dist() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
164 "
165 execsql_float_test 1.$tn.8.3 "
166 SELECT cume_dist() OVER ( ORDER BY b $window ) FROM t2
167 "
168 execsql_float_test 1.$tn.8.4 "
169 SELECT cume_dist() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
170 "
171 execsql_float_test 1.$tn.8.5 "
172 SELECT cume_dist() OVER ( ORDER BY b%10 $window ) FROM t2
173 "
174 execsql_float_test 1.$tn.8.6 "
175 SELECT cume_dist() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2
176 "
dandfa552f2018-06-02 21:04:28 +0000177
dan6bc5c9e2018-06-04 18:55:11 +0000178 execsql_float_test 1.$tn.8.1 "
179 SELECT ntile(100) OVER ( ORDER BY a $window ) FROM t2
180 "
181 execsql_float_test 1.$tn.8.2 "
182 SELECT ntile(101) OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
183 "
184 execsql_float_test 1.$tn.8.3 "
185 SELECT ntile(102) OVER ( ORDER BY b,a $window ) FROM t2
186 "
187 execsql_float_test 1.$tn.8.4 "
188 SELECT ntile(103) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
189 "
190 execsql_float_test 1.$tn.8.5 "
191 SELECT ntile(104) OVER ( ORDER BY b%10,a $window ) FROM t2
192 "
193 execsql_float_test 1.$tn.8.6 "
194 SELECT ntile(105) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
195 "
dan9c277582018-06-20 09:23:49 +0000196 execsql_float_test 1.$tn.8.7 "
197 SELECT ntile(105) OVER ( $window ) FROM t2
198 "
danc9a86682018-05-30 20:44:58 +0000199
dan1c5ed622018-06-05 16:16:17 +0000200 execsql_test 1.$tn.9.1 "
201 SELECT last_value(a+b) OVER ( ORDER BY a $window ) FROM t2
202 "
203 execsql_test 1.$tn.9.2 "
204 SELECT last_value(a+b) OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
205 "
206 execsql_test 1.$tn.9.3 "
207 SELECT last_value(a+b) OVER ( ORDER BY b,a $window ) FROM t2
208 "
209 execsql_test 1.$tn.9.4 "
210 SELECT last_value(a+b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
211 "
212 execsql_test 1.$tn.9.5 "
213 SELECT last_value(a+b) OVER ( ORDER BY b%10,a $window ) FROM t2
214 "
215 execsql_test 1.$tn.9.6 "
216 SELECT last_value(a+b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
217 "
218
danec891fd2018-06-06 20:51:02 +0000219 execsql_test 1.$tn.10.1 "
220 SELECT nth_value(b,b+1) OVER (ORDER BY a $window) FROM t2
221 "
dan2e605682018-06-07 15:54:26 +0000222 execsql_test 1.$tn.10.2 "
danec891fd2018-06-06 20:51:02 +0000223 SELECT nth_value(b,b+1) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
224 "
dan2e605682018-06-07 15:54:26 +0000225 execsql_test 1.$tn.10.3 "
danec891fd2018-06-06 20:51:02 +0000226 SELECT nth_value(b,b+1) OVER ( ORDER BY b,a $window ) FROM t2
227 "
dan2e605682018-06-07 15:54:26 +0000228 execsql_test 1.$tn.10.4 "
danec891fd2018-06-06 20:51:02 +0000229 SELECT nth_value(b,b+1) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
230 "
dan2e605682018-06-07 15:54:26 +0000231 execsql_test 1.$tn.10.5 "
danec891fd2018-06-06 20:51:02 +0000232 SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a $window ) FROM t2
233 "
dan2e605682018-06-07 15:54:26 +0000234 execsql_test 1.$tn.10.6 "
danec891fd2018-06-06 20:51:02 +0000235 SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
236 "
dan7095c002018-06-07 17:45:22 +0000237
238 execsql_test 1.$tn.11.1 "
239 SELECT first_value(b) OVER (ORDER BY a $window) FROM t2
240 "
241 execsql_test 1.$tn.11.2 "
242 SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
243 "
244 execsql_test 1.$tn.11.3 "
245 SELECT first_value(b) OVER ( ORDER BY b,a $window ) FROM t2
246 "
247 execsql_test 1.$tn.11.4 "
248 SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
249 "
250 execsql_test 1.$tn.11.5 "
251 SELECT first_value(b) OVER ( ORDER BY b%10,a $window ) FROM t2
252 "
253 execsql_test 1.$tn.11.6 "
254 SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
255 "
danfe4e25a2018-06-07 20:08:59 +0000256
257 execsql_test 1.$tn.12.1 "
258 SELECT lead(b,b) OVER (ORDER BY a $window) FROM t2
259 "
260 execsql_test 1.$tn.12.2 "
261 SELECT lead(b,b) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
262 "
263 execsql_test 1.$tn.12.3 "
264 SELECT lead(b,b) OVER ( ORDER BY b,a $window ) FROM t2
265 "
266 execsql_test 1.$tn.12.4 "
267 SELECT lead(b,b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
268 "
269 execsql_test 1.$tn.12.5 "
270 SELECT lead(b,b) OVER ( ORDER BY b%10,a $window ) FROM t2
271 "
272 execsql_test 1.$tn.12.6 "
273 SELECT lead(b,b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
274 "
275
276 execsql_test 1.$tn.13.1 "
277 SELECT lag(b,b) OVER (ORDER BY a $window) FROM t2
278 "
279 execsql_test 1.$tn.13.2 "
280 SELECT lag(b,b) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
281 "
282 execsql_test 1.$tn.13.3 "
283 SELECT lag(b,b) OVER ( ORDER BY b,a $window ) FROM t2
284 "
285 execsql_test 1.$tn.13.4 "
286 SELECT lag(b,b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
287 "
288 execsql_test 1.$tn.13.5 "
289 SELECT lag(b,b) OVER ( ORDER BY b%10,a $window ) FROM t2
290 "
291 execsql_test 1.$tn.13.6 "
292 SELECT lag(b,b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
293 "
dan03854d22018-06-08 11:45:28 +0000294
295 execsql_test 1.$tn.14.1 "
296 SELECT string_agg(CAST(b AS TEXT), '.') OVER (ORDER BY a $window) FROM t2
297 "
dan03854d22018-06-08 11:45:28 +0000298 execsql_test 1.$tn.14.2 "
299 SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
300 "
301 execsql_test 1.$tn.14.3 "
302 SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b,a $window ) FROM t2
303 "
304 execsql_test 1.$tn.14.4 "
305 SELECT string_agg(CAST(b AS TEXT), '.') OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
306 "
307 execsql_test 1.$tn.14.5 "
308 SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a $window ) FROM t2
309 "
310 execsql_test 1.$tn.14.6 "
311 SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
312 "
dan8b985602018-06-09 17:43:45 +0000313
dane7c9ca42019-02-16 17:27:51 +0000314 execsql_test 1.$tn.14.7 "
315 SELECT string_agg(CAST(b AS TEXT), '.') OVER (win1 ORDER BY b%10 $window)
316 FROM t2
317 WINDOW win1 AS (PARTITION BY b%2,a)
318 ORDER BY 1
319 "
320
321 execsql_test 1.$tn.14.8 "
322 SELECT string_agg(CAST(b AS TEXT), '.') OVER (win1 $window)
323 FROM t2
324 WINDOW win1 AS (PARTITION BY b%2,a ORDER BY b%10)
325 ORDER BY 1
326 "
327
328 execsql_test 1.$tn.14.9 "
329 SELECT string_agg(CAST(b AS TEXT), '.') OVER win2
330 FROM t2
331 WINDOW win1 AS (PARTITION BY b%2,a ORDER BY b%10),
332 win2 AS (win1 $window)
333 ORDER BY 1
334 "
335
dan8b985602018-06-09 17:43:45 +0000336 execsql_test 1.$tn.15.1 "
dan7262ca92018-07-02 12:07:32 +0000337 SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.')
338 FILTER (WHERE a%2=0) OVER win FROM t2
339 WINDOW win AS (ORDER BY a $window)
dan8b985602018-06-09 17:43:45 +0000340 "
341
342 execsql_test 1.$tn.15.2 "
dan7262ca92018-07-02 12:07:32 +0000343 SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.')
344 FILTER (WHERE 0=1) OVER win FROM t2
345 WINDOW win AS (ORDER BY a $window)
dan8b985602018-06-09 17:43:45 +0000346 "
347
348 execsql_test 1.$tn.15.3 "
dan7262ca92018-07-02 12:07:32 +0000349 SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.')
350 FILTER (WHERE 1=0) OVER win FROM t2
351 WINDOW win AS (PARTITION BY (a%10) ORDER BY a $window)
dan8b985602018-06-09 17:43:45 +0000352 "
353
354 execsql_test 1.$tn.15.4 "
dan7262ca92018-07-02 12:07:32 +0000355 SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.')
356 FILTER (WHERE a%2=0) OVER win FROM t2
357 WINDOW win AS (PARTITION BY (a%10) ORDER BY a $window)
dan8b985602018-06-09 17:43:45 +0000358 "
dan7262ca92018-07-02 12:07:32 +0000359
dan6bc5c9e2018-06-04 18:55:11 +0000360}
dandfa552f2018-06-02 21:04:28 +0000361
danc9a86682018-05-30 20:44:58 +0000362finish_test
363