test/window3.tcl

   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 window3 "2018 May 31"
  18
  19 execsql_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
  51 execsql_test 1.1 {
  52   SELECT max(b) OVER (
  53     ORDER BY a
  54   ) FROM t2
  55 }
  56
  57 foreach {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_float_test 1.$tn.7.1 "
 133     SELECT percent_rank() OVER ( ORDER BY a $window ) FROM t2
 134   "
 135   execsql_float_test 1.$tn.7.2 "
 136     SELECT percent_rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 137   "
 138   execsql_float_test 1.$tn.7.3 "
 139     SELECT percent_rank() OVER ( ORDER BY b $window ) FROM t2
 140   "
 141   execsql_float_test 1.$tn.7.4 "
 142     SELECT percent_rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
 143   "
 144   execsql_float_test 1.$tn.7.5 "
 145     SELECT percent_rank() OVER ( ORDER BY b%10 $window ) FROM t2
 146   "
 147   execsql_float_test 1.$tn.7.6 "
 148     SELECT percent_rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2
 149   "
 150
 151   execsql_float_test 1.$tn.8.1 "
 152     SELECT cume_dist() OVER ( ORDER BY a $window ) FROM t2
 153   "
 154   execsql_float_test 1.$tn.8.2 "
 155     SELECT cume_dist() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 156   "
 157   execsql_float_test 1.$tn.8.3 "
 158     SELECT cume_dist() OVER ( ORDER BY b $window ) FROM t2
 159   "
 160   execsql_float_test 1.$tn.8.4 "
 161     SELECT cume_dist() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
 162   "
 163   execsql_float_test 1.$tn.8.5 "
 164     SELECT cume_dist() OVER ( ORDER BY b%10 $window ) FROM t2
 165   "
 166   execsql_float_test 1.$tn.8.6 "
 167     SELECT cume_dist() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2
 168   "
 169
 170   execsql_float_test 1.$tn.8.1 "
 171     SELECT ntile(100) OVER ( ORDER BY a $window ) FROM t2
 172   "
 173   execsql_float_test 1.$tn.8.2 "
 174     SELECT ntile(101) OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 175   "
 176   execsql_float_test 1.$tn.8.3 "
 177     SELECT ntile(102) OVER ( ORDER BY b,a $window ) FROM t2
 178   "
 179   execsql_float_test 1.$tn.8.4 "
 180     SELECT ntile(103) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 181   "
 182   execsql_float_test 1.$tn.8.5 "
 183     SELECT ntile(104) OVER ( ORDER BY b%10,a $window ) FROM t2
 184   "
 185   execsql_float_test 1.$tn.8.6 "
 186     SELECT ntile(105) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 187   "
 188
 189   execsql_test 1.$tn.9.1 "
 190     SELECT last_value(a+b) OVER ( ORDER BY a $window ) FROM t2
 191   "
 192   execsql_test 1.$tn.9.2 "
 193     SELECT last_value(a+b) OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 194   "
 195   execsql_test 1.$tn.9.3 "
 196     SELECT last_value(a+b) OVER ( ORDER BY b,a $window ) FROM t2
 197   "
 198   execsql_test 1.$tn.9.4 "
 199     SELECT last_value(a+b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 200   "
 201   execsql_test 1.$tn.9.5 "
 202     SELECT last_value(a+b) OVER ( ORDER BY b%10,a $window ) FROM t2
 203   "
 204   execsql_test 1.$tn.9.6 "
 205     SELECT last_value(a+b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 206   "
 207
 208 }
 209
 210 finish_test
 211