1-- 2-- WINDOW FUNCTIONS 3-- 4CREATE TEMPORARY TABLE empsalary ( 5 depname varchar, 6 empno bigint, 7 salary int, 8 enroll_date date 9); 10INSERT INTO empsalary VALUES 11('develop', 10, 5200, '2007-08-01'), 12('sales', 1, 5000, '2006-10-01'), 13('personnel', 5, 3500, '2007-12-10'), 14('sales', 4, 4800, '2007-08-08'), 15('personnel', 2, 3900, '2006-12-23'), 16('develop', 7, 4200, '2008-01-01'), 17('develop', 9, 4500, '2008-01-01'), 18('sales', 3, 4800, '2007-08-01'), 19('develop', 8, 6000, '2006-10-01'), 20('develop', 11, 5200, '2007-08-15'); 21SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; 22 depname | empno | salary | sum 23-----------+-------+--------+------- 24 develop | 7 | 4200 | 25100 25 develop | 9 | 4500 | 25100 26 develop | 11 | 5200 | 25100 27 develop | 10 | 5200 | 25100 28 develop | 8 | 6000 | 25100 29 personnel | 5 | 3500 | 7400 30 personnel | 2 | 3900 | 7400 31 sales | 3 | 4800 | 14600 32 sales | 4 | 4800 | 14600 33 sales | 1 | 5000 | 14600 34(10 rows) 35 36SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; 37 depname | empno | salary | rank 38-----------+-------+--------+------ 39 develop | 7 | 4200 | 1 40 develop | 9 | 4500 | 2 41 develop | 11 | 5200 | 3 42 develop | 10 | 5200 | 3 43 develop | 8 | 6000 | 5 44 personnel | 5 | 3500 | 1 45 personnel | 2 | 3900 | 2 46 sales | 3 | 4800 | 1 47 sales | 4 | 4800 | 1 48 sales | 1 | 5000 | 3 49(10 rows) 50 51-- with GROUP BY 52SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1 53GROUP BY four, ten ORDER BY four, ten; 54 four | ten | sum | avg 55------+-----+------+------------------------ 56 0 | 0 | 0 | 0.00000000000000000000 57 0 | 2 | 0 | 2.0000000000000000 58 0 | 4 | 0 | 4.0000000000000000 59 0 | 6 | 0 | 6.0000000000000000 60 0 | 8 | 0 | 8.0000000000000000 61 1 | 1 | 2500 | 1.00000000000000000000 62 1 | 3 | 2500 | 3.0000000000000000 63 1 | 5 | 2500 | 5.0000000000000000 64 1 | 7 | 2500 | 7.0000000000000000 65 1 | 9 | 2500 | 9.0000000000000000 66 2 | 0 | 5000 | 0.00000000000000000000 67 2 | 2 | 5000 | 2.0000000000000000 68 2 | 4 | 5000 | 4.0000000000000000 69 2 | 6 | 5000 | 6.0000000000000000 70 2 | 8 | 5000 | 8.0000000000000000 71 3 | 1 | 7500 | 1.00000000000000000000 72 3 | 3 | 7500 | 3.0000000000000000 73 3 | 5 | 7500 | 5.0000000000000000 74 3 | 7 | 7500 | 7.0000000000000000 75 3 | 9 | 7500 | 9.0000000000000000 76(20 rows) 77 78SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); 79 depname | empno | salary | sum 80-----------+-------+--------+------- 81 develop | 11 | 5200 | 25100 82 develop | 7 | 4200 | 25100 83 develop | 9 | 4500 | 25100 84 develop | 8 | 6000 | 25100 85 develop | 10 | 5200 | 25100 86 personnel | 5 | 3500 | 7400 87 personnel | 2 | 3900 | 7400 88 sales | 3 | 4800 | 14600 89 sales | 1 | 5000 | 14600 90 sales | 4 | 4800 | 14600 91(10 rows) 92 93SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; 94 depname | empno | salary | rank 95-----------+-------+--------+------ 96 develop | 7 | 4200 | 1 97 personnel | 5 | 3500 | 1 98 sales | 3 | 4800 | 1 99 sales | 4 | 4800 | 1 100 personnel | 2 | 3900 | 2 101 develop | 9 | 4500 | 2 102 sales | 1 | 5000 | 3 103 develop | 11 | 5200 | 3 104 develop | 10 | 5200 | 3 105 develop | 8 | 6000 | 5 106(10 rows) 107 108-- empty window specification 109SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10; 110 count 111------- 112 10 113 10 114 10 115 10 116 10 117 10 118 10 119 10 120 10 121 10 122(10 rows) 123 124SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS (); 125 count 126------- 127 10 128 10 129 10 130 10 131 10 132 10 133 10 134 10 135 10 136 10 137(10 rows) 138 139-- no window operation 140SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten); 141 four 142------ 143(0 rows) 144 145-- cumulative aggregate 146SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10; 147 sum_1 | ten | four 148-------+-----+------ 149 0 | 0 | 0 150 0 | 0 | 0 151 2 | 0 | 2 152 3 | 1 | 3 153 4 | 1 | 1 154 5 | 1 | 1 155 3 | 3 | 3 156 0 | 4 | 0 157 1 | 7 | 1 158 1 | 9 | 1 159(10 rows) 160 161SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10; 162 row_number 163------------ 164 1 165 2 166 3 167 4 168 5 169 6 170 7 171 8 172 9 173 10 174(10 rows) 175 176SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10; 177 rank_1 | ten | four 178--------+-----+------ 179 1 | 0 | 0 180 1 | 0 | 0 181 3 | 4 | 0 182 1 | 1 | 1 183 1 | 1 | 1 184 3 | 7 | 1 185 4 | 9 | 1 186 1 | 0 | 2 187 1 | 1 | 3 188 2 | 3 | 3 189(10 rows) 190 191SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 192 dense_rank | ten | four 193------------+-----+------ 194 1 | 0 | 0 195 1 | 0 | 0 196 2 | 4 | 0 197 1 | 1 | 1 198 1 | 1 | 1 199 2 | 7 | 1 200 3 | 9 | 1 201 1 | 0 | 2 202 1 | 1 | 3 203 2 | 3 | 3 204(10 rows) 205 206SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 207 percent_rank | ten | four 208-------------------+-----+------ 209 0 | 0 | 0 210 0 | 0 | 0 211 1 | 4 | 0 212 0 | 1 | 1 213 0 | 1 | 1 214 0.666666666666667 | 7 | 1 215 1 | 9 | 1 216 0 | 0 | 2 217 0 | 1 | 3 218 1 | 3 | 3 219(10 rows) 220 221SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 222 cume_dist | ten | four 223-------------------+-----+------ 224 0.666666666666667 | 0 | 0 225 0.666666666666667 | 0 | 0 226 1 | 4 | 0 227 0.5 | 1 | 1 228 0.5 | 1 | 1 229 0.75 | 7 | 1 230 1 | 9 | 1 231 1 | 0 | 2 232 0.5 | 1 | 3 233 1 | 3 | 3 234(10 rows) 235 236SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10; 237 ntile | ten | four 238-------+-----+------ 239 1 | 0 | 0 240 1 | 0 | 0 241 1 | 0 | 2 242 1 | 1 | 1 243 2 | 1 | 1 244 2 | 1 | 3 245 2 | 3 | 3 246 3 | 4 | 0 247 3 | 7 | 1 248 3 | 9 | 1 249(10 rows) 250 251SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2; 252 ntile | ten | four 253-------+-----+------ 254 | 0 | 0 255 | 0 | 0 256(2 rows) 257 258SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 259 lag | ten | four 260-----+-----+------ 261 | 0 | 0 262 0 | 0 | 0 263 0 | 4 | 0 264 | 1 | 1 265 1 | 1 | 1 266 1 | 7 | 1 267 7 | 9 | 1 268 | 0 | 2 269 | 1 | 3 270 1 | 3 | 3 271(10 rows) 272 273SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 274 lag | ten | four 275-----+-----+------ 276 0 | 0 | 0 277 0 | 0 | 0 278 4 | 4 | 0 279 | 1 | 1 280 1 | 1 | 1 281 1 | 7 | 1 282 7 | 9 | 1 283 | 0 | 2 284 | 1 | 3 285 | 3 | 3 286(10 rows) 287 288SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 289 lag | ten | four 290-----+-----+------ 291 0 | 0 | 0 292 0 | 0 | 0 293 4 | 4 | 0 294 0 | 1 | 1 295 1 | 1 | 1 296 1 | 7 | 1 297 7 | 9 | 1 298 0 | 0 | 2 299 0 | 1 | 3 300 0 | 3 | 3 301(10 rows) 302 303SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 304 lead | ten | four 305------+-----+------ 306 0 | 0 | 0 307 4 | 0 | 0 308 | 4 | 0 309 1 | 1 | 1 310 7 | 1 | 1 311 9 | 7 | 1 312 | 9 | 1 313 | 0 | 2 314 3 | 1 | 3 315 | 3 | 3 316(10 rows) 317 318SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 319 lead | ten | four 320------+-----+------ 321 0 | 0 | 0 322 8 | 0 | 0 323 | 4 | 0 324 2 | 1 | 1 325 14 | 1 | 1 326 18 | 7 | 1 327 | 9 | 1 328 | 0 | 2 329 6 | 1 | 3 330 | 3 | 3 331(10 rows) 332 333SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 334 lead | ten | four 335------+-----+------ 336 0 | 0 | 0 337 8 | 0 | 0 338 -1 | 4 | 0 339 2 | 1 | 1 340 14 | 1 | 1 341 18 | 7 | 1 342 -1 | 9 | 1 343 -1 | 0 | 2 344 6 | 1 | 3 345 -1 | 3 | 3 346(10 rows) 347 348SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 349 first_value | ten | four 350-------------+-----+------ 351 0 | 0 | 0 352 0 | 0 | 0 353 0 | 4 | 0 354 1 | 1 | 1 355 1 | 1 | 1 356 1 | 7 | 1 357 1 | 9 | 1 358 0 | 0 | 2 359 1 | 1 | 3 360 1 | 3 | 3 361(10 rows) 362 363-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window. 364SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 365 last_value | ten | four 366------------+-----+------ 367 0 | 0 | 0 368 0 | 0 | 2 369 0 | 0 | 0 370 1 | 1 | 1 371 1 | 1 | 3 372 1 | 1 | 1 373 3 | 3 | 3 374 0 | 4 | 0 375 1 | 7 | 1 376 1 | 9 | 1 377(10 rows) 378 379SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM 380 (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s 381 ORDER BY four, ten; 382 last_value | ten | four 383------------+-----+------ 384 4 | 0 | 0 385 4 | 0 | 0 386 4 | 4 | 0 387 9 | 1 | 1 388 9 | 1 | 1 389 9 | 7 | 1 390 9 | 9 | 1 391 0 | 0 | 2 392 3 | 1 | 3 393 3 | 3 | 3 394(10 rows) 395 396SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four 397 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s; 398 nth_value | ten | four 399-----------+-----+------ 400 0 | 0 | 0 401 0 | 0 | 0 402 0 | 4 | 0 403 1 | 1 | 1 404 1 | 1 | 1 405 1 | 7 | 1 406 1 | 9 | 1 407 | 0 | 2 408 | 1 | 3 409 | 3 | 3 410(10 rows) 411 412SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum 413FROM tenk1 GROUP BY ten, two; 414 ten | two | gsum | wsum 415-----+-----+-------+-------- 416 0 | 0 | 45000 | 45000 417 2 | 0 | 47000 | 92000 418 4 | 0 | 49000 | 141000 419 6 | 0 | 51000 | 192000 420 8 | 0 | 53000 | 245000 421 1 | 1 | 46000 | 46000 422 3 | 1 | 48000 | 94000 423 5 | 1 | 50000 | 144000 424 7 | 1 | 52000 | 196000 425 9 | 1 | 54000 | 250000 426(10 rows) 427 428SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10; 429 count | four 430-------+------ 431 4 | 1 432 4 | 1 433 4 | 1 434 4 | 1 435 2 | 3 436 2 | 3 437(6 rows) 438 439SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) + 440 sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum 441 FROM tenk1 WHERE unique2 < 10; 442 cntsum 443-------- 444 22 445 22 446 87 447 24 448 24 449 82 450 92 451 51 452 92 453 136 454(10 rows) 455 456-- opexpr with different windows evaluation. 457SELECT * FROM( 458 SELECT count(*) OVER (PARTITION BY four ORDER BY ten) + 459 sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total, 460 count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount, 461 sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum 462 FROM tenk1 463)sub 464WHERE total <> fourcount + twosum; 465 total | fourcount | twosum 466-------+-----------+-------- 467(0 rows) 468 469SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10; 470 avg 471------------------------ 472 0.00000000000000000000 473 0.00000000000000000000 474 0.00000000000000000000 475 1.00000000000000000000 476 1.00000000000000000000 477 1.00000000000000000000 478 1.00000000000000000000 479 2.0000000000000000 480 3.0000000000000000 481 3.0000000000000000 482(10 rows) 483 484SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum 485FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten); 486 ten | two | gsum | wsum 487-----+-----+-------+-------- 488 0 | 0 | 45000 | 45000 489 2 | 0 | 47000 | 92000 490 4 | 0 | 49000 | 141000 491 6 | 0 | 51000 | 192000 492 8 | 0 | 53000 | 245000 493 1 | 1 | 46000 | 46000 494 3 | 1 | 48000 | 94000 495 5 | 1 | 50000 | 144000 496 7 | 1 | 52000 | 196000 497 9 | 1 | 54000 | 250000 498(10 rows) 499 500-- more than one window with GROUP BY 501SELECT sum(salary), 502 row_number() OVER (ORDER BY depname), 503 sum(sum(salary)) OVER (ORDER BY depname DESC) 504FROM empsalary GROUP BY depname; 505 sum | row_number | sum 506-------+------------+------- 507 14600 | 3 | 14600 508 7400 | 2 | 22000 509 25100 | 1 | 47100 510(3 rows) 511 512-- identical windows with different names 513SELECT sum(salary) OVER w1, count(*) OVER w2 514FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary); 515 sum | count 516-------+------- 517 3500 | 1 518 7400 | 2 519 11600 | 3 520 16100 | 4 521 25700 | 6 522 25700 | 6 523 30700 | 7 524 41100 | 9 525 41100 | 9 526 47100 | 10 527(10 rows) 528 529-- subplan 530SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten) 531FROM tenk1 s WHERE unique2 < 10; 532 lead 533------ 534 0 535 0 536 4 537 1 538 7 539 9 540 541 0 542 3 543 544(10 rows) 545 546-- empty table 547SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s; 548 count 549------- 550(0 rows) 551 552-- mixture of agg/wfunc in the same window 553SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); 554 sum | rank 555-------+------ 556 6000 | 1 557 16400 | 2 558 16400 | 2 559 20900 | 4 560 25100 | 5 561 3900 | 1 562 7400 | 2 563 5000 | 1 564 14600 | 2 565 14600 | 2 566(10 rows) 567 568-- strict aggs 569SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM( 570 SELECT *, 571 CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, 572 CASE WHEN 573 AVG(salary) OVER (PARTITION BY depname) < salary 574 THEN 200 END AS depadj FROM empsalary 575)s; 576 empno | depname | salary | bonus | depadj | min | max 577-------+-----------+--------+-------+--------+------+----- 578 1 | sales | 5000 | 1000 | 200 | 1000 | 200 579 2 | personnel | 3900 | 1000 | 200 | 1000 | 200 580 3 | sales | 4800 | 500 | | 500 | 200 581 4 | sales | 4800 | 500 | | 500 | 200 582 5 | personnel | 3500 | 500 | | 500 | 200 583 7 | develop | 4200 | | | 500 | 200 584 8 | develop | 6000 | 1000 | 200 | 500 | 200 585 9 | develop | 4500 | | | 500 | 200 586 10 | develop | 5200 | 500 | 200 | 500 | 200 587 11 | develop | 5200 | 500 | 200 | 500 | 200 588(10 rows) 589 590-- window function over ungrouped agg over empty row set (bug before 9.1) 591SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42; 592 sum 593----- 594 0 595(1 row) 596 597-- window function with ORDER BY an expression involving aggregates (9.1 bug) 598select ten, 599 sum(unique1) + sum(unique2) as res, 600 rank() over (order by sum(unique1) + sum(unique2)) as rank 601from tenk1 602group by ten order by ten; 603 ten | res | rank 604-----+----------+------ 605 0 | 9976146 | 4 606 1 | 10114187 | 9 607 2 | 10059554 | 8 608 3 | 9878541 | 1 609 4 | 9881005 | 2 610 5 | 9981670 | 5 611 6 | 9947099 | 3 612 7 | 10120309 | 10 613 8 | 9991305 | 6 614 9 | 10040184 | 7 615(10 rows) 616 617-- window and aggregate with GROUP BY expression (9.2 bug) 618explain (costs off) 619select first_value(max(x)) over (), y 620 from (select unique1 as x, ten+four as y from tenk1) ss 621 group by y; 622 QUERY PLAN 623--------------------------------------------- 624 WindowAgg 625 -> HashAggregate 626 Group Key: (tenk1.ten + tenk1.four) 627 -> Seq Scan on tenk1 628(4 rows) 629 630-- test non-default frame specifications 631SELECT four, ten, 632 sum(ten) over (partition by four order by ten), 633 last_value(ten) over (partition by four order by ten) 634FROM (select distinct ten, four from tenk1) ss; 635 four | ten | sum | last_value 636------+-----+-----+------------ 637 0 | 0 | 0 | 0 638 0 | 2 | 2 | 2 639 0 | 4 | 6 | 4 640 0 | 6 | 12 | 6 641 0 | 8 | 20 | 8 642 1 | 1 | 1 | 1 643 1 | 3 | 4 | 3 644 1 | 5 | 9 | 5 645 1 | 7 | 16 | 7 646 1 | 9 | 25 | 9 647 2 | 0 | 0 | 0 648 2 | 2 | 2 | 2 649 2 | 4 | 6 | 4 650 2 | 6 | 12 | 6 651 2 | 8 | 20 | 8 652 3 | 1 | 1 | 1 653 3 | 3 | 4 | 3 654 3 | 5 | 9 | 5 655 3 | 7 | 16 | 7 656 3 | 9 | 25 | 9 657(20 rows) 658 659SELECT four, ten, 660 sum(ten) over (partition by four order by ten range between unbounded preceding and current row), 661 last_value(ten) over (partition by four order by ten range between unbounded preceding and current row) 662FROM (select distinct ten, four from tenk1) ss; 663 four | ten | sum | last_value 664------+-----+-----+------------ 665 0 | 0 | 0 | 0 666 0 | 2 | 2 | 2 667 0 | 4 | 6 | 4 668 0 | 6 | 12 | 6 669 0 | 8 | 20 | 8 670 1 | 1 | 1 | 1 671 1 | 3 | 4 | 3 672 1 | 5 | 9 | 5 673 1 | 7 | 16 | 7 674 1 | 9 | 25 | 9 675 2 | 0 | 0 | 0 676 2 | 2 | 2 | 2 677 2 | 4 | 6 | 4 678 2 | 6 | 12 | 6 679 2 | 8 | 20 | 8 680 3 | 1 | 1 | 1 681 3 | 3 | 4 | 3 682 3 | 5 | 9 | 5 683 3 | 7 | 16 | 7 684 3 | 9 | 25 | 9 685(20 rows) 686 687SELECT four, ten, 688 sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following), 689 last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following) 690FROM (select distinct ten, four from tenk1) ss; 691 four | ten | sum | last_value 692------+-----+-----+------------ 693 0 | 0 | 20 | 8 694 0 | 2 | 20 | 8 695 0 | 4 | 20 | 8 696 0 | 6 | 20 | 8 697 0 | 8 | 20 | 8 698 1 | 1 | 25 | 9 699 1 | 3 | 25 | 9 700 1 | 5 | 25 | 9 701 1 | 7 | 25 | 9 702 1 | 9 | 25 | 9 703 2 | 0 | 20 | 8 704 2 | 2 | 20 | 8 705 2 | 4 | 20 | 8 706 2 | 6 | 20 | 8 707 2 | 8 | 20 | 8 708 3 | 1 | 25 | 9 709 3 | 3 | 25 | 9 710 3 | 5 | 25 | 9 711 3 | 7 | 25 | 9 712 3 | 9 | 25 | 9 713(20 rows) 714 715SELECT four, ten/4 as two, 716 sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row), 717 last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row) 718FROM (select distinct ten, four from tenk1) ss; 719 four | two | sum | last_value 720------+-----+-----+------------ 721 0 | 0 | 0 | 0 722 0 | 0 | 0 | 0 723 0 | 1 | 2 | 1 724 0 | 1 | 2 | 1 725 0 | 2 | 4 | 2 726 1 | 0 | 0 | 0 727 1 | 0 | 0 | 0 728 1 | 1 | 2 | 1 729 1 | 1 | 2 | 1 730 1 | 2 | 4 | 2 731 2 | 0 | 0 | 0 732 2 | 0 | 0 | 0 733 2 | 1 | 2 | 1 734 2 | 1 | 2 | 1 735 2 | 2 | 4 | 2 736 3 | 0 | 0 | 0 737 3 | 0 | 0 | 0 738 3 | 1 | 2 | 1 739 3 | 1 | 2 | 1 740 3 | 2 | 4 | 2 741(20 rows) 742 743SELECT four, ten/4 as two, 744 sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row), 745 last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row) 746FROM (select distinct ten, four from tenk1) ss; 747 four | two | sum | last_value 748------+-----+-----+------------ 749 0 | 0 | 0 | 0 750 0 | 0 | 0 | 0 751 0 | 1 | 1 | 1 752 0 | 1 | 2 | 1 753 0 | 2 | 4 | 2 754 1 | 0 | 0 | 0 755 1 | 0 | 0 | 0 756 1 | 1 | 1 | 1 757 1 | 1 | 2 | 1 758 1 | 2 | 4 | 2 759 2 | 0 | 0 | 0 760 2 | 0 | 0 | 0 761 2 | 1 | 1 | 1 762 2 | 1 | 2 | 1 763 2 | 2 | 4 | 2 764 3 | 0 | 0 | 0 765 3 | 0 | 0 | 0 766 3 | 1 | 1 | 1 767 3 | 1 | 2 | 1 768 3 | 2 | 4 | 2 769(20 rows) 770 771SELECT sum(unique1) over (order by four range between current row and unbounded following), 772 unique1, four 773FROM tenk1 WHERE unique1 < 10; 774 sum | unique1 | four 775-----+---------+------ 776 45 | 0 | 0 777 45 | 8 | 0 778 45 | 4 | 0 779 33 | 5 | 1 780 33 | 9 | 1 781 33 | 1 | 1 782 18 | 6 | 2 783 18 | 2 | 2 784 10 | 3 | 3 785 10 | 7 | 3 786(10 rows) 787 788SELECT sum(unique1) over (rows between current row and unbounded following), 789 unique1, four 790FROM tenk1 WHERE unique1 < 10; 791 sum | unique1 | four 792-----+---------+------ 793 45 | 4 | 0 794 41 | 2 | 2 795 39 | 1 | 1 796 38 | 6 | 2 797 32 | 9 | 1 798 23 | 8 | 0 799 15 | 5 | 1 800 10 | 3 | 3 801 7 | 7 | 3 802 0 | 0 | 0 803(10 rows) 804 805SELECT sum(unique1) over (rows between 2 preceding and 2 following), 806 unique1, four 807FROM tenk1 WHERE unique1 < 10; 808 sum | unique1 | four 809-----+---------+------ 810 7 | 4 | 0 811 13 | 2 | 2 812 22 | 1 | 1 813 26 | 6 | 2 814 29 | 9 | 1 815 31 | 8 | 0 816 32 | 5 | 1 817 23 | 3 | 3 818 15 | 7 | 3 819 10 | 0 | 0 820(10 rows) 821 822SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), 823 unique1, four 824FROM tenk1 WHERE unique1 < 10; 825 sum | unique1 | four 826-----+---------+------ 827 | 4 | 0 828 4 | 2 | 2 829 6 | 1 | 1 830 3 | 6 | 2 831 7 | 9 | 1 832 15 | 8 | 0 833 17 | 5 | 1 834 13 | 3 | 3 835 8 | 7 | 3 836 10 | 0 | 0 837(10 rows) 838 839SELECT sum(unique1) over (rows between 1 following and 3 following), 840 unique1, four 841FROM tenk1 WHERE unique1 < 10; 842 sum | unique1 | four 843-----+---------+------ 844 9 | 4 | 0 845 16 | 2 | 2 846 23 | 1 | 1 847 22 | 6 | 2 848 16 | 9 | 1 849 15 | 8 | 0 850 10 | 5 | 1 851 7 | 3 | 3 852 0 | 7 | 3 853 | 0 | 0 854(10 rows) 855 856SELECT sum(unique1) over (rows between unbounded preceding and 1 following), 857 unique1, four 858FROM tenk1 WHERE unique1 < 10; 859 sum | unique1 | four 860-----+---------+------ 861 6 | 4 | 0 862 7 | 2 | 2 863 13 | 1 | 1 864 22 | 6 | 2 865 30 | 9 | 1 866 35 | 8 | 0 867 38 | 5 | 1 868 45 | 3 | 3 869 45 | 7 | 3 870 45 | 0 | 0 871(10 rows) 872 873SELECT sum(unique1) over (w range between current row and unbounded following), 874 unique1, four 875FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 876 sum | unique1 | four 877-----+---------+------ 878 45 | 0 | 0 879 45 | 8 | 0 880 45 | 4 | 0 881 33 | 5 | 1 882 33 | 9 | 1 883 33 | 1 | 1 884 18 | 6 | 2 885 18 | 2 | 2 886 10 | 3 | 3 887 10 | 7 | 3 888(10 rows) 889 890-- fail: not implemented yet 891SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), 892 unique1, four 893FROM tenk1 WHERE unique1 < 10; 894ERROR: RANGE PRECEDING is only supported with UNBOUNDED 895LINE 1: SELECT sum(unique1) over (order by four range between 2::int... 896 ^ 897SELECT first_value(unique1) over w, 898 nth_value(unique1, 2) over w AS nth_2, 899 last_value(unique1) over w, unique1, four 900FROM tenk1 WHERE unique1 < 10 901WINDOW w AS (order by four range between current row and unbounded following); 902 first_value | nth_2 | last_value | unique1 | four 903-------------+-------+------------+---------+------ 904 0 | 8 | 7 | 0 | 0 905 0 | 8 | 7 | 8 | 0 906 0 | 8 | 7 | 4 | 0 907 5 | 9 | 7 | 5 | 1 908 5 | 9 | 7 | 9 | 1 909 5 | 9 | 7 | 1 | 1 910 6 | 2 | 7 | 6 | 2 911 6 | 2 | 7 | 2 | 2 912 3 | 7 | 7 | 3 | 3 913 3 | 7 | 7 | 7 | 3 914(10 rows) 915 916SELECT sum(unique1) over 917 (order by unique1 918 rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING), 919 unique1 920FROM tenk1 WHERE unique1 < 10; 921 sum | unique1 922-----+--------- 923 0 | 0 924 1 | 1 925 3 | 2 926 5 | 3 927 7 | 4 928 9 | 5 929 11 | 6 930 13 | 7 931 15 | 8 932 17 | 9 933(10 rows) 934 935CREATE TEMP VIEW v_window AS 936 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows 937 FROM generate_series(1, 10) i; 938SELECT * FROM v_window; 939 i | sum_rows 940----+---------- 941 1 | 3 942 2 | 6 943 3 | 9 944 4 | 12 945 5 | 15 946 6 | 18 947 7 | 21 948 8 | 24 949 9 | 27 950 10 | 19 951(10 rows) 952 953SELECT pg_get_viewdef('v_window'); 954 pg_get_viewdef 955--------------------------------------------------------------------------------------- 956 SELECT i.i, + 957 sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ 958 FROM generate_series(1, 10) i(i); 959(1 row) 960 961-- with UNION 962SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; 963 count 964------- 965(0 rows) 966 967-- ordering by a non-integer constant is allowed 968SELECT rank() OVER (ORDER BY length('abc')); 969 rank 970------ 971 1 972(1 row) 973 974-- can't order by another window function 975SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())); 976ERROR: window functions are not allowed in window definitions 977LINE 1: SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())... 978 ^ 979-- some other errors 980SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10; 981ERROR: window functions are not allowed in WHERE 982LINE 1: SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY sa... 983 ^ 984SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10; 985ERROR: window functions are not allowed in JOIN conditions 986LINE 1: SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVE... 987 ^ 988SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1; 989ERROR: window functions are not allowed in GROUP BY 990LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO... 991 ^ 992SELECT * FROM rank() OVER (ORDER BY random()); 993ERROR: syntax error at or near "ORDER" 994LINE 1: SELECT * FROM rank() OVER (ORDER BY random()); 995 ^ 996DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10; 997ERROR: window functions are not allowed in WHERE 998LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())... 999 ^ 1000DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random()); 1001ERROR: window functions are not allowed in RETURNING 1002LINE 1: DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random... 1003 ^ 1004SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1); 1005ERROR: window "w" is already defined 1006LINE 1: ...w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY ... 1007 ^ 1008SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1; 1009ERROR: syntax error at or near "ORDER" 1010LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te... 1011 ^ 1012SELECT count() OVER () FROM tenk1; 1013ERROR: count(*) must be used to call a parameterless aggregate function 1014LINE 1: SELECT count() OVER () FROM tenk1; 1015 ^ 1016SELECT generate_series(1, 100) OVER () FROM empsalary; 1017ERROR: OVER specified, but generate_series is not a window function nor an aggregate function 1018LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary; 1019 ^ 1020SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; 1021ERROR: argument of ntile must be greater than zero 1022SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; 1023ERROR: argument of nth_value must be greater than zero 1024-- filter 1025SELECT sum(salary), row_number() OVER (ORDER BY depname), sum( 1026 sum(salary) FILTER (WHERE enroll_date > '2007-01-01') 1027) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", 1028 depname 1029FROM empsalary GROUP BY depname; 1030 sum | row_number | filtered_sum | depname 1031-------+------------+--------------+----------- 1032 14600 | 3 | | sales 1033 7400 | 2 | 3500 | personnel 1034 25100 | 1 | 22600 | develop 1035(3 rows) 1036 1037-- Test pushdown of quals into a subquery containing window functions 1038-- pushdown is safe because all PARTITION BY clauses include depname: 1039EXPLAIN (COSTS OFF) 1040SELECT * FROM 1041 (SELECT depname, 1042 sum(salary) OVER (PARTITION BY depname) depsalary, 1043 min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary 1044 FROM empsalary) emp 1045WHERE depname = 'sales'; 1046 QUERY PLAN 1047--------------------------------------------------------------------- 1048 Subquery Scan on emp 1049 -> WindowAgg 1050 -> Sort 1051 Sort Key: (((empsalary.depname)::text || 'A'::text)) 1052 -> WindowAgg 1053 -> Seq Scan on empsalary 1054 Filter: ((depname)::text = 'sales'::text) 1055(7 rows) 1056 1057-- pushdown is unsafe because there's a PARTITION BY clause without depname: 1058EXPLAIN (COSTS OFF) 1059SELECT * FROM 1060 (SELECT depname, 1061 sum(salary) OVER (PARTITION BY enroll_date) enroll_salary, 1062 min(salary) OVER (PARTITION BY depname) depminsalary 1063 FROM empsalary) emp 1064WHERE depname = 'sales'; 1065 QUERY PLAN 1066----------------------------------------------------------- 1067 Subquery Scan on emp 1068 Filter: ((emp.depname)::text = 'sales'::text) 1069 -> WindowAgg 1070 -> Sort 1071 Sort Key: empsalary.depname 1072 -> WindowAgg 1073 -> Sort 1074 Sort Key: empsalary.enroll_date 1075 -> Seq Scan on empsalary 1076(9 rows) 1077 1078-- cleanup 1079DROP TABLE empsalary; 1080-- test user-defined window function with named args and default args 1081CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement 1082 LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value'; 1083SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four 1084 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; 1085 nth_value_def | ten | four 1086---------------+-----+------ 1087 0 | 0 | 0 1088 0 | 0 | 0 1089 0 | 4 | 0 1090 1 | 1 | 1 1091 1 | 1 | 1 1092 1 | 7 | 1 1093 1 | 9 | 1 1094 | 0 | 2 1095 3 | 1 | 3 1096 3 | 3 | 3 1097(10 rows) 1098 1099SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four 1100 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; 1101 nth_value_def | ten | four 1102---------------+-----+------ 1103 0 | 0 | 0 1104 0 | 0 | 0 1105 0 | 4 | 0 1106 1 | 1 | 1 1107 1 | 1 | 1 1108 1 | 7 | 1 1109 1 | 9 | 1 1110 0 | 0 | 2 1111 1 | 1 | 3 1112 1 | 3 | 3 1113(10 rows) 1114 1115-- 1116-- Test the basic moving-aggregate machinery 1117-- 1118-- create aggregates that record the series of transform calls (these are 1119-- intentionally not true inverses) 1120CREATE FUNCTION logging_sfunc_nonstrict(text, anyelement) RETURNS text AS 1121$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$ 1122LANGUAGE SQL IMMUTABLE; 1123CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS 1124$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$ 1125LANGUAGE SQL IMMUTABLE; 1126CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS 1127$$ SELECT $1 || '-' || quote_nullable($2) $$ 1128LANGUAGE SQL IMMUTABLE; 1129CREATE AGGREGATE logging_agg_nonstrict (anyelement) 1130( 1131 stype = text, 1132 sfunc = logging_sfunc_nonstrict, 1133 mstype = text, 1134 msfunc = logging_msfunc_nonstrict, 1135 minvfunc = logging_minvfunc_nonstrict 1136); 1137CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement) 1138( 1139 stype = text, 1140 sfunc = logging_sfunc_nonstrict, 1141 mstype = text, 1142 msfunc = logging_msfunc_nonstrict, 1143 minvfunc = logging_minvfunc_nonstrict, 1144 initcond = 'I', 1145 minitcond = 'MI' 1146); 1147CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS 1148$$ SELECT $1 || '*' || quote_nullable($2) $$ 1149LANGUAGE SQL STRICT IMMUTABLE; 1150CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS 1151$$ SELECT $1 || '+' || quote_nullable($2) $$ 1152LANGUAGE SQL STRICT IMMUTABLE; 1153CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS 1154$$ SELECT $1 || '-' || quote_nullable($2) $$ 1155LANGUAGE SQL STRICT IMMUTABLE; 1156CREATE AGGREGATE logging_agg_strict (text) 1157( 1158 stype = text, 1159 sfunc = logging_sfunc_strict, 1160 mstype = text, 1161 msfunc = logging_msfunc_strict, 1162 minvfunc = logging_minvfunc_strict 1163); 1164CREATE AGGREGATE logging_agg_strict_initcond (anyelement) 1165( 1166 stype = text, 1167 sfunc = logging_sfunc_strict, 1168 mstype = text, 1169 msfunc = logging_msfunc_strict, 1170 minvfunc = logging_minvfunc_strict, 1171 initcond = 'I', 1172 minitcond = 'MI' 1173); 1174-- test strict and non-strict cases 1175SELECT 1176 p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS row, 1177 logging_agg_nonstrict(v) over wnd as nstrict, 1178 logging_agg_nonstrict_initcond(v) over wnd as nstrict_init, 1179 logging_agg_strict(v::text) over wnd as strict, 1180 logging_agg_strict_initcond(v) over wnd as strict_init 1181FROM (VALUES 1182 (1, 1, NULL), 1183 (1, 2, 'a'), 1184 (1, 3, 'b'), 1185 (1, 4, NULL), 1186 (1, 5, NULL), 1187 (1, 6, 'c'), 1188 (2, 1, NULL), 1189 (2, 2, 'x'), 1190 (3, 1, 'z') 1191) AS t(p, i, v) 1192WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1193ORDER BY p, i; 1194 row | nstrict | nstrict_init | strict | strict_init 1195----------+-----------------------------------------------+-------------------------------------------------+-----------+---------------- 1196 1,1:NULL | +NULL | MI+NULL | | MI 1197 1,2:a | +NULL+'a' | MI+NULL+'a' | a | MI+'a' 1198 1,3:b | +NULL+'a'-NULL+'b' | MI+NULL+'a'-NULL+'b' | a+'b' | MI+'a'+'b' 1199 1,4:NULL | +NULL+'a'-NULL+'b'-'a'+NULL | MI+NULL+'a'-NULL+'b'-'a'+NULL | a+'b'-'a' | MI+'a'+'b'-'a' 1200 1,5:NULL | +NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL | MI+NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL | | MI 1201 1,6:c | +NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL-NULL+'c' | MI+NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL-NULL+'c' | c | MI+'c' 1202 2,1:NULL | +NULL | MI+NULL | | MI 1203 2,2:x | +NULL+'x' | MI+NULL+'x' | x | MI+'x' 1204 3,1:z | +'z' | MI+'z' | z | MI+'z' 1205(9 rows) 1206 1207-- and again, but with filter 1208SELECT 1209 p::text || ',' || i::text || ':' || 1210 CASE WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row, 1211 logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt, 1212 logging_agg_nonstrict_initcond(v) filter(where f) over wnd as nstrict_init_filt, 1213 logging_agg_strict(v::text) filter(where f) over wnd as strict_filt, 1214 logging_agg_strict_initcond(v) filter(where f) over wnd as strict_init_filt 1215FROM (VALUES 1216 (1, 1, true, NULL), 1217 (1, 2, false, 'a'), 1218 (1, 3, true, 'b'), 1219 (1, 4, false, NULL), 1220 (1, 5, false, NULL), 1221 (1, 6, false, 'c'), 1222 (2, 1, false, NULL), 1223 (2, 2, true, 'x'), 1224 (3, 1, true, 'z') 1225) AS t(p, i, f, v) 1226WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1227ORDER BY p, i; 1228 row | nstrict_filt | nstrict_init_filt | strict_filt | strict_init_filt 1229----------+--------------+-------------------+-------------+------------------ 1230 1,1:NULL | +NULL | MI+NULL | | MI 1231 1,2:- | +NULL | MI+NULL | | MI 1232 1,3:b | +'b' | MI+'b' | b | MI+'b' 1233 1,4:- | +'b' | MI+'b' | b | MI+'b' 1234 1,5:- | | MI | | MI 1235 1,6:- | | MI | | MI 1236 2,1:- | | MI | | MI 1237 2,2:x | +'x' | MI+'x' | x | MI+'x' 1238 3,1:z | +'z' | MI+'z' | z | MI+'z' 1239(9 rows) 1240 1241-- test that volatile arguments disable moving-aggregate mode 1242SELECT 1243 i::text || ':' || COALESCE(v::text, 'NULL') as row, 1244 logging_agg_strict(v::text) 1245 over wnd as inverse, 1246 logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END) 1247 over wnd as noinverse 1248FROM (VALUES 1249 (1, 'a'), 1250 (2, 'b'), 1251 (3, 'c') 1252) AS t(i, v) 1253WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1254ORDER BY i; 1255 row | inverse | noinverse 1256-----+---------------+----------- 1257 1:a | a | a 1258 2:b | a+'b' | a*'b' 1259 3:c | a+'b'-'a'+'c' | b*'c' 1260(3 rows) 1261 1262SELECT 1263 i::text || ':' || COALESCE(v::text, 'NULL') as row, 1264 logging_agg_strict(v::text) filter(where true) 1265 over wnd as inverse, 1266 logging_agg_strict(v::text) filter(where random() >= 0) 1267 over wnd as noinverse 1268FROM (VALUES 1269 (1, 'a'), 1270 (2, 'b'), 1271 (3, 'c') 1272) AS t(i, v) 1273WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1274ORDER BY i; 1275 row | inverse | noinverse 1276-----+---------------+----------- 1277 1:a | a | a 1278 2:b | a+'b' | a*'b' 1279 3:c | a+'b'-'a'+'c' | b*'c' 1280(3 rows) 1281 1282-- test that non-overlapping windows don't use inverse transitions 1283SELECT 1284 logging_agg_strict(v::text) OVER wnd 1285FROM (VALUES 1286 (1, 'a'), 1287 (2, 'b'), 1288 (3, 'c') 1289) AS t(i, v) 1290WINDOW wnd AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) 1291ORDER BY i; 1292 logging_agg_strict 1293-------------------- 1294 a 1295 b 1296 c 1297(3 rows) 1298 1299-- test that returning NULL from the inverse transition functions 1300-- restarts the aggregation from scratch. The second aggregate is supposed 1301-- to test cases where only some aggregates restart, the third one checks 1302-- that one aggregate restarting doesn't cause others to restart. 1303CREATE FUNCTION sum_int_randrestart_minvfunc(int4, int4) RETURNS int4 AS 1304$$ SELECT CASE WHEN random() < 0.2 THEN NULL ELSE $1 - $2 END $$ 1305LANGUAGE SQL STRICT; 1306CREATE AGGREGATE sum_int_randomrestart (int4) 1307( 1308 stype = int4, 1309 sfunc = int4pl, 1310 mstype = int4, 1311 msfunc = int4pl, 1312 minvfunc = sum_int_randrestart_minvfunc 1313); 1314WITH 1315vs AS ( 1316 SELECT i, (random() * 100)::int4 AS v 1317 FROM generate_series(1, 100) AS i 1318), 1319sum_following AS ( 1320 SELECT i, SUM(v) OVER 1321 (ORDER BY i DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s 1322 FROM vs 1323) 1324SELECT DISTINCT 1325 sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1, 1326 -sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2, 1327 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 1328FROM vs 1329JOIN sum_following ON sum_following.i = vs.i 1330WINDOW fwd AS ( 1331 ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 1332); 1333 eq1 | eq2 | eq3 1334-----+-----+----- 1335 t | t | t 1336(1 row) 1337 1338-- 1339-- Test various built-in aggregates that have moving-aggregate support 1340-- 1341-- test inverse transition functions handle NULLs properly 1342SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1343 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1344 i | avg 1345---+-------------------- 1346 1 | 1.5000000000000000 1347 2 | 2.0000000000000000 1348 3 | 1349 4 | 1350(4 rows) 1351 1352SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1353 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1354 i | avg 1355---+-------------------- 1356 1 | 1.5000000000000000 1357 2 | 2.0000000000000000 1358 3 | 1359 4 | 1360(4 rows) 1361 1362SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1363 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1364 i | avg 1365---+-------------------- 1366 1 | 1.5000000000000000 1367 2 | 2.0000000000000000 1368 3 | 1369 4 | 1370(4 rows) 1371 1372SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1373 FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v); 1374 i | avg 1375---+-------------------- 1376 1 | 2.0000000000000000 1377 2 | 2.5000000000000000 1378 3 | 1379 4 | 1380(4 rows) 1381 1382SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1383 FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); 1384 i | avg 1385---+------------ 1386 1 | @ 1.5 secs 1387 2 | @ 2 secs 1388 3 | 1389 4 | 1390(4 rows) 1391 1392SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1393 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1394 i | sum 1395---+----- 1396 1 | 3 1397 2 | 2 1398 3 | 1399 4 | 1400(4 rows) 1401 1402SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1403 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1404 i | sum 1405---+----- 1406 1 | 3 1407 2 | 2 1408 3 | 1409 4 | 1410(4 rows) 1411 1412SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1413 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1414 i | sum 1415---+----- 1416 1 | 3 1417 2 | 2 1418 3 | 1419 4 | 1420(4 rows) 1421 1422SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1423 FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v); 1424 i | sum 1425---+------- 1426 1 | $3.30 1427 2 | $2.20 1428 3 | 1429 4 | 1430(4 rows) 1431 1432SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1433 FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); 1434 i | sum 1435---+---------- 1436 1 | @ 3 secs 1437 2 | @ 2 secs 1438 3 | 1439 4 | 1440(4 rows) 1441 1442SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1443 FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v); 1444 i | sum 1445---+----- 1446 1 | 3.3 1447 2 | 2.2 1448 3 | 1449 4 | 1450(4 rows) 1451 1452SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1453 FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n); 1454 sum 1455------ 1456 6.01 1457 5 1458 3 1459(3 rows) 1460 1461SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1462 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1463 i | count 1464---+------- 1465 1 | 2 1466 2 | 1 1467 3 | 0 1468 4 | 0 1469(4 rows) 1470 1471SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1472 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1473 i | count 1474---+------- 1475 1 | 4 1476 2 | 3 1477 3 | 2 1478 4 | 1 1479(4 rows) 1480 1481SELECT VAR_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1482 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1483 var_pop 1484----------------------- 1485 21704.000000000000 1486 13868.750000000000 1487 11266.666666666667 1488 4225.0000000000000000 1489 0 1490(5 rows) 1491 1492SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1493 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1494 var_pop 1495----------------------- 1496 21704.000000000000 1497 13868.750000000000 1498 11266.666666666667 1499 4225.0000000000000000 1500 0 1501(5 rows) 1502 1503SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1504 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1505 var_pop 1506----------------------- 1507 21704.000000000000 1508 13868.750000000000 1509 11266.666666666667 1510 4225.0000000000000000 1511 0 1512(5 rows) 1513 1514SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1515 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1516 var_pop 1517----------------------- 1518 21704.000000000000 1519 13868.750000000000 1520 11266.666666666667 1521 4225.0000000000000000 1522 0 1523(5 rows) 1524 1525SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1526 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1527 var_samp 1528----------------------- 1529 27130.000000000000 1530 18491.666666666667 1531 16900.000000000000 1532 8450.0000000000000000 1533 1534(5 rows) 1535 1536SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1537 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1538 var_samp 1539----------------------- 1540 27130.000000000000 1541 18491.666666666667 1542 16900.000000000000 1543 8450.0000000000000000 1544 1545(5 rows) 1546 1547SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1548 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1549 var_samp 1550----------------------- 1551 27130.000000000000 1552 18491.666666666667 1553 16900.000000000000 1554 8450.0000000000000000 1555 1556(5 rows) 1557 1558SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1559 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1560 var_samp 1561----------------------- 1562 27130.000000000000 1563 18491.666666666667 1564 16900.000000000000 1565 8450.0000000000000000 1566 1567(5 rows) 1568 1569SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1570 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1571 variance 1572----------------------- 1573 27130.000000000000 1574 18491.666666666667 1575 16900.000000000000 1576 8450.0000000000000000 1577 1578(5 rows) 1579 1580SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1581 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1582 variance 1583----------------------- 1584 27130.000000000000 1585 18491.666666666667 1586 16900.000000000000 1587 8450.0000000000000000 1588 1589(5 rows) 1590 1591SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1592 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1593 variance 1594----------------------- 1595 27130.000000000000 1596 18491.666666666667 1597 16900.000000000000 1598 8450.0000000000000000 1599 1600(5 rows) 1601 1602SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1603 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1604 variance 1605----------------------- 1606 27130.000000000000 1607 18491.666666666667 1608 16900.000000000000 1609 8450.0000000000000000 1610 1611(5 rows) 1612 1613SELECT STDDEV_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1614 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1615 stddev_pop 1616--------------------- 1617 147.322774885623 1618 147.322774885623 1619 117.765657133139 1620 106.144555520604 1621 65.0000000000000000 1622 0 1623(6 rows) 1624 1625SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1626 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1627 stddev_pop 1628--------------------- 1629 147.322774885623 1630 147.322774885623 1631 117.765657133139 1632 106.144555520604 1633 65.0000000000000000 1634 0 1635(6 rows) 1636 1637SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1638 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1639 stddev_pop 1640--------------------- 1641 147.322774885623 1642 147.322774885623 1643 117.765657133139 1644 106.144555520604 1645 65.0000000000000000 1646 0 1647(6 rows) 1648 1649SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1650 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1651 stddev_pop 1652--------------------- 1653 147.322774885623 1654 147.322774885623 1655 117.765657133139 1656 106.144555520604 1657 65.0000000000000000 1658 0 1659(6 rows) 1660 1661SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1662 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1663 stddev_samp 1664--------------------- 1665 164.711869639076 1666 164.711869639076 1667 135.984067694222 1668 130.000000000000 1669 91.9238815542511782 1670 1671(6 rows) 1672 1673SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1674 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1675 stddev_samp 1676--------------------- 1677 164.711869639076 1678 164.711869639076 1679 135.984067694222 1680 130.000000000000 1681 91.9238815542511782 1682 1683(6 rows) 1684 1685SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1686 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1687 stddev_samp 1688--------------------- 1689 164.711869639076 1690 164.711869639076 1691 135.984067694222 1692 130.000000000000 1693 91.9238815542511782 1694 1695(6 rows) 1696 1697SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1698 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1699 stddev_samp 1700--------------------- 1701 164.711869639076 1702 164.711869639076 1703 135.984067694222 1704 130.000000000000 1705 91.9238815542511782 1706 1707(6 rows) 1708 1709SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1710 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1711 stddev 1712--------------------- 1713 164.711869639076 1714 164.711869639076 1715 135.984067694222 1716 130.000000000000 1717 91.9238815542511782 1718 1719(6 rows) 1720 1721SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1722 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1723 stddev 1724--------------------- 1725 164.711869639076 1726 164.711869639076 1727 135.984067694222 1728 130.000000000000 1729 91.9238815542511782 1730 1731(6 rows) 1732 1733SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1734 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1735 stddev 1736--------------------- 1737 164.711869639076 1738 164.711869639076 1739 135.984067694222 1740 130.000000000000 1741 91.9238815542511782 1742 1743(6 rows) 1744 1745SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1746 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1747 stddev 1748--------------------- 1749 164.711869639076 1750 164.711869639076 1751 135.984067694222 1752 130.000000000000 1753 91.9238815542511782 1754 1755(6 rows) 1756 1757-- test that inverse transition functions work with various frame options 1758SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) 1759 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1760 i | sum 1761---+----- 1762 1 | 1 1763 2 | 2 1764 3 | 1765 4 | 1766(4 rows) 1767 1768SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) 1769 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1770 i | sum 1771---+----- 1772 1 | 3 1773 2 | 2 1774 3 | 1775 4 | 1776(4 rows) 1777 1778SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 1779 FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v); 1780 i | sum 1781---+----- 1782 1 | 3 1783 2 | 6 1784 3 | 9 1785 4 | 7 1786(4 rows) 1787 1788-- ensure aggregate over numeric properly recovers from NaN values 1789SELECT a, b, 1790 SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1791FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b); 1792 a | b | sum 1793---+-----+----- 1794 1 | 1 | 1 1795 2 | 2 | 3 1796 3 | NaN | NaN 1797 4 | 3 | NaN 1798 5 | 4 | 7 1799(5 rows) 1800 1801-- It might be tempting for someone to add an inverse trans function for 1802-- float and double precision. This should not be done as it can give incorrect 1803-- results. This test should fail if anyone ever does this without thinking too 1804-- hard about it. 1805SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9') 1806 FROM (VALUES(1,1e20),(2,1)) n(i,n); 1807 to_char 1808-------------------------- 1809 100000000000000000000 1810 1.0 1811(2 rows) 1812 1813SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w 1814 FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b) 1815 WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING); 1816 i | b | bool_and | bool_or 1817---+---+----------+--------- 1818 1 | t | t | t 1819 2 | t | f | t 1820 3 | f | f | f 1821 4 | f | f | t 1822 5 | t | t | t 1823(5 rows) 1824 1825-- Tests for problems with failure to walk or mutate expressions 1826-- within window frame clauses. 1827-- test walker (fails with collation error if expressions are not walked) 1828SELECT array_agg(i) OVER w 1829 FROM generate_series(1,5) i 1830WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW); 1831 array_agg 1832----------- 1833 {1} 1834 {1,2} 1835 {2,3} 1836 {3,4} 1837 {4,5} 1838(5 rows) 1839 1840-- test mutator (fails when inlined if expressions are not mutated) 1841CREATE FUNCTION pg_temp.f(group_size BIGINT) RETURNS SETOF integer[] 1842AS $$ 1843 SELECT array_agg(s) OVER w 1844 FROM generate_series(1,5) s 1845 WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING) 1846$$ LANGUAGE SQL STABLE; 1847EXPLAIN (costs off) SELECT * FROM pg_temp.f(2); 1848 QUERY PLAN 1849------------------------------------------------------ 1850 Subquery Scan on f 1851 -> WindowAgg 1852 -> Sort 1853 Sort Key: s.s 1854 -> Function Scan on generate_series s 1855(5 rows) 1856 1857SELECT * FROM pg_temp.f(2); 1858 f 1859--------- 1860 {1,2,3} 1861 {2,3,4} 1862 {3,4,5} 1863 {4,5} 1864 {5} 1865(5 rows) 1866 1867