xref: /sqlite-3.40.0/test/window4.tcl (revision fe4e25a0) 
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
13source [file join [file dirname $argv0] pg_common.tcl]
14
15#=========================================================================
16
17start_test window4 "2018 June 04"
18
19execsql_test 1.0 {
20  DROP TABLE IF EXISTS t3;
21  CREATE TABLE t3(a TEXT PRIMARY KEY);
22  INSERT INTO t3 VALUES('a'), ('b'), ('c'), ('d'), ('e');
23  INSERT INTO t3 VALUES('f'), ('g'), ('h'), ('i'), ('j');
24}
25
26for {set i 1} {$i < 20} {incr i} {
27  execsql_test 1.$i "SELECT a, ntile($i) OVER (ORDER BY a) FROM t3"
28}
29
30execsql_test 2.0 {
31  DROP TABLE IF EXISTS t4;
32  CREATE TABLE t4(a INTEGER PRIMARY KEY, b TEXT, c INTEGER);
33  INSERT INTO t4 VALUES(1, 'A', 9);
34  INSERT INTO t4 VALUES(2, 'B', 3);
35  INSERT INTO t4 VALUES(3, 'C', 2);
36  INSERT INTO t4 VALUES(4, 'D', 10);
37  INSERT INTO t4 VALUES(5, 'E', 5);
38  INSERT INTO t4 VALUES(6, 'F', 1);
39  INSERT INTO t4 VALUES(7, 'G', 1);
40  INSERT INTO t4 VALUES(8, 'H', 2);
41  INSERT INTO t4 VALUES(9, 'I', 10);
42  INSERT INTO t4 VALUES(10, 'J', 4);
43}
44
45execsql_test 2.1 {
46  SELECT a, nth_value(b, c) OVER (ORDER BY a) FROM t4
47}
48
49execsql_test 2.2.1 {
50  SELECT a, lead(b) OVER (ORDER BY a) FROM t4
51}
52execsql_test 2.2.2 {
53  SELECT a, lead(b, 2) OVER (ORDER BY a) FROM t4
54}
55execsql_test 2.2.3 {
56  SELECT a, lead(b, 3, 'abc') OVER (ORDER BY a) FROM t4
57}
58
59execsql_test 2.3.1 {
60  SELECT a, lag(b) OVER (ORDER BY a) FROM t4
61}
62execsql_test 2.3.2 {
63  SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
64}
65execsql_test 2.3.3 {
66  SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
67}
68
69execsql_test 3.0 {
70  DROP TABLE IF EXISTS t5;
71  CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
72  INSERT INTO t5 VALUES(1, 'A', 'one',   5);
73  INSERT INTO t5 VALUES(2, 'B', 'two',   4);
74  INSERT INTO t5 VALUES(3, 'A', 'three', 3);
75  INSERT INTO t5 VALUES(4, 'B', 'four',  2);
76  INSERT INTO t5 VALUES(5, 'A', 'five',  1);
77}
78
79execsql_test 3.1 {
80  SELECT a, nth_value(c, d) OVER (ORDER BY b) FROM t5
81}
82
83execsql_test 3.2 {
84  SELECT a, nth_value(c, d) OVER (PARTITION BY b ORDER BY a) FROM t5
85}
86
87finish_test
88
89