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 2 following exclude no others), 823 unique1, four 824FROM tenk1 WHERE unique1 < 10; 825 sum | unique1 | four 826-----+---------+------ 827 7 | 4 | 0 828 13 | 2 | 2 829 22 | 1 | 1 830 26 | 6 | 2 831 29 | 9 | 1 832 31 | 8 | 0 833 32 | 5 | 1 834 23 | 3 | 3 835 15 | 7 | 3 836 10 | 0 | 0 837(10 rows) 838 839SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), 840 unique1, four 841FROM tenk1 WHERE unique1 < 10; 842 sum | unique1 | four 843-----+---------+------ 844 3 | 4 | 0 845 11 | 2 | 2 846 21 | 1 | 1 847 20 | 6 | 2 848 20 | 9 | 1 849 23 | 8 | 0 850 27 | 5 | 1 851 20 | 3 | 3 852 8 | 7 | 3 853 10 | 0 | 0 854(10 rows) 855 856SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), 857 unique1, four 858FROM tenk1 WHERE unique1 < 10; 859 sum | unique1 | four 860-----+---------+------ 861 | 4 | 0 862 | 2 | 2 863 | 1 | 1 864 | 6 | 2 865 | 9 | 1 866 | 8 | 0 867 | 5 | 1 868 | 3 | 3 869 | 7 | 3 870 | 0 | 0 871(10 rows) 872 873SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), 874 unique1, four 875FROM tenk1 WHERE unique1 < 10; 876 sum | unique1 | four 877-----+---------+------ 878 4 | 4 | 0 879 2 | 2 | 2 880 1 | 1 | 1 881 6 | 6 | 2 882 9 | 9 | 1 883 8 | 8 | 0 884 5 | 5 | 1 885 3 | 3 | 3 886 7 | 7 | 3 887 0 | 0 | 0 888(10 rows) 889 890SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), 891 unique1, four 892FROM tenk1 WHERE unique1 < 10; 893 first_value | unique1 | four 894-------------+---------+------ 895 8 | 0 | 0 896 4 | 8 | 0 897 5 | 4 | 0 898 9 | 5 | 1 899 1 | 9 | 1 900 6 | 1 | 1 901 2 | 6 | 2 902 3 | 2 | 2 903 7 | 3 | 3 904 | 7 | 3 905(10 rows) 906 907SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), 908 unique1, four 909FROM tenk1 WHERE unique1 < 10; 910 first_value | unique1 | four 911-------------+---------+------ 912 | 0 | 0 913 5 | 8 | 0 914 5 | 4 | 0 915 | 5 | 1 916 6 | 9 | 1 917 6 | 1 | 1 918 3 | 6 | 2 919 3 | 2 | 2 920 | 3 | 3 921 | 7 | 3 922(10 rows) 923 924SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), 925 unique1, four 926FROM tenk1 WHERE unique1 < 10; 927 first_value | unique1 | four 928-------------+---------+------ 929 0 | 0 | 0 930 8 | 8 | 0 931 4 | 4 | 0 932 5 | 5 | 1 933 9 | 9 | 1 934 1 | 1 | 1 935 6 | 6 | 2 936 2 | 2 | 2 937 3 | 3 | 3 938 7 | 7 | 3 939(10 rows) 940 941SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), 942 unique1, four 943FROM tenk1 WHERE unique1 < 10; 944 last_value | unique1 | four 945------------+---------+------ 946 4 | 0 | 0 947 5 | 8 | 0 948 9 | 4 | 0 949 1 | 5 | 1 950 6 | 9 | 1 951 2 | 1 | 1 952 3 | 6 | 2 953 7 | 2 | 2 954 7 | 3 | 3 955 | 7 | 3 956(10 rows) 957 958SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), 959 unique1, four 960FROM tenk1 WHERE unique1 < 10; 961 last_value | unique1 | four 962------------+---------+------ 963 | 0 | 0 964 5 | 8 | 0 965 9 | 4 | 0 966 | 5 | 1 967 6 | 9 | 1 968 2 | 1 | 1 969 3 | 6 | 2 970 7 | 2 | 2 971 | 3 | 3 972 | 7 | 3 973(10 rows) 974 975SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), 976 unique1, four 977FROM tenk1 WHERE unique1 < 10; 978 last_value | unique1 | four 979------------+---------+------ 980 0 | 0 | 0 981 5 | 8 | 0 982 9 | 4 | 0 983 5 | 5 | 1 984 6 | 9 | 1 985 2 | 1 | 1 986 3 | 6 | 2 987 7 | 2 | 2 988 3 | 3 | 3 989 7 | 7 | 3 990(10 rows) 991 992SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), 993 unique1, four 994FROM tenk1 WHERE unique1 < 10; 995 sum | unique1 | four 996-----+---------+------ 997 | 4 | 0 998 4 | 2 | 2 999 6 | 1 | 1 1000 3 | 6 | 2 1001 7 | 9 | 1 1002 15 | 8 | 0 1003 17 | 5 | 1 1004 13 | 3 | 3 1005 8 | 7 | 3 1006 10 | 0 | 0 1007(10 rows) 1008 1009SELECT sum(unique1) over (rows between 1 following and 3 following), 1010 unique1, four 1011FROM tenk1 WHERE unique1 < 10; 1012 sum | unique1 | four 1013-----+---------+------ 1014 9 | 4 | 0 1015 16 | 2 | 2 1016 23 | 1 | 1 1017 22 | 6 | 2 1018 16 | 9 | 1 1019 15 | 8 | 0 1020 10 | 5 | 1 1021 7 | 3 | 3 1022 0 | 7 | 3 1023 | 0 | 0 1024(10 rows) 1025 1026SELECT sum(unique1) over (rows between unbounded preceding and 1 following), 1027 unique1, four 1028FROM tenk1 WHERE unique1 < 10; 1029 sum | unique1 | four 1030-----+---------+------ 1031 6 | 4 | 0 1032 7 | 2 | 2 1033 13 | 1 | 1 1034 22 | 6 | 2 1035 30 | 9 | 1 1036 35 | 8 | 0 1037 38 | 5 | 1 1038 45 | 3 | 3 1039 45 | 7 | 3 1040 45 | 0 | 0 1041(10 rows) 1042 1043SELECT sum(unique1) over (w range between current row and unbounded following), 1044 unique1, four 1045FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 1046 sum | unique1 | four 1047-----+---------+------ 1048 45 | 0 | 0 1049 45 | 8 | 0 1050 45 | 4 | 0 1051 33 | 5 | 1 1052 33 | 9 | 1 1053 33 | 1 | 1 1054 18 | 6 | 2 1055 18 | 2 | 2 1056 10 | 3 | 3 1057 10 | 7 | 3 1058(10 rows) 1059 1060SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), 1061 unique1, four 1062FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 1063 sum | unique1 | four 1064-----+---------+------ 1065 12 | 0 | 0 1066 4 | 8 | 0 1067 8 | 4 | 0 1068 22 | 5 | 1 1069 18 | 9 | 1 1070 26 | 1 | 1 1071 29 | 6 | 2 1072 33 | 2 | 2 1073 42 | 3 | 3 1074 38 | 7 | 3 1075(10 rows) 1076 1077SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), 1078 unique1, four 1079FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 1080 sum | unique1 | four 1081-----+---------+------ 1082 | 0 | 0 1083 | 8 | 0 1084 | 4 | 0 1085 12 | 5 | 1 1086 12 | 9 | 1 1087 12 | 1 | 1 1088 27 | 6 | 2 1089 27 | 2 | 2 1090 35 | 3 | 3 1091 35 | 7 | 3 1092(10 rows) 1093 1094SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), 1095 unique1, four 1096FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 1097 sum | unique1 | four 1098-----+---------+------ 1099 0 | 0 | 0 1100 8 | 8 | 0 1101 4 | 4 | 0 1102 17 | 5 | 1 1103 21 | 9 | 1 1104 13 | 1 | 1 1105 33 | 6 | 2 1106 29 | 2 | 2 1107 38 | 3 | 3 1108 42 | 7 | 3 1109(10 rows) 1110 1111SELECT first_value(unique1) over w, 1112 nth_value(unique1, 2) over w AS nth_2, 1113 last_value(unique1) over w, unique1, four 1114FROM tenk1 WHERE unique1 < 10 1115WINDOW w AS (order by four range between current row and unbounded following); 1116 first_value | nth_2 | last_value | unique1 | four 1117-------------+-------+------------+---------+------ 1118 0 | 8 | 7 | 0 | 0 1119 0 | 8 | 7 | 8 | 0 1120 0 | 8 | 7 | 4 | 0 1121 5 | 9 | 7 | 5 | 1 1122 5 | 9 | 7 | 9 | 1 1123 5 | 9 | 7 | 1 | 1 1124 6 | 2 | 7 | 6 | 2 1125 6 | 2 | 7 | 2 | 2 1126 3 | 7 | 7 | 3 | 3 1127 3 | 7 | 7 | 7 | 3 1128(10 rows) 1129 1130SELECT sum(unique1) over 1131 (order by unique1 1132 rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING), 1133 unique1 1134FROM tenk1 WHERE unique1 < 10; 1135 sum | unique1 1136-----+--------- 1137 0 | 0 1138 1 | 1 1139 3 | 2 1140 5 | 3 1141 7 | 4 1142 9 | 5 1143 11 | 6 1144 13 | 7 1145 15 | 8 1146 17 | 9 1147(10 rows) 1148 1149CREATE TEMP VIEW v_window AS 1150 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows 1151 FROM generate_series(1, 10) i; 1152SELECT * FROM v_window; 1153 i | sum_rows 1154----+---------- 1155 1 | 3 1156 2 | 6 1157 3 | 9 1158 4 | 12 1159 5 | 15 1160 6 | 18 1161 7 | 21 1162 8 | 24 1163 9 | 27 1164 10 | 19 1165(10 rows) 1166 1167SELECT pg_get_viewdef('v_window'); 1168 pg_get_viewdef 1169--------------------------------------------------------------------------------------- 1170 SELECT i.i, + 1171 sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ 1172 FROM generate_series(1, 10) i(i); 1173(1 row) 1174 1175CREATE OR REPLACE TEMP VIEW v_window AS 1176 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following 1177 exclude current row) as sum_rows FROM generate_series(1, 10) i; 1178SELECT * FROM v_window; 1179 i | sum_rows 1180----+---------- 1181 1 | 2 1182 2 | 4 1183 3 | 6 1184 4 | 8 1185 5 | 10 1186 6 | 12 1187 7 | 14 1188 8 | 16 1189 9 | 18 1190 10 | 9 1191(10 rows) 1192 1193SELECT pg_get_viewdef('v_window'); 1194 pg_get_viewdef 1195----------------------------------------------------------------------------------------------------------- 1196 SELECT i.i, + 1197 sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS sum_rows+ 1198 FROM generate_series(1, 10) i(i); 1199(1 row) 1200 1201CREATE OR REPLACE TEMP VIEW v_window AS 1202 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following 1203 exclude group) as sum_rows FROM generate_series(1, 10) i; 1204SELECT * FROM v_window; 1205 i | sum_rows 1206----+---------- 1207 1 | 2 1208 2 | 4 1209 3 | 6 1210 4 | 8 1211 5 | 10 1212 6 | 12 1213 7 | 14 1214 8 | 16 1215 9 | 18 1216 10 | 9 1217(10 rows) 1218 1219SELECT pg_get_viewdef('v_window'); 1220 pg_get_viewdef 1221----------------------------------------------------------------------------------------------------- 1222 SELECT i.i, + 1223 sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS sum_rows+ 1224 FROM generate_series(1, 10) i(i); 1225(1 row) 1226 1227CREATE OR REPLACE TEMP VIEW v_window AS 1228 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following 1229 exclude ties) as sum_rows FROM generate_series(1, 10) i; 1230SELECT * FROM v_window; 1231 i | sum_rows 1232----+---------- 1233 1 | 3 1234 2 | 6 1235 3 | 9 1236 4 | 12 1237 5 | 15 1238 6 | 18 1239 7 | 21 1240 8 | 24 1241 9 | 27 1242 10 | 19 1243(10 rows) 1244 1245SELECT pg_get_viewdef('v_window'); 1246 pg_get_viewdef 1247---------------------------------------------------------------------------------------------------- 1248 SELECT i.i, + 1249 sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS sum_rows+ 1250 FROM generate_series(1, 10) i(i); 1251(1 row) 1252 1253CREATE OR REPLACE TEMP VIEW v_window AS 1254 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following 1255 exclude no others) as sum_rows FROM generate_series(1, 10) i; 1256SELECT * FROM v_window; 1257 i | sum_rows 1258----+---------- 1259 1 | 3 1260 2 | 6 1261 3 | 9 1262 4 | 12 1263 5 | 15 1264 6 | 18 1265 7 | 21 1266 8 | 24 1267 9 | 27 1268 10 | 19 1269(10 rows) 1270 1271SELECT pg_get_viewdef('v_window'); 1272 pg_get_viewdef 1273--------------------------------------------------------------------------------------- 1274 SELECT i.i, + 1275 sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ 1276 FROM generate_series(1, 10) i(i); 1277(1 row) 1278 1279CREATE OR REPLACE TEMP VIEW v_window AS 1280 SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following) as sum_rows FROM generate_series(1, 10) i; 1281SELECT * FROM v_window; 1282 i | sum_rows 1283----+---------- 1284 1 | 3 1285 2 | 6 1286 3 | 9 1287 4 | 12 1288 5 | 15 1289 6 | 18 1290 7 | 21 1291 8 | 24 1292 9 | 27 1293 10 | 19 1294(10 rows) 1295 1296SELECT pg_get_viewdef('v_window'); 1297 pg_get_viewdef 1298----------------------------------------------------------------------------------------- 1299 SELECT i.i, + 1300 sum(i.i) OVER (ORDER BY i.i GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ 1301 FROM generate_series(1, 10) i(i); 1302(1 row) 1303 1304DROP VIEW v_window; 1305CREATE TEMP VIEW v_window AS 1306 SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i 1307 FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i; 1308SELECT pg_get_viewdef('v_window'); 1309 pg_get_viewdef 1310--------------------------------------------------------------------------------------------------------------------------- 1311 SELECT i.i, + 1312 min(i.i) OVER (ORDER BY i.i RANGE BETWEEN '@ 1 day'::interval PRECEDING AND '@ 10 days'::interval FOLLOWING) AS min_i+ 1313 FROM generate_series(now(), (now() + '@ 100 days'::interval), '@ 1 hour'::interval) i(i); 1314(1 row) 1315 1316-- RANGE offset PRECEDING/FOLLOWING tests 1317SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), 1318 unique1, four 1319FROM tenk1 WHERE unique1 < 10; 1320 sum | unique1 | four 1321-----+---------+------ 1322 | 0 | 0 1323 | 8 | 0 1324 | 4 | 0 1325 12 | 5 | 1 1326 12 | 9 | 1 1327 12 | 1 | 1 1328 27 | 6 | 2 1329 27 | 2 | 2 1330 23 | 3 | 3 1331 23 | 7 | 3 1332(10 rows) 1333 1334SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), 1335 unique1, four 1336FROM tenk1 WHERE unique1 < 10; 1337 sum | unique1 | four 1338-----+---------+------ 1339 | 3 | 3 1340 | 7 | 3 1341 10 | 6 | 2 1342 10 | 2 | 2 1343 18 | 9 | 1 1344 18 | 5 | 1 1345 18 | 1 | 1 1346 23 | 0 | 0 1347 23 | 8 | 0 1348 23 | 4 | 0 1349(10 rows) 1350 1351SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), 1352 unique1, four 1353FROM tenk1 WHERE unique1 < 10; 1354 sum | unique1 | four 1355-----+---------+------ 1356 | 0 | 0 1357 | 8 | 0 1358 | 4 | 0 1359 12 | 5 | 1 1360 12 | 9 | 1 1361 12 | 1 | 1 1362 27 | 6 | 2 1363 27 | 2 | 2 1364 23 | 3 | 3 1365 23 | 7 | 3 1366(10 rows) 1367 1368SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), 1369 unique1, four 1370FROM tenk1 WHERE unique1 < 10; 1371 sum | unique1 | four 1372-----+---------+------ 1373 | 0 | 0 1374 | 8 | 0 1375 | 4 | 0 1376 12 | 5 | 1 1377 12 | 9 | 1 1378 12 | 1 | 1 1379 27 | 6 | 2 1380 27 | 2 | 2 1381 23 | 3 | 3 1382 23 | 7 | 3 1383(10 rows) 1384 1385SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), 1386 unique1, four 1387FROM tenk1 WHERE unique1 < 10; 1388 sum | unique1 | four 1389-----+---------+------ 1390 | 0 | 0 1391 | 8 | 0 1392 | 4 | 0 1393 12 | 5 | 1 1394 12 | 9 | 1 1395 12 | 1 | 1 1396 27 | 6 | 2 1397 27 | 2 | 2 1398 23 | 3 | 3 1399 23 | 7 | 3 1400(10 rows) 1401 1402SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), 1403 unique1, four 1404FROM tenk1 WHERE unique1 < 10; 1405 sum | unique1 | four 1406-----+---------+------ 1407 | 0 | 0 1408 | 8 | 0 1409 | 4 | 0 1410 12 | 5 | 1 1411 12 | 9 | 1 1412 12 | 1 | 1 1413 27 | 6 | 2 1414 27 | 2 | 2 1415 23 | 3 | 3 1416 23 | 7 | 3 1417(10 rows) 1418 1419SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), 1420 unique1, four 1421FROM tenk1 WHERE unique1 < 10; 1422 sum | unique1 | four 1423-----+---------+------ 1424 33 | 0 | 0 1425 41 | 8 | 0 1426 37 | 4 | 0 1427 35 | 5 | 1 1428 39 | 9 | 1 1429 31 | 1 | 1 1430 43 | 6 | 2 1431 39 | 2 | 2 1432 26 | 3 | 3 1433 30 | 7 | 3 1434(10 rows) 1435 1436SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), 1437 unique1, four 1438FROM tenk1 WHERE unique1 < 10; 1439 sum | unique1 | four 1440-----+---------+------ 1441 33 | 0 | 0 1442 33 | 8 | 0 1443 33 | 4 | 0 1444 30 | 5 | 1 1445 30 | 9 | 1 1446 30 | 1 | 1 1447 37 | 6 | 2 1448 37 | 2 | 2 1449 23 | 3 | 3 1450 23 | 7 | 3 1451(10 rows) 1452 1453SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), 1454 unique1, four 1455FROM tenk1 WHERE unique1 < 10; 1456 sum | unique1 | four 1457-----+---------+------ 1458 4 | 0 | 0 1459 12 | 4 | 0 1460 12 | 8 | 0 1461 6 | 1 | 1 1462 15 | 5 | 1 1463 14 | 9 | 1 1464 8 | 2 | 2 1465 8 | 6 | 2 1466 10 | 3 | 3 1467 10 | 7 | 3 1468(10 rows) 1469 1470SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following 1471 exclude current row),unique1, four 1472FROM tenk1 WHERE unique1 < 10; 1473 sum | unique1 | four 1474-----+---------+------ 1475 4 | 0 | 0 1476 8 | 4 | 0 1477 4 | 8 | 0 1478 5 | 1 | 1 1479 10 | 5 | 1 1480 5 | 9 | 1 1481 6 | 2 | 2 1482 2 | 6 | 2 1483 7 | 3 | 3 1484 3 | 7 | 3 1485(10 rows) 1486 1487select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), 1488 salary, enroll_date from empsalary; 1489 sum | salary | enroll_date 1490-------+--------+------------- 1491 34900 | 5000 | 10-01-2006 1492 34900 | 6000 | 10-01-2006 1493 38400 | 3900 | 12-23-2006 1494 47100 | 4800 | 08-01-2007 1495 47100 | 5200 | 08-01-2007 1496 47100 | 4800 | 08-08-2007 1497 47100 | 5200 | 08-15-2007 1498 36100 | 3500 | 12-10-2007 1499 32200 | 4500 | 01-01-2008 1500 32200 | 4200 | 01-01-2008 1501(10 rows) 1502 1503select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), 1504 salary, enroll_date from empsalary; 1505 sum | salary | enroll_date 1506-------+--------+------------- 1507 32200 | 4200 | 01-01-2008 1508 32200 | 4500 | 01-01-2008 1509 36100 | 3500 | 12-10-2007 1510 47100 | 5200 | 08-15-2007 1511 47100 | 4800 | 08-08-2007 1512 47100 | 4800 | 08-01-2007 1513 47100 | 5200 | 08-01-2007 1514 38400 | 3900 | 12-23-2006 1515 34900 | 5000 | 10-01-2006 1516 34900 | 6000 | 10-01-2006 1517(10 rows) 1518 1519select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), 1520 salary, enroll_date from empsalary; 1521 sum | salary | enroll_date 1522-----+--------+------------- 1523 | 4200 | 01-01-2008 1524 | 4500 | 01-01-2008 1525 | 3500 | 12-10-2007 1526 | 5200 | 08-15-2007 1527 | 4800 | 08-08-2007 1528 | 4800 | 08-01-2007 1529 | 5200 | 08-01-2007 1530 | 3900 | 12-23-2006 1531 | 5000 | 10-01-2006 1532 | 6000 | 10-01-2006 1533(10 rows) 1534 1535select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following 1536 exclude current row), salary, enroll_date from empsalary; 1537 sum | salary | enroll_date 1538-------+--------+------------- 1539 29900 | 5000 | 10-01-2006 1540 28900 | 6000 | 10-01-2006 1541 34500 | 3900 | 12-23-2006 1542 42300 | 4800 | 08-01-2007 1543 41900 | 5200 | 08-01-2007 1544 42300 | 4800 | 08-08-2007 1545 41900 | 5200 | 08-15-2007 1546 32600 | 3500 | 12-10-2007 1547 27700 | 4500 | 01-01-2008 1548 28000 | 4200 | 01-01-2008 1549(10 rows) 1550 1551select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following 1552 exclude group), salary, enroll_date from empsalary; 1553 sum | salary | enroll_date 1554-------+--------+------------- 1555 23900 | 5000 | 10-01-2006 1556 23900 | 6000 | 10-01-2006 1557 34500 | 3900 | 12-23-2006 1558 37100 | 4800 | 08-01-2007 1559 37100 | 5200 | 08-01-2007 1560 42300 | 4800 | 08-08-2007 1561 41900 | 5200 | 08-15-2007 1562 32600 | 3500 | 12-10-2007 1563 23500 | 4500 | 01-01-2008 1564 23500 | 4200 | 01-01-2008 1565(10 rows) 1566 1567select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following 1568 exclude ties), salary, enroll_date from empsalary; 1569 sum | salary | enroll_date 1570-------+--------+------------- 1571 28900 | 5000 | 10-01-2006 1572 29900 | 6000 | 10-01-2006 1573 38400 | 3900 | 12-23-2006 1574 41900 | 4800 | 08-01-2007 1575 42300 | 5200 | 08-01-2007 1576 47100 | 4800 | 08-08-2007 1577 47100 | 5200 | 08-15-2007 1578 36100 | 3500 | 12-10-2007 1579 28000 | 4500 | 01-01-2008 1580 27700 | 4200 | 01-01-2008 1581(10 rows) 1582 1583select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), 1584 lead(salary) over(order by salary range between 1000 preceding and 1000 following), 1585 nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), 1586 salary from empsalary; 1587 first_value | lead | nth_value | salary 1588-------------+------+-----------+-------- 1589 3500 | 3900 | 3500 | 3500 1590 3500 | 4200 | 3500 | 3900 1591 3500 | 4500 | 3500 | 4200 1592 3500 | 4800 | 3500 | 4500 1593 3900 | 4800 | 3900 | 4800 1594 3900 | 5000 | 3900 | 4800 1595 4200 | 5200 | 4200 | 5000 1596 4200 | 5200 | 4200 | 5200 1597 4200 | 6000 | 4200 | 5200 1598 5000 | | 5000 | 6000 1599(10 rows) 1600 1601select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), 1602 lag(salary) over(order by salary range between 1000 preceding and 1000 following), 1603 salary from empsalary; 1604 last_value | lag | salary 1605------------+------+-------- 1606 4500 | | 3500 1607 4800 | 3500 | 3900 1608 5200 | 3900 | 4200 1609 5200 | 4200 | 4500 1610 5200 | 4500 | 4800 1611 5200 | 4800 | 4800 1612 6000 | 4800 | 5000 1613 6000 | 5000 | 5200 1614 6000 | 5200 | 5200 1615 6000 | 5200 | 6000 1616(10 rows) 1617 1618select first_value(salary) over(order by salary range between 1000 following and 3000 following 1619 exclude current row), 1620 lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), 1621 nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following 1622 exclude ties), 1623 salary from empsalary; 1624 first_value | lead | nth_value | salary 1625-------------+------+-----------+-------- 1626 4500 | 3900 | 4500 | 3500 1627 5000 | 4200 | 5000 | 3900 1628 5200 | 4500 | 5200 | 4200 1629 6000 | 4800 | 6000 | 4500 1630 6000 | 4800 | 6000 | 4800 1631 6000 | 5000 | 6000 | 4800 1632 6000 | 5200 | 6000 | 5000 1633 | 5200 | | 5200 1634 | 6000 | | 5200 1635 | | | 6000 1636(10 rows) 1637 1638select last_value(salary) over(order by salary range between 1000 following and 3000 following 1639 exclude group), 1640 lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), 1641 salary from empsalary; 1642 last_value | lag | salary 1643------------+------+-------- 1644 6000 | | 3500 1645 6000 | 3500 | 3900 1646 6000 | 3900 | 4200 1647 6000 | 4200 | 4500 1648 6000 | 4500 | 4800 1649 6000 | 4800 | 4800 1650 6000 | 4800 | 5000 1651 | 5000 | 5200 1652 | 5200 | 5200 1653 | 5200 | 6000 1654(10 rows) 1655 1656select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 1657 exclude ties), 1658 last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), 1659 salary, enroll_date from empsalary; 1660 first_value | last_value | salary | enroll_date 1661-------------+------------+--------+------------- 1662 5000 | 5200 | 5000 | 10-01-2006 1663 6000 | 5200 | 6000 | 10-01-2006 1664 5000 | 3500 | 3900 | 12-23-2006 1665 5000 | 4200 | 4800 | 08-01-2007 1666 5000 | 4200 | 5200 | 08-01-2007 1667 5000 | 4200 | 4800 | 08-08-2007 1668 5000 | 4200 | 5200 | 08-15-2007 1669 5000 | 4200 | 3500 | 12-10-2007 1670 5000 | 4200 | 4500 | 01-01-2008 1671 5000 | 4200 | 4200 | 01-01-2008 1672(10 rows) 1673 1674select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 1675 exclude ties), 1676 last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 1677 exclude ties), 1678 salary, enroll_date from empsalary; 1679 first_value | last_value | salary | enroll_date 1680-------------+------------+--------+------------- 1681 5000 | 5200 | 5000 | 10-01-2006 1682 6000 | 5200 | 6000 | 10-01-2006 1683 5000 | 3500 | 3900 | 12-23-2006 1684 5000 | 4200 | 4800 | 08-01-2007 1685 5000 | 4200 | 5200 | 08-01-2007 1686 5000 | 4200 | 4800 | 08-08-2007 1687 5000 | 4200 | 5200 | 08-15-2007 1688 5000 | 4200 | 3500 | 12-10-2007 1689 5000 | 4500 | 4500 | 01-01-2008 1690 5000 | 4200 | 4200 | 01-01-2008 1691(10 rows) 1692 1693select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 1694 exclude group), 1695 last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 1696 exclude group), 1697 salary, enroll_date from empsalary; 1698 first_value | last_value | salary | enroll_date 1699-------------+------------+--------+------------- 1700 3900 | 5200 | 5000 | 10-01-2006 1701 3900 | 5200 | 6000 | 10-01-2006 1702 5000 | 3500 | 3900 | 12-23-2006 1703 5000 | 4200 | 4800 | 08-01-2007 1704 5000 | 4200 | 5200 | 08-01-2007 1705 5000 | 4200 | 4800 | 08-08-2007 1706 5000 | 4200 | 5200 | 08-15-2007 1707 5000 | 4200 | 3500 | 12-10-2007 1708 5000 | 3500 | 4500 | 01-01-2008 1709 5000 | 3500 | 4200 | 01-01-2008 1710(10 rows) 1711 1712select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 1713 exclude current row), 1714 last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 1715 exclude current row), 1716 salary, enroll_date from empsalary; 1717 first_value | last_value | salary | enroll_date 1718-------------+------------+--------+------------- 1719 6000 | 5200 | 5000 | 10-01-2006 1720 5000 | 5200 | 6000 | 10-01-2006 1721 5000 | 3500 | 3900 | 12-23-2006 1722 5000 | 4200 | 4800 | 08-01-2007 1723 5000 | 4200 | 5200 | 08-01-2007 1724 5000 | 4200 | 4800 | 08-08-2007 1725 5000 | 4200 | 5200 | 08-15-2007 1726 5000 | 4200 | 3500 | 12-10-2007 1727 5000 | 4200 | 4500 | 01-01-2008 1728 5000 | 4500 | 4200 | 01-01-2008 1729(10 rows) 1730 1731-- RANGE offset PRECEDING/FOLLOWING with null values 1732select x, y, 1733 first_value(y) over w, 1734 last_value(y) over w 1735from 1736 (select x, x as y from generate_series(1,5) as x 1737 union all select null, 42 1738 union all select null, 43) ss 1739window w as 1740 (order by x asc nulls first range between 2 preceding and 2 following); 1741 x | y | first_value | last_value 1742---+----+-------------+------------ 1743 | 42 | 42 | 43 1744 | 43 | 42 | 43 1745 1 | 1 | 1 | 3 1746 2 | 2 | 1 | 4 1747 3 | 3 | 1 | 5 1748 4 | 4 | 2 | 5 1749 5 | 5 | 3 | 5 1750(7 rows) 1751 1752select x, y, 1753 first_value(y) over w, 1754 last_value(y) over w 1755from 1756 (select x, x as y from generate_series(1,5) as x 1757 union all select null, 42 1758 union all select null, 43) ss 1759window w as 1760 (order by x asc nulls last range between 2 preceding and 2 following); 1761 x | y | first_value | last_value 1762---+----+-------------+------------ 1763 1 | 1 | 1 | 3 1764 2 | 2 | 1 | 4 1765 3 | 3 | 1 | 5 1766 4 | 4 | 2 | 5 1767 5 | 5 | 3 | 5 1768 | 42 | 42 | 43 1769 | 43 | 42 | 43 1770(7 rows) 1771 1772select x, y, 1773 first_value(y) over w, 1774 last_value(y) over w 1775from 1776 (select x, x as y from generate_series(1,5) as x 1777 union all select null, 42 1778 union all select null, 43) ss 1779window w as 1780 (order by x desc nulls first range between 2 preceding and 2 following); 1781 x | y | first_value | last_value 1782---+----+-------------+------------ 1783 | 43 | 43 | 42 1784 | 42 | 43 | 42 1785 5 | 5 | 5 | 3 1786 4 | 4 | 5 | 2 1787 3 | 3 | 5 | 1 1788 2 | 2 | 4 | 1 1789 1 | 1 | 3 | 1 1790(7 rows) 1791 1792select x, y, 1793 first_value(y) over w, 1794 last_value(y) over w 1795from 1796 (select x, x as y from generate_series(1,5) as x 1797 union all select null, 42 1798 union all select null, 43) ss 1799window w as 1800 (order by x desc nulls last range between 2 preceding and 2 following); 1801 x | y | first_value | last_value 1802---+----+-------------+------------ 1803 5 | 5 | 5 | 3 1804 4 | 4 | 5 | 2 1805 3 | 3 | 5 | 1 1806 2 | 2 | 4 | 1 1807 1 | 1 | 3 | 1 1808 | 42 | 42 | 43 1809 | 43 | 42 | 43 1810(7 rows) 1811 1812-- Check overflow behavior for various integer sizes 1813select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following) 1814from generate_series(32764, 32766) x; 1815 x | last_value 1816-------+------------ 1817 32764 | 32766 1818 32765 | 32766 1819 32766 | 32766 1820(3 rows) 1821 1822select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following) 1823from generate_series(-32766, -32764) x; 1824 x | last_value 1825--------+------------ 1826 -32764 | -32766 1827 -32765 | -32766 1828 -32766 | -32766 1829(3 rows) 1830 1831select x, last_value(x) over (order by x range between current row and 4 following) 1832from generate_series(2147483644, 2147483646) x; 1833 x | last_value 1834------------+------------ 1835 2147483644 | 2147483646 1836 2147483645 | 2147483646 1837 2147483646 | 2147483646 1838(3 rows) 1839 1840select x, last_value(x) over (order by x desc range between current row and 5 following) 1841from generate_series(-2147483646, -2147483644) x; 1842 x | last_value 1843-------------+------------- 1844 -2147483644 | -2147483646 1845 -2147483645 | -2147483646 1846 -2147483646 | -2147483646 1847(3 rows) 1848 1849select x, last_value(x) over (order by x range between current row and 4 following) 1850from generate_series(9223372036854775804, 9223372036854775806) x; 1851 x | last_value 1852---------------------+--------------------- 1853 9223372036854775804 | 9223372036854775806 1854 9223372036854775805 | 9223372036854775806 1855 9223372036854775806 | 9223372036854775806 1856(3 rows) 1857 1858select x, last_value(x) over (order by x desc range between current row and 5 following) 1859from generate_series(-9223372036854775806, -9223372036854775804) x; 1860 x | last_value 1861----------------------+---------------------- 1862 -9223372036854775804 | -9223372036854775806 1863 -9223372036854775805 | -9223372036854775806 1864 -9223372036854775806 | -9223372036854775806 1865(3 rows) 1866 1867-- Test in_range for other numeric datatypes 1868create temp table numerics( 1869 id int, 1870 f_float4 float4, 1871 f_float8 float8, 1872 f_numeric numeric 1873); 1874insert into numerics values 1875(0, '-infinity', '-infinity', '-1000'), -- numeric type lacks infinities 1876(1, -3, -3, -3), 1877(2, -1, -1, -1), 1878(3, 0, 0, 0), 1879(4, 1.1, 1.1, 1.1), 1880(5, 1.12, 1.12, 1.12), 1881(6, 2, 2, 2), 1882(7, 100, 100, 100), 1883(8, 'infinity', 'infinity', '1000'), 1884(9, 'NaN', 'NaN', 'NaN'); 1885select id, f_float4, first_value(id) over w, last_value(id) over w 1886from numerics 1887window w as (order by f_float4 range between 1888 1 preceding and 1 following); 1889 id | f_float4 | first_value | last_value 1890----+-----------+-------------+------------ 1891 0 | -Infinity | 0 | 0 1892 1 | -3 | 1 | 1 1893 2 | -1 | 2 | 3 1894 3 | 0 | 2 | 3 1895 4 | 1.1 | 4 | 6 1896 5 | 1.12 | 4 | 6 1897 6 | 2 | 4 | 6 1898 7 | 100 | 7 | 7 1899 8 | Infinity | 8 | 8 1900 9 | NaN | 9 | 9 1901(10 rows) 1902 1903select id, f_float4, first_value(id) over w, last_value(id) over w 1904from numerics 1905window w as (order by f_float4 range between 1906 1 preceding and 1.1::float4 following); 1907 id | f_float4 | first_value | last_value 1908----+-----------+-------------+------------ 1909 0 | -Infinity | 0 | 0 1910 1 | -3 | 1 | 1 1911 2 | -1 | 2 | 3 1912 3 | 0 | 2 | 4 1913 4 | 1.1 | 4 | 6 1914 5 | 1.12 | 4 | 6 1915 6 | 2 | 4 | 6 1916 7 | 100 | 7 | 7 1917 8 | Infinity | 8 | 8 1918 9 | NaN | 9 | 9 1919(10 rows) 1920 1921select id, f_float4, first_value(id) over w, last_value(id) over w 1922from numerics 1923window w as (order by f_float4 range between 1924 'inf' preceding and 'inf' following); 1925 id | f_float4 | first_value | last_value 1926----+-----------+-------------+------------ 1927 0 | -Infinity | 0 | 8 1928 1 | -3 | 0 | 8 1929 2 | -1 | 0 | 8 1930 3 | 0 | 0 | 8 1931 4 | 1.1 | 0 | 8 1932 5 | 1.12 | 0 | 8 1933 6 | 2 | 0 | 8 1934 7 | 100 | 0 | 8 1935 8 | Infinity | 0 | 8 1936 9 | NaN | 9 | 9 1937(10 rows) 1938 1939select id, f_float4, first_value(id) over w, last_value(id) over w 1940from numerics 1941window w as (order by f_float4 range between 1942 1.1 preceding and 'NaN' following); -- error, NaN disallowed 1943ERROR: invalid preceding or following size in window function 1944select id, f_float8, first_value(id) over w, last_value(id) over w 1945from numerics 1946window w as (order by f_float8 range between 1947 1 preceding and 1 following); 1948 id | f_float8 | first_value | last_value 1949----+-----------+-------------+------------ 1950 0 | -Infinity | 0 | 0 1951 1 | -3 | 1 | 1 1952 2 | -1 | 2 | 3 1953 3 | 0 | 2 | 3 1954 4 | 1.1 | 4 | 6 1955 5 | 1.12 | 4 | 6 1956 6 | 2 | 4 | 6 1957 7 | 100 | 7 | 7 1958 8 | Infinity | 8 | 8 1959 9 | NaN | 9 | 9 1960(10 rows) 1961 1962select id, f_float8, first_value(id) over w, last_value(id) over w 1963from numerics 1964window w as (order by f_float8 range between 1965 1 preceding and 1.1::float8 following); 1966 id | f_float8 | first_value | last_value 1967----+-----------+-------------+------------ 1968 0 | -Infinity | 0 | 0 1969 1 | -3 | 1 | 1 1970 2 | -1 | 2 | 3 1971 3 | 0 | 2 | 4 1972 4 | 1.1 | 4 | 6 1973 5 | 1.12 | 4 | 6 1974 6 | 2 | 4 | 6 1975 7 | 100 | 7 | 7 1976 8 | Infinity | 8 | 8 1977 9 | NaN | 9 | 9 1978(10 rows) 1979 1980select id, f_float8, first_value(id) over w, last_value(id) over w 1981from numerics 1982window w as (order by f_float8 range between 1983 'inf' preceding and 'inf' following); 1984 id | f_float8 | first_value | last_value 1985----+-----------+-------------+------------ 1986 0 | -Infinity | 0 | 8 1987 1 | -3 | 0 | 8 1988 2 | -1 | 0 | 8 1989 3 | 0 | 0 | 8 1990 4 | 1.1 | 0 | 8 1991 5 | 1.12 | 0 | 8 1992 6 | 2 | 0 | 8 1993 7 | 100 | 0 | 8 1994 8 | Infinity | 0 | 8 1995 9 | NaN | 9 | 9 1996(10 rows) 1997 1998select id, f_float8, first_value(id) over w, last_value(id) over w 1999from numerics 2000window w as (order by f_float8 range between 2001 1.1 preceding and 'NaN' following); -- error, NaN disallowed 2002ERROR: invalid preceding or following size in window function 2003select id, f_numeric, first_value(id) over w, last_value(id) over w 2004from numerics 2005window w as (order by f_numeric range between 2006 1 preceding and 1 following); 2007 id | f_numeric | first_value | last_value 2008----+-----------+-------------+------------ 2009 0 | -1000 | 0 | 0 2010 1 | -3 | 1 | 1 2011 2 | -1 | 2 | 3 2012 3 | 0 | 2 | 3 2013 4 | 1.1 | 4 | 6 2014 5 | 1.12 | 4 | 6 2015 6 | 2 | 4 | 6 2016 7 | 100 | 7 | 7 2017 8 | 1000 | 8 | 8 2018 9 | NaN | 9 | 9 2019(10 rows) 2020 2021select id, f_numeric, first_value(id) over w, last_value(id) over w 2022from numerics 2023window w as (order by f_numeric range between 2024 1 preceding and 1.1::numeric following); 2025 id | f_numeric | first_value | last_value 2026----+-----------+-------------+------------ 2027 0 | -1000 | 0 | 0 2028 1 | -3 | 1 | 1 2029 2 | -1 | 2 | 3 2030 3 | 0 | 2 | 4 2031 4 | 1.1 | 4 | 6 2032 5 | 1.12 | 4 | 6 2033 6 | 2 | 4 | 6 2034 7 | 100 | 7 | 7 2035 8 | 1000 | 8 | 8 2036 9 | NaN | 9 | 9 2037(10 rows) 2038 2039select id, f_numeric, first_value(id) over w, last_value(id) over w 2040from numerics 2041window w as (order by f_numeric range between 2042 1 preceding and 1.1::float8 following); -- currently unsupported 2043ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type numeric and offset type double precision 2044LINE 4: 1 preceding and 1.1::float8 following); 2045 ^ 2046HINT: Cast the offset value to an appropriate type. 2047select id, f_numeric, first_value(id) over w, last_value(id) over w 2048from numerics 2049window w as (order by f_numeric range between 2050 1.1 preceding and 'NaN' following); -- error, NaN disallowed 2051ERROR: invalid preceding or following size in window function 2052-- Test in_range for other datetime datatypes 2053create temp table datetimes( 2054 id int, 2055 f_time time, 2056 f_timetz timetz, 2057 f_interval interval, 2058 f_timestamptz timestamptz, 2059 f_timestamp timestamp 2060); 2061insert into datetimes values 2062(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'), 2063(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), 2064(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), 2065(4, '14:00', '14:00 BST', '4 years', '2002-10-19 10:23:54+01', '2002-10-19 10:23:54'), 2066(5, '15:00', '15:00 BST', '5 years', '2003-10-19 10:23:54+01', '2003-10-19 10:23:54'), 2067(6, '15:00', '15:00 BST', '5 years', '2004-10-19 10:23:54+01', '2004-10-19 10:23:54'), 2068(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'), 2069(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'), 2070(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'), 2071(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54'); 2072select id, f_time, first_value(id) over w, last_value(id) over w 2073from datetimes 2074window w as (order by f_time range between 2075 '70 min'::interval preceding and '2 hours'::interval following); 2076 id | f_time | first_value | last_value 2077----+----------+-------------+------------ 2078 1 | 11:00:00 | 1 | 3 2079 2 | 12:00:00 | 1 | 4 2080 3 | 13:00:00 | 2 | 6 2081 4 | 14:00:00 | 3 | 6 2082 5 | 15:00:00 | 4 | 7 2083 6 | 15:00:00 | 4 | 7 2084 7 | 17:00:00 | 7 | 9 2085 8 | 18:00:00 | 7 | 10 2086 9 | 19:00:00 | 8 | 10 2087 10 | 20:00:00 | 9 | 10 2088(10 rows) 2089 2090select id, f_time, first_value(id) over w, last_value(id) over w 2091from datetimes 2092window w as (order by f_time desc range between 2093 '70 min' preceding and '2 hours' following); 2094 id | f_time | first_value | last_value 2095----+----------+-------------+------------ 2096 10 | 20:00:00 | 10 | 8 2097 9 | 19:00:00 | 10 | 7 2098 8 | 18:00:00 | 9 | 7 2099 7 | 17:00:00 | 8 | 5 2100 6 | 15:00:00 | 6 | 3 2101 5 | 15:00:00 | 6 | 3 2102 4 | 14:00:00 | 6 | 2 2103 3 | 13:00:00 | 4 | 1 2104 2 | 12:00:00 | 3 | 1 2105 1 | 11:00:00 | 2 | 1 2106(10 rows) 2107 2108select id, f_timetz, first_value(id) over w, last_value(id) over w 2109from datetimes 2110window w as (order by f_timetz range between 2111 '70 min'::interval preceding and '2 hours'::interval following); 2112 id | f_timetz | first_value | last_value 2113----+-------------+-------------+------------ 2114 1 | 11:00:00+01 | 1 | 3 2115 2 | 12:00:00+01 | 1 | 4 2116 3 | 13:00:00+01 | 2 | 6 2117 4 | 14:00:00+01 | 3 | 6 2118 5 | 15:00:00+01 | 4 | 7 2119 6 | 15:00:00+01 | 4 | 7 2120 7 | 17:00:00+01 | 7 | 9 2121 8 | 18:00:00+01 | 7 | 10 2122 9 | 19:00:00+01 | 8 | 10 2123 10 | 20:00:00+01 | 9 | 10 2124(10 rows) 2125 2126select id, f_timetz, first_value(id) over w, last_value(id) over w 2127from datetimes 2128window w as (order by f_timetz desc range between 2129 '70 min' preceding and '2 hours' following); 2130 id | f_timetz | first_value | last_value 2131----+-------------+-------------+------------ 2132 10 | 20:00:00+01 | 10 | 8 2133 9 | 19:00:00+01 | 10 | 7 2134 8 | 18:00:00+01 | 9 | 7 2135 7 | 17:00:00+01 | 8 | 5 2136 6 | 15:00:00+01 | 6 | 3 2137 5 | 15:00:00+01 | 6 | 3 2138 4 | 14:00:00+01 | 6 | 2 2139 3 | 13:00:00+01 | 4 | 1 2140 2 | 12:00:00+01 | 3 | 1 2141 1 | 11:00:00+01 | 2 | 1 2142(10 rows) 2143 2144select id, f_interval, first_value(id) over w, last_value(id) over w 2145from datetimes 2146window w as (order by f_interval range between 2147 '1 year'::interval preceding and '1 year'::interval following); 2148 id | f_interval | first_value | last_value 2149----+------------+-------------+------------ 2150 1 | @ 1 year | 1 | 2 2151 2 | @ 2 years | 1 | 3 2152 3 | @ 3 years | 2 | 4 2153 4 | @ 4 years | 3 | 6 2154 5 | @ 5 years | 4 | 6 2155 6 | @ 5 years | 4 | 6 2156 7 | @ 7 years | 7 | 8 2157 8 | @ 8 years | 7 | 9 2158 9 | @ 9 years | 8 | 10 2159 10 | @ 10 years | 9 | 10 2160(10 rows) 2161 2162select id, f_interval, first_value(id) over w, last_value(id) over w 2163from datetimes 2164window w as (order by f_interval desc range between 2165 '1 year' preceding and '1 year' following); 2166 id | f_interval | first_value | last_value 2167----+------------+-------------+------------ 2168 10 | @ 10 years | 10 | 9 2169 9 | @ 9 years | 10 | 8 2170 8 | @ 8 years | 9 | 7 2171 7 | @ 7 years | 8 | 7 2172 6 | @ 5 years | 6 | 4 2173 5 | @ 5 years | 6 | 4 2174 4 | @ 4 years | 6 | 3 2175 3 | @ 3 years | 4 | 2 2176 2 | @ 2 years | 3 | 1 2177 1 | @ 1 year | 2 | 1 2178(10 rows) 2179 2180select id, f_timestamptz, first_value(id) over w, last_value(id) over w 2181from datetimes 2182window w as (order by f_timestamptz range between 2183 '1 year'::interval preceding and '1 year'::interval following); 2184 id | f_timestamptz | first_value | last_value 2185----+------------------------------+-------------+------------ 2186 1 | Thu Oct 19 02:23:54 2000 PDT | 1 | 3 2187 2 | Fri Oct 19 02:23:54 2001 PDT | 1 | 4 2188 3 | Fri Oct 19 02:23:54 2001 PDT | 1 | 4 2189 4 | Sat Oct 19 02:23:54 2002 PDT | 2 | 5 2190 5 | Sun Oct 19 02:23:54 2003 PDT | 4 | 6 2191 6 | Tue Oct 19 02:23:54 2004 PDT | 5 | 7 2192 7 | Wed Oct 19 02:23:54 2005 PDT | 6 | 8 2193 8 | Thu Oct 19 02:23:54 2006 PDT | 7 | 9 2194 9 | Fri Oct 19 02:23:54 2007 PDT | 8 | 10 2195 10 | Sun Oct 19 02:23:54 2008 PDT | 9 | 10 2196(10 rows) 2197 2198select id, f_timestamptz, first_value(id) over w, last_value(id) over w 2199from datetimes 2200window w as (order by f_timestamptz desc range between 2201 '1 year' preceding and '1 year' following); 2202 id | f_timestamptz | first_value | last_value 2203----+------------------------------+-------------+------------ 2204 10 | Sun Oct 19 02:23:54 2008 PDT | 10 | 9 2205 9 | Fri Oct 19 02:23:54 2007 PDT | 10 | 8 2206 8 | Thu Oct 19 02:23:54 2006 PDT | 9 | 7 2207 7 | Wed Oct 19 02:23:54 2005 PDT | 8 | 6 2208 6 | Tue Oct 19 02:23:54 2004 PDT | 7 | 5 2209 5 | Sun Oct 19 02:23:54 2003 PDT | 6 | 4 2210 4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 2 2211 3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1 2212 2 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1 2213 1 | Thu Oct 19 02:23:54 2000 PDT | 3 | 1 2214(10 rows) 2215 2216select id, f_timestamp, first_value(id) over w, last_value(id) over w 2217from datetimes 2218window w as (order by f_timestamp range between 2219 '1 year'::interval preceding and '1 year'::interval following); 2220 id | f_timestamp | first_value | last_value 2221----+--------------------------+-------------+------------ 2222 1 | Thu Oct 19 10:23:54 2000 | 1 | 3 2223 2 | Fri Oct 19 10:23:54 2001 | 1 | 4 2224 3 | Fri Oct 19 10:23:54 2001 | 1 | 4 2225 4 | Sat Oct 19 10:23:54 2002 | 2 | 5 2226 5 | Sun Oct 19 10:23:54 2003 | 4 | 6 2227 6 | Tue Oct 19 10:23:54 2004 | 5 | 7 2228 7 | Wed Oct 19 10:23:54 2005 | 6 | 8 2229 8 | Thu Oct 19 10:23:54 2006 | 7 | 9 2230 9 | Fri Oct 19 10:23:54 2007 | 8 | 10 2231 10 | Sun Oct 19 10:23:54 2008 | 9 | 10 2232(10 rows) 2233 2234select id, f_timestamp, first_value(id) over w, last_value(id) over w 2235from datetimes 2236window w as (order by f_timestamp desc range between 2237 '1 year' preceding and '1 year' following); 2238 id | f_timestamp | first_value | last_value 2239----+--------------------------+-------------+------------ 2240 10 | Sun Oct 19 10:23:54 2008 | 10 | 9 2241 9 | Fri Oct 19 10:23:54 2007 | 10 | 8 2242 8 | Thu Oct 19 10:23:54 2006 | 9 | 7 2243 7 | Wed Oct 19 10:23:54 2005 | 8 | 6 2244 6 | Tue Oct 19 10:23:54 2004 | 7 | 5 2245 5 | Sun Oct 19 10:23:54 2003 | 6 | 4 2246 4 | Sat Oct 19 10:23:54 2002 | 5 | 2 2247 3 | Fri Oct 19 10:23:54 2001 | 4 | 1 2248 2 | Fri Oct 19 10:23:54 2001 | 4 | 1 2249 1 | Thu Oct 19 10:23:54 2000 | 3 | 1 2250(10 rows) 2251 2252-- RANGE offset PRECEDING/FOLLOWING error cases 2253select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following 2254 exclude ties), salary, enroll_date from empsalary; 2255ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column 2256LINE 1: select sum(salary) over (order by enroll_date, salary range ... 2257 ^ 2258select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following 2259 exclude ties), salary, enroll_date from empsalary; 2260ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column 2261LINE 1: select sum(salary) over (range between '1 year'::interval pr... 2262 ^ 2263select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following 2264 exclude ties), salary, enroll_date from empsalary; 2265ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type text 2266LINE 1: ... sum(salary) over (order by depname range between '1 year'::... 2267 ^ 2268select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following 2269 exclude ties), salary, enroll_date from empsalary; 2270ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type date and offset type integer 2271LINE 1: ...ll_date) over (order by enroll_date range between 1 precedin... 2272 ^ 2273HINT: Cast the offset value to an appropriate type. 2274select max(enroll_date) over (order by salary range between -1 preceding and 2 following 2275 exclude ties), salary, enroll_date from empsalary; 2276ERROR: invalid preceding or following size in window function 2277select max(enroll_date) over (order by salary range between 1 preceding and -2 following 2278 exclude ties), salary, enroll_date from empsalary; 2279ERROR: invalid preceding or following size in window function 2280select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following 2281 exclude ties), salary, enroll_date from empsalary; 2282ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type integer and offset type interval 2283LINE 1: ...(enroll_date) over (order by salary range between '1 year'::... 2284 ^ 2285HINT: Cast the offset value to an appropriate type. 2286select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following 2287 exclude ties), salary, enroll_date from empsalary; 2288ERROR: invalid preceding or following size in window function 2289-- GROUPS tests 2290SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), 2291 unique1, four 2292FROM tenk1 WHERE unique1 < 10; 2293 sum | unique1 | four 2294-----+---------+------ 2295 12 | 0 | 0 2296 12 | 8 | 0 2297 12 | 4 | 0 2298 27 | 5 | 1 2299 27 | 9 | 1 2300 27 | 1 | 1 2301 35 | 6 | 2 2302 35 | 2 | 2 2303 45 | 3 | 3 2304 45 | 7 | 3 2305(10 rows) 2306 2307SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), 2308 unique1, four 2309FROM tenk1 WHERE unique1 < 10; 2310 sum | unique1 | four 2311-----+---------+------ 2312 45 | 0 | 0 2313 45 | 8 | 0 2314 45 | 4 | 0 2315 45 | 5 | 1 2316 45 | 9 | 1 2317 45 | 1 | 1 2318 45 | 6 | 2 2319 45 | 2 | 2 2320 45 | 3 | 3 2321 45 | 7 | 3 2322(10 rows) 2323 2324SELECT sum(unique1) over (order by four groups between current row and unbounded following), 2325 unique1, four 2326FROM tenk1 WHERE unique1 < 10; 2327 sum | unique1 | four 2328-----+---------+------ 2329 45 | 0 | 0 2330 45 | 8 | 0 2331 45 | 4 | 0 2332 33 | 5 | 1 2333 33 | 9 | 1 2334 33 | 1 | 1 2335 18 | 6 | 2 2336 18 | 2 | 2 2337 10 | 3 | 3 2338 10 | 7 | 3 2339(10 rows) 2340 2341SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), 2342 unique1, four 2343FROM tenk1 WHERE unique1 < 10; 2344 sum | unique1 | four 2345-----+---------+------ 2346 45 | 0 | 0 2347 45 | 8 | 0 2348 45 | 4 | 0 2349 45 | 5 | 1 2350 45 | 9 | 1 2351 45 | 1 | 1 2352 33 | 6 | 2 2353 33 | 2 | 2 2354 18 | 3 | 3 2355 18 | 7 | 3 2356(10 rows) 2357 2358SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), 2359 unique1, four 2360FROM tenk1 WHERE unique1 < 10; 2361 sum | unique1 | four 2362-----+---------+------ 2363 33 | 0 | 0 2364 33 | 8 | 0 2365 33 | 4 | 0 2366 18 | 5 | 1 2367 18 | 9 | 1 2368 18 | 1 | 1 2369 10 | 6 | 2 2370 10 | 2 | 2 2371 | 3 | 3 2372 | 7 | 3 2373(10 rows) 2374 2375SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), 2376 unique1, four 2377FROM tenk1 WHERE unique1 < 10; 2378 sum | unique1 | four 2379-----+---------+------ 2380 35 | 0 | 0 2381 35 | 8 | 0 2382 35 | 4 | 0 2383 45 | 5 | 1 2384 45 | 9 | 1 2385 45 | 1 | 1 2386 45 | 6 | 2 2387 45 | 2 | 2 2388 45 | 3 | 3 2389 45 | 7 | 3 2390(10 rows) 2391 2392SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), 2393 unique1, four 2394FROM tenk1 WHERE unique1 < 10; 2395 sum | unique1 | four 2396-----+---------+------ 2397 | 0 | 0 2398 | 8 | 0 2399 | 4 | 0 2400 12 | 5 | 1 2401 12 | 9 | 1 2402 12 | 1 | 1 2403 27 | 6 | 2 2404 27 | 2 | 2 2405 23 | 3 | 3 2406 23 | 7 | 3 2407(10 rows) 2408 2409SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), 2410 unique1, four 2411FROM tenk1 WHERE unique1 < 10; 2412 sum | unique1 | four 2413-----+---------+------ 2414 27 | 0 | 0 2415 27 | 8 | 0 2416 27 | 4 | 0 2417 35 | 5 | 1 2418 35 | 9 | 1 2419 35 | 1 | 1 2420 45 | 6 | 2 2421 45 | 2 | 2 2422 33 | 3 | 3 2423 33 | 7 | 3 2424(10 rows) 2425 2426SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), 2427 unique1, four 2428FROM tenk1 WHERE unique1 < 10; 2429 sum | unique1 | four 2430-----+---------+------ 2431 12 | 0 | 0 2432 12 | 8 | 0 2433 12 | 4 | 0 2434 15 | 5 | 1 2435 15 | 9 | 1 2436 15 | 1 | 1 2437 8 | 6 | 2 2438 8 | 2 | 2 2439 10 | 3 | 3 2440 10 | 7 | 3 2441(10 rows) 2442 2443SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following 2444 exclude current row), unique1, four 2445FROM tenk1 WHERE unique1 < 10; 2446 sum | unique1 | four 2447-----+---------+------ 2448 27 | 0 | 0 2449 19 | 8 | 0 2450 23 | 4 | 0 2451 30 | 5 | 1 2452 26 | 9 | 1 2453 34 | 1 | 1 2454 39 | 6 | 2 2455 43 | 2 | 2 2456 30 | 3 | 3 2457 26 | 7 | 3 2458(10 rows) 2459 2460SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following 2461 exclude group), unique1, four 2462FROM tenk1 WHERE unique1 < 10; 2463 sum | unique1 | four 2464-----+---------+------ 2465 15 | 0 | 0 2466 15 | 8 | 0 2467 15 | 4 | 0 2468 20 | 5 | 1 2469 20 | 9 | 1 2470 20 | 1 | 1 2471 37 | 6 | 2 2472 37 | 2 | 2 2473 23 | 3 | 3 2474 23 | 7 | 3 2475(10 rows) 2476 2477SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following 2478 exclude ties), unique1, four 2479FROM tenk1 WHERE unique1 < 10; 2480 sum | unique1 | four 2481-----+---------+------ 2482 15 | 0 | 0 2483 23 | 8 | 0 2484 19 | 4 | 0 2485 25 | 5 | 1 2486 29 | 9 | 1 2487 21 | 1 | 1 2488 43 | 6 | 2 2489 39 | 2 | 2 2490 26 | 3 | 3 2491 30 | 7 | 3 2492(10 rows) 2493 2494SELECT sum(unique1) over (partition by ten 2495 order by four groups between 0 preceding and 0 following),unique1, four, ten 2496FROM tenk1 WHERE unique1 < 10; 2497 sum | unique1 | four | ten 2498-----+---------+------+----- 2499 0 | 0 | 0 | 0 2500 1 | 1 | 1 | 1 2501 2 | 2 | 2 | 2 2502 3 | 3 | 3 | 3 2503 4 | 4 | 0 | 4 2504 5 | 5 | 1 | 5 2505 6 | 6 | 2 | 6 2506 7 | 7 | 3 | 7 2507 8 | 8 | 0 | 8 2508 9 | 9 | 1 | 9 2509(10 rows) 2510 2511SELECT sum(unique1) over (partition by ten 2512 order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten 2513FROM tenk1 WHERE unique1 < 10; 2514 sum | unique1 | four | ten 2515-----+---------+------+----- 2516 | 0 | 0 | 0 2517 | 1 | 1 | 1 2518 | 2 | 2 | 2 2519 | 3 | 3 | 3 2520 | 4 | 0 | 4 2521 | 5 | 1 | 5 2522 | 6 | 2 | 6 2523 | 7 | 3 | 7 2524 | 8 | 0 | 8 2525 | 9 | 1 | 9 2526(10 rows) 2527 2528SELECT sum(unique1) over (partition by ten 2529 order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten 2530FROM tenk1 WHERE unique1 < 10; 2531 sum | unique1 | four | ten 2532-----+---------+------+----- 2533 | 0 | 0 | 0 2534 | 1 | 1 | 1 2535 | 2 | 2 | 2 2536 | 3 | 3 | 3 2537 | 4 | 0 | 4 2538 | 5 | 1 | 5 2539 | 6 | 2 | 6 2540 | 7 | 3 | 7 2541 | 8 | 0 | 8 2542 | 9 | 1 | 9 2543(10 rows) 2544 2545SELECT sum(unique1) over (partition by ten 2546 order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten 2547FROM tenk1 WHERE unique1 < 10; 2548 sum | unique1 | four | ten 2549-----+---------+------+----- 2550 0 | 0 | 0 | 0 2551 1 | 1 | 1 | 1 2552 2 | 2 | 2 | 2 2553 3 | 3 | 3 | 3 2554 4 | 4 | 0 | 4 2555 5 | 5 | 1 | 5 2556 6 | 6 | 2 | 6 2557 7 | 7 | 3 | 7 2558 8 | 8 | 0 | 8 2559 9 | 9 | 1 | 9 2560(10 rows) 2561 2562select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), 2563 lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), 2564 nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), 2565 salary, enroll_date from empsalary; 2566 first_value | lead | nth_value | salary | enroll_date 2567-------------+------+-----------+--------+------------- 2568 5000 | 6000 | 5000 | 5000 | 10-01-2006 2569 5000 | 3900 | 5000 | 6000 | 10-01-2006 2570 5000 | 4800 | 5000 | 3900 | 12-23-2006 2571 3900 | 5200 | 3900 | 4800 | 08-01-2007 2572 3900 | 4800 | 3900 | 5200 | 08-01-2007 2573 4800 | 5200 | 4800 | 4800 | 08-08-2007 2574 4800 | 3500 | 4800 | 5200 | 08-15-2007 2575 5200 | 4500 | 5200 | 3500 | 12-10-2007 2576 3500 | 4200 | 3500 | 4500 | 01-01-2008 2577 3500 | | 3500 | 4200 | 01-01-2008 2578(10 rows) 2579 2580select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), 2581 lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), 2582 salary, enroll_date from empsalary; 2583 last_value | lag | salary | enroll_date 2584------------+------+--------+------------- 2585 3900 | | 5000 | 10-01-2006 2586 3900 | 5000 | 6000 | 10-01-2006 2587 5200 | 6000 | 3900 | 12-23-2006 2588 4800 | 3900 | 4800 | 08-01-2007 2589 4800 | 4800 | 5200 | 08-01-2007 2590 5200 | 5200 | 4800 | 08-08-2007 2591 3500 | 4800 | 5200 | 08-15-2007 2592 4200 | 5200 | 3500 | 12-10-2007 2593 4200 | 3500 | 4500 | 01-01-2008 2594 4200 | 4500 | 4200 | 01-01-2008 2595(10 rows) 2596 2597select first_value(salary) over(order by enroll_date groups between 1 following and 3 following 2598 exclude current row), 2599 lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), 2600 nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following 2601 exclude ties), 2602 salary, enroll_date from empsalary; 2603 first_value | lead | nth_value | salary | enroll_date 2604-------------+------+-----------+--------+------------- 2605 3900 | 6000 | 3900 | 5000 | 10-01-2006 2606 3900 | 3900 | 3900 | 6000 | 10-01-2006 2607 4800 | 4800 | 4800 | 3900 | 12-23-2006 2608 4800 | 5200 | 4800 | 4800 | 08-01-2007 2609 4800 | 4800 | 4800 | 5200 | 08-01-2007 2610 5200 | 5200 | 5200 | 4800 | 08-08-2007 2611 3500 | 3500 | 3500 | 5200 | 08-15-2007 2612 4500 | 4500 | 4500 | 3500 | 12-10-2007 2613 | 4200 | | 4500 | 01-01-2008 2614 | | | 4200 | 01-01-2008 2615(10 rows) 2616 2617select last_value(salary) over(order by enroll_date groups between 1 following and 3 following 2618 exclude group), 2619 lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), 2620 salary, enroll_date from empsalary; 2621 last_value | lag | salary | enroll_date 2622------------+------+--------+------------- 2623 4800 | | 5000 | 10-01-2006 2624 4800 | 5000 | 6000 | 10-01-2006 2625 5200 | 6000 | 3900 | 12-23-2006 2626 3500 | 3900 | 4800 | 08-01-2007 2627 3500 | 4800 | 5200 | 08-01-2007 2628 4200 | 5200 | 4800 | 08-08-2007 2629 4200 | 4800 | 5200 | 08-15-2007 2630 4200 | 5200 | 3500 | 12-10-2007 2631 | 3500 | 4500 | 01-01-2008 2632 | 4500 | 4200 | 01-01-2008 2633(10 rows) 2634 2635-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS 2636WITH cte (x) AS ( 2637 SELECT * FROM generate_series(1, 35, 2) 2638) 2639SELECT x, (sum(x) over w) 2640FROM cte 2641WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); 2642 x | sum 2643----+----- 2644 1 | 4 2645 3 | 9 2646 5 | 15 2647 7 | 21 2648 9 | 27 2649 11 | 33 2650 13 | 39 2651 15 | 45 2652 17 | 51 2653 19 | 57 2654 21 | 63 2655 23 | 69 2656 25 | 75 2657 27 | 81 2658 29 | 87 2659 31 | 93 2660 33 | 99 2661 35 | 68 2662(18 rows) 2663 2664WITH cte (x) AS ( 2665 SELECT * FROM generate_series(1, 35, 2) 2666) 2667SELECT x, (sum(x) over w) 2668FROM cte 2669WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); 2670 x | sum 2671----+----- 2672 1 | 1 2673 3 | 3 2674 5 | 5 2675 7 | 7 2676 9 | 9 2677 11 | 11 2678 13 | 13 2679 15 | 15 2680 17 | 17 2681 19 | 19 2682 21 | 21 2683 23 | 23 2684 25 | 25 2685 27 | 27 2686 29 | 29 2687 31 | 31 2688 33 | 33 2689 35 | 35 2690(18 rows) 2691 2692WITH cte (x) AS ( 2693 SELECT * FROM generate_series(1, 35, 2) 2694) 2695SELECT x, (sum(x) over w) 2696FROM cte 2697WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); 2698 x | sum 2699----+----- 2700 1 | 4 2701 3 | 9 2702 5 | 15 2703 7 | 21 2704 9 | 27 2705 11 | 33 2706 13 | 39 2707 15 | 45 2708 17 | 51 2709 19 | 57 2710 21 | 63 2711 23 | 69 2712 25 | 75 2713 27 | 81 2714 29 | 87 2715 31 | 93 2716 33 | 99 2717 35 | 68 2718(18 rows) 2719 2720WITH cte (x) AS ( 2721 select 1 union all select 1 union all select 1 union all 2722 SELECT * FROM generate_series(5, 49, 2) 2723) 2724SELECT x, (sum(x) over w) 2725FROM cte 2726WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); 2727 x | sum 2728----+----- 2729 1 | 2 2730 1 | 3 2731 1 | 7 2732 5 | 13 2733 7 | 21 2734 9 | 27 2735 11 | 33 2736 13 | 39 2737 15 | 45 2738 17 | 51 2739 19 | 57 2740 21 | 63 2741 23 | 69 2742 25 | 75 2743 27 | 81 2744 29 | 87 2745 31 | 93 2746 33 | 99 2747 35 | 105 2748 37 | 111 2749 39 | 117 2750 41 | 123 2751 43 | 129 2752 45 | 135 2753 47 | 141 2754 49 | 96 2755(26 rows) 2756 2757WITH cte (x) AS ( 2758 select 1 union all select 1 union all select 1 union all 2759 SELECT * FROM generate_series(5, 49, 2) 2760) 2761SELECT x, (sum(x) over w) 2762FROM cte 2763WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); 2764 x | sum 2765----+----- 2766 1 | 3 2767 1 | 3 2768 1 | 3 2769 5 | 5 2770 7 | 7 2771 9 | 9 2772 11 | 11 2773 13 | 13 2774 15 | 15 2775 17 | 17 2776 19 | 19 2777 21 | 21 2778 23 | 23 2779 25 | 25 2780 27 | 27 2781 29 | 29 2782 31 | 31 2783 33 | 33 2784 35 | 35 2785 37 | 37 2786 39 | 39 2787 41 | 41 2788 43 | 43 2789 45 | 45 2790 47 | 47 2791 49 | 49 2792(26 rows) 2793 2794WITH cte (x) AS ( 2795 select 1 union all select 1 union all select 1 union all 2796 SELECT * FROM generate_series(5, 49, 2) 2797) 2798SELECT x, (sum(x) over w) 2799FROM cte 2800WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); 2801 x | sum 2802----+----- 2803 1 | 8 2804 1 | 8 2805 1 | 8 2806 5 | 15 2807 7 | 21 2808 9 | 27 2809 11 | 33 2810 13 | 39 2811 15 | 45 2812 17 | 51 2813 19 | 57 2814 21 | 63 2815 23 | 69 2816 25 | 75 2817 27 | 81 2818 29 | 87 2819 31 | 93 2820 33 | 99 2821 35 | 105 2822 37 | 111 2823 39 | 117 2824 41 | 123 2825 43 | 129 2826 45 | 135 2827 47 | 141 2828 49 | 96 2829(26 rows) 2830 2831-- with UNION 2832SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; 2833 count 2834------- 2835(0 rows) 2836 2837-- check some degenerate cases 2838create temp table t1 (f1 int, f2 int8); 2839insert into t1 values (1,1),(1,2),(2,2); 2840select f1, sum(f1) over (partition by f1 2841 range between 1 preceding and 1 following) 2842from t1 where f1 = f2; -- error, must have order by 2843ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column 2844LINE 1: select f1, sum(f1) over (partition by f1 2845 ^ 2846explain (costs off) 2847select f1, sum(f1) over (partition by f1 order by f2 2848 range between 1 preceding and 1 following) 2849from t1 where f1 = f2; 2850 QUERY PLAN 2851--------------------------------- 2852 WindowAgg 2853 -> Sort 2854 Sort Key: f1 2855 -> Seq Scan on t1 2856 Filter: (f1 = f2) 2857(5 rows) 2858 2859select f1, sum(f1) over (partition by f1 order by f2 2860 range between 1 preceding and 1 following) 2861from t1 where f1 = f2; 2862 f1 | sum 2863----+----- 2864 1 | 1 2865 2 | 2 2866(2 rows) 2867 2868select f1, sum(f1) over (partition by f1, f1 order by f2 2869 range between 2 preceding and 1 preceding) 2870from t1 where f1 = f2; 2871 f1 | sum 2872----+----- 2873 1 | 2874 2 | 2875(2 rows) 2876 2877select f1, sum(f1) over (partition by f1, f2 order by f2 2878 range between 1 following and 2 following) 2879from t1 where f1 = f2; 2880 f1 | sum 2881----+----- 2882 1 | 2883 2 | 2884(2 rows) 2885 2886select f1, sum(f1) over (partition by f1 2887 groups between 1 preceding and 1 following) 2888from t1 where f1 = f2; -- error, must have order by 2889ERROR: GROUPS mode requires an ORDER BY clause 2890LINE 1: select f1, sum(f1) over (partition by f1 2891 ^ 2892explain (costs off) 2893select f1, sum(f1) over (partition by f1 order by f2 2894 groups between 1 preceding and 1 following) 2895from t1 where f1 = f2; 2896 QUERY PLAN 2897--------------------------------- 2898 WindowAgg 2899 -> Sort 2900 Sort Key: f1 2901 -> Seq Scan on t1 2902 Filter: (f1 = f2) 2903(5 rows) 2904 2905select f1, sum(f1) over (partition by f1 order by f2 2906 groups between 1 preceding and 1 following) 2907from t1 where f1 = f2; 2908 f1 | sum 2909----+----- 2910 1 | 1 2911 2 | 2 2912(2 rows) 2913 2914select f1, sum(f1) over (partition by f1, f1 order by f2 2915 groups between 2 preceding and 1 preceding) 2916from t1 where f1 = f2; 2917 f1 | sum 2918----+----- 2919 1 | 2920 2 | 2921(2 rows) 2922 2923select f1, sum(f1) over (partition by f1, f2 order by f2 2924 groups between 1 following and 2 following) 2925from t1 where f1 = f2; 2926 f1 | sum 2927----+----- 2928 1 | 2929 2 | 2930(2 rows) 2931 2932-- ordering by a non-integer constant is allowed 2933SELECT rank() OVER (ORDER BY length('abc')); 2934 rank 2935------ 2936 1 2937(1 row) 2938 2939-- can't order by another window function 2940SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())); 2941ERROR: window functions are not allowed in window definitions 2942LINE 1: SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())... 2943 ^ 2944-- some other errors 2945SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10; 2946ERROR: window functions are not allowed in WHERE 2947LINE 1: SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY sa... 2948 ^ 2949SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10; 2950ERROR: window functions are not allowed in JOIN conditions 2951LINE 1: SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVE... 2952 ^ 2953SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1; 2954ERROR: window functions are not allowed in GROUP BY 2955LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO... 2956 ^ 2957SELECT * FROM rank() OVER (ORDER BY random()); 2958ERROR: syntax error at or near "ORDER" 2959LINE 1: SELECT * FROM rank() OVER (ORDER BY random()); 2960 ^ 2961DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10; 2962ERROR: window functions are not allowed in WHERE 2963LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())... 2964 ^ 2965DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random()); 2966ERROR: window functions are not allowed in RETURNING 2967LINE 1: DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random... 2968 ^ 2969SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1); 2970ERROR: window "w" is already defined 2971LINE 1: ...w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY ... 2972 ^ 2973SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1; 2974ERROR: syntax error at or near "ORDER" 2975LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te... 2976 ^ 2977SELECT count() OVER () FROM tenk1; 2978ERROR: count(*) must be used to call a parameterless aggregate function 2979LINE 1: SELECT count() OVER () FROM tenk1; 2980 ^ 2981SELECT generate_series(1, 100) OVER () FROM empsalary; 2982ERROR: OVER specified, but generate_series is not a window function nor an aggregate function 2983LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary; 2984 ^ 2985SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; 2986ERROR: argument of ntile must be greater than zero 2987SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; 2988ERROR: argument of nth_value must be greater than zero 2989-- filter 2990SELECT sum(salary), row_number() OVER (ORDER BY depname), sum( 2991 sum(salary) FILTER (WHERE enroll_date > '2007-01-01') 2992) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", 2993 depname 2994FROM empsalary GROUP BY depname; 2995 sum | row_number | filtered_sum | depname 2996-------+------------+--------------+----------- 2997 14600 | 3 | | sales 2998 7400 | 2 | 3500 | personnel 2999 25100 | 1 | 22600 | develop 3000(3 rows) 3001 3002-- Test pushdown of quals into a subquery containing window functions 3003-- pushdown is safe because all PARTITION BY clauses include depname: 3004EXPLAIN (COSTS OFF) 3005SELECT * FROM 3006 (SELECT depname, 3007 sum(salary) OVER (PARTITION BY depname) depsalary, 3008 min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary 3009 FROM empsalary) emp 3010WHERE depname = 'sales'; 3011 QUERY PLAN 3012--------------------------------------------------------------------- 3013 Subquery Scan on emp 3014 -> WindowAgg 3015 -> Sort 3016 Sort Key: (((empsalary.depname)::text || 'A'::text)) 3017 -> WindowAgg 3018 -> Seq Scan on empsalary 3019 Filter: ((depname)::text = 'sales'::text) 3020(7 rows) 3021 3022-- pushdown is unsafe because there's a PARTITION BY clause without depname: 3023EXPLAIN (COSTS OFF) 3024SELECT * FROM 3025 (SELECT depname, 3026 sum(salary) OVER (PARTITION BY enroll_date) enroll_salary, 3027 min(salary) OVER (PARTITION BY depname) depminsalary 3028 FROM empsalary) emp 3029WHERE depname = 'sales'; 3030 QUERY PLAN 3031----------------------------------------------------------- 3032 Subquery Scan on emp 3033 Filter: ((emp.depname)::text = 'sales'::text) 3034 -> WindowAgg 3035 -> Sort 3036 Sort Key: empsalary.depname 3037 -> WindowAgg 3038 -> Sort 3039 Sort Key: empsalary.enroll_date 3040 -> Seq Scan on empsalary 3041(9 rows) 3042 3043-- cleanup 3044DROP TABLE empsalary; 3045-- test user-defined window function with named args and default args 3046CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement 3047 LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value'; 3048SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four 3049 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; 3050 nth_value_def | ten | four 3051---------------+-----+------ 3052 0 | 0 | 0 3053 0 | 0 | 0 3054 0 | 4 | 0 3055 1 | 1 | 1 3056 1 | 1 | 1 3057 1 | 7 | 1 3058 1 | 9 | 1 3059 | 0 | 2 3060 3 | 1 | 3 3061 3 | 3 | 3 3062(10 rows) 3063 3064SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four 3065 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; 3066 nth_value_def | ten | four 3067---------------+-----+------ 3068 0 | 0 | 0 3069 0 | 0 | 0 3070 0 | 4 | 0 3071 1 | 1 | 1 3072 1 | 1 | 1 3073 1 | 7 | 1 3074 1 | 9 | 1 3075 0 | 0 | 2 3076 1 | 1 | 3 3077 1 | 3 | 3 3078(10 rows) 3079 3080-- 3081-- Test the basic moving-aggregate machinery 3082-- 3083-- create aggregates that record the series of transform calls (these are 3084-- intentionally not true inverses) 3085CREATE FUNCTION logging_sfunc_nonstrict(text, anyelement) RETURNS text AS 3086$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$ 3087LANGUAGE SQL IMMUTABLE; 3088CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS 3089$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$ 3090LANGUAGE SQL IMMUTABLE; 3091CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS 3092$$ SELECT $1 || '-' || quote_nullable($2) $$ 3093LANGUAGE SQL IMMUTABLE; 3094CREATE AGGREGATE logging_agg_nonstrict (anyelement) 3095( 3096 stype = text, 3097 sfunc = logging_sfunc_nonstrict, 3098 mstype = text, 3099 msfunc = logging_msfunc_nonstrict, 3100 minvfunc = logging_minvfunc_nonstrict 3101); 3102CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement) 3103( 3104 stype = text, 3105 sfunc = logging_sfunc_nonstrict, 3106 mstype = text, 3107 msfunc = logging_msfunc_nonstrict, 3108 minvfunc = logging_minvfunc_nonstrict, 3109 initcond = 'I', 3110 minitcond = 'MI' 3111); 3112CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS 3113$$ SELECT $1 || '*' || quote_nullable($2) $$ 3114LANGUAGE SQL STRICT IMMUTABLE; 3115CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS 3116$$ SELECT $1 || '+' || quote_nullable($2) $$ 3117LANGUAGE SQL STRICT IMMUTABLE; 3118CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS 3119$$ SELECT $1 || '-' || quote_nullable($2) $$ 3120LANGUAGE SQL STRICT IMMUTABLE; 3121CREATE AGGREGATE logging_agg_strict (text) 3122( 3123 stype = text, 3124 sfunc = logging_sfunc_strict, 3125 mstype = text, 3126 msfunc = logging_msfunc_strict, 3127 minvfunc = logging_minvfunc_strict 3128); 3129CREATE AGGREGATE logging_agg_strict_initcond (anyelement) 3130( 3131 stype = text, 3132 sfunc = logging_sfunc_strict, 3133 mstype = text, 3134 msfunc = logging_msfunc_strict, 3135 minvfunc = logging_minvfunc_strict, 3136 initcond = 'I', 3137 minitcond = 'MI' 3138); 3139-- test strict and non-strict cases 3140SELECT 3141 p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS row, 3142 logging_agg_nonstrict(v) over wnd as nstrict, 3143 logging_agg_nonstrict_initcond(v) over wnd as nstrict_init, 3144 logging_agg_strict(v::text) over wnd as strict, 3145 logging_agg_strict_initcond(v) over wnd as strict_init 3146FROM (VALUES 3147 (1, 1, NULL), 3148 (1, 2, 'a'), 3149 (1, 3, 'b'), 3150 (1, 4, NULL), 3151 (1, 5, NULL), 3152 (1, 6, 'c'), 3153 (2, 1, NULL), 3154 (2, 2, 'x'), 3155 (3, 1, 'z') 3156) AS t(p, i, v) 3157WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 3158ORDER BY p, i; 3159 row | nstrict | nstrict_init | strict | strict_init 3160----------+-----------------------------------------------+-------------------------------------------------+-----------+---------------- 3161 1,1:NULL | +NULL | MI+NULL | | MI 3162 1,2:a | +NULL+'a' | MI+NULL+'a' | a | MI+'a' 3163 1,3:b | +NULL+'a'-NULL+'b' | MI+NULL+'a'-NULL+'b' | a+'b' | MI+'a'+'b' 3164 1,4:NULL | +NULL+'a'-NULL+'b'-'a'+NULL | MI+NULL+'a'-NULL+'b'-'a'+NULL | a+'b'-'a' | MI+'a'+'b'-'a' 3165 1,5:NULL | +NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL | MI+NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL | | MI 3166 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' 3167 2,1:NULL | +NULL | MI+NULL | | MI 3168 2,2:x | +NULL+'x' | MI+NULL+'x' | x | MI+'x' 3169 3,1:z | +'z' | MI+'z' | z | MI+'z' 3170(9 rows) 3171 3172-- and again, but with filter 3173SELECT 3174 p::text || ',' || i::text || ':' || 3175 CASE WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row, 3176 logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt, 3177 logging_agg_nonstrict_initcond(v) filter(where f) over wnd as nstrict_init_filt, 3178 logging_agg_strict(v::text) filter(where f) over wnd as strict_filt, 3179 logging_agg_strict_initcond(v) filter(where f) over wnd as strict_init_filt 3180FROM (VALUES 3181 (1, 1, true, NULL), 3182 (1, 2, false, 'a'), 3183 (1, 3, true, 'b'), 3184 (1, 4, false, NULL), 3185 (1, 5, false, NULL), 3186 (1, 6, false, 'c'), 3187 (2, 1, false, NULL), 3188 (2, 2, true, 'x'), 3189 (3, 1, true, 'z') 3190) AS t(p, i, f, v) 3191WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 3192ORDER BY p, i; 3193 row | nstrict_filt | nstrict_init_filt | strict_filt | strict_init_filt 3194----------+--------------+-------------------+-------------+------------------ 3195 1,1:NULL | +NULL | MI+NULL | | MI 3196 1,2:- | +NULL | MI+NULL | | MI 3197 1,3:b | +'b' | MI+'b' | b | MI+'b' 3198 1,4:- | +'b' | MI+'b' | b | MI+'b' 3199 1,5:- | | MI | | MI 3200 1,6:- | | MI | | MI 3201 2,1:- | | MI | | MI 3202 2,2:x | +'x' | MI+'x' | x | MI+'x' 3203 3,1:z | +'z' | MI+'z' | z | MI+'z' 3204(9 rows) 3205 3206-- test that volatile arguments disable moving-aggregate mode 3207SELECT 3208 i::text || ':' || COALESCE(v::text, 'NULL') as row, 3209 logging_agg_strict(v::text) 3210 over wnd as inverse, 3211 logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END) 3212 over wnd as noinverse 3213FROM (VALUES 3214 (1, 'a'), 3215 (2, 'b'), 3216 (3, 'c') 3217) AS t(i, v) 3218WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 3219ORDER BY i; 3220 row | inverse | noinverse 3221-----+---------------+----------- 3222 1:a | a | a 3223 2:b | a+'b' | a*'b' 3224 3:c | a+'b'-'a'+'c' | b*'c' 3225(3 rows) 3226 3227SELECT 3228 i::text || ':' || COALESCE(v::text, 'NULL') as row, 3229 logging_agg_strict(v::text) filter(where true) 3230 over wnd as inverse, 3231 logging_agg_strict(v::text) filter(where random() >= 0) 3232 over wnd as noinverse 3233FROM (VALUES 3234 (1, 'a'), 3235 (2, 'b'), 3236 (3, 'c') 3237) AS t(i, v) 3238WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 3239ORDER BY i; 3240 row | inverse | noinverse 3241-----+---------------+----------- 3242 1:a | a | a 3243 2:b | a+'b' | a*'b' 3244 3:c | a+'b'-'a'+'c' | b*'c' 3245(3 rows) 3246 3247-- test that non-overlapping windows don't use inverse transitions 3248SELECT 3249 logging_agg_strict(v::text) OVER wnd 3250FROM (VALUES 3251 (1, 'a'), 3252 (2, 'b'), 3253 (3, 'c') 3254) AS t(i, v) 3255WINDOW wnd AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) 3256ORDER BY i; 3257 logging_agg_strict 3258-------------------- 3259 a 3260 b 3261 c 3262(3 rows) 3263 3264-- test that returning NULL from the inverse transition functions 3265-- restarts the aggregation from scratch. The second aggregate is supposed 3266-- to test cases where only some aggregates restart, the third one checks 3267-- that one aggregate restarting doesn't cause others to restart. 3268CREATE FUNCTION sum_int_randrestart_minvfunc(int4, int4) RETURNS int4 AS 3269$$ SELECT CASE WHEN random() < 0.2 THEN NULL ELSE $1 - $2 END $$ 3270LANGUAGE SQL STRICT; 3271CREATE AGGREGATE sum_int_randomrestart (int4) 3272( 3273 stype = int4, 3274 sfunc = int4pl, 3275 mstype = int4, 3276 msfunc = int4pl, 3277 minvfunc = sum_int_randrestart_minvfunc 3278); 3279WITH 3280vs AS ( 3281 SELECT i, (random() * 100)::int4 AS v 3282 FROM generate_series(1, 100) AS i 3283), 3284sum_following AS ( 3285 SELECT i, SUM(v) OVER 3286 (ORDER BY i DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s 3287 FROM vs 3288) 3289SELECT DISTINCT 3290 sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1, 3291 -sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2, 3292 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 3293FROM vs 3294JOIN sum_following ON sum_following.i = vs.i 3295WINDOW fwd AS ( 3296 ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 3297); 3298 eq1 | eq2 | eq3 3299-----+-----+----- 3300 t | t | t 3301(1 row) 3302 3303-- 3304-- Test various built-in aggregates that have moving-aggregate support 3305-- 3306-- test inverse transition functions handle NULLs properly 3307SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3308 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3309 i | avg 3310---+-------------------- 3311 1 | 1.5000000000000000 3312 2 | 2.0000000000000000 3313 3 | 3314 4 | 3315(4 rows) 3316 3317SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3318 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3319 i | avg 3320---+-------------------- 3321 1 | 1.5000000000000000 3322 2 | 2.0000000000000000 3323 3 | 3324 4 | 3325(4 rows) 3326 3327SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3328 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3329 i | avg 3330---+-------------------- 3331 1 | 1.5000000000000000 3332 2 | 2.0000000000000000 3333 3 | 3334 4 | 3335(4 rows) 3336 3337SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3338 FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v); 3339 i | avg 3340---+-------------------- 3341 1 | 2.0000000000000000 3342 2 | 2.5000000000000000 3343 3 | 3344 4 | 3345(4 rows) 3346 3347SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3348 FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); 3349 i | avg 3350---+------------ 3351 1 | @ 1.5 secs 3352 2 | @ 2 secs 3353 3 | 3354 4 | 3355(4 rows) 3356 3357SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3358 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3359 i | sum 3360---+----- 3361 1 | 3 3362 2 | 2 3363 3 | 3364 4 | 3365(4 rows) 3366 3367SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3368 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3369 i | sum 3370---+----- 3371 1 | 3 3372 2 | 2 3373 3 | 3374 4 | 3375(4 rows) 3376 3377SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3378 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3379 i | sum 3380---+----- 3381 1 | 3 3382 2 | 2 3383 3 | 3384 4 | 3385(4 rows) 3386 3387SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3388 FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v); 3389 i | sum 3390---+------- 3391 1 | $3.30 3392 2 | $2.20 3393 3 | 3394 4 | 3395(4 rows) 3396 3397SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3398 FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); 3399 i | sum 3400---+---------- 3401 1 | @ 3 secs 3402 2 | @ 2 secs 3403 3 | 3404 4 | 3405(4 rows) 3406 3407SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3408 FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v); 3409 i | sum 3410---+----- 3411 1 | 3.3 3412 2 | 2.2 3413 3 | 3414 4 | 3415(4 rows) 3416 3417SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3418 FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n); 3419 sum 3420------ 3421 6.01 3422 5 3423 3 3424(3 rows) 3425 3426SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3427 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3428 i | count 3429---+------- 3430 1 | 2 3431 2 | 1 3432 3 | 0 3433 4 | 0 3434(4 rows) 3435 3436SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3437 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3438 i | count 3439---+------- 3440 1 | 4 3441 2 | 3 3442 3 | 2 3443 4 | 1 3444(4 rows) 3445 3446SELECT VAR_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3447 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3448 var_pop 3449----------------------- 3450 21704.000000000000 3451 13868.750000000000 3452 11266.666666666667 3453 4225.0000000000000000 3454 0 3455(5 rows) 3456 3457SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3458 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3459 var_pop 3460----------------------- 3461 21704.000000000000 3462 13868.750000000000 3463 11266.666666666667 3464 4225.0000000000000000 3465 0 3466(5 rows) 3467 3468SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3469 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3470 var_pop 3471----------------------- 3472 21704.000000000000 3473 13868.750000000000 3474 11266.666666666667 3475 4225.0000000000000000 3476 0 3477(5 rows) 3478 3479SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3480 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3481 var_pop 3482----------------------- 3483 21704.000000000000 3484 13868.750000000000 3485 11266.666666666667 3486 4225.0000000000000000 3487 0 3488(5 rows) 3489 3490SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3491 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3492 var_samp 3493----------------------- 3494 27130.000000000000 3495 18491.666666666667 3496 16900.000000000000 3497 8450.0000000000000000 3498 3499(5 rows) 3500 3501SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3502 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3503 var_samp 3504----------------------- 3505 27130.000000000000 3506 18491.666666666667 3507 16900.000000000000 3508 8450.0000000000000000 3509 3510(5 rows) 3511 3512SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3513 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3514 var_samp 3515----------------------- 3516 27130.000000000000 3517 18491.666666666667 3518 16900.000000000000 3519 8450.0000000000000000 3520 3521(5 rows) 3522 3523SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3524 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3525 var_samp 3526----------------------- 3527 27130.000000000000 3528 18491.666666666667 3529 16900.000000000000 3530 8450.0000000000000000 3531 3532(5 rows) 3533 3534SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3535 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3536 variance 3537----------------------- 3538 27130.000000000000 3539 18491.666666666667 3540 16900.000000000000 3541 8450.0000000000000000 3542 3543(5 rows) 3544 3545SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3546 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3547 variance 3548----------------------- 3549 27130.000000000000 3550 18491.666666666667 3551 16900.000000000000 3552 8450.0000000000000000 3553 3554(5 rows) 3555 3556SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3557 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3558 variance 3559----------------------- 3560 27130.000000000000 3561 18491.666666666667 3562 16900.000000000000 3563 8450.0000000000000000 3564 3565(5 rows) 3566 3567SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3568 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3569 variance 3570----------------------- 3571 27130.000000000000 3572 18491.666666666667 3573 16900.000000000000 3574 8450.0000000000000000 3575 3576(5 rows) 3577 3578SELECT STDDEV_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3579 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 3580 stddev_pop 3581--------------------- 3582 147.322774885623 3583 147.322774885623 3584 117.765657133139 3585 106.144555520604 3586 65.0000000000000000 3587 0 3588(6 rows) 3589 3590SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3591 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 3592 stddev_pop 3593--------------------- 3594 147.322774885623 3595 147.322774885623 3596 117.765657133139 3597 106.144555520604 3598 65.0000000000000000 3599 0 3600(6 rows) 3601 3602SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3603 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 3604 stddev_pop 3605--------------------- 3606 147.322774885623 3607 147.322774885623 3608 117.765657133139 3609 106.144555520604 3610 65.0000000000000000 3611 0 3612(6 rows) 3613 3614SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3615 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 3616 stddev_pop 3617--------------------- 3618 147.322774885623 3619 147.322774885623 3620 117.765657133139 3621 106.144555520604 3622 65.0000000000000000 3623 0 3624(6 rows) 3625 3626SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3627 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 3628 stddev_samp 3629--------------------- 3630 164.711869639076 3631 164.711869639076 3632 135.984067694222 3633 130.000000000000 3634 91.9238815542511782 3635 3636(6 rows) 3637 3638SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3639 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 3640 stddev_samp 3641--------------------- 3642 164.711869639076 3643 164.711869639076 3644 135.984067694222 3645 130.000000000000 3646 91.9238815542511782 3647 3648(6 rows) 3649 3650SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3651 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 3652 stddev_samp 3653--------------------- 3654 164.711869639076 3655 164.711869639076 3656 135.984067694222 3657 130.000000000000 3658 91.9238815542511782 3659 3660(6 rows) 3661 3662SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3663 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 3664 stddev_samp 3665--------------------- 3666 164.711869639076 3667 164.711869639076 3668 135.984067694222 3669 130.000000000000 3670 91.9238815542511782 3671 3672(6 rows) 3673 3674SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3675 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3676 stddev 3677--------------------- 3678 164.711869639076 3679 164.711869639076 3680 135.984067694222 3681 130.000000000000 3682 91.9238815542511782 3683 3684(6 rows) 3685 3686SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3687 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3688 stddev 3689--------------------- 3690 164.711869639076 3691 164.711869639076 3692 135.984067694222 3693 130.000000000000 3694 91.9238815542511782 3695 3696(6 rows) 3697 3698SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3699 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3700 stddev 3701--------------------- 3702 164.711869639076 3703 164.711869639076 3704 135.984067694222 3705 130.000000000000 3706 91.9238815542511782 3707 3708(6 rows) 3709 3710SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 3711 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 3712 stddev 3713--------------------- 3714 164.711869639076 3715 164.711869639076 3716 135.984067694222 3717 130.000000000000 3718 91.9238815542511782 3719 3720(6 rows) 3721 3722-- test that inverse transition functions work with various frame options 3723SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) 3724 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3725 i | sum 3726---+----- 3727 1 | 1 3728 2 | 2 3729 3 | 3730 4 | 3731(4 rows) 3732 3733SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) 3734 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 3735 i | sum 3736---+----- 3737 1 | 3 3738 2 | 2 3739 3 | 3740 4 | 3741(4 rows) 3742 3743SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 3744 FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v); 3745 i | sum 3746---+----- 3747 1 | 3 3748 2 | 6 3749 3 | 9 3750 4 | 7 3751(4 rows) 3752 3753-- ensure aggregate over numeric properly recovers from NaN values 3754SELECT a, b, 3755 SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 3756FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b); 3757 a | b | sum 3758---+-----+----- 3759 1 | 1 | 1 3760 2 | 2 | 3 3761 3 | NaN | NaN 3762 4 | 3 | NaN 3763 5 | 4 | 7 3764(5 rows) 3765 3766-- It might be tempting for someone to add an inverse trans function for 3767-- float and double precision. This should not be done as it can give incorrect 3768-- results. This test should fail if anyone ever does this without thinking too 3769-- hard about it. 3770SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9') 3771 FROM (VALUES(1,1e20),(2,1)) n(i,n); 3772 to_char 3773-------------------------- 3774 100000000000000000000 3775 1.0 3776(2 rows) 3777 3778SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w 3779 FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b) 3780 WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING); 3781 i | b | bool_and | bool_or 3782---+---+----------+--------- 3783 1 | t | t | t 3784 2 | t | f | t 3785 3 | f | f | f 3786 4 | f | f | t 3787 5 | t | t | t 3788(5 rows) 3789 3790-- Tests for problems with failure to walk or mutate expressions 3791-- within window frame clauses. 3792-- test walker (fails with collation error if expressions are not walked) 3793SELECT array_agg(i) OVER w 3794 FROM generate_series(1,5) i 3795WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW); 3796 array_agg 3797----------- 3798 {1} 3799 {1,2} 3800 {2,3} 3801 {3,4} 3802 {4,5} 3803(5 rows) 3804 3805-- test mutator (fails when inlined if expressions are not mutated) 3806CREATE FUNCTION pg_temp.f(group_size BIGINT) RETURNS SETOF integer[] 3807AS $$ 3808 SELECT array_agg(s) OVER w 3809 FROM generate_series(1,5) s 3810 WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING) 3811$$ LANGUAGE SQL STABLE; 3812EXPLAIN (costs off) SELECT * FROM pg_temp.f(2); 3813 QUERY PLAN 3814------------------------------------------------------ 3815 Subquery Scan on f 3816 -> WindowAgg 3817 -> Sort 3818 Sort Key: s.s 3819 -> Function Scan on generate_series s 3820(5 rows) 3821 3822SELECT * FROM pg_temp.f(2); 3823 f 3824--------- 3825 {1,2,3} 3826 {2,3,4} 3827 {3,4,5} 3828 {4,5} 3829 {5} 3830(5 rows) 3831 3832