github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/window (about) 1 # LogicTest: default-configs !3node-tenant 5node-default-configs 2 3 statement ok 4 CREATE TABLE kv ( 5 -- don't add column "a" 6 k INT PRIMARY KEY, 7 v INT, 8 w INT, 9 f FLOAT, 10 d DECIMAL, 11 s STRING, 12 b BOOL, 13 i INTERVAL, 14 FAMILY (k, v, w, f, b), 15 FAMILY (d), 16 FAMILY (s) 17 ) 18 19 statement OK 20 INSERT INTO kv VALUES 21 (1, 2, 3, 1.0, 1, 'a', true, '1min'), 22 (3, 4, 5, 2, 8, 'a', true, '2sec'), 23 (5, NULL, 5, 9.9, -321, NULL, false, NULL), 24 (6, 2, 3, 4.4, 4.4, 'b', true, '1ms'), 25 (7, 2, 2, 6, 7.9, 'b', true, '4 days'), 26 (8, 4, 2, 3, 3, 'A', false, '3 years') 27 28 query error window functions are not allowed in GROUP BY 29 SELECT * FROM kv GROUP BY v, count(w) OVER () 30 31 query error window functions are not allowed in GROUP BY 32 SELECT count(w) OVER () FROM kv GROUP BY 1 33 34 query error window functions are not allowed in RETURNING 35 INSERT INTO kv (k, v) VALUES (99, 100) RETURNING sum(v) OVER () 36 37 query error column "v" does not exist 38 SELECT sum(v) FROM kv GROUP BY k LIMIT sum(v) OVER () 39 40 query error column "v" does not exist 41 SELECT sum(v) FROM kv GROUP BY k LIMIT 1 OFFSET sum(v) OVER () 42 43 query error window functions are not allowed in VALUES 44 INSERT INTO kv (k, v) VALUES (99, count(1) OVER ()) 45 46 query error window functions are not allowed in WHERE 47 SELECT k FROM kv WHERE avg(k) OVER () > 1 48 49 query error window functions are not allowed in HAVING 50 SELECT 1 FROM kv GROUP BY 1 HAVING sum(1) OVER (PARTITION BY 1) > 1 51 52 query R 53 SELECT avg(k) OVER () FROM kv ORDER BY 1 54 ---- 55 5 56 5 57 5 58 5 59 5 60 5 61 62 query R 63 SELECT avg(k) OVER (PARTITION BY v) FROM kv ORDER BY 1 64 ---- 65 4.6666666666666666667 66 4.6666666666666666667 67 4.6666666666666666667 68 5 69 5.5 70 5.5 71 72 query R 73 SELECT avg(k) OVER (PARTITION BY w) FROM kv ORDER BY 1 74 ---- 75 3.5 76 3.5 77 4 78 4 79 7.5 80 7.5 81 82 query R 83 SELECT avg(k) OVER (PARTITION BY b) FROM kv ORDER BY 1 84 ---- 85 4.25 86 4.25 87 4.25 88 4.25 89 6.5 90 6.5 91 92 query R 93 SELECT avg(k) OVER (PARTITION BY w, b) FROM kv ORDER BY 1 94 ---- 95 3 96 3.5 97 3.5 98 5 99 7 100 8 101 102 query R 103 SELECT avg(k) OVER (PARTITION BY kv.*) FROM kv ORDER BY 1 104 ---- 105 1 106 3 107 5 108 6 109 7 110 8 111 112 query R 113 SELECT avg(k) OVER (ORDER BY w) FROM kv ORDER BY 1 114 ---- 115 5 116 5 117 5.5 118 5.5 119 7.5 120 7.5 121 122 query R 123 SELECT avg(k) OVER (ORDER BY b) FROM kv ORDER BY 1 124 ---- 125 5 126 5 127 5 128 5 129 6.5 130 6.5 131 132 query R 133 SELECT avg(k) OVER (ORDER BY w, b) FROM kv ORDER BY 1 134 ---- 135 5 136 5.4 137 5.5 138 5.5 139 7.5 140 8 141 142 query R 143 SELECT avg(k) OVER (ORDER BY 1-w) FROM kv ORDER BY 1 144 ---- 145 3.75 146 3.75 147 4 148 4 149 5 150 5 151 152 query R 153 SELECT avg(k) OVER (ORDER BY kv.*) FROM kv ORDER BY 1 154 ---- 155 1 156 2 157 3 158 3.75 159 4.4 160 5 161 162 query R 163 SELECT avg(k) OVER (ORDER BY w DESC) FROM kv ORDER BY 1 164 ---- 165 3.75 166 3.75 167 4 168 4 169 5 170 5 171 172 query R 173 SELECT avg(k) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 174 ---- 175 4.6666666666666666667 176 4.6666666666666666667 177 5 178 5.5 179 7 180 8 181 182 query R 183 SELECT avg(k) OVER w FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY 1 184 ---- 185 4.6666666666666666667 186 4.6666666666666666667 187 5 188 5.5 189 7 190 8 191 192 query R 193 SELECT avg(k) OVER (w) FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY 1 194 ---- 195 4.6666666666666666667 196 4.6666666666666666667 197 5 198 5.5 199 7 200 8 201 202 query R 203 SELECT avg(k) OVER (w ORDER BY w) FROM kv WINDOW w AS (PARTITION BY v) ORDER BY 1 204 ---- 205 4.6666666666666666667 206 4.6666666666666666667 207 5 208 5.5 209 7 210 8 211 212 query IIIRRTBTR colnames 213 SELECT *, avg(k) OVER (w ORDER BY w) FROM kv WINDOW w AS (PARTITION BY v) ORDER BY 1 214 ---- 215 k v w f d s b i avg 216 1 2 3 1 1 a true 00:01:00 4.6666666666666666667 217 3 4 5 2 8 a true 00:00:02 5.5 218 5 NULL 5 9.9 -321 NULL false NULL 5 219 6 2 3 4.4 4.4 b true 00:00:00.001 4.6666666666666666667 220 7 2 2 6 7.9 b true 4 days 7 221 8 4 2 3 3 A false 3 years 8 222 223 query IIIRRTBTR colnames 224 SELECT *, avg(k) OVER w FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY avg(k) OVER w, k 225 ---- 226 k v w f d s b i avg 227 1 2 3 1 1 a true 00:01:00 4.6666666666666666667 228 6 2 3 4.4 4.4 b true 00:00:00.001 4.6666666666666666667 229 5 NULL 5 9.9 -321 NULL false NULL 5 230 3 4 5 2 8 a true 00:00:02 5.5 231 7 2 2 6 7.9 b true 4 days 7 232 8 4 2 3 3 A false 3 years 8 233 234 query IIIRRTBT colnames 235 SELECT * FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY avg(k) OVER w DESC, k 236 ---- 237 k v w f d s b i 238 8 4 2 3 3 A false 3 years 239 7 2 2 6 7.9 b true 4 days 240 3 4 5 2 8 a true 00:00:02 241 5 NULL 5 9.9 -321 NULL false NULL 242 1 2 3 1 1 a true 00:01:00 243 6 2 3 4.4 4.4 b true 00:00:00.001 244 245 query error window "w" is already defined 246 SELECT avg(k) OVER w FROM kv WINDOW w AS (), w AS () 247 248 query error window "x" does not exist 249 SELECT avg(k) OVER x FROM kv WINDOW w AS () 250 251 query error window "x" does not exist 252 SELECT avg(k) OVER (x) FROM kv WINDOW w AS () 253 254 query error cannot override PARTITION BY clause of window "w" 255 SELECT avg(k) OVER (w PARTITION BY v) FROM kv WINDOW w AS () 256 257 query error cannot override PARTITION BY clause of window "w" 258 SELECT avg(k) OVER (w PARTITION BY v) FROM kv WINDOW w AS (PARTITION BY v) 259 260 query error cannot override ORDER BY clause of window "w" 261 SELECT avg(k) OVER (w ORDER BY v) FROM kv WINDOW w AS (ORDER BY v) 262 263 query error column "a" does not exist 264 SELECT avg(k) OVER (PARTITION BY a) FROM kv 265 266 query error column "a" does not exist 267 SELECT avg(k) OVER (ORDER BY a) FROM kv 268 269 # TODO(justin): this should have pgcode 42803 but CBO currently doesn't get 270 # it right. 271 query error window functions are not allowed in aggregate 272 SELECT avg(avg(k) OVER ()) FROM kv ORDER BY 1 273 274 query R 275 SELECT avg(avg(k)) OVER () FROM kv ORDER BY 1 276 ---- 277 5 278 279 query RR 280 SELECT avg(k) OVER (), avg(v) OVER () FROM kv ORDER BY 1 281 ---- 282 5 2.8 283 5 2.8 284 5 2.8 285 5 2.8 286 5 2.8 287 5 2.8 288 289 query error OVER specified, but now\(\) is neither a window function nor an aggregate function 290 SELECT now() OVER () FROM kv ORDER BY 1 291 292 query error window function rank\(\) requires an OVER clause 293 SELECT rank() FROM kv 294 295 query error unknown signature: rank\(int\) 296 SELECT rank(22) FROM kv 297 298 query error window function calls cannot be nested 299 SELECT avg(avg(k) OVER ()) OVER () FROM kv ORDER BY 1 300 301 query error OVER specified, but round\(\) is neither a window function nor an aggregate function 302 SELECT round(avg(k) OVER ()) OVER () FROM kv ORDER BY 1 303 304 query R 305 SELECT round(avg(k) OVER (PARTITION BY v ORDER BY w)) FROM kv ORDER BY 1 306 ---- 307 5 308 5 309 5 310 6 311 7 312 8 313 314 query R 315 SELECT avg(f) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 316 ---- 317 2.5 318 3 319 3.8 320 3.8 321 6 322 9.9 323 324 query R 325 SELECT avg(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 326 ---- 327 -321 328 3 329 4.4333333333333333333 330 4.4333333333333333333 331 5.5 332 7.9 333 334 query R 335 SELECT avg(d) OVER (PARTITION BY w ORDER BY v) FROM kv ORDER BY 1 336 ---- 337 -321 338 -156.5 339 2.7 340 2.7 341 5.45 342 7.9 343 344 query R 345 SELECT (avg(d) OVER (PARTITION BY v ORDER BY w) + avg(d) OVER (PARTITION BY v ORDER BY w)) FROM kv ORDER BY 1 346 ---- 347 -642 348 6 349 8.8666666666666666666 350 8.8666666666666666666 351 11.0 352 15.8 353 354 query R 355 SELECT (avg(d) OVER (PARTITION BY v ORDER BY w) + avg(d) OVER (PARTITION BY w ORDER BY v)) FROM kv ORDER BY 1 356 ---- 357 -642 358 -151.0 359 7.1333333333333333333 360 7.1333333333333333333 361 8.45 362 15.8 363 364 query R 365 SELECT avg(d) OVER (PARTITION BY v) FROM kv WHERE FALSE ORDER BY 1 366 ---- 367 368 query R 369 SELECT avg(d) OVER (PARTITION BY v, v, v, v, v, v, v, v, v, v) FROM kv WHERE FALSE ORDER BY 1 370 ---- 371 372 query R 373 SELECT avg(d) OVER (PARTITION BY v, v, v, v, v, v, v, v, v, v) FROM kv WHERE k = 3 ORDER BY 1 374 ---- 375 8 376 377 query IT 378 SELECT k, concat_agg(s) OVER (PARTITION BY k ORDER BY w) FROM kv ORDER BY 1 379 ---- 380 1 a 381 3 a 382 5 NULL 383 6 b 384 7 b 385 8 A 386 387 query IT 388 SELECT k, concat_agg(s) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 389 ---- 390 1 ba 391 3 Aa 392 5 NULL 393 6 bab 394 7 b 395 8 A 396 397 query IB 398 SELECT k, bool_and(b) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 399 ---- 400 1 true 401 3 false 402 5 false 403 6 true 404 7 true 405 8 false 406 407 query IB 408 SELECT k, bool_or(b) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 409 ---- 410 1 true 411 3 true 412 5 false 413 6 true 414 7 true 415 8 false 416 417 query II 418 SELECT k, count(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 419 ---- 420 1 3 421 3 2 422 5 1 423 6 3 424 7 1 425 8 1 426 427 query II 428 SELECT k, count(*) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 429 ---- 430 1 3 431 3 2 432 5 1 433 6 3 434 7 1 435 8 1 436 437 query IR 438 SELECT k, max(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 439 ---- 440 1 7.9 441 3 8 442 5 -321 443 6 7.9 444 7 7.9 445 8 3 446 447 query IR 448 SELECT k, min(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 449 ---- 450 1 1 451 3 3 452 5 -321 453 6 1 454 7 7.9 455 8 3 456 457 query IR 458 SELECT k, pow(max(d) OVER (PARTITION BY v), k::DECIMAL) FROM kv ORDER BY 1 459 ---- 460 1 7.9 461 3 512 462 5 -3408200705601 463 6 243087.455521 464 7 1920390.8986159 465 8 16777216 466 467 query IR 468 SELECT k, max(d) OVER (PARTITION BY v) FROM kv ORDER BY 1 469 ---- 470 1 7.9 471 3 8 472 5 -321 473 6 7.9 474 7 7.9 475 8 8 476 477 query IR 478 SELECT k, sum(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 479 ---- 480 1 13.3 481 3 11 482 5 -321 483 6 13.3 484 7 7.9 485 8 3 486 487 query IR 488 SELECT k, variance(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 489 ---- 490 1 11.903333333333333333 491 3 12.5 492 5 NULL 493 6 11.903333333333333333 494 7 NULL 495 8 NULL 496 497 query IR 498 SELECT k, stddev(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 499 ---- 500 1 3.4501207708330056852 501 3 3.5355339059327376220 502 5 NULL 503 6 3.4501207708330056852 504 7 NULL 505 8 NULL 506 507 query IR 508 SELECT k, stddev(d) OVER w FROM kv WINDOW w as (PARTITION BY v) ORDER BY variance(d) OVER w, k 509 ---- 510 5 NULL 511 1 3.4501207708330056852 512 6 3.4501207708330056852 513 7 3.4501207708330056852 514 3 3.5355339059327376220 515 8 3.5355339059327376220 516 517 query IRIR 518 SELECT * FROM (SELECT k, d, v, stddev(d) OVER (PARTITION BY v) FROM kv) sub ORDER BY variance(d) OVER (PARTITION BY v), k 519 ---- 520 5 -321 NULL NULL 521 1 1 2 3.4501207708330056852 522 6 4.4 2 3.4501207708330056852 523 7 7.9 2 3.4501207708330056852 524 3 8 4 3.5355339059327376220 525 8 3 4 3.5355339059327376220 526 527 query IR 528 SELECT k, max(stddev) OVER (ORDER BY d) FROM (SELECT k, d, stddev(d) OVER (PARTITION BY v) as stddev FROM kv) sub ORDER BY 2, k 529 ---- 530 5 NULL 531 1 3.4501207708330056852 532 3 3.5355339059327376220 533 6 3.5355339059327376220 534 7 3.5355339059327376220 535 8 3.5355339059327376220 536 537 query IR 538 SELECT k, max(stddev) OVER (ORDER BY d DESC) FROM (SELECT k, d, stddev(d) OVER (PARTITION BY v) as stddev FROM kv) sub ORDER BY 2, k 539 ---- 540 1 3.5355339059327376220 541 3 3.5355339059327376220 542 5 3.5355339059327376220 543 6 3.5355339059327376220 544 7 3.5355339059327376220 545 8 3.5355339059327376220 546 547 query IRIII 548 SELECT k, (rank() OVER wind + avg(w) OVER wind), w, (v + row_number() OVER wind), v FROM kv WINDOW wind AS (ORDER BY k) ORDER BY 1 549 ---- 550 1 4 3 3 2 551 3 6 5 6 4 552 5 7.3333333333333333333 5 NULL NULL 553 6 8 3 6 2 554 7 8.6 2 7 2 555 8 9.3333333333333333333 2 10 4 556 557 query TIRRI 558 SELECT s, w + k, (sum(w) OVER wind + avg(d) OVER wind), (min(w) OVER wind + d), v FROM kv WINDOW wind AS (ORDER BY w, k) ORDER BY k 559 ---- 560 a 4 10.9666666666666666667 3 2 561 a 8 19.86 10 4 562 NULL 10 -29.45 -319 NULL 563 b 9 14.075 6.4 2 564 b 9 9.9 9.9 2 565 A 10 9.45 5 4 566 567 query IIII 568 SELECT k, v + w, round(rank() OVER wind + lead(k, 3, v) OVER wind + lag(w, 1, 2) OVER wind + f::DECIMAL + avg(d) OVER wind)::INT, round(row_number() OVER wind::FLOAT + round(f) + dense_rank() OVER wind::FLOAT)::INT FROM kv WINDOW wind as (PARTITION BY v ORDER BY k) ORDER BY k 569 ---- 570 1 5 7 3 571 3 9 17 4 572 5 NULL NULL 12 573 6 5 14 8 574 7 4 18 12 575 8 6 20 7 576 577 query II 578 SELECT (rank() OVER wind + lead(k, 3, v) OVER wind + lag(w, 1, 2) OVER wind), (row_number() OVER wind + dense_rank() OVER wind) FROM kv WINDOW wind as (PARTITION BY v ORDER BY k) ORDER BY k 579 ---- 580 5 2 581 7 2 582 NULL 2 583 7 4 584 8 6 585 11 4 586 587 query RIR 588 SELECT (round(avg(k) OVER w1 + sum(w) OVER w2) + row_number() OVER w2 + d + min(d) OVER w3 + f::DECIMAL) AS big_sum, v + w AS v_plus_w, (rank() OVER w3 + first_value(d) OVER w1 + nth_value(k, 2) OVER w1) AS small_sum FROM kv WINDOW w1 AS (PARTITION BY b ORDER BY k), w2 AS (PARTITION BY w ORDER BY k), w3 AS (PARTITION BY v ORDER BY k) ORDER BY k 589 ---- 590 8 5 NULL 591 26 9 5 592 -615.1 NULL NULL 593 20.8 5 6 594 21.9 4 7 595 22 6 -311 596 597 query RI 598 SELECT round(row_number() OVER w1 + lead(k, v, w) OVER w2 + avg(k) OVER w1), (lag(k, 1) OVER w1 + v + rank() OVER w2 + min(k) OVER w1) FROM kv WINDOW w1 AS (PARTITION BY w ORDER BY k), w2 AS (PARTITION BY b ORDER BY k) ORDER BY k 599 ---- 600 8 NULL 601 9 NULL 602 NULL NULL 603 9 7 604 10 NULL 605 12 20 606 607 query R 608 SELECT f::DECIMAL + round(max(k) * w * avg(d) OVER wind) + (lead(f, 2, 17::FLOAT) OVER wind::DECIMAL / d * row_number() OVER wind) FROM kv GROUP BY k, w, f, d WINDOW wind AS (ORDER BY k) ORDER BY k 609 ---- 610 13.9 611 71.10 612 -2590.156074766355140186916 613 -1376.87272727272727272728 614 -822.2405063291139240505 615 -753.9999999999999999998 616 617 query R 618 SELECT round(max(w) * w * avg(w) OVER wind) + (lead(w, 2, 17) OVER wind::DECIMAL / w * row_number() OVER wind) FROM kv GROUP BY w WINDOW wind AS (PARTITION BY w) ORDER BY 1 619 ---- 620 16.5 621 32.6666666666666666667 622 128.4 623 624 query IRRIRIR 625 SELECT k, avg(d) OVER w1, avg(d) OVER w2, row_number() OVER w2, sum(f) OVER w1, row_number() OVER w1, sum(f) OVER w2 FROM kv WINDOW w1 AS (ORDER BY k), w2 AS (ORDER BY w, k) ORDER BY k 626 ---- 627 1 1 3.9666666666666666667 3 1 1 10 628 3 4.5 4.86 5 3 2 16.4 629 5 -104 -49.45 6 12.9 3 26.3 630 6 -76.9 4.075 4 17.3 4 14.4 631 7 -59.94 7.9 1 23.3 5 6 632 8 -49.45 5.45 2 26.3 6 9 633 634 query R 635 SELECT round((avg(d) OVER wind) * max(k) + (lag(d, 1, 42.0) OVER wind) * max(d)) FROM kv GROUP BY d, k WINDOW wind AS (ORDER BY k) ORDER BY k 636 ---- 637 43 638 22 639 -3088 640 -1874 641 -385 642 -372 643 644 query RR 645 SELECT avg(k) OVER w, avg(k) OVER w + 1 FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY k 646 ---- 647 4.6666666666666666667 5.6666666666666666667 648 5.5 6.5 649 5 6 650 4.6666666666666666667 5.6666666666666666667 651 7 8 652 8 9 653 654 statement OK 655 INSERT INTO kv VALUES 656 (9, 2, 9, .1, DEFAULT, DEFAULT, DEFAULT), 657 (10, 4, 9, .2, DEFAULT, DEFAULT, DEFAULT), 658 (11, NULL, 9, .3, DEFAULT, DEFAULT, DEFAULT) 659 660 query II 661 SELECT k, row_number() OVER (ORDER BY k) FROM kv ORDER BY 1 662 ---- 663 1 1 664 3 2 665 5 3 666 6 4 667 7 5 668 8 6 669 9 7 670 10 8 671 11 9 672 673 query III 674 SELECT k, v, row_number() OVER (PARTITION BY v ORDER BY k) FROM kv ORDER BY 1 675 ---- 676 1 2 1 677 3 4 1 678 5 NULL 1 679 6 2 2 680 7 2 3 681 8 4 2 682 9 2 4 683 10 4 3 684 11 NULL 2 685 686 query IIII 687 SELECT k, v, w, row_number() OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 688 ---- 689 1 2 3 2 690 3 4 5 2 691 5 NULL 5 1 692 6 2 3 3 693 7 2 2 1 694 8 4 2 1 695 9 2 9 4 696 10 4 9 3 697 11 NULL 9 2 698 699 query IIII 700 SELECT k, v, w, v - w + 2 + row_number() OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 701 ---- 702 1 2 3 3 703 3 4 5 3 704 5 NULL 5 NULL 705 6 2 3 4 706 7 2 2 3 707 8 4 2 5 708 9 2 9 -1 709 10 4 9 0 710 11 NULL 9 NULL 711 712 query II 713 SELECT k, row_number() OVER (PARTITION BY k) FROM kv ORDER BY 1 714 ---- 715 1 1 716 3 1 717 5 1 718 6 1 719 7 1 720 8 1 721 9 1 722 10 1 723 11 1 724 725 query IIII 726 SELECT k, v, w, v - w + 2 + row_number() OVER (PARTITION BY v, k ORDER BY w) FROM kv ORDER BY 1 727 ---- 728 1 2 3 2 729 3 4 5 2 730 5 NULL 5 NULL 731 6 2 3 2 732 7 2 2 3 733 8 4 2 5 734 9 2 9 -4 735 10 4 9 -2 736 11 NULL 9 NULL 737 738 query RIII 739 SELECT avg(k), max(v), min(w), 2 + row_number() OVER () FROM kv ORDER BY 1 740 ---- 741 6.6666666666666666667 4 2 3 742 743 query II 744 SELECT k, rank() OVER () FROM kv ORDER BY 1 745 ---- 746 1 1 747 3 1 748 5 1 749 6 1 750 7 1 751 8 1 752 9 1 753 10 1 754 11 1 755 756 query III 757 SELECT k, v, rank() OVER (PARTITION BY v) FROM kv ORDER BY 1 758 ---- 759 1 2 1 760 3 4 1 761 5 NULL 1 762 6 2 1 763 7 2 1 764 8 4 1 765 9 2 1 766 10 4 1 767 11 NULL 1 768 769 query IIII 770 SELECT k, v, w, rank() OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 771 ---- 772 1 2 3 2 773 3 4 5 2 774 5 NULL 5 1 775 6 2 3 2 776 7 2 2 1 777 8 4 2 1 778 9 2 9 4 779 10 4 9 3 780 11 NULL 9 2 781 782 query IRI 783 SELECT k, (rank() OVER w + avg(w) OVER w), k FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY 1 784 ---- 785 1 4.6666666666666666667 1 786 3 5.5 3 787 5 6 5 788 6 4.6666666666666666667 6 789 7 3 7 790 8 3 8 791 9 8.25 9 792 10 8.3333333333333333333 10 793 11 9 11 794 795 query IRI 796 SELECT k, (avg(w) OVER w + rank() OVER w), k FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY 1 797 ---- 798 1 4.6666666666666666667 1 799 3 5.5 3 800 5 6 5 801 6 4.6666666666666666667 6 802 7 3 7 803 8 3 8 804 9 8.25 9 805 10 8.3333333333333333333 10 806 11 9 11 807 808 query II 809 SELECT k, dense_rank() OVER () FROM kv ORDER BY 1 810 ---- 811 1 1 812 3 1 813 5 1 814 6 1 815 7 1 816 8 1 817 9 1 818 10 1 819 11 1 820 821 query III 822 SELECT k, v, dense_rank() OVER (PARTITION BY v) FROM kv ORDER BY 1 823 ---- 824 1 2 1 825 3 4 1 826 5 NULL 1 827 6 2 1 828 7 2 1 829 8 4 1 830 9 2 1 831 10 4 1 832 11 NULL 1 833 834 query IIII 835 SELECT k, v, w, dense_rank() OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 836 ---- 837 1 2 3 2 838 3 4 5 2 839 5 NULL 5 1 840 6 2 3 2 841 7 2 2 1 842 8 4 2 1 843 9 2 9 3 844 10 4 9 3 845 11 NULL 9 2 846 847 query IR 848 SELECT k, percent_rank() OVER () FROM kv ORDER BY 1 849 ---- 850 1 0 851 3 0 852 5 0 853 6 0 854 7 0 855 8 0 856 9 0 857 10 0 858 11 0 859 860 query IIR 861 SELECT k, v, percent_rank() OVER (PARTITION BY v) FROM kv ORDER BY 1 862 ---- 863 1 2 0 864 3 4 0 865 5 NULL 0 866 6 2 0 867 7 2 0 868 8 4 0 869 9 2 0 870 10 4 0 871 11 NULL 0 872 873 query IIIR 874 SELECT k, v, w, percent_rank() OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 875 ---- 876 1 2 3 0.333333333333333 877 3 4 5 0.5 878 5 NULL 5 0 879 6 2 3 0.333333333333333 880 7 2 2 0 881 8 4 2 0 882 9 2 9 1 883 10 4 9 1 884 11 NULL 9 1 885 886 query IR 887 SELECT k, cume_dist() OVER () FROM kv ORDER BY 1 888 ---- 889 1 1 890 3 1 891 5 1 892 6 1 893 7 1 894 8 1 895 9 1 896 10 1 897 11 1 898 899 query IIR 900 SELECT k, v, cume_dist() OVER (PARTITION BY v) FROM kv ORDER BY 1 901 ---- 902 1 2 1 903 3 4 1 904 5 NULL 1 905 6 2 1 906 7 2 1 907 8 4 1 908 9 2 1 909 10 4 1 910 11 NULL 1 911 912 query IIIR 913 SELECT k, v, w, cume_dist() OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 914 ---- 915 1 2 3 0.75 916 3 4 5 0.666666666666667 917 5 NULL 5 0.5 918 6 2 3 0.75 919 7 2 2 0.25 920 8 4 2 0.333333333333333 921 9 2 9 1 922 10 4 9 1 923 11 NULL 9 1 924 925 query error argument of ntile\(\) must be greater than zero 926 SELECT k, ntile(-10) OVER () FROM kv ORDER BY 1 927 928 query error argument of ntile\(\) must be greater than zero 929 SELECT k, ntile(0) OVER () FROM kv ORDER BY 1 930 931 query II 932 SELECT k, ntile(NULL::INT) OVER () FROM kv ORDER BY 1 933 ---- 934 1 NULL 935 3 NULL 936 5 NULL 937 6 NULL 938 7 NULL 939 8 NULL 940 9 NULL 941 10 NULL 942 11 NULL 943 944 query II 945 SELECT k, ntile(1) OVER () FROM kv ORDER BY 1 946 ---- 947 1 1 948 3 1 949 5 1 950 6 1 951 7 1 952 8 1 953 9 1 954 10 1 955 11 1 956 957 query II 958 SELECT k, ntile(4) OVER (ORDER BY k) FROM kv ORDER BY 1 959 ---- 960 1 1 961 3 1 962 5 1 963 6 2 964 7 2 965 8 3 966 9 3 967 10 4 968 11 4 969 970 query II 971 SELECT k, ntile(20) OVER (ORDER BY k) FROM kv ORDER BY 1 972 ---- 973 1 1 974 3 2 975 5 3 976 6 4 977 7 5 978 8 6 979 9 7 980 10 8 981 11 9 982 983 # The value of 'w' in the first row will be 3. 984 query II 985 SELECT k, ntile(w) OVER (ORDER BY k) FROM kv ORDER BY 1 986 ---- 987 1 1 988 3 1 989 5 1 990 6 2 991 7 2 992 8 2 993 9 3 994 10 3 995 11 3 996 997 query III 998 SELECT k, v, ntile(3) OVER (PARTITION BY v ORDER BY k) FROM kv ORDER BY 1 999 ---- 1000 1 2 1 1001 3 4 1 1002 5 NULL 1 1003 6 2 1 1004 7 2 2 1005 8 4 2 1006 9 2 3 1007 10 4 3 1008 11 NULL 2 1009 1010 query IIII 1011 SELECT k, v, w, ntile(6) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 1012 ---- 1013 1 2 3 2 1014 3 4 5 2 1015 5 NULL 5 1 1016 6 2 3 3 1017 7 2 2 1 1018 8 4 2 1 1019 9 2 9 4 1020 10 4 9 3 1021 11 NULL 9 2 1022 1023 query II 1024 SELECT k, ntile(w) OVER (PARTITION BY k) FROM kv ORDER BY 1 1025 ---- 1026 1 1 1027 3 1 1028 5 1 1029 6 1 1030 7 1 1031 8 1 1032 9 1 1033 10 1 1034 11 1 1035 1036 query III 1037 SELECT k, v, ntile(3) OVER (PARTITION BY v, k) FROM kv ORDER BY 1 1038 ---- 1039 1 2 1 1040 3 4 1 1041 5 NULL 1 1042 6 2 1 1043 7 2 1 1044 8 4 1 1045 9 2 1 1046 10 4 1 1047 11 NULL 1 1048 1049 query IIII 1050 SELECT k, v, w, ntile(6) OVER (PARTITION BY v, k ORDER BY w) FROM kv ORDER BY 1 1051 ---- 1052 1 2 3 1 1053 3 4 5 1 1054 5 NULL 5 1 1055 6 2 3 1 1056 7 2 2 1 1057 8 4 2 1 1058 9 2 9 1 1059 10 4 9 1 1060 11 NULL 9 1 1061 1062 query II 1063 SELECT k, lag(9) OVER (ORDER BY k) FROM kv ORDER BY 1 1064 ---- 1065 1 NULL 1066 3 9 1067 5 9 1068 6 9 1069 7 9 1070 8 9 1071 9 9 1072 10 9 1073 11 9 1074 1075 query II 1076 SELECT k, lead(9) OVER (ORDER BY k) FROM kv ORDER BY 1 1077 ---- 1078 1 9 1079 3 9 1080 5 9 1081 6 9 1082 7 9 1083 8 9 1084 9 9 1085 10 9 1086 11 NULL 1087 1088 query II 1089 SELECT k, lag(k) OVER (ORDER BY k) FROM kv ORDER BY 1 1090 ---- 1091 1 NULL 1092 3 1 1093 5 3 1094 6 5 1095 7 6 1096 8 7 1097 9 8 1098 10 9 1099 11 10 1100 1101 query II 1102 SELECT k, lag(k) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 1103 ---- 1104 1 7 1105 3 8 1106 5 NULL 1107 6 1 1108 7 NULL 1109 8 NULL 1110 9 6 1111 10 3 1112 11 5 1113 1114 query II 1115 SELECT k, lead(k) OVER (ORDER BY k) FROM kv ORDER BY 1 1116 ---- 1117 1 3 1118 3 5 1119 5 6 1120 6 7 1121 7 8 1122 8 9 1123 9 10 1124 10 11 1125 11 NULL 1126 1127 query II 1128 SELECT k, lead(k) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 1129 ---- 1130 1 6 1131 3 10 1132 5 11 1133 6 9 1134 7 1 1135 8 3 1136 9 NULL 1137 10 NULL 1138 11 NULL 1139 1140 query II 1141 SELECT k, lag(k, 3) OVER (ORDER BY k) FROM kv ORDER BY 1 1142 ---- 1143 1 NULL 1144 3 NULL 1145 5 NULL 1146 6 1 1147 7 3 1148 8 5 1149 9 6 1150 10 7 1151 11 8 1152 1153 query II 1154 SELECT k, lag(k, 3) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 1155 ---- 1156 1 NULL 1157 3 NULL 1158 5 NULL 1159 6 NULL 1160 7 NULL 1161 8 NULL 1162 9 7 1163 10 NULL 1164 11 NULL 1165 1166 query II 1167 SELECT k, lead(k, 3) OVER (ORDER BY k) FROM kv ORDER BY 1 1168 ---- 1169 1 6 1170 3 7 1171 5 8 1172 6 9 1173 7 10 1174 8 11 1175 9 NULL 1176 10 NULL 1177 11 NULL 1178 1179 query II 1180 SELECT k, lead(k, 3) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 1181 ---- 1182 1 NULL 1183 3 NULL 1184 5 NULL 1185 6 NULL 1186 7 9 1187 8 NULL 1188 9 NULL 1189 10 NULL 1190 11 NULL 1191 1192 query II 1193 SELECT k, lag(k, -5) OVER (ORDER BY k) FROM kv ORDER BY 1 1194 ---- 1195 1 8 1196 3 9 1197 5 10 1198 6 11 1199 7 NULL 1200 8 NULL 1201 9 NULL 1202 10 NULL 1203 11 NULL 1204 1205 query II 1206 SELECT k, lead(k, -5) OVER (ORDER BY k) FROM kv ORDER BY 1 1207 ---- 1208 1 NULL 1209 3 NULL 1210 5 NULL 1211 6 NULL 1212 7 NULL 1213 8 1 1214 9 3 1215 10 5 1216 11 6 1217 1218 query II 1219 SELECT k, lag(k, 0) OVER () FROM kv ORDER BY 1 1220 ---- 1221 1 1 1222 3 3 1223 5 5 1224 6 6 1225 7 7 1226 8 8 1227 9 9 1228 10 10 1229 11 11 1230 1231 query II 1232 SELECT k, lead(k, 0) OVER () FROM kv ORDER BY 1 1233 ---- 1234 1 1 1235 3 3 1236 5 5 1237 6 6 1238 7 7 1239 8 8 1240 9 9 1241 10 10 1242 11 11 1243 1244 query II 1245 SELECT k, lag(k, NULL::INT) OVER () FROM kv ORDER BY 1 1246 ---- 1247 1 NULL 1248 3 NULL 1249 5 NULL 1250 6 NULL 1251 7 NULL 1252 8 NULL 1253 9 NULL 1254 10 NULL 1255 11 NULL 1256 1257 query II 1258 SELECT k, lead(k, NULL::INT) OVER () FROM kv ORDER BY 1 1259 ---- 1260 1 NULL 1261 3 NULL 1262 5 NULL 1263 6 NULL 1264 7 NULL 1265 8 NULL 1266 9 NULL 1267 10 NULL 1268 11 NULL 1269 1270 query II 1271 SELECT k, lag(k, w) OVER (ORDER BY k) FROM kv ORDER BY 1 1272 ---- 1273 1 NULL 1274 3 NULL 1275 5 NULL 1276 6 1 1277 7 5 1278 8 6 1279 9 NULL 1280 10 NULL 1281 11 NULL 1282 1283 query II 1284 SELECT k, lag(k, w) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 1285 ---- 1286 1 NULL 1287 3 NULL 1288 5 NULL 1289 6 NULL 1290 7 NULL 1291 8 NULL 1292 9 NULL 1293 10 NULL 1294 11 NULL 1295 1296 query II 1297 SELECT k, lead(k, w) OVER (ORDER BY k) FROM kv ORDER BY 1 1298 ---- 1299 1 6 1300 3 9 1301 5 10 1302 6 9 1303 7 9 1304 8 10 1305 9 NULL 1306 10 NULL 1307 11 NULL 1308 1309 query II 1310 SELECT k, lead(k, w) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 1311 ---- 1312 1 NULL 1313 3 NULL 1314 5 NULL 1315 6 NULL 1316 7 6 1317 8 10 1318 9 NULL 1319 10 NULL 1320 11 NULL 1321 1322 query error pq: lag\(\): could not parse "FOO" as type int 1323 SELECT k, lag(k, 1, 'FOO') OVER () FROM kv ORDER BY 1 1324 1325 query error pq: lead\(\): could not parse "FOO" as type int 1326 SELECT k, lead(k, 1, 'FOO') OVER () FROM kv ORDER BY 1 1327 1328 query error unknown signature: lag\(int, int, string\) 1329 SELECT k, lag(k, 1, s) OVER () FROM kv ORDER BY 1 1330 1331 query error unknown signature: lead\(int, int, string\) 1332 SELECT k, lead(k, 1, s) OVER () FROM kv ORDER BY 1 1333 1334 query II 1335 SELECT k, lag(k, 3, -99) OVER (ORDER BY k) FROM kv ORDER BY 1 1336 ---- 1337 1 -99 1338 3 -99 1339 5 -99 1340 6 1 1341 7 3 1342 8 5 1343 9 6 1344 10 7 1345 11 8 1346 1347 query II 1348 SELECT k, lead(k, 3, -99) OVER (ORDER BY k) FROM kv ORDER BY 1 1349 ---- 1350 1 6 1351 3 7 1352 5 8 1353 6 9 1354 7 10 1355 8 11 1356 9 -99 1357 10 -99 1358 11 -99 1359 1360 query II 1361 SELECT k, lag(k, 3, v) OVER (ORDER BY k) FROM kv ORDER BY 1 1362 ---- 1363 1 2 1364 3 4 1365 5 NULL 1366 6 1 1367 7 3 1368 8 5 1369 9 6 1370 10 7 1371 11 8 1372 1373 query II 1374 SELECT k, lead(k, 3, v) OVER (ORDER BY k) FROM kv ORDER BY 1 1375 ---- 1376 1 6 1377 3 7 1378 5 8 1379 6 9 1380 7 10 1381 8 11 1382 9 2 1383 10 4 1384 11 NULL 1385 1386 query II 1387 SELECT k, (lag(k, 5, w) OVER w + lead(k, 3, v) OVER w) FROM kv WINDOW w AS (ORDER BY k) ORDER BY 1 1388 ---- 1389 1 9 1390 3 12 1391 5 13 1392 6 12 1393 7 12 1394 8 12 1395 9 5 1396 10 9 1397 11 NULL 1398 1399 query II 1400 SELECT k, lag(k) OVER (PARTITION BY k) FROM kv ORDER BY 1 1401 ---- 1402 1 NULL 1403 3 NULL 1404 5 NULL 1405 6 NULL 1406 7 NULL 1407 8 NULL 1408 9 NULL 1409 10 NULL 1410 11 NULL 1411 1412 query II 1413 SELECT k, lead(k) OVER (PARTITION BY k) FROM kv ORDER BY 1 1414 ---- 1415 1 NULL 1416 3 NULL 1417 5 NULL 1418 6 NULL 1419 7 NULL 1420 8 NULL 1421 9 NULL 1422 10 NULL 1423 11 NULL 1424 1425 query II 1426 SELECT k, lag(k, 0) OVER (PARTITION BY k) FROM kv ORDER BY 1 1427 ---- 1428 1 1 1429 3 3 1430 5 5 1431 6 6 1432 7 7 1433 8 8 1434 9 9 1435 10 10 1436 11 11 1437 1438 query II 1439 SELECT k, lead(k, 0) OVER (PARTITION BY k) FROM kv ORDER BY 1 1440 ---- 1441 1 1 1442 3 3 1443 5 5 1444 6 6 1445 7 7 1446 8 8 1447 9 9 1448 10 10 1449 11 11 1450 1451 query II 1452 SELECT k, lag(k, -5) OVER (PARTITION BY k) FROM kv ORDER BY 1 1453 ---- 1454 1 NULL 1455 3 NULL 1456 5 NULL 1457 6 NULL 1458 7 NULL 1459 8 NULL 1460 9 NULL 1461 10 NULL 1462 11 NULL 1463 1464 query II 1465 SELECT k, lead(k, -5) OVER (PARTITION BY k) FROM kv ORDER BY 1 1466 ---- 1467 1 NULL 1468 3 NULL 1469 5 NULL 1470 6 NULL 1471 7 NULL 1472 8 NULL 1473 9 NULL 1474 10 NULL 1475 11 NULL 1476 1477 query II 1478 SELECT k, lag(k, w - w) OVER (PARTITION BY k) FROM kv ORDER BY 1 1479 ---- 1480 1 1 1481 3 3 1482 5 5 1483 6 6 1484 7 7 1485 8 8 1486 9 9 1487 10 10 1488 11 11 1489 1490 query II 1491 SELECT k, lead(k, w - w) OVER (PARTITION BY k) FROM kv ORDER BY 1 1492 ---- 1493 1 1 1494 3 3 1495 5 5 1496 6 6 1497 7 7 1498 8 8 1499 9 9 1500 10 10 1501 11 11 1502 1503 query II 1504 SELECT k, lag(k, 3, -99) OVER (PARTITION BY k) FROM kv ORDER BY 1 1505 ---- 1506 1 -99 1507 3 -99 1508 5 -99 1509 6 -99 1510 7 -99 1511 8 -99 1512 9 -99 1513 10 -99 1514 11 -99 1515 1516 query II 1517 SELECT k, lead(k, 3, -99) OVER (PARTITION BY k) FROM kv ORDER BY 1 1518 ---- 1519 1 -99 1520 3 -99 1521 5 -99 1522 6 -99 1523 7 -99 1524 8 -99 1525 9 -99 1526 10 -99 1527 11 -99 1528 1529 query II 1530 SELECT k, lag(k, 3, v) OVER (PARTITION BY k) FROM kv ORDER BY 1 1531 ---- 1532 1 2 1533 3 4 1534 5 NULL 1535 6 2 1536 7 2 1537 8 4 1538 9 2 1539 10 4 1540 11 NULL 1541 1542 query II 1543 SELECT k, lead(k, 3, v) OVER (PARTITION BY k) FROM kv ORDER BY 1 1544 ---- 1545 1 2 1546 3 4 1547 5 NULL 1548 6 2 1549 7 2 1550 8 4 1551 9 2 1552 10 4 1553 11 NULL 1554 1555 query II 1556 SELECT k, first_value(NULL::INT) OVER () FROM kv ORDER BY 1 1557 ---- 1558 1 NULL 1559 3 NULL 1560 5 NULL 1561 6 NULL 1562 7 NULL 1563 8 NULL 1564 9 NULL 1565 10 NULL 1566 11 NULL 1567 1568 query II 1569 SELECT k, first_value(1) OVER () FROM kv ORDER BY 1 1570 ---- 1571 1 1 1572 3 1 1573 5 1 1574 6 1 1575 7 1 1576 8 1 1577 9 1 1578 10 1 1579 11 1 1580 1581 query IR 1582 SELECT k, first_value(199.9 * 23.3) OVER () FROM kv ORDER BY 1 1583 ---- 1584 1 4657.67 1585 3 4657.67 1586 5 4657.67 1587 6 4657.67 1588 7 4657.67 1589 8 4657.67 1590 9 4657.67 1591 10 4657.67 1592 11 4657.67 1593 1594 query II 1595 SELECT k, first_value(v) OVER (ORDER BY k) FROM kv ORDER BY 1 1596 ---- 1597 1 2 1598 3 2 1599 5 2 1600 6 2 1601 7 2 1602 8 2 1603 9 2 1604 10 2 1605 11 2 1606 1607 query IIII 1608 SELECT k, v, w, first_value(w) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 1609 ---- 1610 1 2 3 2 1611 3 4 5 2 1612 5 NULL 5 5 1613 6 2 3 2 1614 7 2 2 2 1615 8 4 2 2 1616 9 2 9 2 1617 10 4 9 2 1618 11 NULL 9 5 1619 1620 query IIII 1621 SELECT k, v, w, first_value(w) OVER (PARTITION BY v ORDER BY w DESC) FROM kv ORDER BY 1 1622 ---- 1623 1 2 3 9 1624 3 4 5 9 1625 5 NULL 5 9 1626 6 2 3 9 1627 7 2 2 9 1628 8 4 2 9 1629 9 2 9 9 1630 10 4 9 9 1631 11 NULL 9 9 1632 1633 query II 1634 SELECT k, first_value(v) OVER (PARTITION BY k) FROM kv ORDER BY 1 1635 ---- 1636 1 2 1637 3 4 1638 5 NULL 1639 6 2 1640 7 2 1641 8 4 1642 9 2 1643 10 4 1644 11 NULL 1645 1646 query II 1647 SELECT k, last_value(NULL::INT) OVER () FROM kv ORDER BY 1 1648 ---- 1649 1 NULL 1650 3 NULL 1651 5 NULL 1652 6 NULL 1653 7 NULL 1654 8 NULL 1655 9 NULL 1656 10 NULL 1657 11 NULL 1658 1659 query II 1660 SELECT k, last_value(1) OVER () FROM kv ORDER BY 1 1661 ---- 1662 1 1 1663 3 1 1664 5 1 1665 6 1 1666 7 1 1667 8 1 1668 9 1 1669 10 1 1670 11 1 1671 1672 query IR 1673 SELECT k, last_value(199.9 * 23.3) OVER () FROM kv ORDER BY 1 1674 ---- 1675 1 4657.67 1676 3 4657.67 1677 5 4657.67 1678 6 4657.67 1679 7 4657.67 1680 8 4657.67 1681 9 4657.67 1682 10 4657.67 1683 11 4657.67 1684 1685 query II 1686 SELECT k, last_value(v) OVER (ORDER BY k) FROM kv ORDER BY 1 1687 ---- 1688 1 2 1689 3 4 1690 5 NULL 1691 6 2 1692 7 2 1693 8 4 1694 9 2 1695 10 4 1696 11 NULL 1697 1698 query IIII 1699 SELECT k, v, w, last_value(w) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 1700 ---- 1701 1 2 3 3 1702 3 4 5 5 1703 5 NULL 5 5 1704 6 2 3 3 1705 7 2 2 2 1706 8 4 2 2 1707 9 2 9 9 1708 10 4 9 9 1709 11 NULL 9 9 1710 1711 query IIII 1712 SELECT k, v, w, last_value(w) OVER (PARTITION BY v ORDER BY w DESC) FROM kv ORDER BY 1 1713 ---- 1714 1 2 3 3 1715 3 4 5 5 1716 5 NULL 5 5 1717 6 2 3 3 1718 7 2 2 2 1719 8 4 2 2 1720 9 2 9 9 1721 10 4 9 9 1722 11 NULL 9 9 1723 1724 query II 1725 SELECT k, last_value(v) OVER (PARTITION BY k) FROM kv ORDER BY 1 1726 ---- 1727 1 2 1728 3 4 1729 5 NULL 1730 6 2 1731 7 2 1732 8 4 1733 9 2 1734 10 4 1735 11 NULL 1736 1737 query error pq: nth_value\(\): could not parse "FOO" as type int 1738 SELECT k, nth_value(v, 'FOO') OVER () FROM kv ORDER BY 1 1739 1740 query error argument of nth_value\(\) must be greater than zero 1741 SELECT k, nth_value(v, -99) OVER () FROM kv ORDER BY 1 1742 1743 query error argument of nth_value\(\) must be greater than zero 1744 SELECT k, nth_value(v, 0) OVER () FROM kv ORDER BY 1 1745 1746 query II 1747 SELECT k, nth_value(NULL::INT, 5) OVER () FROM kv ORDER BY 1 1748 ---- 1749 1 NULL 1750 3 NULL 1751 5 NULL 1752 6 NULL 1753 7 NULL 1754 8 NULL 1755 9 NULL 1756 10 NULL 1757 11 NULL 1758 1759 query II 1760 SELECT k, nth_value(1, 3) OVER () FROM kv ORDER BY 1 1761 ---- 1762 1 1 1763 3 1 1764 5 1 1765 6 1 1766 7 1 1767 8 1 1768 9 1 1769 10 1 1770 11 1 1771 1772 query II 1773 SELECT k, nth_value(1, 33) OVER () FROM kv ORDER BY 1 1774 ---- 1775 1 NULL 1776 3 NULL 1777 5 NULL 1778 6 NULL 1779 7 NULL 1780 8 NULL 1781 9 NULL 1782 10 NULL 1783 11 NULL 1784 1785 query IR 1786 SELECT k, nth_value(199.9 * 23.3, 7) OVER () FROM kv ORDER BY 1 1787 ---- 1788 1 4657.67 1789 3 4657.67 1790 5 4657.67 1791 6 4657.67 1792 7 4657.67 1793 8 4657.67 1794 9 4657.67 1795 10 4657.67 1796 11 4657.67 1797 1798 query II 1799 SELECT k, nth_value(v, 8) OVER (ORDER BY k) FROM kv ORDER BY 1 1800 ---- 1801 1 NULL 1802 3 NULL 1803 5 NULL 1804 6 NULL 1805 7 NULL 1806 8 NULL 1807 9 NULL 1808 10 4 1809 11 4 1810 1811 query IIII 1812 SELECT k, v, w, nth_value(w, 2) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 1813 ---- 1814 1 2 3 3 1815 3 4 5 5 1816 5 NULL 5 NULL 1817 6 2 3 3 1818 7 2 2 NULL 1819 8 4 2 NULL 1820 9 2 9 3 1821 10 4 9 5 1822 11 NULL 9 9 1823 1824 query IIII 1825 SELECT k, v, w, nth_value(w, 2) OVER (PARTITION BY v ORDER BY w DESC) FROM kv ORDER BY 1 1826 ---- 1827 1 2 3 3 1828 3 4 5 5 1829 5 NULL 5 5 1830 6 2 3 3 1831 7 2 2 3 1832 8 4 2 5 1833 9 2 9 NULL 1834 10 4 9 NULL 1835 11 NULL 9 NULL 1836 1837 query II 1838 SELECT k, nth_value(v, k) OVER (ORDER BY k) FROM kv ORDER BY 1 1839 ---- 1840 1 2 1841 3 NULL 1842 5 NULL 1843 6 NULL 1844 7 NULL 1845 8 NULL 1846 9 NULL 1847 10 NULL 1848 11 NULL 1849 1850 query II 1851 SELECT k, nth_value(v, v) OVER (ORDER BY k) FROM kv ORDER BY 1 1852 ---- 1853 1 NULL 1854 3 NULL 1855 5 NULL 1856 6 4 1857 7 4 1858 8 2 1859 9 4 1860 10 2 1861 11 NULL 1862 1863 query II 1864 SELECT k, nth_value(v, 1) OVER (PARTITION BY k) FROM kv ORDER BY 1 1865 ---- 1866 1 2 1867 3 4 1868 5 NULL 1869 6 2 1870 7 2 1871 8 4 1872 9 2 1873 10 4 1874 11 NULL 1875 1876 query II 1877 SELECT k, nth_value(v, v) OVER (PARTITION BY k) FROM kv ORDER BY 1 1878 ---- 1879 1 NULL 1880 3 NULL 1881 5 NULL 1882 6 NULL 1883 7 NULL 1884 8 NULL 1885 9 NULL 1886 10 NULL 1887 11 NULL 1888 1889 1890 statement ok 1891 INSERT INTO kv VALUES (12, -1, DEFAULT, DEFAULT, DEFAULT, DEFAULT) 1892 1893 query error argument of nth_value\(\) must be greater than zero 1894 SELECT k, nth_value(v, v) OVER () FROM kv ORDER BY 1 1895 1896 statement ok 1897 DELETE FROM kv WHERE k = 12 1898 1899 query error FILTER specified but rank\(\) is not an aggregate function 1900 SELECT k, rank() FILTER (WHERE k=1) OVER () FROM kv 1901 1902 query TT 1903 SELECT i, avg(i) OVER (ORDER BY i) FROM kv ORDER BY i 1904 ---- 1905 NULL NULL 1906 NULL NULL 1907 NULL NULL 1908 NULL NULL 1909 00:00:00.001 00:00:00.001 1910 00:00:02 00:00:01.0005 1911 00:01:00 00:00:20.667 1912 4 days 1 day 00:00:15.50025 1913 3 years 7 mons 6 days 19:12:12.4002 1914 1915 1916 # Issue #14606: correctly handle aggregation functions above the windowing level 1917 query I 1918 SELECT max(i) * (row_number() OVER (ORDER BY max(i))) FROM (SELECT 1 AS i, 2 AS j) GROUP BY j 1919 ---- 1920 1 1921 1922 query R 1923 SELECT (1/j) * max(i) * (row_number() OVER (ORDER BY max(i))) FROM (SELECT 1 AS i, 2 AS j) GROUP BY j 1924 ---- 1925 0.5 1926 1927 query R 1928 SELECT max(i) * (1/j) * (row_number() OVER (ORDER BY max(i))) FROM (SELECT 1 AS i, 2 AS j) GROUP BY j 1929 ---- 1930 0.5 1931 1932 # regression test for #23798 until #10495 is fixed. 1933 statement error function reserved for internal use 1934 SELECT final_variance(1.2, 1.2, 123) OVER (PARTITION BY k) FROM kv 1935 1936 statement ok 1937 CREATE TABLE products ( 1938 group_id serial PRIMARY KEY, 1939 group_name VARCHAR (255) NOT NULL, 1940 product_name VARCHAR (255) NOT NULL, 1941 price DECIMAL (11, 2), 1942 priceInt INT, 1943 priceFloat FLOAT, 1944 pDate DATE, 1945 pTime TIME, 1946 pTimestamp TIMESTAMP, 1947 pTimestampTZ TIMESTAMPTZ, 1948 pInterval INTERVAL 1949 ) 1950 1951 statement ok 1952 INSERT INTO products (group_name, product_name, price, priceInt, priceFloat, pDate, pTime, pTimestamp, pTimestampTZ, pInterval) VALUES 1953 ('Smartphone', 'Microsoft Lumia', 200, 200, 200, '2018-07-30', TIME '01:23:45', TIMESTAMP '2018-07-30 01:23:45', TIMESTAMPTZ '2018-07-30 01:23:45', INTERVAL '1 months 2 days 3 hours 4 minutes 5 seconds'), 1954 ('Smartphone', 'HTC One', 400, 400, 400, '2018-07-31', TIME '12:34:56', TIMESTAMP '2018-07-31 12:34:56', TIMESTAMPTZ '2018-07-31 12:34:56', INTERVAL '1 days 2 hours 3 minutes 4 seconds'), 1955 ('Smartphone', 'Nexus', 500, 500, 500, '2018-07-30', TIME '11:23:45', TIMESTAMP '2018-07-30 11:23:45', TIMESTAMPTZ '2018-07-30 11:23:45', INTERVAL '1 hours 2 minutes 3 seconds'), 1956 ('Smartphone', 'iPhone', 900, 900, 900, '2018-07-31', TIME '07:34:56', TIMESTAMP '2018-07-31 07:34:56', TIMESTAMPTZ '2018-07-31 07:34:56', INTERVAL '1 minutes 2 seconds'), 1957 ('Laptop', 'HP Elite', 1200, 1200, 1200, '2018-07-30', TIME '01:23:45', TIMESTAMP '2018-07-30 01:23:45', TIMESTAMPTZ '2018-07-30 01:23:45', INTERVAL '1 months 2 days 3 hours 4 minutes 5 seconds'), 1958 ('Laptop', 'Lenovo Thinkpad', 700, 700, 700, '2018-07-31', TIME '12:34:56', TIMESTAMP '2018-07-31 12:34:56', TIMESTAMPTZ '2018-07-31 12:34:56', INTERVAL '1 days 2 hours 3 minutes 4 seconds'), 1959 ('Laptop', 'Sony VAIO', 700, 700, 700, '2018-07-30', TIME '11:23:45', TIMESTAMP '2018-07-30 11:23:45', TIMESTAMPTZ '2018-07-30 11:23:45', INTERVAL '1 hours 2 minutes 3 seconds'), 1960 ('Laptop', 'Dell', 800, 800, 800, '2018-07-31', TIME '07:34:56', TIMESTAMP '2018-07-31 07:34:56', TIMESTAMPTZ '2018-07-31 07:34:56', INTERVAL '1 minutes 2 seconds'), 1961 ('Tablet', 'iPad', 700, 700, 700, '2018-07-30', TIME '01:23:45', TIMESTAMP '2018-07-30 01:23:45', TIMESTAMPTZ '2018-07-30 01:23:45', INTERVAL '1 months 2 days 3 hours 4 minutes 5 seconds'), 1962 ('Tablet', 'Kindle Fire', 150, 150, 150, '2018-07-31', TIME '12:34:56', TIMESTAMP '2018-07-31 12:34:56', TIMESTAMPTZ '2018-07-31 12:34:56', INTERVAL '1 days 2 hours 3 minutes 4 seconds'), 1963 ('Tablet', 'Samsung', 200, 200, 200, '2018-07-30', TIME '11:23:45', TIMESTAMP '2018-07-30 11:23:45', TIMESTAMPTZ '2018-07-30 11:23:45', INTERVAL '1 hours 2 minutes 3 seconds') 1964 1965 statement error cannot copy window "w" because it has a frame clause 1966 SELECT avg(price) OVER (w) FROM products WINDOW w AS (ROWS 1 PRECEDING) 1967 1968 statement error cannot copy window "w" because it has a frame clause 1969 SELECT avg(price) OVER (w ORDER BY price) FROM products WINDOW w AS (ROWS 1 PRECEDING) 1970 1971 statement error frame starting offset must not be null 1972 SELECT avg(price) OVER (ROWS NULL PRECEDING) FROM products 1973 1974 statement error frame starting offset must not be null 1975 SELECT avg(price) OVER (ROWS BETWEEN NULL PRECEDING AND 1 FOLLOWING) FROM products 1976 1977 statement error frame starting offset must not be negative 1978 SELECT price, avg(price) OVER (PARTITION BY price ROWS -1 PRECEDING) AS avg_price FROM products 1979 1980 statement error frame starting offset must not be negative 1981 SELECT price, avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY price ROWS -1 PRECEDING) 1982 1983 statement error frame ending offset must not be null 1984 SELECT avg(price) OVER (ROWS BETWEEN 1 PRECEDING AND NULL FOLLOWING) FROM products 1985 1986 statement error frame ending offset must not be negative 1987 SELECT price, avg(price) OVER (PARTITION BY price ROWS BETWEEN 1 FOLLOWING AND -1 FOLLOWING) AS avg_price FROM products 1988 1989 statement error frame ending offset must not be negative 1990 SELECT price, avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY price ROWS BETWEEN 1 FOLLOWING AND -1 FOLLOWING) 1991 1992 statement error frame ending offset must not be negative 1993 SELECT product_name, price, min(price) OVER (PARTITION BY group_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS min_over_three, max(price) OVER (PARTITION BY group_name ROWS BETWEEN UNBOUNDED PRECEDING AND -1 FOLLOWING) AS max_over_partition FROM products ORDER BY group_id 1994 1995 statement error argument of WINDOW FRAME START must be type int, not type decimal 1996 SELECT avg(price) OVER (PARTITION BY group_name ROWS 1.5 PRECEDING) AS avg_price FROM products 1997 1998 statement error argument of WINDOW FRAME START must be type int, not type decimal 1999 SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ROWS 1.5 PRECEDING) 2000 2001 statement error argument of WINDOW FRAME START must be type int, not type decimal 2002 SELECT avg(price) OVER (PARTITION BY group_name ROWS BETWEEN 1.5 PRECEDING AND UNBOUNDED FOLLOWING) AS avg_price FROM products 2003 2004 statement error argument of WINDOW FRAME START must be type int, not type decimal 2005 SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ROWS BETWEEN 1.5 PRECEDING AND UNBOUNDED FOLLOWING) 2006 2007 statement error argument of WINDOW FRAME END must be type int, not type decimal 2008 SELECT avg(price) OVER (PARTITION BY group_name ROWS BETWEEN UNBOUNDED PRECEDING AND 1.5 FOLLOWING) AS avg_price FROM products 2009 2010 statement error argument of WINDOW FRAME END must be type int, not type decimal 2011 SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ROWS BETWEEN UNBOUNDED PRECEDING AND 1.5 FOLLOWING) 2012 2013 query TRT 2014 SELECT product_name, price, first_value(product_name) OVER w AS first FROM products WHERE price = 200 OR price = 700 WINDOW w as (PARTITION BY price ORDER BY product_name RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ORDER BY price, product_name 2015 ---- 2016 Microsoft Lumia 200.00 Microsoft Lumia 2017 Samsung 200.00 Microsoft Lumia 2018 Lenovo Thinkpad 700.00 Lenovo Thinkpad 2019 Sony VAIO 700.00 Lenovo Thinkpad 2020 iPad 700.00 Lenovo Thinkpad 2021 2022 query TRT 2023 SELECT product_name, price, last_value(product_name) OVER w AS last FROM products WHERE price = 200 OR price = 700 WINDOW w as (PARTITION BY price ORDER BY product_name RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ORDER BY price, product_name 2024 ---- 2025 Microsoft Lumia 200.00 Samsung 2026 Samsung 200.00 Samsung 2027 Lenovo Thinkpad 700.00 iPad 2028 Sony VAIO 700.00 iPad 2029 iPad 700.00 iPad 2030 2031 query TRT 2032 SELECT product_name, price, nth_value(product_name, 2) OVER w AS second FROM products WHERE price = 200 OR price = 700 WINDOW w as (PARTITION BY price ORDER BY product_name RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ORDER BY price, product_name 2033 ---- 2034 Microsoft Lumia 200.00 Samsung 2035 Samsung 200.00 NULL 2036 Lenovo Thinkpad 700.00 Sony VAIO 2037 Sony VAIO 700.00 iPad 2038 iPad 700.00 NULL 2039 2040 query TTRR 2041 SELECT product_name, group_name, price, avg(price) OVER (PARTITION BY group_name ORDER BY price, product_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_of_three FROM products ORDER BY group_name, price, product_name 2042 ---- 2043 Lenovo Thinkpad Laptop 700.00 700.00 2044 Sony VAIO Laptop 700.00 733.33333333333333333 2045 Dell Laptop 800.00 900.00 2046 HP Elite Laptop 1200.00 1000.00 2047 Microsoft Lumia Smartphone 200.00 300.00 2048 HTC One Smartphone 400.00 366.66666666666666667 2049 Nexus Smartphone 500.00 600.00 2050 iPhone Smartphone 900.00 700.00 2051 Kindle Fire Tablet 150.00 175.00 2052 Samsung Tablet 200.00 350.00 2053 iPad Tablet 700.00 450.00 2054 2055 query TTRR 2056 SELECT product_name, group_name, price, avg(priceFloat) OVER (PARTITION BY group_name ORDER BY price, product_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_of_three_floats FROM products ORDER BY group_name, price, product_name 2057 ---- 2058 Lenovo Thinkpad Laptop 700.00 700 2059 Sony VAIO Laptop 700.00 733.333333333333 2060 Dell Laptop 800.00 900 2061 HP Elite Laptop 1200.00 1000 2062 Microsoft Lumia Smartphone 200.00 300 2063 HTC One Smartphone 400.00 366.666666666667 2064 Nexus Smartphone 500.00 600 2065 iPhone Smartphone 900.00 700 2066 Kindle Fire Tablet 150.00 175 2067 Samsung Tablet 200.00 350 2068 iPad Tablet 700.00 450 2069 2070 query TTRR 2071 SELECT product_name, group_name, price, avg(priceInt) OVER (PARTITION BY group_name ORDER BY price, product_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_of_three_ints FROM products ORDER BY group_name, price, product_name 2072 ---- 2073 Lenovo Thinkpad Laptop 700.00 700 2074 Sony VAIO Laptop 700.00 733.33333333333333333 2075 Dell Laptop 800.00 900 2076 HP Elite Laptop 1200.00 1000 2077 Microsoft Lumia Smartphone 200.00 300 2078 HTC One Smartphone 400.00 366.66666666666666667 2079 Nexus Smartphone 500.00 600 2080 iPhone Smartphone 900.00 700 2081 Kindle Fire Tablet 150.00 175 2082 Samsung Tablet 200.00 350 2083 iPad Tablet 700.00 450 2084 2085 query TTRR 2086 SELECT group_name, product_name, price, avg(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS (SELECT count(*) FROM PRODUCTS WHERE price = 200) PRECEDING) AS running_avg_of_three FROM products ORDER BY group_id 2087 ---- 2088 Smartphone Microsoft Lumia 200.00 200.00 2089 Smartphone HTC One 400.00 300.00 2090 Smartphone Nexus 500.00 366.66666666666666667 2091 Smartphone iPhone 900.00 600.00 2092 Laptop HP Elite 1200.00 1200.00 2093 Laptop Lenovo Thinkpad 700.00 950.00 2094 Laptop Sony VAIO 700.00 866.66666666666666667 2095 Laptop Dell 800.00 733.33333333333333333 2096 Tablet iPad 700.00 700.00 2097 Tablet Kindle Fire 150.00 425.00 2098 Tablet Samsung 200.00 350.00 2099 2100 query TTRR 2101 SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS 2 PRECEDING) AS running_sum FROM products ORDER BY group_id 2102 ---- 2103 Smartphone Microsoft Lumia 200.00 200.00 2104 Smartphone HTC One 400.00 600.00 2105 Smartphone Nexus 500.00 1100.00 2106 Smartphone iPhone 900.00 1800.00 2107 Laptop HP Elite 1200.00 1200.00 2108 Laptop Lenovo Thinkpad 700.00 1900.00 2109 Laptop Sony VAIO 700.00 2600.00 2110 Laptop Dell 800.00 2200.00 2111 Tablet iPad 700.00 700.00 2112 Tablet Kindle Fire 150.00 850.00 2113 Tablet Samsung 200.00 1050.00 2114 2115 query TTRT 2116 SELECT group_name, product_name, price, array_agg(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS array_agg_price FROM products ORDER BY group_id 2117 ---- 2118 Smartphone Microsoft Lumia 200.00 {200.00,400.00,500.00} 2119 Smartphone HTC One 400.00 {200.00,400.00,500.00,900.00} 2120 Smartphone Nexus 500.00 {400.00,500.00,900.00} 2121 Smartphone iPhone 900.00 {500.00,900.00} 2122 Laptop HP Elite 1200.00 {1200.00,700.00,700.00} 2123 Laptop Lenovo Thinkpad 700.00 {1200.00,700.00,700.00,800.00} 2124 Laptop Sony VAIO 700.00 {700.00,700.00,800.00} 2125 Laptop Dell 800.00 {700.00,800.00} 2126 Tablet iPad 700.00 {700.00,150.00,200.00} 2127 Tablet Kindle Fire 150.00 {700.00,150.00,200.00} 2128 Tablet Samsung 200.00 {150.00,200.00} 2129 2130 query TTRTTTT 2131 SELECT group_name, product_name, price, array_agg(price) OVER (w ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING), array_agg(price) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING), array_agg(price) OVER (w GROUPS BETWEEN 3 PRECEDING AND UNBOUNDED FOLLOWING), array_agg(price) OVER (w RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (PARTITION BY group_name ORDER BY group_id DESC) ORDER BY group_id 2132 ---- 2133 Smartphone Microsoft Lumia 200.00 {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} 2134 Smartphone HTC One 400.00 {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} 2135 Smartphone Nexus 500.00 {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} 2136 Smartphone iPhone 900.00 {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} {900.00,500.00,400.00,200.00} 2137 Laptop HP Elite 1200.00 {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} 2138 Laptop Lenovo Thinkpad 700.00 {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} 2139 Laptop Sony VAIO 700.00 {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} 2140 Laptop Dell 800.00 {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} {800.00,700.00,700.00,1200.00} 2141 Tablet iPad 700.00 {200.00,150.00,700.00} {200.00,150.00,700.00} {200.00,150.00,700.00} {200.00,150.00,700.00} 2142 Tablet Kindle Fire 150.00 {200.00,150.00,700.00} {200.00,150.00,700.00} {200.00,150.00,700.00} {200.00,150.00,700.00} 2143 Tablet Samsung 200.00 {200.00,150.00,700.00} {200.00,150.00,700.00} {200.00,150.00,700.00} {200.00,150.00,700.00} 2144 2145 query TTRR 2146 SELECT group_name, product_name, price, avg(price) OVER (PARTITION BY group_name RANGE UNBOUNDED PRECEDING) AS avg_price FROM products ORDER BY group_id 2147 ---- 2148 Smartphone Microsoft Lumia 200.00 500.00 2149 Smartphone HTC One 400.00 500.00 2150 Smartphone Nexus 500.00 500.00 2151 Smartphone iPhone 900.00 500.00 2152 Laptop HP Elite 1200.00 850.00 2153 Laptop Lenovo Thinkpad 700.00 850.00 2154 Laptop Sony VAIO 700.00 850.00 2155 Laptop Dell 800.00 850.00 2156 Tablet iPad 700.00 350.00 2157 Tablet Kindle Fire 150.00 350.00 2158 Tablet Samsung 200.00 350.00 2159 2160 query TTRT 2161 SELECT group_name, product_name, price, min(price) OVER (PARTITION BY group_name ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING) AS min_over_empty_frame FROM products ORDER BY group_id 2162 ---- 2163 Smartphone Microsoft Lumia 200.00 NULL 2164 Smartphone HTC One 400.00 NULL 2165 Smartphone Nexus 500.00 NULL 2166 Smartphone iPhone 900.00 NULL 2167 Laptop HP Elite 1200.00 NULL 2168 Laptop Lenovo Thinkpad 700.00 NULL 2169 Laptop Sony VAIO 700.00 NULL 2170 Laptop Dell 800.00 NULL 2171 Tablet iPad 700.00 NULL 2172 Tablet Kindle Fire 150.00 NULL 2173 Tablet Samsung 200.00 NULL 2174 2175 query TRRR 2176 SELECT product_name, price, min(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS min_over_three, max(price) OVER (PARTITION BY group_name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_over_partition FROM products ORDER BY group_id 2177 ---- 2178 Microsoft Lumia 200.00 200.00 900.00 2179 HTC One 400.00 200.00 900.00 2180 Nexus 500.00 400.00 900.00 2181 iPhone 900.00 500.00 900.00 2182 HP Elite 1200.00 700.00 1200.00 2183 Lenovo Thinkpad 700.00 700.00 1200.00 2184 Sony VAIO 700.00 700.00 1200.00 2185 Dell 800.00 700.00 1200.00 2186 iPad 700.00 150.00 700.00 2187 Kindle Fire 150.00 150.00 700.00 2188 Samsung 200.00 150.00 700.00 2189 2190 query TTRT 2191 SELECT group_name, product_name, price, min(price) OVER (PARTITION BY group_name ROWS CURRENT ROW) AS min_over_single_row FROM products ORDER BY group_id 2192 ---- 2193 Smartphone Microsoft Lumia 200.00 200.00 2194 Smartphone HTC One 400.00 400.00 2195 Smartphone Nexus 500.00 500.00 2196 Smartphone iPhone 900.00 900.00 2197 Laptop HP Elite 1200.00 1200.00 2198 Laptop Lenovo Thinkpad 700.00 700.00 2199 Laptop Sony VAIO 700.00 700.00 2200 Laptop Dell 800.00 800.00 2201 Tablet iPad 700.00 700.00 2202 Tablet Kindle Fire 150.00 150.00 2203 Tablet Samsung 200.00 200.00 2204 2205 query TTRR 2206 SELECT group_name, product_name, price, avg(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS running_avg FROM products ORDER BY group_id 2207 ---- 2208 Smartphone Microsoft Lumia 200.00 600.00 2209 Smartphone HTC One 400.00 700.00 2210 Smartphone Nexus 500.00 900.00 2211 Smartphone iPhone 900.00 NULL 2212 Laptop HP Elite 1200.00 733.33333333333333333 2213 Laptop Lenovo Thinkpad 700.00 750.00 2214 Laptop Sony VAIO 700.00 800.00 2215 Laptop Dell 800.00 NULL 2216 Tablet iPad 700.00 175.00 2217 Tablet Kindle Fire 150.00 200.00 2218 Tablet Samsung 200.00 NULL 2219 2220 query TRRRRR 2221 SELECT product_name, price, min(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS UNBOUNDED PRECEDING), max(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING), sum(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING), avg(price) OVER (PARTITION BY group_name ROWS CURRENT ROW) FROM products ORDER BY group_id 2222 ---- 2223 Microsoft Lumia 200.00 200.00 400.00 2000.00 200.00 2224 HTC One 400.00 200.00 500.00 2000.00 400.00 2225 Nexus 500.00 200.00 900.00 1800.00 500.00 2226 iPhone 900.00 200.00 900.00 1400.00 900.00 2227 HP Elite 1200.00 1200.00 1200.00 3400.00 1200.00 2228 Lenovo Thinkpad 700.00 700.00 1200.00 3400.00 700.00 2229 Sony VAIO 700.00 700.00 1200.00 2200.00 700.00 2230 Dell 800.00 700.00 1200.00 1500.00 800.00 2231 iPad 700.00 700.00 700.00 1050.00 700.00 2232 Kindle Fire 150.00 150.00 700.00 1050.00 150.00 2233 Samsung 200.00 150.00 700.00 350.00 200.00 2234 2235 query RRR 2236 SELECT avg(price) OVER w1, avg(price) OVER w2, avg(price) OVER w1 FROM products WINDOW w1 AS (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), w2 AS (ORDER BY group_id ROWS 1 PRECEDING) ORDER BY group_id 2237 ---- 2238 300.00 200.00 300.00 2239 366.66666666666666667 300.00 366.66666666666666667 2240 600.00 450.00 600.00 2241 700.00 700.00 700.00 2242 950.00 1050.00 950.00 2243 866.66666666666666667 950.00 866.66666666666666667 2244 733.33333333333333333 700.00 733.33333333333333333 2245 750.00 750.00 750.00 2246 425.00 750.00 425.00 2247 350.00 425.00 350.00 2248 175.00 175.00 175.00 2249 2250 # In the following 4 tests, since ORDER BY is omitted, all rows are peers, so frame includes all the rows for every row. 2251 query TTRR 2252 SELECT group_name, product_name, price, sum(price) OVER (RANGE CURRENT ROW) FROM products ORDER BY group_id 2253 ---- 2254 Smartphone Microsoft Lumia 200.00 6450.00 2255 Smartphone HTC One 400.00 6450.00 2256 Smartphone Nexus 500.00 6450.00 2257 Smartphone iPhone 900.00 6450.00 2258 Laptop HP Elite 1200.00 6450.00 2259 Laptop Lenovo Thinkpad 700.00 6450.00 2260 Laptop Sony VAIO 700.00 6450.00 2261 Laptop Dell 800.00 6450.00 2262 Tablet iPad 700.00 6450.00 2263 Tablet Kindle Fire 150.00 6450.00 2264 Tablet Samsung 200.00 6450.00 2265 2266 query TTRR 2267 SELECT group_name, product_name, price, sum(price) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM products ORDER BY group_id 2268 ---- 2269 Smartphone Microsoft Lumia 200.00 6450.00 2270 Smartphone HTC One 400.00 6450.00 2271 Smartphone Nexus 500.00 6450.00 2272 Smartphone iPhone 900.00 6450.00 2273 Laptop HP Elite 1200.00 6450.00 2274 Laptop Lenovo Thinkpad 700.00 6450.00 2275 Laptop Sony VAIO 700.00 6450.00 2276 Laptop Dell 800.00 6450.00 2277 Tablet iPad 700.00 6450.00 2278 Tablet Kindle Fire 150.00 6450.00 2279 Tablet Samsung 200.00 6450.00 2280 2281 query TTRR 2282 SELECT group_name, product_name, price, sum(price) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM products ORDER BY group_id 2283 ---- 2284 Smartphone Microsoft Lumia 200.00 6450.00 2285 Smartphone HTC One 400.00 6450.00 2286 Smartphone Nexus 500.00 6450.00 2287 Smartphone iPhone 900.00 6450.00 2288 Laptop HP Elite 1200.00 6450.00 2289 Laptop Lenovo Thinkpad 700.00 6450.00 2290 Laptop Sony VAIO 700.00 6450.00 2291 Laptop Dell 800.00 6450.00 2292 Tablet iPad 700.00 6450.00 2293 Tablet Kindle Fire 150.00 6450.00 2294 Tablet Samsung 200.00 6450.00 2295 2296 query TTRR 2297 SELECT group_name, product_name, price, sum(price) OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM products ORDER BY group_id 2298 ---- 2299 Smartphone Microsoft Lumia 200.00 6450.00 2300 Smartphone HTC One 400.00 6450.00 2301 Smartphone Nexus 500.00 6450.00 2302 Smartphone iPhone 900.00 6450.00 2303 Laptop HP Elite 1200.00 6450.00 2304 Laptop Lenovo Thinkpad 700.00 6450.00 2305 Laptop Sony VAIO 700.00 6450.00 2306 Laptop Dell 800.00 6450.00 2307 Tablet iPad 700.00 6450.00 2308 Tablet Kindle Fire 150.00 6450.00 2309 Tablet Samsung 200.00 6450.00 2310 2311 statement error aggregate functions are not allowed in FILTER 2312 SELECT count(*) FILTER (WHERE count(*) > 5) OVER () FROM products 2313 2314 statement error window function calls cannot be nested 2315 SELECT count(*) FILTER (WHERE count(*) OVER () > 5) OVER () FROM products 2316 2317 statement error incompatible FILTER expression type: int 2318 SELECT count(*) FILTER (WHERE 1) OVER () FROM products 2319 2320 statement error at or near "filter": syntax error 2321 SELECT price FILTER (WHERE price=1) OVER () FROM products 2322 2323 query II 2324 SELECT count(*) FILTER (WHERE true) OVER (), count(*) FILTER (WHERE false) OVER () FROM products 2325 ---- 2326 11 0 2327 11 0 2328 11 0 2329 11 0 2330 11 0 2331 11 0 2332 11 0 2333 11 0 2334 11 0 2335 11 0 2336 11 0 2337 2338 query RRRR 2339 SELECT avg(price) FILTER (WHERE price > 300) OVER w1, sum(price) FILTER (WHERE group_name = 'Smartphone') OVER w2, avg(price) FILTER (WHERE price = 200 OR price = 700) OVER w1, avg(price) FILTER (WHERE price < 900) OVER w2 FROM products WINDOW w1 AS (ORDER BY group_id), w2 AS (PARTITION BY group_name ORDER BY price, group_id) ORDER BY group_id 2340 ---- 2341 NULL 200.00 200.00 200.00 2342 400.00 600.00 200.00 300.00 2343 450.00 1100.00 200.00 366.66666666666666667 2344 600.00 2000.00 200.00 366.66666666666666667 2345 750.00 NULL 200.00 733.33333333333333333 2346 740.00 NULL 450.00 700.00 2347 733.33333333333333333 NULL 533.33333333333333333 700.00 2348 742.85714285714285714 NULL 533.33333333333333333 733.33333333333333333 2349 737.50 NULL 575.00 350.00 2350 737.50 NULL 575.00 150.00 2351 737.50 NULL 500.00 175.00 2352 2353 statement error DISTINCT is not implemented for window functions 2354 SELECT count(DISTINCT group_name) OVER (), count(DISTINCT product_name) OVER () FROM products 2355 2356 statement error RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column 2357 SELECT sum(price) OVER (RANGE 100 PRECEDING) FROM products 2358 2359 statement error RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column 2360 SELECT sum(price) OVER (ORDER BY price, priceint RANGE 100 PRECEDING) FROM products 2361 2362 statement error invalid preceding or following size in window function 2363 SELECT sum(price) OVER (ORDER BY pdate RANGE '-1 days' PRECEDING) FROM products 2364 2365 statement error invalid preceding or following size in window function 2366 SELECT sum(price) OVER (ORDER BY ptime RANGE BETWEEN '-1 hours' PRECEDING AND '1 hours' FOLLOWING) FROM products 2367 2368 statement error invalid preceding or following size in window function 2369 SELECT sum(price) OVER (ORDER BY ptime RANGE BETWEEN '1 hours' PRECEDING AND '-1 hours' FOLLOWING) FROM products 2370 2371 statement error argument of WINDOW FRAME START must be type interval, not type decimal 2372 SELECT sum(price) OVER (ORDER BY ptimestamp RANGE 123.4 PRECEDING) FROM products 2373 2374 statement error argument of WINDOW FRAME START must be type interval, not type int 2375 SELECT sum(price) OVER (ORDER BY ptimestamptz RANGE BETWEEN 123 PRECEDING AND CURRENT ROW) FROM products 2376 2377 statement error could not parse "1 days" as type decimal 2378 SELECT sum(price) OVER (ORDER BY price RANGE BETWEEN 123.4 PRECEDING AND '1 days' FOLLOWING) FROM products 2379 2380 statement error RANGE with offset PRECEDING/FOLLOWING is not supported for column type varchar 2381 SELECT sum(price) OVER (ORDER BY product_name RANGE 'foo' PRECEDING) FROM products 2382 2383 query TTRR 2384 SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint RANGE 200 PRECEDING) FROM products ORDER BY group_name, priceint, group_id 2385 ---- 2386 Laptop Lenovo Thinkpad 700.00 1400 2387 Laptop Sony VAIO 700.00 1400 2388 Laptop Dell 800.00 2200 2389 Laptop HP Elite 1200.00 1200 2390 Smartphone Microsoft Lumia 200.00 200 2391 Smartphone HTC One 400.00 600 2392 Smartphone Nexus 500.00 900 2393 Smartphone iPhone 900.00 900 2394 Tablet Kindle Fire 150.00 150 2395 Tablet Samsung 200.00 350 2396 Tablet iPad 700.00 700 2397 2398 query TTRR 2399 SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price RANGE 200.002 PRECEDING) FROM products ORDER BY group_name, price, group_id 2400 ---- 2401 Laptop Lenovo Thinkpad 700.00 1400.00 2402 Laptop Sony VAIO 700.00 1400.00 2403 Laptop Dell 800.00 2200.00 2404 Laptop HP Elite 1200.00 1200.00 2405 Smartphone Microsoft Lumia 200.00 200.00 2406 Smartphone HTC One 400.00 600.00 2407 Smartphone Nexus 500.00 900.00 2408 Smartphone iPhone 900.00 900.00 2409 Tablet Kindle Fire 150.00 150.00 2410 Tablet Samsung 200.00 350.00 2411 Tablet iPad 700.00 700.00 2412 2413 query TTRR 2414 SELECT group_name, product_name, price, sum(pricefloat) OVER (PARTITION BY group_name ORDER BY pricefloat RANGE BETWEEN 200.01 PRECEDING AND 99.99 FOLLOWING) FROM products ORDER BY group_name, priceint, group_id 2415 ---- 2416 Laptop Lenovo Thinkpad 700.00 1400 2417 Laptop Sony VAIO 700.00 1400 2418 Laptop Dell 800.00 2200 2419 Laptop HP Elite 1200.00 1200 2420 Smartphone Microsoft Lumia 200.00 200 2421 Smartphone HTC One 400.00 600 2422 Smartphone Nexus 500.00 900 2423 Smartphone iPhone 900.00 900 2424 Tablet Kindle Fire 150.00 350 2425 Tablet Samsung 200.00 350 2426 Tablet iPad 700.00 700 2427 2428 query TTRR 2429 SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price RANGE BETWEEN 99.99 PRECEDING AND 100.00 PRECEDING) FROM products ORDER BY group_name, priceint, group_id 2430 ---- 2431 Laptop Lenovo Thinkpad 700.00 NULL 2432 Laptop Sony VAIO 700.00 NULL 2433 Laptop Dell 800.00 NULL 2434 Laptop HP Elite 1200.00 NULL 2435 Smartphone Microsoft Lumia 200.00 NULL 2436 Smartphone HTC One 400.00 NULL 2437 Smartphone Nexus 500.00 NULL 2438 Smartphone iPhone 900.00 NULL 2439 Tablet Kindle Fire 150.00 NULL 2440 Tablet Samsung 200.00 NULL 2441 Tablet iPad 700.00 NULL 2442 2443 query TTRR 2444 SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint RANGE BETWEEN 300 PRECEDING AND 50 PRECEDING) FROM products ORDER BY group_name, priceint, group_id 2445 ---- 2446 Laptop Lenovo Thinkpad 700.00 NULL 2447 Laptop Sony VAIO 700.00 NULL 2448 Laptop Dell 800.00 1400 2449 Laptop HP Elite 1200.00 NULL 2450 Smartphone Microsoft Lumia 200.00 NULL 2451 Smartphone HTC One 400.00 200 2452 Smartphone Nexus 500.00 600 2453 Smartphone iPhone 900.00 NULL 2454 Tablet Kindle Fire 150.00 NULL 2455 Tablet Samsung 200.00 150 2456 Tablet iPad 700.00 NULL 2457 2458 query TTRR 2459 SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint RANGE BETWEEN 50 FOLLOWING AND 300 FOLLOWING) FROM products ORDER BY group_name, priceint, group_id 2460 ---- 2461 Laptop Lenovo Thinkpad 700.00 800 2462 Laptop Sony VAIO 700.00 800 2463 Laptop Dell 800.00 NULL 2464 Laptop HP Elite 1200.00 NULL 2465 Smartphone Microsoft Lumia 200.00 900 2466 Smartphone HTC One 400.00 500 2467 Smartphone Nexus 500.00 NULL 2468 Smartphone iPhone 900.00 NULL 2469 Tablet Kindle Fire 150.00 200 2470 Tablet Samsung 200.00 NULL 2471 Tablet iPad 700.00 NULL 2472 2473 query TRR 2474 SELECT group_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price RANGE BETWEEN 49.999 FOLLOWING AND 300.001 FOLLOWING) FROM products ORDER BY group_name, price, group_id 2475 ---- 2476 Laptop 700.00 800.00 2477 Laptop 700.00 800.00 2478 Laptop 800.00 NULL 2479 Laptop 1200.00 NULL 2480 Smartphone 200.00 900.00 2481 Smartphone 400.00 500.00 2482 Smartphone 500.00 NULL 2483 Smartphone 900.00 NULL 2484 Tablet 150.00 200.00 2485 Tablet 200.00 NULL 2486 Tablet 700.00 NULL 2487 2488 query TRR 2489 SELECT group_name, price, sum(pricefloat) OVER (PARTITION BY group_name ORDER BY pricefloat RANGE BETWEEN 50 FOLLOWING AND 300 FOLLOWING) FROM products ORDER BY group_name, price, group_id 2490 ---- 2491 Laptop 700.00 800 2492 Laptop 700.00 800 2493 Laptop 800.00 NULL 2494 Laptop 1200.00 NULL 2495 Smartphone 200.00 900 2496 Smartphone 400.00 500 2497 Smartphone 500.00 NULL 2498 Smartphone 900.00 NULL 2499 Tablet 150.00 200 2500 Tablet 200.00 NULL 2501 Tablet 700.00 NULL 2502 2503 query TTRR 2504 SELECT group_name, product_name, price, nth_value(pricefloat, 2) OVER (PARTITION BY group_name ORDER BY pricefloat RANGE BETWEEN 1.23 FOLLOWING AND 500.23 FOLLOWING) FROM products ORDER BY group_name, pricefloat, group_id 2505 ---- 2506 Laptop Lenovo Thinkpad 700.00 1200 2507 Laptop Sony VAIO 700.00 1200 2508 Laptop Dell 800.00 NULL 2509 Laptop HP Elite 1200.00 NULL 2510 Smartphone Microsoft Lumia 200.00 500 2511 Smartphone HTC One 400.00 900 2512 Smartphone Nexus 500.00 NULL 2513 Smartphone iPhone 900.00 NULL 2514 Tablet Kindle Fire 150.00 NULL 2515 Tablet Samsung 200.00 NULL 2516 Tablet iPad 700.00 NULL 2517 2518 query TTTRR 2519 SELECT group_name, product_name, pdate, price, sum(price) OVER (ORDER BY pdate RANGE '1 days' PRECEDING) FROM products ORDER BY pdate, group_id 2520 ---- 2521 Smartphone Microsoft Lumia 2018-07-30 00:00:00 +0000 +0000 200.00 3500.00 2522 Smartphone Nexus 2018-07-30 00:00:00 +0000 +0000 500.00 3500.00 2523 Laptop HP Elite 2018-07-30 00:00:00 +0000 +0000 1200.00 3500.00 2524 Laptop Sony VAIO 2018-07-30 00:00:00 +0000 +0000 700.00 3500.00 2525 Tablet iPad 2018-07-30 00:00:00 +0000 +0000 700.00 3500.00 2526 Tablet Samsung 2018-07-30 00:00:00 +0000 +0000 200.00 3500.00 2527 Smartphone HTC One 2018-07-31 00:00:00 +0000 +0000 400.00 6450.00 2528 Smartphone iPhone 2018-07-31 00:00:00 +0000 +0000 900.00 6450.00 2529 Laptop Lenovo Thinkpad 2018-07-31 00:00:00 +0000 +0000 700.00 6450.00 2530 Laptop Dell 2018-07-31 00:00:00 +0000 +0000 800.00 6450.00 2531 Tablet Kindle Fire 2018-07-31 00:00:00 +0000 +0000 150.00 6450.00 2532 2533 query TTRR 2534 SELECT product_name, ptime, price, avg(price) OVER (ORDER BY ptime RANGE BETWEEN '1 hours 15 minutes' PRECEDING AND '1 hours 15 minutes' FOLLOWING) FROM products ORDER BY ptime, group_id 2535 ---- 2536 Microsoft Lumia 0000-01-01 01:23:45 +0000 UTC 200.00 700.00 2537 HP Elite 0000-01-01 01:23:45 +0000 UTC 1200.00 700.00 2538 iPad 0000-01-01 01:23:45 +0000 UTC 700.00 700.00 2539 iPhone 0000-01-01 07:34:56 +0000 UTC 900.00 850.00 2540 Dell 0000-01-01 07:34:56 +0000 UTC 800.00 850.00 2541 Nexus 0000-01-01 11:23:45 +0000 UTC 500.00 441.66666666666666667 2542 Sony VAIO 0000-01-01 11:23:45 +0000 UTC 700.00 441.66666666666666667 2543 Samsung 0000-01-01 11:23:45 +0000 UTC 200.00 441.66666666666666667 2544 HTC One 0000-01-01 12:34:56 +0000 UTC 400.00 441.66666666666666667 2545 Lenovo Thinkpad 0000-01-01 12:34:56 +0000 UTC 700.00 441.66666666666666667 2546 Kindle Fire 0000-01-01 12:34:56 +0000 UTC 150.00 441.66666666666666667 2547 2548 query TTTRR 2549 SELECT group_name, product_name, ptime, price, min(price) OVER (PARTITION BY group_name ORDER BY ptime RANGE BETWEEN '1 hours' FOLLOWING AND UNBOUNDED FOLLOWING) FROM products ORDER BY group_name, ptime 2550 ---- 2551 Laptop HP Elite 0000-01-01 01:23:45 +0000 UTC 1200.00 700.00 2552 Laptop Dell 0000-01-01 07:34:56 +0000 UTC 800.00 700.00 2553 Laptop Sony VAIO 0000-01-01 11:23:45 +0000 UTC 700.00 700.00 2554 Laptop Lenovo Thinkpad 0000-01-01 12:34:56 +0000 UTC 700.00 NULL 2555 Smartphone Microsoft Lumia 0000-01-01 01:23:45 +0000 UTC 200.00 400.00 2556 Smartphone iPhone 0000-01-01 07:34:56 +0000 UTC 900.00 400.00 2557 Smartphone Nexus 0000-01-01 11:23:45 +0000 UTC 500.00 400.00 2558 Smartphone HTC One 0000-01-01 12:34:56 +0000 UTC 400.00 NULL 2559 Tablet iPad 0000-01-01 01:23:45 +0000 UTC 700.00 150.00 2560 Tablet Samsung 0000-01-01 11:23:45 +0000 UTC 200.00 150.00 2561 Tablet Kindle Fire 0000-01-01 12:34:56 +0000 UTC 150.00 NULL 2562 2563 query TTTRR 2564 SELECT group_name, product_name, ptimestamp, price, first_value(price) OVER (PARTITION BY group_name ORDER BY ptimestamp RANGE BETWEEN '12 hours' PRECEDING AND '6 hours' FOLLOWING) FROM products ORDER BY group_name, ptimestamp 2565 ---- 2566 Laptop HP Elite 2018-07-30 01:23:45 +0000 +0000 1200.00 1200.00 2567 Laptop Sony VAIO 2018-07-30 11:23:45 +0000 +0000 700.00 1200.00 2568 Laptop Dell 2018-07-31 07:34:56 +0000 +0000 800.00 800.00 2569 Laptop Lenovo Thinkpad 2018-07-31 12:34:56 +0000 +0000 700.00 800.00 2570 Smartphone Microsoft Lumia 2018-07-30 01:23:45 +0000 +0000 200.00 200.00 2571 Smartphone Nexus 2018-07-30 11:23:45 +0000 +0000 500.00 200.00 2572 Smartphone iPhone 2018-07-31 07:34:56 +0000 +0000 900.00 900.00 2573 Smartphone HTC One 2018-07-31 12:34:56 +0000 +0000 400.00 900.00 2574 Tablet iPad 2018-07-30 01:23:45 +0000 +0000 700.00 700.00 2575 Tablet Samsung 2018-07-30 11:23:45 +0000 +0000 200.00 700.00 2576 Tablet Kindle Fire 2018-07-31 12:34:56 +0000 +0000 150.00 150.00 2577 2578 query TTTRR 2579 SELECT group_name, product_name, ptimestamptz, price, avg(price) OVER (PARTITION BY group_name ORDER BY ptimestamptz RANGE BETWEEN '1 days 12 hours' PRECEDING AND CURRENT ROW) FROM products ORDER BY group_name, ptimestamptz 2580 ---- 2581 Laptop HP Elite 2018-07-30 01:23:45 +0000 UTC 1200.00 1200.00 2582 Laptop Sony VAIO 2018-07-30 11:23:45 +0000 UTC 700.00 950.00 2583 Laptop Dell 2018-07-31 07:34:56 +0000 UTC 800.00 900.00 2584 Laptop Lenovo Thinkpad 2018-07-31 12:34:56 +0000 UTC 700.00 850.00 2585 Smartphone Microsoft Lumia 2018-07-30 01:23:45 +0000 UTC 200.00 200.00 2586 Smartphone Nexus 2018-07-30 11:23:45 +0000 UTC 500.00 350.00 2587 Smartphone iPhone 2018-07-31 07:34:56 +0000 UTC 900.00 533.33333333333333333 2588 Smartphone HTC One 2018-07-31 12:34:56 +0000 UTC 400.00 500.00 2589 Tablet iPad 2018-07-30 01:23:45 +0000 UTC 700.00 700.00 2590 Tablet Samsung 2018-07-30 11:23:45 +0000 UTC 200.00 450.00 2591 Tablet Kindle Fire 2018-07-31 12:34:56 +0000 UTC 150.00 350.00 2592 2593 query TTRR 2594 SELECT product_name, pinterval, price, avg(price) OVER (ORDER BY pinterval RANGE BETWEEN '2 hours 34 minutes 56 seconds' PRECEDING AND '3 months' FOLLOWING) FROM products ORDER BY pinterval, group_id 2595 ---- 2596 iPhone 00:01:02 900.00 586.36363636363636364 2597 Dell 00:01:02 800.00 586.36363636363636364 2598 Nexus 01:02:03 500.00 586.36363636363636364 2599 Sony VAIO 01:02:03 700.00 586.36363636363636364 2600 Samsung 01:02:03 200.00 586.36363636363636364 2601 HTC One 1 day 02:03:04 400.00 558.33333333333333333 2602 Lenovo Thinkpad 1 day 02:03:04 700.00 558.33333333333333333 2603 Kindle Fire 1 day 02:03:04 150.00 558.33333333333333333 2604 Microsoft Lumia 1 mon 2 days 03:04:05 200.00 700.00 2605 HP Elite 1 mon 2 days 03:04:05 1200.00 700.00 2606 iPad 1 mon 2 days 03:04:05 700.00 700.00 2607 2608 query TTRR 2609 SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint DESC RANGE 200 PRECEDING) FROM products ORDER BY group_name, priceint DESC, group_id 2610 ---- 2611 Laptop HP Elite 1200.00 1200 2612 Laptop Dell 800.00 800 2613 Laptop Lenovo Thinkpad 700.00 2200 2614 Laptop Sony VAIO 700.00 2200 2615 Smartphone iPhone 900.00 900 2616 Smartphone Nexus 500.00 500 2617 Smartphone HTC One 400.00 900 2618 Smartphone Microsoft Lumia 200.00 600 2619 Tablet iPad 700.00 700 2620 Tablet Samsung 200.00 200 2621 Tablet Kindle Fire 150.00 350 2622 2623 query TTRR 2624 SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price DESC RANGE 200.002 PRECEDING) FROM products ORDER BY group_name, price DESC, group_id 2625 ---- 2626 Laptop HP Elite 1200.00 1200.00 2627 Laptop Dell 800.00 800.00 2628 Laptop Lenovo Thinkpad 700.00 2200.00 2629 Laptop Sony VAIO 700.00 2200.00 2630 Smartphone iPhone 900.00 900.00 2631 Smartphone Nexus 500.00 500.00 2632 Smartphone HTC One 400.00 900.00 2633 Smartphone Microsoft Lumia 200.00 600.00 2634 Tablet iPad 700.00 700.00 2635 Tablet Samsung 200.00 200.00 2636 Tablet Kindle Fire 150.00 350.00 2637 2638 query TTRR 2639 SELECT group_name, product_name, price, sum(pricefloat) OVER (PARTITION BY group_name ORDER BY pricefloat DESC RANGE BETWEEN 200.01 PRECEDING AND 99.99 FOLLOWING) FROM products ORDER BY group_name, priceint DESC, group_id 2640 ---- 2641 Laptop HP Elite 1200.00 1200 2642 Laptop Dell 800.00 800 2643 Laptop Lenovo Thinkpad 700.00 2200 2644 Laptop Sony VAIO 700.00 2200 2645 Smartphone iPhone 900.00 900 2646 Smartphone Nexus 500.00 500 2647 Smartphone HTC One 400.00 900 2648 Smartphone Microsoft Lumia 200.00 600 2649 Tablet iPad 700.00 700 2650 Tablet Samsung 200.00 350 2651 Tablet Kindle Fire 150.00 350 2652 2653 query TTRR 2654 SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price DESC RANGE BETWEEN 99.99 PRECEDING AND 100.00 PRECEDING) FROM products ORDER BY group_name, priceint DESC, group_id 2655 ---- 2656 Laptop HP Elite 1200.00 NULL 2657 Laptop Dell 800.00 NULL 2658 Laptop Lenovo Thinkpad 700.00 NULL 2659 Laptop Sony VAIO 700.00 NULL 2660 Smartphone iPhone 900.00 NULL 2661 Smartphone Nexus 500.00 NULL 2662 Smartphone HTC One 400.00 NULL 2663 Smartphone Microsoft Lumia 200.00 NULL 2664 Tablet iPad 700.00 NULL 2665 Tablet Samsung 200.00 NULL 2666 Tablet Kindle Fire 150.00 NULL 2667 2668 query TTRR 2669 SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint DESC RANGE BETWEEN 300 PRECEDING AND 50 PRECEDING) FROM products ORDER BY group_name, priceint DESC, group_id 2670 ---- 2671 Laptop HP Elite 1200.00 NULL 2672 Laptop Dell 800.00 NULL 2673 Laptop Lenovo Thinkpad 700.00 800 2674 Laptop Sony VAIO 700.00 800 2675 Smartphone iPhone 900.00 NULL 2676 Smartphone Nexus 500.00 NULL 2677 Smartphone HTC One 400.00 500 2678 Smartphone Microsoft Lumia 200.00 900 2679 Tablet iPad 700.00 NULL 2680 Tablet Samsung 200.00 NULL 2681 Tablet Kindle Fire 150.00 200 2682 2683 query TTRR 2684 SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint DESC RANGE BETWEEN 50 FOLLOWING AND 300 FOLLOWING) FROM products ORDER BY group_name, priceint DESC, group_id 2685 ---- 2686 Laptop HP Elite 1200.00 NULL 2687 Laptop Dell 800.00 1400 2688 Laptop Lenovo Thinkpad 700.00 NULL 2689 Laptop Sony VAIO 700.00 NULL 2690 Smartphone iPhone 900.00 NULL 2691 Smartphone Nexus 500.00 600 2692 Smartphone HTC One 400.00 200 2693 Smartphone Microsoft Lumia 200.00 NULL 2694 Tablet iPad 700.00 NULL 2695 Tablet Samsung 200.00 150 2696 Tablet Kindle Fire 150.00 NULL 2697 2698 query TRR 2699 SELECT group_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price DESC RANGE BETWEEN 49.999 FOLLOWING AND 300.001 FOLLOWING) FROM products ORDER BY group_name, price DESC, group_id 2700 ---- 2701 Laptop 1200.00 NULL 2702 Laptop 800.00 1400.00 2703 Laptop 700.00 NULL 2704 Laptop 700.00 NULL 2705 Smartphone 900.00 NULL 2706 Smartphone 500.00 600.00 2707 Smartphone 400.00 200.00 2708 Smartphone 200.00 NULL 2709 Tablet 700.00 NULL 2710 Tablet 200.00 150.00 2711 Tablet 150.00 NULL 2712 2713 query TRR 2714 SELECT group_name, price, sum(pricefloat) OVER (PARTITION BY group_name ORDER BY pricefloat DESC RANGE BETWEEN 50 FOLLOWING AND 300 FOLLOWING) FROM products ORDER BY group_name, price DESC, group_id 2715 ---- 2716 Laptop 1200.00 NULL 2717 Laptop 800.00 1400 2718 Laptop 700.00 NULL 2719 Laptop 700.00 NULL 2720 Smartphone 900.00 NULL 2721 Smartphone 500.00 600 2722 Smartphone 400.00 200 2723 Smartphone 200.00 NULL 2724 Tablet 700.00 NULL 2725 Tablet 200.00 150 2726 Tablet 150.00 NULL 2727 2728 query TTRR 2729 SELECT group_name, product_name, price, nth_value(pricefloat, 2) OVER (PARTITION BY group_name ORDER BY pricefloat DESC RANGE BETWEEN 1.23 FOLLOWING AND 500.23 FOLLOWING) FROM products ORDER BY group_name, pricefloat DESC, group_id 2730 ---- 2731 Laptop HP Elite 1200.00 700 2732 Laptop Dell 800.00 700 2733 Laptop Lenovo Thinkpad 700.00 NULL 2734 Laptop Sony VAIO 700.00 NULL 2735 Smartphone iPhone 900.00 400 2736 Smartphone Nexus 500.00 200 2737 Smartphone HTC One 400.00 NULL 2738 Smartphone Microsoft Lumia 200.00 NULL 2739 Tablet iPad 700.00 NULL 2740 Tablet Samsung 200.00 NULL 2741 Tablet Kindle Fire 150.00 NULL 2742 2743 query TTTRR 2744 SELECT group_name, product_name, pdate, price, sum(price) OVER (ORDER BY pdate DESC RANGE '1 days' PRECEDING) FROM products ORDER BY pdate DESC, group_id 2745 ---- 2746 Smartphone HTC One 2018-07-31 00:00:00 +0000 +0000 400.00 2950.00 2747 Smartphone iPhone 2018-07-31 00:00:00 +0000 +0000 900.00 2950.00 2748 Laptop Lenovo Thinkpad 2018-07-31 00:00:00 +0000 +0000 700.00 2950.00 2749 Laptop Dell 2018-07-31 00:00:00 +0000 +0000 800.00 2950.00 2750 Tablet Kindle Fire 2018-07-31 00:00:00 +0000 +0000 150.00 2950.00 2751 Smartphone Microsoft Lumia 2018-07-30 00:00:00 +0000 +0000 200.00 6450.00 2752 Smartphone Nexus 2018-07-30 00:00:00 +0000 +0000 500.00 6450.00 2753 Laptop HP Elite 2018-07-30 00:00:00 +0000 +0000 1200.00 6450.00 2754 Laptop Sony VAIO 2018-07-30 00:00:00 +0000 +0000 700.00 6450.00 2755 Tablet iPad 2018-07-30 00:00:00 +0000 +0000 700.00 6450.00 2756 Tablet Samsung 2018-07-30 00:00:00 +0000 +0000 200.00 6450.00 2757 2758 query TTRR 2759 SELECT product_name, ptime, price, avg(price) OVER (ORDER BY ptime DESC RANGE BETWEEN '1 hours 15 minutes' PRECEDING AND '1 hours 15 minutes' FOLLOWING) FROM products ORDER BY ptime DESC, group_id 2760 ---- 2761 HTC One 0000-01-01 12:34:56 +0000 UTC 400.00 441.66666666666666667 2762 Lenovo Thinkpad 0000-01-01 12:34:56 +0000 UTC 700.00 441.66666666666666667 2763 Kindle Fire 0000-01-01 12:34:56 +0000 UTC 150.00 441.66666666666666667 2764 Nexus 0000-01-01 11:23:45 +0000 UTC 500.00 441.66666666666666667 2765 Sony VAIO 0000-01-01 11:23:45 +0000 UTC 700.00 441.66666666666666667 2766 Samsung 0000-01-01 11:23:45 +0000 UTC 200.00 441.66666666666666667 2767 iPhone 0000-01-01 07:34:56 +0000 UTC 900.00 850.00 2768 Dell 0000-01-01 07:34:56 +0000 UTC 800.00 850.00 2769 Microsoft Lumia 0000-01-01 01:23:45 +0000 UTC 200.00 700.00 2770 HP Elite 0000-01-01 01:23:45 +0000 UTC 1200.00 700.00 2771 iPad 0000-01-01 01:23:45 +0000 UTC 700.00 700.00 2772 2773 query TTTRR 2774 SELECT group_name, product_name, ptime, price, min(price) OVER (PARTITION BY group_name ORDER BY ptime DESC RANGE BETWEEN '1 hours' FOLLOWING AND UNBOUNDED FOLLOWING) FROM products ORDER BY group_name, ptime DESC 2775 ---- 2776 Laptop Lenovo Thinkpad 0000-01-01 12:34:56 +0000 UTC 700.00 700.00 2777 Laptop Sony VAIO 0000-01-01 11:23:45 +0000 UTC 700.00 800.00 2778 Laptop Dell 0000-01-01 07:34:56 +0000 UTC 800.00 1200.00 2779 Laptop HP Elite 0000-01-01 01:23:45 +0000 UTC 1200.00 NULL 2780 Smartphone HTC One 0000-01-01 12:34:56 +0000 UTC 400.00 200.00 2781 Smartphone Nexus 0000-01-01 11:23:45 +0000 UTC 500.00 200.00 2782 Smartphone iPhone 0000-01-01 07:34:56 +0000 UTC 900.00 200.00 2783 Smartphone Microsoft Lumia 0000-01-01 01:23:45 +0000 UTC 200.00 NULL 2784 Tablet Kindle Fire 0000-01-01 12:34:56 +0000 UTC 150.00 200.00 2785 Tablet Samsung 0000-01-01 11:23:45 +0000 UTC 200.00 700.00 2786 Tablet iPad 0000-01-01 01:23:45 +0000 UTC 700.00 NULL 2787 2788 query TTTRR 2789 SELECT group_name, product_name, ptimestamp, price, first_value(price) OVER (PARTITION BY group_name ORDER BY ptimestamp DESC RANGE BETWEEN '12 hours' PRECEDING AND '6 hours' FOLLOWING) FROM products ORDER BY group_name, ptimestamp DESC 2790 ---- 2791 Laptop Lenovo Thinkpad 2018-07-31 12:34:56 +0000 +0000 700.00 700.00 2792 Laptop Dell 2018-07-31 07:34:56 +0000 +0000 800.00 700.00 2793 Laptop Sony VAIO 2018-07-30 11:23:45 +0000 +0000 700.00 700.00 2794 Laptop HP Elite 2018-07-30 01:23:45 +0000 +0000 1200.00 700.00 2795 Smartphone HTC One 2018-07-31 12:34:56 +0000 +0000 400.00 400.00 2796 Smartphone iPhone 2018-07-31 07:34:56 +0000 +0000 900.00 400.00 2797 Smartphone Nexus 2018-07-30 11:23:45 +0000 +0000 500.00 500.00 2798 Smartphone Microsoft Lumia 2018-07-30 01:23:45 +0000 +0000 200.00 500.00 2799 Tablet Kindle Fire 2018-07-31 12:34:56 +0000 +0000 150.00 150.00 2800 Tablet Samsung 2018-07-30 11:23:45 +0000 +0000 200.00 200.00 2801 Tablet iPad 2018-07-30 01:23:45 +0000 +0000 700.00 200.00 2802 2803 query TTTRR 2804 SELECT group_name, product_name, ptimestamptz, price, avg(price) OVER (PARTITION BY group_name ORDER BY ptimestamptz DESC RANGE BETWEEN '1 days 12 hours' PRECEDING AND CURRENT ROW) FROM products ORDER BY group_name, ptimestamptz DESC 2805 ---- 2806 Laptop Lenovo Thinkpad 2018-07-31 12:34:56 +0000 UTC 700.00 700.00 2807 Laptop Dell 2018-07-31 07:34:56 +0000 UTC 800.00 750.00 2808 Laptop Sony VAIO 2018-07-30 11:23:45 +0000 UTC 700.00 733.33333333333333333 2809 Laptop HP Elite 2018-07-30 01:23:45 +0000 UTC 1200.00 850.00 2810 Smartphone HTC One 2018-07-31 12:34:56 +0000 UTC 400.00 400.00 2811 Smartphone iPhone 2018-07-31 07:34:56 +0000 UTC 900.00 650.00 2812 Smartphone Nexus 2018-07-30 11:23:45 +0000 UTC 500.00 600.00 2813 Smartphone Microsoft Lumia 2018-07-30 01:23:45 +0000 UTC 200.00 500.00 2814 Tablet Kindle Fire 2018-07-31 12:34:56 +0000 UTC 150.00 150.00 2815 Tablet Samsung 2018-07-30 11:23:45 +0000 UTC 200.00 175.00 2816 Tablet iPad 2018-07-30 01:23:45 +0000 UTC 700.00 350.00 2817 2818 query TTRR 2819 SELECT product_name, pinterval, price, avg(price) OVER (ORDER BY pinterval DESC RANGE BETWEEN '2 hours 34 minutes 56 seconds' PRECEDING AND '3 months' FOLLOWING) FROM products ORDER BY pinterval DESC, group_id 2820 ---- 2821 Microsoft Lumia 1 mon 2 days 03:04:05 200.00 586.36363636363636364 2822 HP Elite 1 mon 2 days 03:04:05 1200.00 586.36363636363636364 2823 iPad 1 mon 2 days 03:04:05 700.00 586.36363636363636364 2824 HTC One 1 day 02:03:04 400.00 543.75 2825 Lenovo Thinkpad 1 day 02:03:04 700.00 543.75 2826 Kindle Fire 1 day 02:03:04 150.00 543.75 2827 Nexus 01:02:03 500.00 620.00 2828 Sony VAIO 01:02:03 700.00 620.00 2829 Samsung 01:02:03 200.00 620.00 2830 iPhone 00:01:02 900.00 620.00 2831 Dell 00:01:02 800.00 620.00 2832 2833 query TRTT 2834 SELECT group_name, price, product_name, array_agg(product_name) OVER (PARTITION BY group_name ORDER BY price, group_id) FROM products ORDER BY group_id 2835 ---- 2836 Smartphone 200.00 Microsoft Lumia {"Microsoft Lumia"} 2837 Smartphone 400.00 HTC One {"Microsoft Lumia","HTC One"} 2838 Smartphone 500.00 Nexus {"Microsoft Lumia","HTC One",Nexus} 2839 Smartphone 900.00 iPhone {"Microsoft Lumia","HTC One",Nexus,iPhone} 2840 Laptop 1200.00 HP Elite {"Lenovo Thinkpad","Sony VAIO",Dell,"HP Elite"} 2841 Laptop 700.00 Lenovo Thinkpad {"Lenovo Thinkpad"} 2842 Laptop 700.00 Sony VAIO {"Lenovo Thinkpad","Sony VAIO"} 2843 Laptop 800.00 Dell {"Lenovo Thinkpad","Sony VAIO",Dell} 2844 Tablet 700.00 iPad {"Kindle Fire",Samsung,iPad} 2845 Tablet 150.00 Kindle Fire {"Kindle Fire"} 2846 Tablet 200.00 Samsung {"Kindle Fire",Samsung} 2847 2848 query TT 2849 SELECT product_name, array_agg(product_name) OVER (ORDER BY group_id) FROM products ORDER BY group_id 2850 ---- 2851 Microsoft Lumia {"Microsoft Lumia"} 2852 HTC One {"Microsoft Lumia","HTC One"} 2853 Nexus {"Microsoft Lumia","HTC One",Nexus} 2854 iPhone {"Microsoft Lumia","HTC One",Nexus,iPhone} 2855 HP Elite {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite"} 2856 Lenovo Thinkpad {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad"} 2857 Sony VAIO {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO"} 2858 Dell {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO",Dell} 2859 iPad {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO",Dell,iPad} 2860 Kindle Fire {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO",Dell,iPad,"Kindle Fire"} 2861 Samsung {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO",Dell,iPad,"Kindle Fire",Samsung} 2862 2863 statement error GROUPS mode requires an ORDER BY clause 2864 SELECT avg(price) OVER (GROUPS group_id PRECEDING) FROM products 2865 2866 statement error GROUPS mode requires an ORDER BY clause 2867 SELECT avg(price) OVER (GROUPS 1 PRECEDING) FROM products 2868 2869 statement error frame starting offset must not be null 2870 SELECT avg(price) OVER (ORDER BY group_id GROUPS NULL PRECEDING) FROM products 2871 2872 statement error frame starting offset must not be null 2873 SELECT avg(price) OVER (ORDER BY group_id GROUPS BETWEEN NULL PRECEDING AND 1 FOLLOWING) FROM products 2874 2875 statement error frame starting offset must not be negative 2876 SELECT price, avg(price) OVER (PARTITION BY price ORDER BY group_id GROUPS -1 PRECEDING) AS avg_price FROM products 2877 2878 statement error frame starting offset must not be negative 2879 SELECT price, avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY price ORDER BY group_id GROUPS -1 PRECEDING) 2880 2881 statement error frame ending offset must not be null 2882 SELECT avg(price) OVER (ORDER BY group_id GROUPS BETWEEN 1 PRECEDING AND NULL FOLLOWING) FROM products 2883 2884 statement error frame ending offset must not be negative 2885 SELECT price, avg(price) OVER (PARTITION BY price ORDER BY group_id GROUPS BETWEEN 1 FOLLOWING AND -1 FOLLOWING) AS avg_price FROM products 2886 2887 statement error frame ending offset must not be negative 2888 SELECT price, avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY price ORDER BY group_id GROUPS BETWEEN 1 FOLLOWING AND -1 FOLLOWING) 2889 2890 statement error frame ending offset must not be negative 2891 SELECT product_name, price, min(price) OVER (PARTITION BY group_name ORDER BY group_id GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS min_over_three, max(price) OVER (PARTITION BY group_name ORDER BY group_id GROUPS BETWEEN UNBOUNDED PRECEDING AND -1 FOLLOWING) AS max_over_partition FROM products ORDER BY group_id 2892 2893 statement error argument of WINDOW FRAME START must be type int, not type decimal 2894 SELECT avg(price) OVER (PARTITION BY group_name ORDER BY group_id GROUPS 1.5 PRECEDING) AS avg_price FROM products 2895 2896 statement error argument of WINDOW FRAME START must be type int, not type decimal 2897 SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ORDER BY group_id GROUPS 1.5 PRECEDING) 2898 2899 statement error argument of WINDOW FRAME START must be type int, not type decimal 2900 SELECT avg(price) OVER (PARTITION BY group_name ORDER BY group_id GROUPS BETWEEN 1.5 PRECEDING AND UNBOUNDED FOLLOWING) AS avg_price FROM products 2901 2902 statement error argument of WINDOW FRAME START must be type int, not type decimal 2903 SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ORDER BY group_id GROUPS BETWEEN 1.5 PRECEDING AND UNBOUNDED FOLLOWING) 2904 2905 statement error argument of WINDOW FRAME END must be type int, not type decimal 2906 SELECT avg(price) OVER (PARTITION BY group_name ORDER BY group_id GROUPS BETWEEN UNBOUNDED PRECEDING AND 1.5 FOLLOWING) AS avg_price FROM products 2907 2908 statement error argument of WINDOW FRAME END must be type int, not type decimal 2909 SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ORDER BY group_id GROUPS BETWEEN UNBOUNDED PRECEDING AND 1.5 FOLLOWING) 2910 2911 query RRRRR 2912 SELECT price, sum(price) OVER (ORDER BY price GROUPS UNBOUNDED PRECEDING), sum(price) OVER (ORDER BY price GROUPS 100 PRECEDING), sum(price) OVER (ORDER BY price GROUPS 1 PRECEDING), sum(price) OVER (ORDER BY group_name GROUPS CURRENT ROW) FROM products ORDER BY price, group_id 2913 ---- 2914 150.00 150.00 150.00 150.00 1050.00 2915 200.00 550.00 550.00 550.00 2000.00 2916 200.00 550.00 550.00 550.00 1050.00 2917 400.00 950.00 950.00 800.00 2000.00 2918 500.00 1450.00 1450.00 900.00 2000.00 2919 700.00 3550.00 3550.00 2600.00 3400.00 2920 700.00 3550.00 3550.00 2600.00 3400.00 2921 700.00 3550.00 3550.00 2600.00 1050.00 2922 800.00 4350.00 4350.00 2900.00 3400.00 2923 900.00 5250.00 5250.00 1700.00 2000.00 2924 1200.00 6450.00 6450.00 2100.00 3400.00 2925 2926 query RIRRRRRR 2927 SELECT price, dense_rank() OVER w, avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND 100 PRECEDING), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND 100 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (ORDER BY price) ORDER BY price 2928 ---- 2929 150.00 1 NULL NULL 150.00 237.50 586.36363636363636364 586.36363636363636364 2930 200.00 2 NULL NULL 183.33333333333333333 290.00 586.36363636363636364 586.36363636363636364 2931 200.00 2 NULL NULL 183.33333333333333333 290.00 586.36363636363636364 586.36363636363636364 2932 400.00 3 NULL 150.00 237.50 443.75 586.36363636363636364 586.36363636363636364 2933 500.00 4 NULL 183.33333333333333333 290.00 483.33333333333333333 586.36363636363636364 586.36363636363636364 2934 700.00 5 NULL 237.50 443.75 525.00 586.36363636363636364 586.36363636363636364 2935 700.00 5 NULL 237.50 443.75 525.00 586.36363636363636364 586.36363636363636364 2936 700.00 5 NULL 237.50 443.75 525.00 586.36363636363636364 586.36363636363636364 2937 800.00 6 NULL 290.00 483.33333333333333333 586.36363636363636364 586.36363636363636364 586.36363636363636364 2938 900.00 7 NULL 443.75 525.00 586.36363636363636364 586.36363636363636364 586.36363636363636364 2939 1200.00 8 NULL 483.33333333333333333 586.36363636363636364 586.36363636363636364 586.36363636363636364 586.36363636363636364 2940 2941 query RIRRRRRR 2942 SELECT price, dense_rank() OVER w, avg(price) OVER (w GROUPS BETWEEN 4 PRECEDING AND 100 PRECEDING), avg(price) OVER (w GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING), avg(price) OVER (w GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW), avg(price) OVER (w GROUPS BETWEEN 1 PRECEDING AND 2 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 1 PRECEDING AND 100 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (ORDER BY price) ORDER BY price 2943 ---- 2944 150.00 1 NULL NULL 150.00 237.50 586.36363636363636364 586.36363636363636364 2945 200.00 2 NULL NULL 183.33333333333333333 290.00 586.36363636363636364 586.36363636363636364 2946 200.00 2 NULL NULL 183.33333333333333333 290.00 586.36363636363636364 586.36363636363636364 2947 400.00 3 NULL 150.00 237.50 485.71428571428571429 630.00 630.00 2948 500.00 4 NULL 183.33333333333333333 325.00 633.33333333333333333 737.50 737.50 2949 700.00 5 NULL 266.66666666666666667 600.00 716.66666666666666667 785.71428571428571429 785.71428571428571429 2950 700.00 5 NULL 266.66666666666666667 600.00 716.66666666666666667 785.71428571428571429 785.71428571428571429 2951 700.00 5 NULL 266.66666666666666667 600.00 716.66666666666666667 785.71428571428571429 785.71428571428571429 2952 800.00 6 NULL 450.00 680.00 833.33333333333333333 833.33333333333333333 833.33333333333333333 2953 900.00 7 NULL 650.00 760.00 966.66666666666666667 966.66666666666666667 966.66666666666666667 2954 1200.00 8 NULL 725.00 966.66666666666666667 1050.00 1050.00 1050.00 2955 2956 query RIRRRRRRR 2957 SELECT price, dense_rank() OVER w, avg(price) OVER (w GROUPS BETWEEN 0 PRECEDING AND 0 PRECEDING), avg(price) OVER (w GROUPS BETWEEN 0 PRECEDING AND CURRENT ROW), avg(price) OVER (w GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN CURRENT ROW AND CURRENT ROW), avg(price) OVER (w GROUPS BETWEEN CURRENT ROW AND 2 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (ORDER BY price) ORDER BY price 2958 ---- 2959 150.00 1 150.00 150.00 150.00 150.00 237.50 586.36363636363636364 586.36363636363636364 2960 200.00 2 200.00 200.00 200.00 200.00 325.00 630.00 630.00 2961 200.00 2 200.00 200.00 200.00 200.00 325.00 630.00 630.00 2962 400.00 3 400.00 400.00 400.00 400.00 600.00 737.50 737.50 2963 500.00 4 500.00 500.00 500.00 500.00 680.00 785.71428571428571429 785.71428571428571429 2964 700.00 5 700.00 700.00 700.00 700.00 760.00 833.33333333333333333 833.33333333333333333 2965 700.00 5 700.00 700.00 700.00 700.00 760.00 833.33333333333333333 833.33333333333333333 2966 700.00 5 700.00 700.00 700.00 700.00 760.00 833.33333333333333333 833.33333333333333333 2967 800.00 6 800.00 800.00 800.00 800.00 966.66666666666666667 966.66666666666666667 966.66666666666666667 2968 900.00 7 900.00 900.00 900.00 900.00 1050.00 1050.00 1050.00 2969 1200.00 8 1200.00 1200.00 1200.00 1200.00 1200.00 1200.00 1200.00 2970 2971 query RIRRRRRR 2972 SELECT price, dense_rank() OVER w, avg(price) OVER (w GROUPS BETWEEN 3 FOLLOWING AND 100 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 3 FOLLOWING AND 1 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 2 FOLLOWING AND 6 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 3 FOLLOWING AND 3 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 0 FOLLOWING AND 4 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 5 FOLLOWING AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (ORDER BY price) ORDER BY price 2973 ---- 2974 150.00 1 785.71428571428571429 NULL 671.42857142857142857 500.00 443.75 966.66666666666666667 2975 200.00 2 833.33333333333333333 NULL 785.71428571428571429 700.00 525.00 1050.00 2976 200.00 2 833.33333333333333333 NULL 785.71428571428571429 700.00 525.00 1050.00 2977 400.00 3 966.66666666666666667 NULL 833.33333333333333333 800.00 671.42857142857142857 1200.00 2978 500.00 4 1050.00 NULL 966.66666666666666667 900.00 785.71428571428571429 NULL 2979 700.00 5 1200.00 NULL 1050.00 1200.00 833.33333333333333333 NULL 2980 700.00 5 1200.00 NULL 1050.00 1200.00 833.33333333333333333 NULL 2981 700.00 5 1200.00 NULL 1050.00 1200.00 833.33333333333333333 NULL 2982 800.00 6 NULL NULL 1200.00 NULL 966.66666666666666667 NULL 2983 900.00 7 NULL NULL NULL NULL 1050.00 NULL 2984 1200.00 8 NULL NULL NULL NULL 1200.00 NULL 2985 2986 query TTRRR 2987 SELECT group_name, product_name, price, avg(price) OVER (PARTITION BY group_name ORDER BY price GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING), avg(price) OVER (ORDER BY price GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM products ORDER BY group_id 2988 ---- 2989 Smartphone Microsoft Lumia 200.00 500.00 586.36363636363636364 2990 Smartphone HTC One 400.00 600.00 586.36363636363636364 2991 Smartphone Nexus 500.00 700.00 586.36363636363636364 2992 Smartphone iPhone 900.00 900.00 586.36363636363636364 2993 Laptop HP Elite 1200.00 1200.00 586.36363636363636364 2994 Laptop Lenovo Thinkpad 700.00 850.00 586.36363636363636364 2995 Laptop Sony VAIO 700.00 850.00 586.36363636363636364 2996 Laptop Dell 800.00 1000.00 586.36363636363636364 2997 Tablet iPad 700.00 700.00 586.36363636363636364 2998 Tablet Kindle Fire 150.00 350.00 586.36363636363636364 2999 Tablet Samsung 200.00 450.00 586.36363636363636364 3000 3001 query TTRRR 3002 SELECT group_name, product_name, price, avg(price) OVER (ORDER BY group_id GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING), avg(price) OVER (ORDER BY price GROUPS BETWEEN CURRENT ROW AND CURRENT ROW) FROM products ORDER BY group_id 3003 ---- 3004 Smartphone Microsoft Lumia 200.00 NULL 200.00 3005 Smartphone HTC One 400.00 NULL 400.00 3006 Smartphone Nexus 500.00 NULL 500.00 3007 Smartphone iPhone 900.00 NULL 900.00 3008 Laptop HP Elite 1200.00 NULL 1200.00 3009 Laptop Lenovo Thinkpad 700.00 NULL 700.00 3010 Laptop Sony VAIO 700.00 NULL 700.00 3011 Laptop Dell 800.00 NULL 800.00 3012 Tablet iPad 700.00 NULL 700.00 3013 Tablet Kindle Fire 150.00 NULL 150.00 3014 Tablet Samsung 200.00 NULL 200.00 3015 3016 query RTR 3017 SELECT 3018 price, array_agg(price) OVER w, sum(price) OVER w 3019 FROM 3020 products 3021 WINDOW 3022 w AS ( 3023 ORDER BY 3024 price 3025 RANGE 3026 UNBOUNDED PRECEDING EXCLUDE CURRENT ROW 3027 ) 3028 ORDER BY 3029 price 3030 ---- 3031 150.00 NULL NULL 3032 200.00 {150.00,200.00} 350.00 3033 200.00 {150.00,200.00} 350.00 3034 400.00 {150.00,200.00,200.00} 550.00 3035 500.00 {150.00,200.00,200.00,400.00} 950.00 3036 700.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00} 2850.00 3037 700.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00} 2850.00 3038 700.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00} 2850.00 3039 800.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00} 3550.00 3040 900.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00} 4350.00 3041 1200.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00,900.00} 5250.00 3042 3043 query RTR 3044 SELECT 3045 price, array_agg(price) OVER w, max(price) OVER w 3046 FROM 3047 products 3048 WINDOW 3049 w AS ( 3050 ORDER BY 3051 price 3052 RANGE 3053 UNBOUNDED PRECEDING EXCLUDE GROUP 3054 ) 3055 ORDER BY 3056 price 3057 ---- 3058 150.00 NULL NULL 3059 200.00 {150.00} 150.00 3060 200.00 {150.00} 150.00 3061 400.00 {150.00,200.00,200.00} 200.00 3062 500.00 {150.00,200.00,200.00,400.00} 400.00 3063 700.00 {150.00,200.00,200.00,400.00,500.00} 500.00 3064 700.00 {150.00,200.00,200.00,400.00,500.00} 500.00 3065 700.00 {150.00,200.00,200.00,400.00,500.00} 500.00 3066 800.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00} 700.00 3067 900.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00} 800.00 3068 1200.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00,900.00} 900.00 3069 3070 query RTR 3071 SELECT 3072 price, array_agg(price) OVER w, avg(price) OVER w 3073 FROM 3074 products 3075 WINDOW 3076 w AS ( 3077 ORDER BY 3078 price 3079 RANGE 3080 UNBOUNDED PRECEDING EXCLUDE TIES 3081 ) 3082 ORDER BY 3083 price 3084 ---- 3085 150.00 {150.00} 150.00 3086 200.00 {150.00,200.00} 175.00 3087 200.00 {150.00,200.00} 175.00 3088 400.00 {150.00,200.00,200.00,400.00} 237.50 3089 500.00 {150.00,200.00,200.00,400.00,500.00} 290.00 3090 700.00 {150.00,200.00,200.00,400.00,500.00,700.00} 358.33333333333333333 3091 700.00 {150.00,200.00,200.00,400.00,500.00,700.00} 358.33333333333333333 3092 700.00 {150.00,200.00,200.00,400.00,500.00,700.00} 358.33333333333333333 3093 800.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00} 483.33333333333333333 3094 900.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00,900.00} 525.00 3095 1200.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00,900.00,1200.00} 586.36363636363636364 3096 3097 query RTR 3098 SELECT 3099 price, array_agg(price) OVER w, avg(price) OVER w 3100 FROM 3101 products 3102 WINDOW 3103 w AS ( 3104 ORDER BY 3105 price 3106 RANGE 3107 UNBOUNDED PRECEDING EXCLUDE NO OTHERS 3108 ) 3109 ORDER BY 3110 price 3111 ---- 3112 150.00 {150.00} 150.00 3113 200.00 {150.00,200.00,200.00} 183.33333333333333333 3114 200.00 {150.00,200.00,200.00} 183.33333333333333333 3115 400.00 {150.00,200.00,200.00,400.00} 237.50 3116 500.00 {150.00,200.00,200.00,400.00,500.00} 290.00 3117 700.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00} 443.75 3118 700.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00} 443.75 3119 700.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00} 443.75 3120 800.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00} 483.33333333333333333 3121 900.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00,900.00} 525.00 3122 1200.00 {150.00,200.00,200.00,400.00,500.00,700.00,700.00,700.00,800.00,900.00,1200.00} 586.36363636363636364 3123 3124 query TTTT 3125 SELECT 3126 first_value(product_name) OVER ( 3127 w RANGE UNBOUNDED PRECEDING EXCLUDE CURRENT ROW 3128 ), 3129 first_value(product_name) OVER ( 3130 w RANGE UNBOUNDED PRECEDING EXCLUDE GROUP 3131 ), 3132 first_value(product_name) OVER ( 3133 w RANGE UNBOUNDED PRECEDING EXCLUDE TIES 3134 ), 3135 first_value(product_name) OVER ( 3136 w RANGE UNBOUNDED PRECEDING EXCLUDE NO OTHERS 3137 ) 3138 FROM 3139 products 3140 WINDOW 3141 w AS (ORDER BY group_id) 3142 ORDER BY 3143 group_id 3144 ---- 3145 NULL NULL Microsoft Lumia Microsoft Lumia 3146 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3147 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3148 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3149 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3150 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3151 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3152 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3153 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3154 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3155 Microsoft Lumia Microsoft Lumia Microsoft Lumia Microsoft Lumia 3156 3157 query TTTT 3158 SELECT 3159 last_value(product_name) OVER ( 3160 w RANGE UNBOUNDED PRECEDING EXCLUDE CURRENT ROW 3161 ), 3162 last_value(product_name) OVER ( 3163 w RANGE UNBOUNDED PRECEDING EXCLUDE GROUP 3164 ), 3165 last_value(product_name) OVER ( 3166 w RANGE UNBOUNDED PRECEDING EXCLUDE TIES 3167 ), 3168 last_value(product_name) OVER ( 3169 w RANGE UNBOUNDED PRECEDING EXCLUDE NO OTHERS 3170 ) 3171 FROM 3172 products 3173 WINDOW 3174 w AS (ORDER BY group_id) 3175 ORDER BY 3176 group_id 3177 ---- 3178 NULL NULL Microsoft Lumia Microsoft Lumia 3179 Microsoft Lumia Microsoft Lumia HTC One HTC One 3180 HTC One HTC One Nexus Nexus 3181 Nexus Nexus iPhone iPhone 3182 iPhone iPhone HP Elite HP Elite 3183 HP Elite HP Elite Lenovo Thinkpad Lenovo Thinkpad 3184 Lenovo Thinkpad Lenovo Thinkpad Sony VAIO Sony VAIO 3185 Sony VAIO Sony VAIO Dell Dell 3186 Dell Dell iPad iPad 3187 iPad iPad Kindle Fire Kindle Fire 3188 Kindle Fire Kindle Fire Samsung Samsung 3189 3190 query TTTT 3191 SELECT 3192 nth_value(product_name, 2) OVER ( 3193 w RANGE UNBOUNDED PRECEDING EXCLUDE CURRENT ROW 3194 ), 3195 nth_value(product_name, 3) OVER ( 3196 w RANGE UNBOUNDED PRECEDING EXCLUDE GROUP 3197 ), 3198 nth_value(product_name, 4) OVER ( 3199 w RANGE UNBOUNDED PRECEDING EXCLUDE TIES 3200 ), 3201 nth_value(product_name, 5) OVER ( 3202 w RANGE UNBOUNDED PRECEDING EXCLUDE NO OTHERS 3203 ) 3204 FROM 3205 products 3206 WINDOW 3207 w AS (ORDER BY group_id) 3208 ORDER BY 3209 group_id 3210 ---- 3211 NULL NULL NULL NULL 3212 NULL NULL NULL NULL 3213 HTC One NULL NULL NULL 3214 HTC One Nexus iPhone NULL 3215 HTC One Nexus iPhone HP Elite 3216 HTC One Nexus iPhone HP Elite 3217 HTC One Nexus iPhone HP Elite 3218 HTC One Nexus iPhone HP Elite 3219 HTC One Nexus iPhone HP Elite 3220 HTC One Nexus iPhone HP Elite 3221 HTC One Nexus iPhone HP Elite 3222 3223 # Test for #32702 3224 3225 statement ok 3226 CREATE TABLE x (a INT) 3227 3228 statement ok 3229 INSERT INTO x VALUES (1), (2), (3) 3230 3231 query IT 3232 SELECT a, json_agg(a) OVER (ORDER BY a) FROM x ORDER BY a 3233 ---- 3234 1 [1] 3235 2 [1, 2] 3236 3 [1, 2, 3] 3237 3238 # Test for #35267 3239 query I 3240 SELECT 3241 row_number() OVER (PARTITION BY s) 3242 FROM 3243 (SELECT sum(a) AS s FROM (SELECT a FROM x UNION ALL SELECT a FROM x) GROUP BY a) 3244 ---- 3245 1 3246 1 3247 1 3248 3249 # Tests for #32050 3250 3251 statement error window function calls cannot be nested 3252 SELECT sum(a) OVER (PARTITION BY count(a) OVER ()) FROM x 3253 3254 statement error window function calls cannot be nested 3255 SELECT sum(a) OVER (ORDER BY count(a) OVER ()) FROM x 3256 3257 statement error window function calls cannot be nested 3258 SELECT sum(a) OVER (PARTITION BY count(a) OVER () + 1) FROM x 3259 3260 statement error window function calls cannot be nested 3261 SELECT sum(a) OVER (ORDER BY count(a) OVER () + 1) FROM x 3262 3263 # TODO(justin): blocked by #37134. 3264 # statement error more than one row returned by a subquery used as an expression 3265 # SELECT sum(a) OVER (PARTITION BY (SELECT count(*) FROM x GROUP BY a)) FROM x 3266 3267 query I 3268 SELECT sum(a) OVER (PARTITION BY (SELECT count(*) FROM x GROUP BY a LIMIT 1))::INT FROM x 3269 ---- 3270 6 3271 6 3272 6 3273 3274 # Regression test for #27293 - make sure comparing two tuple types when 3275 # generating window functions expressions doesn't panic. 3276 3277 query II 3278 SELECT 3279 min(a) OVER (PARTITION BY (a, a)) AS min, 3280 max(a) OVER (PARTITION BY (a, a)) AS max 3281 FROM 3282 (SELECT 1 AS a) 3283 ---- 3284 1 1 3285 3286 query II 3287 SELECT 3288 min(a) OVER (PARTITION BY (())) AS min, 3289 max(a) OVER (PARTITION BY (())) AS max 3290 FROM 3291 (SELECT 1 AS a) 3292 ---- 3293 1 1 3294 3295 query T 3296 SELECT string_agg('foo', s) OVER () FROM (SELECT * FROM kv LIMIT 1) 3297 ---- 3298 foo 3299 3300 # Regression test for #37201. 3301 query I 3302 SELECT jsonb_agg(a) OVER (ORDER BY a GROUPS BETWEEN 5 FOLLOWING AND UNBOUNDED FOLLOWING) FROM x 3303 ---- 3304 NULL 3305 NULL 3306 NULL 3307 3308 statement ok 3309 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT) 3310 3311 statement ok 3312 INSERT INTO abc VALUES 3313 (1, 10, 20), 3314 (2, 10, 20), 3315 (3, 10, 20), 3316 (4, 10, 30), 3317 (5, 10, 30), 3318 (6, 10, 30) 3319 3320 query TTTTTTTTTTTT rowsort 3321 SELECT 3322 avg(a) OVER (), 3323 avg(a) OVER (ORDER BY a), 3324 avg(a) OVER (ORDER BY b), 3325 avg(a) OVER (ORDER BY c), 3326 avg(b) OVER (), 3327 avg(b) OVER (ORDER BY a), 3328 avg(b) OVER (ORDER BY b), 3329 avg(b) OVER (ORDER BY c), 3330 avg(c) OVER (), 3331 avg(c) OVER (ORDER BY a), 3332 avg(c) OVER (ORDER BY b), 3333 avg(c) OVER (ORDER BY c) 3334 FROM abc 3335 ---- 3336 3.5 1 3.5 2 10 10 10 10 25 20 25 20 3337 3.5 1.5 3.5 2 10 10 10 10 25 20 25 20 3338 3.5 2 3.5 2 10 10 10 10 25 20 25 20 3339 3.5 2.5 3.5 3.5 10 10 10 10 25 22.5 25 25 3340 3.5 3 3.5 3.5 10 10 10 10 25 24 25 25 3341 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3342 3343 query TTTTTTTTTTTT rowsort 3344 SELECT 3345 avg(a) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3346 avg(a) OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3347 avg(a) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3348 avg(a) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3349 avg(b) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3350 avg(b) OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3351 avg(b) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3352 avg(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3353 avg(c) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3354 avg(c) OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3355 avg(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 3356 avg(c) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 3357 FROM abc 3358 ---- 3359 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3360 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3361 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3362 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3363 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3364 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3365 3366 query T 3367 SELECT array_agg(a) OVER (w RANGE 1 PRECEDING) FROM x WINDOW w AS (ORDER BY a DESC) ORDER BY a 3368 ---- 3369 {2,1} 3370 {3,2} 3371 {3} 3372 3373 statement error GROUPS mode requires an ORDER BY clause 3374 SELECT array_agg(a) OVER (w GROUPS 1 PRECEDING) FROM x WINDOW w AS (PARTITION BY a) 3375 3376 query T 3377 SELECT array_agg(a) OVER (w GROUPS 1 PRECEDING) FROM x WINDOW w AS (PARTITION BY a ORDER BY a DESC) ORDER BY a 3378 ---- 3379 {1} 3380 {2} 3381 {3} 3382 3383 # Regression tests for #38103 3384 statement ok 3385 DROP TABLE IF EXISTS t 3386 3387 statement ok 3388 CREATE TABLE t (a INT PRIMARY KEY, b INT) 3389 3390 statement ok 3391 INSERT INTO t VALUES (1, 1), (2, NULL), (3, 3) 3392 3393 query I 3394 SELECT min(b) OVER () FROM t 3395 ---- 3396 1 3397 1 3398 1 3399 3400 query IIR 3401 SELECT a, b, sum(b) OVER (ROWS 0 PRECEDING) FROM t ORDER BY a 3402 ---- 3403 1 1 1 3404 2 NULL NULL 3405 3 3 3 3406 3407 query IIR 3408 SELECT a, b, avg(b) OVER () FROM t ORDER BY a 3409 ---- 3410 1 1 2 3411 2 NULL 2 3412 3 3 2 3413 3414 query IIR 3415 SELECT a, b, avg(b) OVER (ROWS 0 PRECEDING) FROM t ORDER BY a 3416 ---- 3417 1 1 1 3418 2 NULL NULL 3419 3 3 3 3420 3421 statement ok 3422 CREATE TABLE wxyz (w INT PRIMARY KEY, x INT, y INT, z INT) 3423 3424 statement ok 3425 INSERT INTO wxyz VALUES 3426 (1, 10, 1, 1), 3427 (2, 10, 2, 0), 3428 (3, 10, 1, 1), 3429 (4, 10, 2, 0), 3430 (5, 10, 2, 1), 3431 (6, 10, 2, 0) 3432 3433 # Cases involving interaction between limits and window functions. 3434 query IIIII rowsort 3435 SELECT *, rank() OVER (PARTITION BY z ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 3436 ---- 3437 3 10 1 1 1 3438 1 10 1 1 1 3439 3440 query IIIII rowsort 3441 SELECT *, dense_rank() OVER (PARTITION BY z ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 3442 ---- 3443 3 10 1 1 1 3444 1 10 1 1 1 3445 3446 query IIIIR rowsort 3447 SELECT *, avg(w) OVER (PARTITION BY z ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 3448 ---- 3449 3 10 1 1 2 3450 1 10 1 1 2 3451 3452 query IIIII rowsort 3453 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 3454 ---- 3455 3 10 1 1 1 3456 1 10 1 1 1 3457 3458 query IIIII rowsort 3459 SELECT *, dense_rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 3460 ---- 3461 3 10 1 1 1 3462 1 10 1 1 1 3463 3464 query IIIIR rowsort 3465 SELECT *, avg(w) OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY y LIMIT 2 3466 ---- 3467 3 10 1 1 3 3468 1 10 1 1 1 3469 3470 query IIIII rowsort 3471 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY w, y LIMIT 2 3472 ---- 3473 1 10 1 1 1 3474 2 10 2 0 1 3475 3476 query IIIII rowsort 3477 SELECT *, dense_rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY w, y LIMIT 2 3478 ---- 3479 1 10 1 1 1 3480 2 10 2 0 1 3481 3482 query IIIIR rowsort 3483 SELECT *, avg(w) OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY w, y LIMIT 2 3484 ---- 3485 1 10 1 1 1 3486 2 10 2 0 2 3487 3488 query IIIII rowsort 3489 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY w LIMIT 2 3490 ---- 3491 1 10 1 1 1 3492 2 10 2 0 1 3493 3494 query IIIII rowsort 3495 SELECT *, dense_rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY w LIMIT 2 3496 ---- 3497 1 10 1 1 1 3498 2 10 2 0 1 3499 3500 query IIIIR rowsort 3501 SELECT *, avg(w) OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY w LIMIT 2 3502 ---- 3503 1 10 1 1 1 3504 2 10 2 0 2 3505 3506 query IIIII rowsort 3507 SELECT *, rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY y, w LIMIT 2 3508 ---- 3509 1 10 1 1 1 3510 3 10 1 1 1 3511 3512 query IIIII rowsort 3513 SELECT *, dense_rank() OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY y, w LIMIT 2 3514 ---- 3515 1 10 1 1 1 3516 3 10 1 1 1 3517 3518 query IIIIR rowsort 3519 SELECT *, avg(w) OVER (PARTITION BY w ORDER BY y) FROM wxyz ORDER BY y, w LIMIT 2 3520 ---- 3521 1 10 1 1 1 3522 3 10 1 1 3 3523 3524 query IIIII rowsort 3525 SELECT *, rank() OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY w, z, y LIMIT 2 3526 ---- 3527 1 10 1 1 1 3528 2 10 2 0 1 3529 3530 query IIIII rowsort 3531 SELECT *, dense_rank() OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY w, z, y LIMIT 2 3532 ---- 3533 1 10 1 1 1 3534 2 10 2 0 1 3535 3536 query IIIIR rowsort 3537 SELECT *, avg(w) OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY w, z, y LIMIT 2 3538 ---- 3539 1 10 1 1 1 3540 2 10 2 0 2 3541 3542 query IIIII rowsort 3543 SELECT *, rank() OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY z, w, y LIMIT 2 3544 ---- 3545 2 10 2 0 1 3546 4 10 2 0 1 3547 3548 query IIIII rowsort 3549 SELECT *, dense_rank() OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY z, w, y LIMIT 2 3550 ---- 3551 2 10 2 0 1 3552 4 10 2 0 1 3553 3554 query IIIIR rowsort 3555 SELECT *, avg(w) OVER (PARTITION BY w, z ORDER BY y) FROM wxyz ORDER BY z, w, y LIMIT 2 3556 ---- 3557 2 10 2 0 2 3558 4 10 2 0 4 3559 3560 # Test that windower respects the memory limit set via the cluster setting. 3561 statement ok 3562 SET CLUSTER SETTING sql.distsql.temp_storage.workmem='200KB' 3563 3564 statement ok 3565 CREATE TABLE l (a INT PRIMARY KEY) 3566 3567 statement ok 3568 INSERT INTO l SELECT g FROM generate_series(0,10000) g(g) 3569 3570 statement error memory budget exceeded 3571 SELECT array_agg(a) OVER () FROM l LIMIT 1 3572 3573 statement ok 3574 RESET CLUSTER SETTING sql.distsql.temp_storage.workmem 3575 3576 # Regression test for #38901 verifying that window frame takes precedence over 3577 # the concept of peers. 3578 query I 3579 SELECT count(a) OVER (ROWS 1 PRECEDING) FROM t 3580 ---- 3581 1 3582 2 3583 2 3584 3585 statement ok 3586 CREATE TABLE t38901 (a INT PRIMARY KEY); INSERT INTO t38901 VALUES (1), (2), (3) 3587 3588 query T 3589 SELECT array_agg(a) OVER (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM t38901 ORDER BY a 3590 ---- 3591 NULL 3592 {1} 3593 {1,2} 3594 3595 # Regression test for #42935. 3596 query IIIII 3597 SELECT 3598 a, 3599 b, 3600 count(*) OVER (ORDER BY b), 3601 count(*) OVER ( 3602 ORDER BY 3603 b 3604 RANGE 3605 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 3606 ), 3607 count(*) OVER ( 3608 ORDER BY 3609 b 3610 ROWS 3611 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 3612 ) 3613 FROM 3614 (VALUES (1, 1), (2, 1), (3, 2), (4, 2)) AS t (a, b) 3615 ORDER BY 3616 a, b 3617 ---- 3618 1 1 2 2 1 3619 2 1 2 2 2 3620 3 2 4 4 3 3621 4 2 4 4 4 3622 3623 query IIIRRTBTTTTTTTTT 3624 SELECT 3625 *, 3626 array_agg(v) OVER (wv RANGE BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING), 3627 array_agg(v) OVER (wv RANGE BETWEEN 0 PRECEDING AND 1 PRECEDING), 3628 array_agg(f) OVER (wf RANGE BETWEEN UNBOUNDED PRECEDING AND -0.0 PRECEDING), 3629 array_agg(f) OVER (wf RANGE BETWEEN 0.0 PRECEDING AND 1.0 PRECEDING), 3630 array_agg(d) OVER (wd RANGE BETWEEN 0.0 FOLLOWING AND 0.0 FOLLOWING), 3631 array_agg(d) OVER (wd RANGE BETWEEN 1.0 FOLLOWING AND UNBOUNDED FOLLOWING), 3632 array_agg(i) OVER (wi RANGE BETWEEN '1s'::INTERVAL PRECEDING AND '0s'::INTERVAL PRECEDING), 3633 array_agg(i) OVER (wi RANGE BETWEEN '1s'::INTERVAL FOLLOWING AND '1s'::INTERVAL FOLLOWING) 3634 FROM 3635 kv 3636 WINDOW 3637 wv AS (ORDER BY v DESC), 3638 wf AS (ORDER BY f), 3639 wd AS (ORDER BY d DESC), 3640 wi AS (ORDER BY i) 3641 ORDER BY 3642 k 3643 ---- 3644 1 2 3 1 1 a true 00:01:00 {4,4,4,2,2,2,2} NULL {0.1,0.2,0.3,1.0} NULL {1} {-321,NULL,NULL,NULL} {00:01:00} NULL 3645 3 4 5 2 8 a true 00:00:02 {4,4,4} NULL {0.1,0.2,0.3,1.0,2.0} NULL {8} {4.4,3,1,-321,NULL,NULL,NULL} {00:00:02} NULL 3646 5 NULL 5 9.9 -321 NULL false NULL {4,4,4,2,2,2,2,NULL,NULL} {NULL,NULL} {0.1,0.2,0.3,1.0,2.0,3.0,4.4,6.0,9.9} NULL {-321} {NULL,NULL,NULL} {NULL,NULL,NULL,NULL} {NULL,NULL,NULL,NULL} 3647 6 2 3 4.4 4.4 b true 00:00:00.001 {4,4,4,2,2,2,2} NULL {0.1,0.2,0.3,1.0,2.0,3.0,4.4} NULL {4.4} {3,1,-321,NULL,NULL,NULL} {00:00:00.001} NULL 3648 7 2 2 6 7.9 b true 4 days {4,4,4,2,2,2,2} NULL {0.1,0.2,0.3,1.0,2.0,3.0,4.4,6.0} NULL {7.9} {4.4,3,1,-321,NULL,NULL,NULL} {"4 days"} NULL 3649 8 4 2 3 3 A false 3 years {4,4,4} NULL {0.1,0.2,0.3,1.0,2.0,3.0} NULL {3} {1,-321,NULL,NULL,NULL} {"3 years"} NULL 3650 9 2 9 0.1 NULL NULL NULL NULL {4,4,4,2,2,2,2} NULL {0.1} NULL {NULL,NULL,NULL} {NULL,NULL,NULL} {NULL,NULL,NULL,NULL} {NULL,NULL,NULL,NULL} 3651 10 4 9 0.2 NULL NULL NULL NULL {4,4,4} NULL {0.1,0.2} NULL {NULL,NULL,NULL} {NULL,NULL,NULL} {NULL,NULL,NULL,NULL} {NULL,NULL,NULL,NULL} 3652 11 NULL 9 0.3 NULL NULL NULL NULL {4,4,4,2,2,2,2,NULL,NULL} {NULL,NULL} {0.1,0.2,0.3} NULL {NULL,NULL,NULL} {NULL,NULL,NULL} {NULL,NULL,NULL,NULL} {NULL,NULL,NULL,NULL} 3653 3654 # Regression test for #43083 (CBO optimizing out the single column from ORDER 3655 # BY clause which led to a crash in the execution engine). 3656 query IIIRRTBTTTTTTTTT 3657 SELECT 3658 *, 3659 array_agg(v) OVER (wv RANGE BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING), 3660 array_agg(v) OVER (wv RANGE BETWEEN 0 PRECEDING AND 1 PRECEDING), 3661 array_agg(f) OVER (wf RANGE BETWEEN UNBOUNDED PRECEDING AND -0.0 PRECEDING), 3662 array_agg(f) OVER (wf RANGE BETWEEN 0.0 PRECEDING AND 1.0 PRECEDING), 3663 array_agg(d) OVER (wd RANGE BETWEEN 0.0 FOLLOWING AND 0.0 FOLLOWING), 3664 array_agg(d) OVER (wd RANGE BETWEEN 1.0 FOLLOWING AND UNBOUNDED FOLLOWING), 3665 array_agg(i) OVER (wi RANGE BETWEEN '1s'::INTERVAL PRECEDING AND '0s'::INTERVAL PRECEDING), 3666 array_agg(i) OVER (wi RANGE BETWEEN '1s'::INTERVAL FOLLOWING AND '1s'::INTERVAL FOLLOWING) 3667 FROM 3668 kv 3669 WINDOW 3670 wv AS (PARTITION BY v ORDER BY v), 3671 wf AS (PARTITION BY f ORDER BY f DESC), 3672 wd AS (PARTITION BY d ORDER BY d), 3673 wi AS (PARTITION BY i ORDER BY i DESC) 3674 ORDER BY 3675 k 3676 ---- 3677 1 2 3 1 1 a true 00:01:00 {2,2,2,2} NULL {1.0} NULL {1} NULL {00:01:00} NULL 3678 3 4 5 2 8 a true 00:00:02 {4,4,4} NULL {2.0} NULL {8} NULL {00:00:02} NULL 3679 5 NULL 5 9.9 -321 NULL false NULL {NULL,NULL} {NULL,NULL} {9.9} NULL {-321} NULL {NULL,NULL,NULL,NULL} {NULL,NULL,NULL,NULL} 3680 6 2 3 4.4 4.4 b true 00:00:00.001 {2,2,2,2} NULL {4.4} NULL {4.4} NULL {00:00:00.001} NULL 3681 7 2 2 6 7.9 b true 4 days {2,2,2,2} NULL {6.0} NULL {7.9} NULL {"4 days"} NULL 3682 8 4 2 3 3 A false 3 years {4,4,4} NULL {3.0} NULL {3} NULL {"3 years"} NULL 3683 9 2 9 0.1 NULL NULL NULL NULL {2,2,2,2} NULL {0.1} NULL {NULL,NULL,NULL} {NULL,NULL,NULL} {NULL,NULL,NULL,NULL} {NULL,NULL,NULL,NULL} 3684 10 4 9 0.2 NULL NULL NULL NULL {4,4,4} NULL {0.2} NULL {NULL,NULL,NULL} {NULL,NULL,NULL} {NULL,NULL,NULL,NULL} {NULL,NULL,NULL,NULL} 3685 11 NULL 9 0.3 NULL NULL NULL NULL {NULL,NULL} {NULL,NULL} {0.3} NULL {NULL,NULL,NULL} {NULL,NULL,NULL} {NULL,NULL,NULL,NULL} {NULL,NULL,NULL,NULL} 3686 3687 # Check that telemetry is being collected on the window functions' usage. 3688 query B 3689 SELECT count(*) >= 26 FROM crdb_internal.feature_usage WHERE feature_name LIKE 'sql.plan.window_function%' AND usage_count > 0 3690 ---- 3691 true 3692 3693 statement ok 3694 DROP TABLE t; CREATE TABLE t (a INT, b INT, c INT PRIMARY KEY) 3695 3696 statement ok 3697 INSERT INTO t VALUES 3698 (0, 1, 0), 3699 (1, 1, 1), 3700 (0, 2, 2), 3701 (1, 2, 3) 3702 3703 # We sort the output on all queries with row_number window function to get 3704 # deterministic results. 3705 query III 3706 SELECT a, b, row_number() OVER (ORDER BY a, b) FROM t ORDER BY a, b 3707 ---- 3708 0 1 1 3709 0 2 2 3710 1 1 3 3711 1 2 4 3712 3713 query III 3714 SELECT a, b, row_number() OVER (PARTITION BY a ORDER BY b) FROM t ORDER BY a, b 3715 ---- 3716 0 1 1 3717 0 2 2 3718 1 1 1 3719 1 2 2 3720 3721 query III 3722 SELECT a, b, row_number() OVER (PARTITION BY a, b) FROM t ORDER BY a, b 3723 ---- 3724 0 1 1 3725 0 2 1 3726 1 1 1 3727 1 2 1 3728 3729 query III rowsort 3730 SELECT a, b, rank() OVER () FROM t 3731 ---- 3732 0 1 1 3733 0 2 1 3734 1 1 1 3735 1 2 1 3736 3737 query III rowsort 3738 SELECT a, b, rank() OVER (ORDER BY a) FROM t 3739 ---- 3740 0 1 1 3741 0 2 1 3742 1 1 3 3743 1 2 3 3744 3745 query IIII rowsort 3746 SELECT a, b, c, rank() OVER (PARTITION BY a ORDER BY c) FROM t 3747 ---- 3748 0 1 0 1 3749 0 2 2 2 3750 1 1 1 1 3751 1 2 3 2 3752 3753 query III rowsort 3754 SELECT a, b, dense_rank() OVER () FROM t 3755 ---- 3756 0 1 1 3757 0 2 1 3758 1 1 1 3759 1 2 1 3760 3761 query III rowsort 3762 SELECT a, b, dense_rank() OVER (ORDER BY a) FROM t 3763 ---- 3764 0 1 1 3765 0 2 1 3766 1 1 2 3767 1 2 2 3768 3769 query IIII rowsort 3770 SELECT a, b, c, dense_rank() OVER (PARTITION BY a ORDER BY c) FROM t 3771 ---- 3772 0 1 0 1 3773 0 2 2 2 3774 1 1 1 1 3775 1 2 3 2 3776 3777 query IIIIRR rowsort 3778 SELECT a, b, rank() OVER w, dense_rank() OVER w, percent_rank() OVER w, cume_dist() OVER w FROM t WINDOW w AS () 3779 ---- 3780 0 1 1 1 0 1 3781 1 1 1 1 0 1 3782 0 2 1 1 0 1 3783 1 2 1 1 0 1 3784 3785 query IIIIRR rowsort 3786 SELECT a, b, rank() OVER w, dense_rank() OVER w, percent_rank() OVER w, cume_dist() OVER w FROM t WINDOW w AS (PARTITION BY a) 3787 ---- 3788 0 1 1 1 0 1 3789 0 2 1 1 0 1 3790 1 1 1 1 0 1 3791 1 2 1 1 0 1 3792 3793 3794 query IIIIRR rowsort 3795 SELECT a, b, rank() OVER w, dense_rank() OVER w, percent_rank() OVER w, cume_dist() OVER w FROM t WINDOW w AS (ORDER BY a) 3796 ---- 3797 0 1 1 1 0 0.5 3798 0 2 1 1 0 0.5 3799 1 1 3 2 0.666666666666667 1 3800 1 2 3 2 0.666666666666667 1 3801 3802 query IIIIRR rowsort 3803 SELECT a, b, rank() OVER w, dense_rank() OVER w, percent_rank() OVER w, cume_dist() OVER w FROM t WINDOW w AS (PARTITION BY a ORDER BY b) 3804 ---- 3805 0 1 1 1 0 0.5 3806 0 2 2 2 1 1 3807 1 1 1 1 0 0.5 3808 1 2 2 2 1 1