test/window4.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 window4 "2018 June 04"
  18 ifcapable !windowfunc
  19
  20 execsql_test 1.0 {
  21   DROP TABLE IF EXISTS t3;
  22   CREATE TABLE t3(a TEXT PRIMARY KEY);
  23   INSERT INTO t3 VALUES('a'), ('b'), ('c'), ('d'), ('e');
  24   INSERT INTO t3 VALUES('f'), ('g'), ('h'), ('i'), ('j');
  25 }
  26
  27 for {set i 1} {$i < 20} {incr i} {
  28   execsql_test 1.$i "SELECT a, ntile($i) OVER (ORDER BY a) FROM t3"
  29 }
  30
  31 execsql_test 2.0 {
  32   DROP TABLE IF EXISTS t4;
  33   CREATE TABLE t4(a INTEGER PRIMARY KEY, b TEXT, c INTEGER);
  34   INSERT INTO t4 VALUES(1, 'A', 9);
  35   INSERT INTO t4 VALUES(2, 'B', 3);
  36   INSERT INTO t4 VALUES(3, 'C', 2);
  37   INSERT INTO t4 VALUES(4, 'D', 10);
  38   INSERT INTO t4 VALUES(5, 'E', 5);
  39   INSERT INTO t4 VALUES(6, 'F', 1);
  40   INSERT INTO t4 VALUES(7, 'G', 1);
  41   INSERT INTO t4 VALUES(8, 'H', 2);
  42   INSERT INTO t4 VALUES(9, 'I', 10);
  43   INSERT INTO t4 VALUES(10, 'J', 4);
  44 }
  45
  46 execsql_test 2.1 {
  47   SELECT a, nth_value(b, c) OVER (ORDER BY a) FROM t4
  48 }
  49
  50 execsql_test 2.2.1 {
  51   SELECT a, lead(b) OVER (ORDER BY a) FROM t4
  52 }
  53 execsql_test 2.2.2 {
  54   SELECT a, lead(b, 2) OVER (ORDER BY a) FROM t4
  55 }
  56 execsql_test 2.2.3 {
  57   SELECT a, lead(b, 3, 'abc') OVER (ORDER BY a) FROM t4
  58 }
  59
  60 execsql_test 2.3.1 {
  61   SELECT a, lag(b) OVER (ORDER BY a) FROM t4
  62 }
  63 execsql_test 2.3.2 {
  64   SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
  65 }
  66 execsql_test 2.3.3 {
  67   SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
  68 }
  69
  70 execsql_test 2.4.1 {
  71   SELECT string_agg(b, '.') OVER (
  72     ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  73   ) FROM t4
  74 }
  75
  76 execsql_test 3.0 {
  77   DROP TABLE IF EXISTS t5;
  78   CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
  79   INSERT INTO t5 VALUES(1, 'A', 'one',   5);
  80   INSERT INTO t5 VALUES(2, 'B', 'two',   4);
  81   INSERT INTO t5 VALUES(3, 'A', 'three', 3);
  82   INSERT INTO t5 VALUES(4, 'B', 'four',  2);
  83   INSERT INTO t5 VALUES(5, 'A', 'five',  1);
  84 }
  85
  86 execsql_test 3.1 {
  87   SELECT a, nth_value(c, d) OVER (ORDER BY b) FROM t5
  88 }
  89
  90 execsql_test 3.2 {
  91   SELECT a, nth_value(c, d) OVER (PARTITION BY b ORDER BY a) FROM t5
  92 }
  93
  94 execsql_test 3.3 {
  95   SELECT a, count(*) OVER abc, count(*) OVER def FROM t5
  96   WINDOW abc AS (ORDER BY a),
  97          def AS (ORDER BY a DESC)
  98   ORDER BY a;
  99 }
 100
 101 execsql_test 3.4 {
 102   SELECT a, max(a) FILTER (WHERE (a%2)=0) OVER w FROM t5
 103   WINDOW w AS (ORDER BY a)
 104 }
 105
 106 execsql_test 3.5.1 {
 107   SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING)
 108   FROM t5
 109 }
 110 execsql_test 3.5.2 {
 111   SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
 112   FROM t5
 113 }
 114 execsql_test 3.5.3 {
 115   SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
 116   FROM t5
 117 }
 118
 119 execsql_test 3.6.1 {
 120   SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
 121   FROM t5
 122 }
 123 execsql_test 3.6.2 {
 124   SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
 125   FROM t5
 126 }
 127 execsql_test 3.6.3 {
 128   SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
 129   FROM t5
 130 }
 131
 132 ==========
 133
 134 execsql_test 4.0 {
 135   DROP TABLE IF EXISTS ttt;
 136   CREATE TABLE ttt(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
 137   INSERT INTO ttt VALUES(1, 1, 1);
 138   INSERT INTO ttt VALUES(2, 2, 2);
 139   INSERT INTO ttt VALUES(3, 3, 3);
 140
 141   INSERT INTO ttt VALUES(4, 1, 2);
 142   INSERT INTO ttt VALUES(5, 2, 3);
 143   INSERT INTO ttt VALUES(6, 3, 4);
 144
 145   INSERT INTO ttt VALUES(7, 1, 3);
 146   INSERT INTO ttt VALUES(8, 2, 4);
 147   INSERT INTO ttt VALUES(9, 3, 5);
 148 }
 149
 150 execsql_test 4.1 {
 151   SELECT max(c), max(b) OVER (ORDER BY b) FROM ttt GROUP BY b;
 152 }
 153
 154 execsql_test 4.2 {
 155   SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
 156 }
 157
 158 execsql_test 4.3 {
 159   SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
 160 }
 161
 162 execsql_test 4.4 {
 163   SELECT sum(b) OVER (
 164     ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 165   ) FROM ttt;
 166 }
 167
 168 set lPart  [list "PARTITION BY b" "PARTITION BY b, a" "" "PARTITION BY a"]
 169 set lOrder [list "ORDER BY a" "ORDER BY a DESC" "" "ORDER BY b, a"]
 170 set lRange {
 171     "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
 172     "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
 173     "RANGE BETWEEN CURRENT ROW AND CURRENT ROW"
 174     "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING"
 175 }
 176
 177 set lRows {
 178     "ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING"
 179     "ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING"
 180     "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING"
 181     "ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING"
 182     "ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING"
 183 }
 184
 185 set tn 1
 186 set SQL {
 187   SELECT max(c) OVER ($p1 $o1 $r1),
 188   min(c) OVER ($p2 $o2 $r2)
 189   FROM ttt ORDER BY a
 190 }
 191 set SQL2 {
 192   SELECT sum(c) OVER ($p1 $o1 $r1),
 193          sum(c) OVER ($p2 $o2 $r2)
 194   FROM ttt ORDER BY a
 195 }
 196
 197 set o1 [lindex $lOrder 0]
 198 set o2 [lindex $lOrder 0]
 199 set r1 [lindex $lRange 0]
 200 set r2 [lindex $lRange 0]
 201 foreach p1 $lPart { foreach p2 $lPart {
 202   execsql_test 4.5.$tn.1 [subst $SQL]
 203   execsql_test 4.5.$tn.2 [subst $SQL2]
 204   incr tn
 205 }}
 206
 207 set o1 [lindex $lOrder 0]
 208 set o2 [lindex $lOrder 0]
 209 set p1 [lindex $lPart 0]
 210 set p2 [lindex $lPart 0]
 211 foreach r1 $lRange { foreach r2 $lRange {
 212   execsql_test 4.5.$tn.1 [subst $SQL]
 213   execsql_test 4.5.$tn.2 [subst $SQL2]
 214   incr tn
 215 }}
 216 foreach r1 $lRows { foreach r2 $lRows {
 217   execsql_test 4.5.$tn.1 [subst $SQL]
 218   execsql_test 4.5.$tn.2 [subst $SQL2]
 219   incr tn
 220 }}
 221
 222 set r1 [lindex $lRange 0]
 223 set r2 [lindex $lRange 0]
 224 set p1 [lindex $lPart 0]
 225 set p2 [lindex $lPart 0]
 226 foreach o1 $lOrder { foreach o2 $lOrder {
 227   execsql_test 4.5.$tn.1 [subst $SQL]
 228   execsql_test 4.5.$tn.2 [subst $SQL2]
 229   incr tn
 230 }}
 231
 232 ==========
 233
 234 execsql_test 7.0 {
 235   DROP TABLE IF EXISTS t1;
 236   CREATE TABLE t1(x INTEGER, y INTEGER);
 237   INSERT INTO t1 VALUES(1, 2);
 238   INSERT INTO t1 VALUES(3, 4);
 239   INSERT INTO t1 VALUES(5, 6);
 240   INSERT INTO t1 VALUES(7, 8);
 241   INSERT INTO t1 VALUES(9, 10);
 242 }
 243
 244 execsql_test 7.1 {
 245   SELECT lead(y) OVER win FROM t1
 246   WINDOW win AS (ORDER BY x)
 247 }
 248
 249 execsql_test 7.2 {
 250   SELECT lead(y, 2) OVER win FROM t1
 251   WINDOW win AS (ORDER BY x)
 252 }
 253
 254 execsql_test 7.3 {
 255   SELECT lead(y, 3, -1) OVER win FROM t1
 256   WINDOW win AS (ORDER BY x)
 257 }
 258
 259 execsql_test 7.4 {
 260   SELECT
 261     lead(y) OVER win, lead(y) OVER win
 262   FROM t1
 263   WINDOW win AS (ORDER BY x)
 264 }
 265
 266 execsql_test 7.5 {
 267   SELECT
 268     lead(y) OVER win,
 269     lead(y, 2) OVER win,
 270     lead(y, 3, -1) OVER win
 271   FROM t1
 272   WINDOW win AS (ORDER BY x)
 273 }
 274
 275 ==========
 276
 277 execsql_test 8.0 {
 278   DROP TABLE IF EXISTS t1;
 279   CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
 280   INSERT INTO t1 VALUES(1, 2, 3, 4);
 281   INSERT INTO t1 VALUES(5, 6, 7, 8);
 282   INSERT INTO t1 VALUES(9, 10, 11, 12);
 283 }
 284
 285 execsql_test 8.1 {
 286   SELECT row_number() OVER win,
 287          nth_value(d,2) OVER win,
 288          lead(d) OVER win
 289   FROM t1
 290   WINDOW win AS (ORDER BY a)
 291 }
 292
 293 execsql_test 8.2 {
 294     SELECT row_number() OVER win,
 295            rank() OVER win,
 296            dense_rank() OVER win,
 297            ntile(2) OVER win,
 298            first_value(d) OVER win,
 299            last_value(d) OVER win,
 300            nth_value(d,2) OVER win,
 301            lead(d) OVER win,
 302            lag(d) OVER win,
 303            max(d) OVER win,
 304            min(d) OVER win
 305     FROM t1
 306     WINDOW win AS (ORDER BY a)
 307 }
 308
 309 ==========
 310
 311 execsql_test 9.0 {
 312   DROP TABLE IF EXISTS t2;
 313   CREATE TABLE t2(x INTEGER);
 314   INSERT INTO t2 VALUES(1), (1), (1), (4), (4), (6), (7);
 315 }
 316
 317 execsql_test 9.1 {
 318   SELECT rank() OVER () FROM t2
 319 }
 320 execsql_test 9.2 {
 321   SELECT dense_rank() OVER (PARTITION BY x) FROM t2
 322 }
 323 execsql_float_test 9.3 {
 324   SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2
 325 }
 326
 327 execsql_test 9.4 {
 328   SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
 329 }
 330
 331 execsql_test 9.5 {
 332   SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
 333 }
 334
 335 execsql_float_test 9.6 {
 336   SELECT percent_rank() OVER () FROM t1
 337 }
 338
 339 execsql_float_test 9.7 {
 340   SELECT cume_dist() OVER () FROM t1
 341 }
 342
 343 execsql_test 10.0 {
 344   DROP TABLE IF EXISTS t7;
 345   CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
 346   INSERT INTO t7(id, a, b) VALUES
 347     (1, 1, 2), (2, 1, NULL), (3, 1, 4),
 348     (4, 3, NULL), (5, 3, 8), (6, 3, 1);
 349 }
 350 execsql_test 10.1 {
 351   SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
 352 }
 353
 354 execsql_test 10.2 {
 355   SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
 356 }
 357 execsql_test 10.3 {
 358   SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
 359 }
 360
 361 execsql_test 11.0 {
 362   DROP VIEW IF EXISTS v8;
 363   DROP TABLE IF EXISTS t8;
 364   CREATE TABLE t8(t INT, total INT);
 365   INSERT INTO t8 VALUES(0,2);
 366   INSERT INTO t8 VALUES(5,1);
 367   INSERT INTO t8 VALUES(10,1);
 368 }
 369
 370 execsql_test 11.1 {
 371   SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
 372 }
 373
 374 execsql_test 11.2 {
 375   CREATE VIEW v8 AS SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
 376 }
 377
 378 execsql_test 11.3 {
 379   SELECT * FROM v8;
 380 }
 381
 382 execsql_test 11.4 {
 383   SELECT * FROM (
 384     SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8
 385   ) sub;
 386 }
 387
 388 execsql_test 11.5 {
 389   SELECT sum( min(t) ) OVER () FROM t8 GROUP BY total;
 390 }
 391 execsql_test 11.5 {
 392   SELECT sum( max(t) ) OVER () FROM t8 GROUP BY total;
 393 }
 394
 395 execsql_test 11.7 {
 396   SELECT sum( min(t) ) OVER () FROM t8;
 397 }
 398 execsql_test 11.8 {
 399   SELECT sum( max(t) ) OVER () FROM t8;
 400 }
 401
 402 execsql_test 12.0 {
 403   DROP TABLE IF EXISTS t2;
 404   CREATE TABLE t2(a INTEGER);
 405   INSERT INTO t2 VALUES(1), (2), (3);
 406 }
 407
 408 execsql_test 12.1 {
 409   SELECT (SELECT min(a) OVER ()) FROM t2
 410 }
 411
 412 execsql_float_test 12.2 {
 413   SELECT (SELECT avg(a)) FROM t2 ORDER BY 1
 414 }
 415
 416 execsql_float_test 12.3 {
 417   SELECT
 418     (SELECT avg(a) UNION SELECT min(a) OVER ())
 419   FROM t2 GROUP BY a
 420   ORDER BY 1
 421 }
 422
 423 finish_test
 424