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 window3 "2018 May 31" 18 19execsql_test 1.0 { 20 DROP TABLE IF EXISTS t2; 21 CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER); 22 INSERT INTO t2(a, b) VALUES 23 (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2), 24 (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" 75} { 76 execsql_test 1.$tn.2.1 "SELECT max(b) OVER ( ORDER BY a $window ) FROM t2" 77 execsql_test 1.$tn.2.2 "SELECT min(b) OVER ( ORDER BY a $window ) FROM t2" 78 79 execsql_test 1.$tn.3.1 " 80 SELECT row_number() OVER ( ORDER BY a $window ) FROM t2 81 " 82 execsql_test 1.$tn.3.2 " 83 SELECT row_number() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2 84 " 85 86 execsql_test 1.$tn.4.1 " 87 SELECT dense_rank() OVER ( ORDER BY a $window ) FROM t2 88 " 89 execsql_test 1.$tn.4.2 " 90 SELECT dense_rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2 91 " 92 execsql_test 1.$tn.4.3 " 93 SELECT dense_rank() OVER ( ORDER BY b $window ) FROM t2 94 " 95 execsql_test 1.$tn.4.4 " 96 SELECT dense_rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2 97 " 98 execsql_test 1.$tn.4.5 " 99 SELECT dense_rank() OVER ( ORDER BY b%10 $window ) FROM t2 100 " 101 execsql_test 1.$tn.4.6 " 102 SELECT dense_rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2 103 " 104 105 execsql_test 1.$tn.5.1 " 106 SELECT rank() OVER ( ORDER BY a $window ) FROM t2 107 " 108 execsql_test 1.$tn.5.2 " 109 SELECT rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2 110 " 111 execsql_test 1.$tn.5.3 " 112 SELECT rank() OVER ( ORDER BY b $window ) FROM t2 113 " 114 execsql_test 1.$tn.5.4 " 115 SELECT rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2 116 " 117 execsql_test 1.$tn.5.5 " 118 SELECT rank() OVER ( ORDER BY b%10 $window ) FROM t2 119 " 120 execsql_test 1.$tn.5.6 " 121 SELECT rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2 122 " 123 124 execsql_test 1.$tn.6.1 " 125 SELECT 126 row_number() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ), 127 rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ), 128 dense_rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) 129 FROM t2 130 " 131 132 execsql_test 1.$tn.7.1 " 133 SELECT CAST( round( 100 * 134 percent_rank() OVER ( ORDER BY a $window ) 135 ) AS integer) FROM t2" 136 137 #execsql_test 1.$tn.7.2 " 138 #SELECT CAST( round( 100 * 139 #percent_rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) 140 #) AS integer) FROM t2" 141 142 execsql_test 1.$tn.7.3 " 143 SELECT CAST( round( 100 * 144 percent_rank() OVER ( ORDER BY b $window ) 145 ) AS integer) FROM t2" 146 147 #execsql_test 1.$tn.7.4 " 148 #SELECT CAST( round( 100 * 149 #percent_rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) 150 #) AS integer) FROM t2" 151 152 execsql_test 1.$tn.7.5 " 153 SELECT CAST( round( 100 * 154 percent_rank() OVER ( ORDER BY b%10 $window ) 155 ) AS integer) FROM t2" 156 157 execsql_test 1.$tn.7.6 " 158 SELECT CAST( round( 100 * 159 percent_rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) 160 ) AS integer) FROM t2" 161 162 163} 164 165 166finish_test 167 168