Debian Codegraph @

test/window8.tcl

raw
1 # 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
13 source [file join [file dirname $argv0] pg_common.tcl]
14
15 #=========================================================================
16
17 start_test window8 "2019 March 01"
18 ifcapable !windowfunc
19
20 execsql_test 1.0 {
21 DROP TABLE IF EXISTS t3;
22 CREATE TABLE t3(a TEXT, b TEXT, c INTEGER);
23 INSERT INTO t3 VALUES
24 ('HH', 'bb', 355), ('CC', 'aa', 158), ('BB', 'aa', 399),
25 ('FF', 'bb', 938), ('HH', 'aa', 480), ('FF', 'bb', 870),
26 ('JJ', 'aa', 768), ('JJ', 'aa', 899), ('GG', 'bb', 929),
27 ('II', 'bb', 421), ('GG', 'bb', 844), ('FF', 'bb', 574),
28 ('CC', 'bb', 822), ('GG', 'bb', 938), ('BB', 'aa', 660),
29 ('HH', 'aa', 979), ('BB', 'bb', 792), ('DD', 'aa', 845),
30 ('JJ', 'bb', 354), ('FF', 'bb', 295), ('JJ', 'aa', 234),
31 ('BB', 'bb', 840), ('AA', 'aa', 934), ('EE', 'aa', 113),
32 ('AA', 'bb', 309), ('BB', 'aa', 412), ('AA', 'aa', 911),
33 ('AA', 'bb', 572), ('II', 'aa', 398), ('II', 'bb', 250),
34 ('II', 'aa', 652), ('BB', 'bb', 633), ('AA', 'aa', 239),
35 ('FF', 'aa', 670), ('BB', 'bb', 705), ('HH', 'bb', 963),
36 ('CC', 'bb', 346), ('II', 'bb', 671), ('BB', 'aa', 247),
37 ('AA', 'aa', 223), ('GG', 'aa', 480), ('HH', 'aa', 790),
38 ('FF', 'aa', 208), ('BB', 'bb', 711), ('EE', 'aa', 777),
39 ('DD', 'bb', 716), ('CC', 'aa', 759), ('CC', 'aa', 430),
40 ('CC', 'aa', 607), ('DD', 'bb', 794), ('GG', 'aa', 148),
41 ('GG', 'aa', 634), ('JJ', 'bb', 257), ('DD', 'bb', 959),
42 ('FF', 'bb', 726), ('BB', 'aa', 762), ('JJ', 'bb', 336),
43 ('GG', 'aa', 335), ('HH', 'bb', 330), ('GG', 'bb', 160),
44 ('JJ', 'bb', 839), ('FF', 'aa', 618), ('BB', 'aa', 393),
45 ('EE', 'bb', 629), ('FF', 'aa', 667), ('AA', 'bb', 870),
46 ('FF', 'bb', 102), ('JJ', 'aa', 113), ('DD', 'aa', 224),
47 ('AA', 'bb', 627), ('HH', 'bb', 730), ('II', 'bb', 443),
48 ('HH', 'bb', 133), ('EE', 'bb', 252), ('II', 'bb', 805),
49 ('BB', 'bb', 786), ('EE', 'bb', 768), ('HH', 'bb', 683),
50 ('DD', 'bb', 238), ('DD', 'aa', 256);
51 }
52
53 foreach {tn frame} {
54 1 { GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING }
55 2 { GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW }
56 3 { GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING }
57 4 { GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING }
58 5 { GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING }
59 6 { GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING }
60 7 { GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING }
61 8 { GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING }
62 9 { GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW }
63 10 { GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING }
64 11 { GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING }
65 12 { GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING }
66 13 { GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING }
67 14 { GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING }
68 15 { GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
69 16 { GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING }
70 17 { GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING }
71 18 { GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING }
72 19 { GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING }
73
74 } {
75 execsql_test 1.$tn.1 "
76 SELECT a, b, sum(c) OVER (ORDER BY a $frame) FROM t3 ORDER BY 1, 2, 3;
77 "
78 execsql_test 1.$tn.2 "
79 SELECT a, b, sum(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
80 "
81 execsql_test 1.$tn.3 "
82 SELECT a, b, rank() OVER (ORDER BY a $frame) FROM t3 ORDER BY 1, 2, 3;
83 "
84 execsql_test 1.$tn.4 "
85 SELECT a, b, max(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
86 "
87 execsql_test 1.$tn.5 "
88 SELECT a, b, min(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
89 "
90
91 set f2 "$frame EXCLUDE CURRENT ROW"
92
93 execsql_test 1.$tn.6 "
94 SELECT a, b, sum(c) OVER (ORDER BY a $f2) FROM t3 ORDER BY 1, 2, 3;
95 "
96 execsql_test 1.$tn.7 "
97 SELECT a, b, sum(c) OVER (ORDER BY a,b $f2) FROM t3 ORDER BY 1, 2, 3;
98 "
99
100 execsql_test 1.$tn.8 "
101 SELECT a, b,
102 sum(c) OVER (ORDER BY a $f2),
103 sum(c) OVER (ORDER BY a $frame),
104 sum(c) OVER (ORDER BY a,b $f2),
105 sum(c) OVER (ORDER BY a,b $frame)
106 FROM t3 ORDER BY 1, 2, 3;
107 "
108 }
109
110
111 foreach {tn ex} {
112 1 { EXCLUDE NO OTHERS }
113 2 { EXCLUDE CURRENT ROW }
114 3 { EXCLUDE GROUP }
115 4 { EXCLUDE TIES }
116 } {
117 execsql_test 2.$tn.1 "
118 SELECT row_number() OVER win
119 FROM t3
120 WINDOW win AS (
121 ORDER BY c, b, a
122 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex
123 )
124 "
125
126 execsql_test 2.$tn.2 "
127 SELECT nth_value(c, 14) OVER win
128 FROM t3
129 WINDOW win AS (
130 ORDER BY c, b, a
131 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex
132 )
133 "
134
135 execsql_test 2.$tn.3 "
136 SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
137 WINDOW win AS (
138 ORDER BY c, b, a
139 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW $ex
140 ) ORDER BY a, b, c;
141 "
142 }
143
144 ==========
145
146 execsql_test 3.0 {
147 DROP TABLE IF EXISTS t1;
148 CREATE TABLE t1(a REAL, b INTEGER);
149 INSERT INTO t1 VALUES
150 (5, 10), (10, 20), (13, 26), (13, 26),
151 (15, 30), (20, 40), (22,80), (30, 90);
152 }
153
154 foreach {tn frame} {
155 1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
156 2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
157 3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
158 4 { ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
159 5 { ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
160 6 { ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
161
162 7 { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
163 8 { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
164 9 { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
165 10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
166 11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
167 12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
168 13 { ORDER BY a RANGE 5.1 PRECEDING }
169 } {
170 execsql_test 3.$tn "
171 SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
172 "
173 }
174
175 ==========
176
177 execsql_test 4.0 {
178 DROP TABLE IF EXISTS t1;
179 CREATE TABLE t1(a INTEGER, b INTEGER);
180 INSERT INTO t1 VALUES
181 (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
182 }
183
184 execsql_test 4.1.1 {
185 SELECT sum(b) OVER (
186 ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
187 ) FROM t1 ORDER BY 1;
188 }
189 execsql_test 4.1.2 {
190 SELECT sum(b) OVER (
191 ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
192 ) FROM t1 ORDER BY 1;
193 }
194
195 execsql_test 4.2.1 {
196 SELECT sum(b) OVER (
197 ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
198 ) FROM t1 ORDER BY 1 NULLS FIRST;
199 }
200 execsql_test 4.2.2 {
201 SELECT sum(b) OVER (
202 ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
203 ) FROM t1 ORDER BY 1 NULLS LAST;
204 }
205
206 execsql_test 4.2.3 {
207 SELECT sum(b) OVER (
208 ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
209 ) FROM t1 ORDER BY 1 NULLS FIRST;
210 }
211 execsql_test 4.2.4 {
212 SELECT sum(b) OVER (
213 ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
214 ) FROM t1 ORDER BY 1 NULLS LAST;
215 }
216
217 execsql_test 4.3.1 {
218 SELECT sum(b) OVER (
219 ORDER BY a NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
220 ) FROM t1 ORDER BY 1 NULLS FIRST;
221 }
222 execsql_test 4.3.2 {
223 SELECT sum(b) OVER (
224 ORDER BY a NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
225 ) FROM t1 ORDER BY 1 NULLS LAST;
226 }
227
228 execsql_test 4.4.1 {
229 SELECT sum(b) OVER (
230 ORDER BY a NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
231 ) FROM t1 ORDER BY 1 NULLS FIRST;
232 }
233 execsql_test 4.4.2 {
234 SELECT sum(b) OVER (
235 ORDER BY a NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
236 ) FROM t1 ORDER BY 1 NULLS LAST;
237 }
238
239 execsql_test 4.4.3 {
240 SELECT sum(b) OVER (
241 ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
242 ) FROM t1 ORDER BY 1 NULLS FIRST;
243 }
244 execsql_test 4.4.4 {
245 SELECT sum(b) OVER (
246 ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
247 ) FROM t1 ORDER BY 1 NULLS LAST;
248 }
249
250 execsql_test 4.5.1 {
251 SELECT sum(b) OVER (
252 ORDER BY a ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
253 ) FROM t1 ORDER BY 1 NULLS LAST;
254 }
255 execsql_test 4.5.2 {
256 SELECT sum(b) OVER (
257 ORDER BY a DESC NULLS FIRST RANGE
258 BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
259 ) FROM t1 ORDER BY 1 NULLS LAST;
260 }
261
262 ==========
263
264 execsql_test 5.0 {
265 INSERT INTO t3 VALUES
266 (NULL, 'bb', 355), (NULL, 'cc', 158), (NULL, 'aa', 399),
267 ('JJ', NULL, 839), ('FF', NULL, 618), ('BB', NULL, 393),
268 (NULL, 'bb', 629), (NULL, NULL, 667), (NULL, NULL, 870);
269 }
270
271 foreach {tn ex} {
272 1 { EXCLUDE NO OTHERS }
273 2 { EXCLUDE CURRENT ROW }
274 3 { EXCLUDE GROUP }
275 4 { EXCLUDE TIES }
276 } {
277 foreach {tn2 frame} {
278 1 { RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
279 2 { ORDER BY a NULLS FIRST
280 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
281 3 { PARTITION BY coalesce(a, '')
282 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
283 4 { ORDER BY a NULLS FIRST GROUPS 6 PRECEDING }
284 5 { ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING }
285 6 { ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING }
286 7 { ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
287 ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING }
288
289 8 { RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
290 9 { ORDER BY a NULLS LAST
291 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
292 10 { PARTITION BY coalesce(a, '')
293 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
294 11 { ORDER BY a NULLS LAST GROUPS 6 PRECEDING }
295 12 { ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING }
296 13 { ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING }
297 14 { ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
298 ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING }
299 } {
300 execsql_test 5.$tn.$tn2.1 "
301 SELECT max(c) OVER win,
302 min(c) OVER win,
303 count(a) OVER win
304 FROM t3
305 WINDOW win AS ( $frame $ex )
306 ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
307 "
308
309 execsql_test 5.$tn.$tn2.2 "
310 SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
311 rank() OVER win,
312 dense_rank() OVER win
313 FROM t3
314 WINDOW win AS ( $frame $ex )
315 ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
316 "
317 }
318 }
319
320 ==========
321
322 execsql_test 6.0 {
323 DROP TABLE IF EXISTS t2;
324 CREATE TABLE t2(a TEXT, b INTEGER);
325 INSERT INTO t2 VALUES('A', NULL);
326 INSERT INTO t2 VALUES('B', NULL);
327 INSERT INTO t2 VALUES('C', 1);
328 }
329
330 execsql_test 6.1 {
331 SELECT string_agg(a, '.') OVER (
332 ORDER BY b NULLS FIRST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
333 )
334 FROM t2
335 }
336
337 execsql_test 6.2 {
338 SELECT string_agg(a, '.') OVER (
339 ORDER BY b DESC NULLS LAST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
340 )
341 FROM t2
342 }
343
344 ==========
345
346 execsql_test 7.0 {
347 DROP TABLE IF EXISTS t2;
348 CREATE TABLE t2(a INTEGER, b INTEGER);
349
350 INSERT INTO t2 VALUES(1, 65);
351 INSERT INTO t2 VALUES(2, NULL);
352 INSERT INTO t2 VALUES(3, NULL);
353 INSERT INTO t2 VALUES(4, NULL);
354 INSERT INTO t2 VALUES(5, 66);
355 INSERT INTO t2 VALUES(6, 67);
356 }
357
358 foreach {tn f ex} {
359 1 sum ""
360 2 min ""
361 3 sum "EXCLUDE CURRENT ROW"
362 4 max "EXCLUDE CURRENT ROW"
363 } {
364 execsql_test 7.$tn.1 "
365 SELECT $f (a) OVER win FROM t2
366 WINDOW win AS (
367 ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
368 );
369 "
370 execsql_test 7.$tn.2 "
371 SELECT $f (a) OVER win FROM t2
372 WINDOW win AS (
373 ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
374 );
375 "
376 execsql_test 7.$tn.3 "
377 SELECT $f (a) OVER win FROM t2
378 WINDOW win AS (
379 ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
380 );
381 "
382 execsql_test 7.$tn.4 "
383 SELECT $f (a) OVER win FROM t2
384 WINDOW win AS (
385 ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
386 );
387 "
388 execsql_test 7.$tn.5 "
389 SELECT $f (a) OVER win FROM t2
390 WINDOW win AS (
391 ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
392 );
393 "
394
395 execsql_test 7.$tn.6 "
396 SELECT $f (a) OVER win FROM t2
397 WINDOW win AS (
398 ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
399 );
400 "
401 execsql_test 7.$tn.7 "
402 SELECT $f (a) OVER win FROM t2
403 WINDOW win AS (
404 ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
405 );
406 "
407 execsql_test 7.$tn.8 "
408 SELECT $f (a) OVER win FROM t2
409 WINDOW win AS (
410 ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
411 );
412 "
413 execsql_test 7.$tn.9 "
414 SELECT $f (a) OVER win FROM t2
415 WINDOW win AS (
416 ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
417 );
418 "
419 }
420
421 ==========
422
423 execsql_test 8.0 {
424 DROP TABLE IF EXISTS tx;
425 CREATE TABLE tx(a INTEGER PRIMARY KEY);
426 INSERT INTO tx VALUES(1), (2), (3), (4), (5), (6);
427
428 DROP TABLE IF EXISTS map;
429 CREATE TABLE map(v INTEGER PRIMARY KEY, t TEXT);
430 INSERT INTO map VALUES
431 (1, 'odd'), (2, 'even'), (3, 'odd'),
432 (4, 'even'), (5, 'odd'), (6, 'even');
433 }
434
435 execsql_test 8.1 {
436 SELECT sum(a) OVER (
437 PARTITION BY (
438 SELECT t FROM map WHERE v=a
439 ) ORDER BY a
440 ) FROM tx;
441 }
442
443 execsql_test 8.2 {
444 SELECT sum(a) OVER win FROM tx
445 WINDOW win AS (
446 PARTITION BY (
447 SELECT t FROM map WHERE v=a
448 ) ORDER BY a
449 );
450 }
451
452 execsql_test 8.3 {
453 WITH map2 AS (
454 SELECT * FROM map
455 )
456 SELECT sum(a) OVER (
457 PARTITION BY (
458 SELECT t FROM map2 WHERE v=a
459 ) ORDER BY a
460 ) FROM tx;
461 }
462
463 execsql_test 8.4 {
464 WITH map2 AS (
465 SELECT * FROM map
466 )
467 SELECT sum(a) OVER win FROM tx
468 WINDOW win AS (
469 PARTITION BY (
470 SELECT t FROM map2 WHERE v=a
471 ) ORDER BY a
472 );
473 }
474
475 ==========
476
477 execsql_test 9.1 {
478 DROP TABLE IF EXISTS t1;
479 DROP TABLE IF EXISTS t2;
480 CREATE TABLE t1(a INTEGER);
481 CREATE TABLE t2(y INTEGER);
482 }
483
484 execsql_test 9.2 {
485 SELECT (
486 SELECT max(a) OVER ( ORDER BY (SELECT sum(a) FROM t1) )
487 + min(a) OVER()
488 )
489 FROM t1
490 }
491
492 ==========
493
494 execsql_test 10.0 {
495 DROP TABLE IF EXISTS t1;
496 CREATE TABLE t1(a INTEGER, b INTEGER);
497 INSERT INTO t1 VALUES (10, 1),
498 (20, -1),
499 (5, 2),
500 (15, 0),
501 (25, 3);
502 }
503
504 execsql_test 10.1 {
505 SELECT
506 a, b, MIN(a) FILTER(WHERE b > 0) OVER win
507 FROM t1
508 WINDOW win AS (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
509 }
510
511 execsql_test 10.2 {
512 SELECT
513 a, b, MIN(a) FILTER(WHERE b > 0) OVER win
514 FROM t1
515 WINDOW win AS ();
516 }
517
518 execsql_test 10.3 {
519 SELECT
520 a, b, MIN(a) FILTER(WHERE b > 0) OVER win
521 FROM t1
522 WINDOW win AS (ORDER BY a);
523 }
524
525 execsql_test 10.4 {
526 SELECT
527 a, b, MIN(a) OVER win
528 FROM t1
529 WINDOW win AS (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
530 }
531
532 ==========
533
534 execsql_test 11.0 {
535 DROP TABLE IF EXISTS t2;
536 CREATE TABLE t2(a INTEGER, b INTEGER);
537 INSERT INTO t2 VALUES(1, 12);
538 INSERT INTO t2 VALUES(2, 10);
539 INSERT INTO t2 VALUES(3, 15);
540 INSERT INTO t2 VALUES(4, 22);
541 INSERT INTO t2 VALUES(5, 1);
542 INSERT INTO t2 VALUES(6, 4);
543 INSERT INTO t2 VALUES(7, 7);
544 INSERT INTO t2 VALUES(8, 6);
545 INSERT INTO t2 VALUES(9, 22);
546 INSERT INTO t2 VALUES(10, 2);
547 }
548
549 execsql_test 11.1 {
550 SELECT a, min(b) FILTER (WHERE a%2 != 0) OVER win
551 FROM t2
552 WINDOW win AS (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
553 }
554
555 finish_test
556
557