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_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
 166 finish_test
 167