3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix windowE
17 proc custom {a b} { return [string compare $a $b] }
18 db collate custom custom
21 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT COLLATE custom);
22 INSERT INTO t1 VALUES(1, 'one');
23 INSERT INTO t1 VALUES(2, 'two');
24 INSERT INTO t1 VALUES(3, 'three');
25 INSERT INTO t1 VALUES(4, 'four');
26 INSERT INTO t1 VALUES(5, 'five');
27 INSERT INTO t1 VALUES(6, 'six');
28 CREATE INDEX t1b ON t1(b);
34 1 one 2 two 3 three 4 four 5 five 6 six
38 SELECT group_concat(a,',') OVER win FROM t1
40 ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
46 proc custom {a b} { return [string compare $b $a] }
49 SELECT group_concat(a,',') OVER win FROM t1
51 ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
54 5 5,4 5,4,1 5,4,1,6 5,4,1,6,3 5,4,1,6,3,2
57 #-------------------------------------------------------------------------
63 sqlite3_create_aggregate db
66 do_catchsql_test 2.1 {
67 SELECT min(x) OVER w1 FROM t1
68 WINDOW w1 AS (PARTITION BY x_count(x) OVER w1);
69 } {1 {x_count() may not be used as a window function}}
71 do_catchsql_test 2.2 {
72 SELECT min(x) FILTER (WHERE x_count(x) OVER w1) OVER w1 FROM t1
73 WINDOW w1 AS (PARTITION BY x OVER w1);
74 } {1 {near "OVER": syntax error}}
76 #-------------------------------------------------------------------------
80 CREATE TABLE t2(c1 INT, c2 REAL);
82 (447,0.0), (448,0.0), (449,0.0), (452,0.0), (453,0.0), (454,0.0), (455,0.0),
83 (456,0.0), (459,0.0), (460,0.0), (462,0.0), (463,0.0), (466,0.0), (467,0.0),
84 (468,0.0), (469,0.0), (470,0.0), (473,0.0), (474,0.0), (475,0.0), (476,0.0),
85 (477,0.0), (480,0.0), (481,0.0), (482,0.0), (483,0.0), (484,0.0), (487,0.0),
86 (488,0.0), (489,0.0), (490,0.0), (491,0.0), (494,0.0), (495,0.0), (496,0.0),
87 (497,0.0), (498,0.0), (501,0.0), (502,0.0), (503,0.0), (504,0.0), (505,0.0),
88 (508,0.0), (509,0.0), (510,0.0), (511,0.0), (512,0.0), (515,0.0), (516,0.0),
89 (517,0.0), (518,0.0), (519,0.0), (522,0.0), (523,0.0), (524,0.0), (525,0.0),
90 (526,0.0), (529,0.0), (530,0.0), (531,0.0), (532,0.0), (533,0.0), (536,0.0),
91 (537,1.0), (538,0.0), (539,0.0), (540,0.0), (543,0.0), (544,0.0);
96 select c1, max(c2) over (order by c1 range 366.0 preceding) from t2;
98 447 0.0 448 0.0 449 0.0 452 0.0 453 0.0 454 0.0 455 0.0 456 0.0 459 0.0
99 460 0.0 462 0.0 463 0.0 466 0.0 467 0.0 468 0.0 469 0.0 470 0.0 473 0.0
100 474 0.0 475 0.0 476 0.0 477 0.0 480 0.0 481 0.0 482 0.0 483 0.0 484 0.0
101 487 0.0 488 0.0 489 0.0 490 0.0 491 0.0 494 0.0 495 0.0 496 0.0 497 0.0
102 498 0.0 501 0.0 502 0.0 503 0.0 504 0.0 505 0.0 508 0.0 509 0.0 510 0.0
103 511 0.0 512 0.0 515 0.0 516 0.0 517 0.0 518 0.0 519 0.0 522 0.0 523 0.0
104 524 0.0 525 0.0 526 0.0 529 0.0 530 0.0 531 0.0 532 0.0 533 0.0 536 0.0
105 537 1.0 538 1.0 539 1.0 540 1.0 543 1.0 544 1.0