test/window2.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
  18 start_test window2 "2018 May 19"
  19
  20 ifcapable !windowfunc
  21
  22 execsql_test 1.0 {
  23   DROP TABLE IF EXISTS t1;
  24   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
  25   INSERT INTO t1 VALUES(1, 'odd',  'one',   1);
  26   INSERT INTO t1 VALUES(2, 'even', 'two',   2);
  27   INSERT INTO t1 VALUES(3, 'odd',  'three', 3);
  28   INSERT INTO t1 VALUES(4, 'even', 'four',  4);
  29   INSERT INTO t1 VALUES(5, 'odd',  'five',  5);
  30   INSERT INTO t1 VALUES(6, 'even', 'six',   6);
  31 }
  32
  33 execsql_test 1.1 {
  34   SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1;
  35 }
  36
  37 execsql_test 1.2 {
  38   SELECT sum(d) OVER () FROM t1;
  39 }
  40
  41 execsql_test 1.3 {
  42   SELECT sum(d) OVER (PARTITION BY b) FROM t1;
  43 }
  44
  45 ==========
  46 execsql_test 2.1 {
  47   SELECT a, sum(d) OVER (
  48     ORDER BY d
  49     ROWS BETWEEN 1000 PRECEDING AND 1 FOLLOWING
  50   ) FROM t1
  51 }
  52 execsql_test 2.2 {
  53   SELECT a, sum(d) OVER (
  54     ORDER BY d
  55     ROWS BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  56   ) FROM t1
  57 }
  58 execsql_test 2.3 {
  59   SELECT a, sum(d) OVER (
  60     ORDER BY d
  61     ROWS BETWEEN 1 PRECEDING AND 1000 FOLLOWING
  62   ) FROM t1
  63 }
  64 execsql_test 2.4 {
  65   SELECT a, sum(d) OVER (
  66     ORDER BY d
  67     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  68   ) FROM t1
  69 }
  70 execsql_test 2.5 {
  71   SELECT a, sum(d) OVER (
  72     ORDER BY d
  73     ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING
  74   ) FROM t1
  75 }
  76
  77 execsql_test 2.6 {
  78   SELECT a, sum(d) OVER (
  79     PARTITION BY b
  80     ORDER BY d
  81     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  82   ) FROM t1
  83 }
  84
  85 execsql_test 2.7 {
  86   SELECT a, sum(d) OVER (
  87     PARTITION BY b
  88     ORDER BY d
  89     ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
  90   ) FROM t1
  91 }
  92
  93 execsql_test 2.8 {
  94   SELECT a, sum(d) OVER (
  95     ORDER BY d
  96     ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
  97   ) FROM t1
  98 }
  99
 100 execsql_test 2.9 {
 101   SELECT a, sum(d) OVER (
 102     ORDER BY d
 103     ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING
 104   ) FROM t1
 105 }
 106
 107 execsql_test 2.10 {
 108   SELECT a, sum(d) OVER (
 109     ORDER BY d
 110     ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
 111   ) FROM t1
 112 }
 113
 114 execsql_test 2.11 {
 115   SELECT a, sum(d) OVER (
 116     ORDER BY d
 117     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
 118   ) FROM t1
 119 }
 120
 121 execsql_test 2.13 {
 122   SELECT a, sum(d) OVER (
 123     ORDER BY d
 124     ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING
 125   ) FROM t1
 126 }
 127
 128 execsql_test 2.14 {
 129   SELECT a, sum(d) OVER (
 130     ORDER BY d
 131     ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
 132   ) FROM t1
 133 }
 134
 135 execsql_test 2.15 {
 136   SELECT a, sum(d) OVER (
 137     PARTITION BY b
 138     ORDER BY d
 139     ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING
 140   ) FROM t1
 141 }
 142
 143 execsql_test 2.16 {
 144   SELECT a, sum(d) OVER (
 145     PARTITION BY b
 146     ORDER BY d
 147     ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
 148   ) FROM t1
 149 }
 150
 151 execsql_test 2.17 {
 152   SELECT a, sum(d) OVER (
 153     PARTITION BY b
 154     ORDER BY d
 155     ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING
 156   ) FROM t1
 157 }
 158
 159 execsql_test 2.18 {
 160   SELECT a, sum(d) OVER (
 161     PARTITION BY b
 162     ORDER BY d
 163     ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
 164   ) FROM t1
 165 }
 166
 167 execsql_test 2.19 {
 168   SELECT a, sum(d) OVER (
 169     PARTITION BY b
 170     ORDER BY d
 171     ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING
 172   ) FROM t1
 173 }
 174
 175 execsql_test 2.20 {
 176   SELECT a, sum(d) OVER (
 177     ORDER BY d
 178     ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
 179   ) FROM t1
 180 }
 181
 182 execsql_test 2.21 {
 183   SELECT a, sum(d) OVER (
 184     ORDER BY d
 185     ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
 186   ) FROM t1
 187 }
 188
 189 execsql_test 2.22 {
 190   SELECT a, sum(d) OVER (
 191     PARTITION BY b
 192     ORDER BY d
 193     ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
 194   ) FROM t1
 195 }
 196
 197 execsql_test 2.23 {
 198   SELECT a, sum(d) OVER (
 199     ORDER BY d
 200     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 201   ) FROM t1
 202 }
 203
 204 execsql_test 2.24 {
 205   SELECT a, sum(d) OVER (
 206     PARTITION BY a%2
 207     ORDER BY d
 208     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 209   ) FROM t1
 210 }
 211
 212 execsql_test 2.25 {
 213   SELECT a, sum(d) OVER (
 214     ORDER BY d
 215     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 216   ) FROM t1
 217 }
 218
 219 execsql_test 2.26 {
 220   SELECT a, sum(d) OVER (
 221     PARTITION BY b
 222     ORDER BY d
 223     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 224   ) FROM t1
 225 }
 226
 227 execsql_test 2.27 {
 228   SELECT a, sum(d) OVER (
 229     ORDER BY d
 230     ROWS BETWEEN CURRENT ROW AND CURRENT ROW
 231   ) FROM t1
 232 }
 233
 234 execsql_test 2.28 {
 235   SELECT a, sum(d) OVER (
 236     PARTITION BY b
 237     ORDER BY d
 238     ROWS BETWEEN CURRENT ROW AND CURRENT ROW
 239   ) FROM t1
 240 }
 241
 242 execsql_test 2.29 {
 243   SELECT a, sum(d) OVER (
 244     ORDER BY d
 245     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 246   ) FROM t1
 247 }
 248 execsql_test 2.30 {
 249   SELECT a, sum(d) OVER (
 250     ORDER BY b
 251     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 252   ) FROM t1
 253 }
 254
 255 execsql_test 3.1 {
 256   SELECT a, sum(d) OVER (
 257     PARTITION BY b ORDER BY d
 258     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 259   ) FROM t1
 260 }
 261
 262 execsql_test 3.2 {
 263   SELECT a, sum(d) OVER (
 264     ORDER BY b
 265     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 266   ) FROM t1
 267 }
 268
 269 execsql_test 3.3 {
 270   SELECT a, sum(d) OVER (
 271     ORDER BY d
 272     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 273   ) FROM t1
 274 }
 275
 276 execsql_test 3.4 {
 277   SELECT a, sum(d) OVER (
 278     ORDER BY d/2
 279     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 280   ) FROM t1
 281 }
 282
 283 #puts $::fd finish_test
 284
 285 ==========
 286
 287 execsql_test 4.0 {
 288   DROP TABLE IF EXISTS t2;
 289   CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
 290   INSERT INTO t2(a, b) VALUES
 291   (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2),
 292   (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62),
 293   (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78),
 294   (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77),
 295   (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7),
 296   (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43),
 297   (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90),
 298   (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56),
 299   (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98),
 300   (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33),
 301   (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84),
 302   (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13),
 303   (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35),
 304   (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8),
 305   (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73),
 306   (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34),
 307   (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77),
 308   (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70),
 309   (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80),
 310   (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66),
 311   (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37),
 312   (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91),
 313   (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69),
 314   (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84),
 315   (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38),
 316   (195,34), (196,83), (197,27), (198,82), (199,17), (200,7);
 317 }
 318
 319 execsql_test 4.1 {
 320   SELECT a, sum(b) OVER (
 321     PARTITION BY (b%10)
 322     ORDER BY b
 323   ) FROM t2 ORDER BY a;
 324 }
 325
 326 execsql_test 4.2 {
 327   SELECT a, sum(b) OVER (
 328     PARTITION BY (b%10)
 329     ORDER BY b
 330     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 331   ) FROM t2 ORDER BY a;
 332 }
 333
 334 execsql_test 4.3 {
 335   SELECT b, sum(b) OVER (
 336     ORDER BY b
 337     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 338   ) FROM t2 ORDER BY b;
 339 }
 340
 341 execsql_test 4.4 {
 342   SELECT b, sum(b) OVER (
 343     ORDER BY b
 344     RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 345   ) FROM t2 ORDER BY b;
 346 }
 347
 348 execsql_test 4.5 {
 349   SELECT b, sum(b) OVER (
 350     ORDER BY b
 351     RANGE BETWEEN CURRENT ROW AND CURRENT ROW
 352   ) FROM t2 ORDER BY b;
 353 }
 354
 355 execsql_test 4.6.1 {
 356   SELECT b, sum(b) OVER (
 357     RANGE BETWEEN CURRENT ROW AND CURRENT ROW
 358   ) FROM t2 ORDER BY b;
 359 }
 360 execsql_test 4.6.2 {
 361   SELECT b, sum(b) OVER () FROM t2 ORDER BY b;
 362 }
 363 execsql_test 4.6.3 {
 364   SELECT b, sum(b) OVER (
 365     RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 366   ) FROM t2 ORDER BY b;
 367 }
 368 execsql_test 4.6.4 {
 369   SELECT b, sum(b) OVER (
 370     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 371   ) FROM t2 ORDER BY b;
 372 }
 373
 374 execsql_test 4.7.1 {
 375   SELECT b, sum(b) OVER (
 376     ROWS BETWEEN CURRENT ROW AND CURRENT ROW
 377   ) FROM t2 ORDER BY 1, 2;
 378 }
 379 execsql_test 4.7.2 {
 380   SELECT b, sum(b) OVER (
 381     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 382   ) FROM t2 ORDER BY 1, 2;
 383 }
 384 execsql_test 4.7.3 {
 385   SELECT b, sum(b) OVER (
 386     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 387   ) FROM t2 ORDER BY 1, 2;
 388 }
 389 execsql_test 4.7.4 {
 390   SELECT b, sum(b) OVER (
 391     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 392   ) FROM t2 ORDER BY 1, 2;
 393 }
 394
 395 execsql_test 4.8.1 {
 396   SELECT b, sum(b) OVER (
 397     ORDER BY a
 398     ROWS BETWEEN CURRENT ROW AND CURRENT ROW
 399   ) FROM t2 ORDER BY 1, 2;
 400 }
 401 execsql_test 4.8.2 {
 402   SELECT b, sum(b) OVER (
 403     ORDER BY a
 404     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 405   ) FROM t2 ORDER BY 1, 2;
 406 }
 407 execsql_test 4.8.3 {
 408   SELECT b, sum(b) OVER (
 409     ORDER BY a
 410     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 411   ) FROM t2 ORDER BY 1, 2;
 412 }
 413 execsql_test 4.8.4 {
 414   SELECT b, sum(b) OVER (
 415     ORDER BY a
 416     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 417   ) FROM t2 ORDER BY 1, 2;
 418 }
 419
 420 execsql_float_test 4.9 {
 421   SELECT
 422     rank() OVER win AS rank,
 423     cume_dist() OVER win AS cume_dist FROM t1
 424   WINDOW win AS (ORDER BY 1);
 425 }
 426
 427 execsql_test 4.10 {
 428   SELECT count(*) OVER (ORDER BY b) FROM t1
 429 }
 430
 431 execsql_test 4.11 {
 432   SELECT count(distinct a) FILTER (WHERE b='odd') FROM t1
 433 }
 434
 435 ==========
 436
 437 execsql_test 5.0 {
 438   DROP TABLE IF EXISTS t1;
 439   CREATE TABLE t1(x INTEGER, y INTEGER);
 440   INSERT INTO t1 VALUES(10, 1);
 441   INSERT INTO t1 VALUES(20, 2);
 442   INSERT INTO t1 VALUES(3, 3);
 443   INSERT INTO t1 VALUES(2, 4);
 444   INSERT INTO t1 VALUES(1, 5);
 445 }
 446
 447 execsql_float_test 5.1 {
 448   SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z;
 449 }
 450
 451 ==========
 452
 453 execsql_test 6.0 {
 454   DROP TABLE IF EXISTS t0;
 455   CREATE TABLE t0(c0 INTEGER UNIQUE);
 456   INSERT INTO t0 VALUES(0);
 457 }
 458 execsql_test 6.1 {
 459   SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0;
 460 }
 461 execsql_test 6.2 {
 462   SELECT * FROM t0 WHERE
 463       (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0);
 464 }
 465
 466 ==========
 467
 468 execsql_test 7.0 {
 469   DROP TABLE IF EXISTS t1;
 470   CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
 471   INSERT INTO t1 VALUES(1, 1, 1);
 472   INSERT INTO t1 VALUES(1, 2, 2);
 473   INSERT INTO t1 VALUES(3, 3, 3);
 474   INSERT INTO t1 VALUES(3, 4, 4);
 475 }
 476
 477 execsql_test 7.1 {
 478   SELECT c, sum(c) OVER win1 FROM t1
 479   WINDOW win1 AS (ORDER BY b)
 480 }
 481
 482 execsql_test 7.2 {
 483   SELECT c, sum(c) OVER win1 FROM t1
 484   WINDOW win1 AS (PARTITION BY 1 ORDER BY b)
 485 }
 486
 487 execsql_test 7.3 {
 488   SELECT c, sum(c) OVER win1 FROM t1
 489   WINDOW win1 AS (ORDER BY 1)
 490 }
 491
 492 finish_test
 493
 494