1-- 2-- WINDOW FUNCTIONS 3-- 4 5CREATE TEMPORARY TABLE empsalary ( 6 depname varchar, 7 empno bigint, 8 salary int, 9 enroll_date date 10); 11 12INSERT INTO empsalary VALUES 13('develop', 10, 5200, '2007-08-01'), 14('sales', 1, 5000, '2006-10-01'), 15('personnel', 5, 3500, '2007-12-10'), 16('sales', 4, 4800, '2007-08-08'), 17('personnel', 2, 3900, '2006-12-23'), 18('develop', 7, 4200, '2008-01-01'), 19('develop', 9, 4500, '2008-01-01'), 20('sales', 3, 4800, '2007-08-01'), 21('develop', 8, 6000, '2006-10-01'), 22('develop', 11, 5200, '2007-08-15'); 23 24SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; 25 26SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; 27 28-- with GROUP BY 29SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1 30GROUP BY four, ten ORDER BY four, ten; 31 32SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); 33 34SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; 35 36-- empty window specification 37SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10; 38 39SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS (); 40 41-- no window operation 42SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten); 43 44-- cumulative aggregate 45SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10; 46 47SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10; 48 49SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10; 50 51SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 52 53SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 54 55SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 56 57SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10; 58 59SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2; 60 61SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 62 63SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 64 65SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 66 67SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 68 69SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 70 71SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 72 73SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 74 75-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window. 76SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 77 78SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM 79 (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s 80 ORDER BY four, ten; 81 82SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four 83 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s; 84 85SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum 86FROM tenk1 GROUP BY ten, two; 87 88SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10; 89 90SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) + 91 sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum 92 FROM tenk1 WHERE unique2 < 10; 93 94-- opexpr with different windows evaluation. 95SELECT * FROM( 96 SELECT count(*) OVER (PARTITION BY four ORDER BY ten) + 97 sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total, 98 count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount, 99 sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum 100 FROM tenk1 101)sub 102WHERE total <> fourcount + twosum; 103 104SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10; 105 106SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum 107FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten); 108 109-- more than one window with GROUP BY 110SELECT sum(salary), 111 row_number() OVER (ORDER BY depname), 112 sum(sum(salary)) OVER (ORDER BY depname DESC) 113FROM empsalary GROUP BY depname; 114 115-- identical windows with different names 116SELECT sum(salary) OVER w1, count(*) OVER w2 117FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary); 118 119-- subplan 120SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten) 121FROM tenk1 s WHERE unique2 < 10; 122 123-- empty table 124SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s; 125 126-- mixture of agg/wfunc in the same window 127SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); 128 129-- strict aggs 130SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM( 131 SELECT *, 132 CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, 133 CASE WHEN 134 AVG(salary) OVER (PARTITION BY depname) < salary 135 THEN 200 END AS depadj FROM empsalary 136)s; 137 138-- window function over ungrouped agg over empty row set (bug before 9.1) 139SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42; 140 141-- window function with ORDER BY an expression involving aggregates (9.1 bug) 142select ten, 143 sum(unique1) + sum(unique2) as res, 144 rank() over (order by sum(unique1) + sum(unique2)) as rank 145from tenk1 146group by ten order by ten; 147 148-- window and aggregate with GROUP BY expression (9.2 bug) 149explain (costs off) 150select first_value(max(x)) over (), y 151 from (select unique1 as x, ten+four as y from tenk1) ss 152 group by y; 153 154-- test non-default frame specifications 155SELECT four, ten, 156 sum(ten) over (partition by four order by ten), 157 last_value(ten) over (partition by four order by ten) 158FROM (select distinct ten, four from tenk1) ss; 159 160SELECT four, ten, 161 sum(ten) over (partition by four order by ten range between unbounded preceding and current row), 162 last_value(ten) over (partition by four order by ten range between unbounded preceding and current row) 163FROM (select distinct ten, four from tenk1) ss; 164 165SELECT four, ten, 166 sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following), 167 last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following) 168FROM (select distinct ten, four from tenk1) ss; 169 170SELECT four, ten/4 as two, 171 sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row), 172 last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row) 173FROM (select distinct ten, four from tenk1) ss; 174 175SELECT four, ten/4 as two, 176 sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row), 177 last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row) 178FROM (select distinct ten, four from tenk1) ss; 179 180SELECT sum(unique1) over (order by four range between current row and unbounded following), 181 unique1, four 182FROM tenk1 WHERE unique1 < 10; 183 184SELECT sum(unique1) over (rows between current row and unbounded following), 185 unique1, four 186FROM tenk1 WHERE unique1 < 10; 187 188SELECT sum(unique1) over (rows between 2 preceding and 2 following), 189 unique1, four 190FROM tenk1 WHERE unique1 < 10; 191 192SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others), 193 unique1, four 194FROM tenk1 WHERE unique1 < 10; 195 196SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), 197 unique1, four 198FROM tenk1 WHERE unique1 < 10; 199 200SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), 201 unique1, four 202FROM tenk1 WHERE unique1 < 10; 203 204SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), 205 unique1, four 206FROM tenk1 WHERE unique1 < 10; 207 208SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), 209 unique1, four 210FROM tenk1 WHERE unique1 < 10; 211 212SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), 213 unique1, four 214FROM tenk1 WHERE unique1 < 10; 215 216SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), 217 unique1, four 218FROM tenk1 WHERE unique1 < 10; 219 220SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), 221 unique1, four 222FROM tenk1 WHERE unique1 < 10; 223 224SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), 225 unique1, four 226FROM tenk1 WHERE unique1 < 10; 227 228SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), 229 unique1, four 230FROM tenk1 WHERE unique1 < 10; 231 232SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), 233 unique1, four 234FROM tenk1 WHERE unique1 < 10; 235 236SELECT sum(unique1) over (rows between 1 following and 3 following), 237 unique1, four 238FROM tenk1 WHERE unique1 < 10; 239 240SELECT sum(unique1) over (rows between unbounded preceding and 1 following), 241 unique1, four 242FROM tenk1 WHERE unique1 < 10; 243 244SELECT sum(unique1) over (w range between current row and unbounded following), 245 unique1, four 246FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 247 248SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), 249 unique1, four 250FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 251 252SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), 253 unique1, four 254FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 255 256SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), 257 unique1, four 258FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 259 260SELECT first_value(unique1) over w, 261 nth_value(unique1, 2) over w AS nth_2, 262 last_value(unique1) over w, unique1, four 263FROM tenk1 WHERE unique1 < 10 264WINDOW w AS (order by four range between current row and unbounded following); 265 266SELECT sum(unique1) over 267 (order by unique1 268 rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING), 269 unique1 270FROM tenk1 WHERE unique1 < 10; 271 272CREATE TEMP VIEW v_window AS 273 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows 274 FROM generate_series(1, 10) i; 275 276SELECT * FROM v_window; 277 278SELECT pg_get_viewdef('v_window'); 279 280CREATE OR REPLACE TEMP VIEW v_window AS 281 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following 282 exclude current row) as sum_rows FROM generate_series(1, 10) i; 283 284SELECT * FROM v_window; 285 286SELECT pg_get_viewdef('v_window'); 287 288CREATE OR REPLACE TEMP VIEW v_window AS 289 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following 290 exclude group) as sum_rows FROM generate_series(1, 10) i; 291 292SELECT * FROM v_window; 293 294SELECT pg_get_viewdef('v_window'); 295 296CREATE OR REPLACE TEMP VIEW v_window AS 297 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following 298 exclude ties) as sum_rows FROM generate_series(1, 10) i; 299 300SELECT * FROM v_window; 301 302SELECT pg_get_viewdef('v_window'); 303 304CREATE OR REPLACE TEMP VIEW v_window AS 305 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following 306 exclude no others) as sum_rows FROM generate_series(1, 10) i; 307 308SELECT * FROM v_window; 309 310SELECT pg_get_viewdef('v_window'); 311 312CREATE OR REPLACE TEMP VIEW v_window AS 313 SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following) as sum_rows FROM generate_series(1, 10) i; 314 315SELECT * FROM v_window; 316 317SELECT pg_get_viewdef('v_window'); 318 319DROP VIEW v_window; 320 321CREATE TEMP VIEW v_window AS 322 SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i 323 FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i; 324 325SELECT pg_get_viewdef('v_window'); 326 327-- RANGE offset PRECEDING/FOLLOWING tests 328 329SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), 330 unique1, four 331FROM tenk1 WHERE unique1 < 10; 332 333SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), 334 unique1, four 335FROM tenk1 WHERE unique1 < 10; 336 337SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), 338 unique1, four 339FROM tenk1 WHERE unique1 < 10; 340 341SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), 342 unique1, four 343FROM tenk1 WHERE unique1 < 10; 344 345SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), 346 unique1, four 347FROM tenk1 WHERE unique1 < 10; 348 349SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), 350 unique1, four 351FROM tenk1 WHERE unique1 < 10; 352 353SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), 354 unique1, four 355FROM tenk1 WHERE unique1 < 10; 356 357SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), 358 unique1, four 359FROM tenk1 WHERE unique1 < 10; 360 361SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), 362 unique1, four 363FROM tenk1 WHERE unique1 < 10; 364 365SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following 366 exclude current row),unique1, four 367FROM tenk1 WHERE unique1 < 10; 368 369select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), 370 salary, enroll_date from empsalary; 371 372select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), 373 salary, enroll_date from empsalary; 374 375select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), 376 salary, enroll_date from empsalary; 377 378select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following 379 exclude current row), salary, enroll_date from empsalary; 380 381select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following 382 exclude group), salary, enroll_date from empsalary; 383 384select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following 385 exclude ties), salary, enroll_date from empsalary; 386 387select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), 388 lead(salary) over(order by salary range between 1000 preceding and 1000 following), 389 nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), 390 salary from empsalary; 391 392select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), 393 lag(salary) over(order by salary range between 1000 preceding and 1000 following), 394 salary from empsalary; 395 396select first_value(salary) over(order by salary range between 1000 following and 3000 following 397 exclude current row), 398 lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), 399 nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following 400 exclude ties), 401 salary from empsalary; 402 403select last_value(salary) over(order by salary range between 1000 following and 3000 following 404 exclude group), 405 lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), 406 salary from empsalary; 407 408select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 409 exclude ties), 410 last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), 411 salary, enroll_date from empsalary; 412 413select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 414 exclude ties), 415 last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 416 exclude ties), 417 salary, enroll_date from empsalary; 418 419select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 420 exclude group), 421 last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 422 exclude group), 423 salary, enroll_date from empsalary; 424 425select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 426 exclude current row), 427 last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following 428 exclude current row), 429 salary, enroll_date from empsalary; 430 431-- RANGE offset PRECEDING/FOLLOWING with null values 432select x, y, 433 first_value(y) over w, 434 last_value(y) over w 435from 436 (select x, x as y from generate_series(1,5) as x 437 union all select null, 42 438 union all select null, 43) ss 439window w as 440 (order by x asc nulls first range between 2 preceding and 2 following); 441 442select x, y, 443 first_value(y) over w, 444 last_value(y) over w 445from 446 (select x, x as y from generate_series(1,5) as x 447 union all select null, 42 448 union all select null, 43) ss 449window w as 450 (order by x asc nulls last range between 2 preceding and 2 following); 451 452select x, y, 453 first_value(y) over w, 454 last_value(y) over w 455from 456 (select x, x as y from generate_series(1,5) as x 457 union all select null, 42 458 union all select null, 43) ss 459window w as 460 (order by x desc nulls first range between 2 preceding and 2 following); 461 462select x, y, 463 first_value(y) over w, 464 last_value(y) over w 465from 466 (select x, x as y from generate_series(1,5) as x 467 union all select null, 42 468 union all select null, 43) ss 469window w as 470 (order by x desc nulls last range between 2 preceding and 2 following); 471 472-- Check overflow behavior for various integer sizes 473 474select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following) 475from generate_series(32764, 32766) x; 476 477select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following) 478from generate_series(-32766, -32764) x; 479 480select x, last_value(x) over (order by x range between current row and 4 following) 481from generate_series(2147483644, 2147483646) x; 482 483select x, last_value(x) over (order by x desc range between current row and 5 following) 484from generate_series(-2147483646, -2147483644) x; 485 486select x, last_value(x) over (order by x range between current row and 4 following) 487from generate_series(9223372036854775804, 9223372036854775806) x; 488 489select x, last_value(x) over (order by x desc range between current row and 5 following) 490from generate_series(-9223372036854775806, -9223372036854775804) x; 491 492-- Test in_range for other numeric datatypes 493 494create temp table numerics( 495 id int, 496 f_float4 float4, 497 f_float8 float8, 498 f_numeric numeric 499); 500 501insert into numerics values 502(0, '-infinity', '-infinity', '-1000'), -- numeric type lacks infinities 503(1, -3, -3, -3), 504(2, -1, -1, -1), 505(3, 0, 0, 0), 506(4, 1.1, 1.1, 1.1), 507(5, 1.12, 1.12, 1.12), 508(6, 2, 2, 2), 509(7, 100, 100, 100), 510(8, 'infinity', 'infinity', '1000'), 511(9, 'NaN', 'NaN', 'NaN'); 512 513select id, f_float4, first_value(id) over w, last_value(id) over w 514from numerics 515window w as (order by f_float4 range between 516 1 preceding and 1 following); 517select id, f_float4, first_value(id) over w, last_value(id) over w 518from numerics 519window w as (order by f_float4 range between 520 1 preceding and 1.1::float4 following); 521select id, f_float4, first_value(id) over w, last_value(id) over w 522from numerics 523window w as (order by f_float4 range between 524 'inf' preceding and 'inf' following); 525select id, f_float4, first_value(id) over w, last_value(id) over w 526from numerics 527window w as (order by f_float4 range between 528 1.1 preceding and 'NaN' following); -- error, NaN disallowed 529 530select id, f_float8, first_value(id) over w, last_value(id) over w 531from numerics 532window w as (order by f_float8 range between 533 1 preceding and 1 following); 534select id, f_float8, first_value(id) over w, last_value(id) over w 535from numerics 536window w as (order by f_float8 range between 537 1 preceding and 1.1::float8 following); 538select id, f_float8, first_value(id) over w, last_value(id) over w 539from numerics 540window w as (order by f_float8 range between 541 'inf' preceding and 'inf' following); 542select id, f_float8, first_value(id) over w, last_value(id) over w 543from numerics 544window w as (order by f_float8 range between 545 1.1 preceding and 'NaN' following); -- error, NaN disallowed 546 547select id, f_numeric, first_value(id) over w, last_value(id) over w 548from numerics 549window w as (order by f_numeric range between 550 1 preceding and 1 following); 551select id, f_numeric, first_value(id) over w, last_value(id) over w 552from numerics 553window w as (order by f_numeric range between 554 1 preceding and 1.1::numeric following); 555select id, f_numeric, first_value(id) over w, last_value(id) over w 556from numerics 557window w as (order by f_numeric range between 558 1 preceding and 1.1::float8 following); -- currently unsupported 559select id, f_numeric, first_value(id) over w, last_value(id) over w 560from numerics 561window w as (order by f_numeric range between 562 1.1 preceding and 'NaN' following); -- error, NaN disallowed 563 564-- Test in_range for other datetime datatypes 565 566create temp table datetimes( 567 id int, 568 f_time time, 569 f_timetz timetz, 570 f_interval interval, 571 f_timestamptz timestamptz, 572 f_timestamp timestamp 573); 574 575insert into datetimes values 576(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'), 577(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), 578(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), 579(4, '14:00', '14:00 BST', '4 years', '2002-10-19 10:23:54+01', '2002-10-19 10:23:54'), 580(5, '15:00', '15:00 BST', '5 years', '2003-10-19 10:23:54+01', '2003-10-19 10:23:54'), 581(6, '15:00', '15:00 BST', '5 years', '2004-10-19 10:23:54+01', '2004-10-19 10:23:54'), 582(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'), 583(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'), 584(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'), 585(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54'); 586 587select id, f_time, first_value(id) over w, last_value(id) over w 588from datetimes 589window w as (order by f_time range between 590 '70 min'::interval preceding and '2 hours'::interval following); 591 592select id, f_time, first_value(id) over w, last_value(id) over w 593from datetimes 594window w as (order by f_time desc range between 595 '70 min' preceding and '2 hours' following); 596 597select id, f_timetz, first_value(id) over w, last_value(id) over w 598from datetimes 599window w as (order by f_timetz range between 600 '70 min'::interval preceding and '2 hours'::interval following); 601 602select id, f_timetz, first_value(id) over w, last_value(id) over w 603from datetimes 604window w as (order by f_timetz desc range between 605 '70 min' preceding and '2 hours' following); 606 607select id, f_interval, first_value(id) over w, last_value(id) over w 608from datetimes 609window w as (order by f_interval range between 610 '1 year'::interval preceding and '1 year'::interval following); 611 612select id, f_interval, first_value(id) over w, last_value(id) over w 613from datetimes 614window w as (order by f_interval desc range between 615 '1 year' preceding and '1 year' following); 616 617select id, f_timestamptz, first_value(id) over w, last_value(id) over w 618from datetimes 619window w as (order by f_timestamptz range between 620 '1 year'::interval preceding and '1 year'::interval following); 621 622select id, f_timestamptz, first_value(id) over w, last_value(id) over w 623from datetimes 624window w as (order by f_timestamptz desc range between 625 '1 year' preceding and '1 year' following); 626 627select id, f_timestamp, first_value(id) over w, last_value(id) over w 628from datetimes 629window w as (order by f_timestamp range between 630 '1 year'::interval preceding and '1 year'::interval following); 631 632select id, f_timestamp, first_value(id) over w, last_value(id) over w 633from datetimes 634window w as (order by f_timestamp desc range between 635 '1 year' preceding and '1 year' following); 636 637-- RANGE offset PRECEDING/FOLLOWING error cases 638select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following 639 exclude ties), salary, enroll_date from empsalary; 640 641select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following 642 exclude ties), salary, enroll_date from empsalary; 643 644select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following 645 exclude ties), salary, enroll_date from empsalary; 646 647select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following 648 exclude ties), salary, enroll_date from empsalary; 649 650select max(enroll_date) over (order by salary range between -1 preceding and 2 following 651 exclude ties), salary, enroll_date from empsalary; 652 653select max(enroll_date) over (order by salary range between 1 preceding and -2 following 654 exclude ties), salary, enroll_date from empsalary; 655 656select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following 657 exclude ties), salary, enroll_date from empsalary; 658 659select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following 660 exclude ties), salary, enroll_date from empsalary; 661 662-- GROUPS tests 663 664SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), 665 unique1, four 666FROM tenk1 WHERE unique1 < 10; 667 668SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), 669 unique1, four 670FROM tenk1 WHERE unique1 < 10; 671 672SELECT sum(unique1) over (order by four groups between current row and unbounded following), 673 unique1, four 674FROM tenk1 WHERE unique1 < 10; 675 676SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), 677 unique1, four 678FROM tenk1 WHERE unique1 < 10; 679 680SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), 681 unique1, four 682FROM tenk1 WHERE unique1 < 10; 683 684SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), 685 unique1, four 686FROM tenk1 WHERE unique1 < 10; 687 688SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), 689 unique1, four 690FROM tenk1 WHERE unique1 < 10; 691 692SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), 693 unique1, four 694FROM tenk1 WHERE unique1 < 10; 695 696SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), 697 unique1, four 698FROM tenk1 WHERE unique1 < 10; 699 700SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following 701 exclude current row), unique1, four 702FROM tenk1 WHERE unique1 < 10; 703 704SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following 705 exclude group), unique1, four 706FROM tenk1 WHERE unique1 < 10; 707 708SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following 709 exclude ties), unique1, four 710FROM tenk1 WHERE unique1 < 10; 711 712SELECT sum(unique1) over (partition by ten 713 order by four groups between 0 preceding and 0 following),unique1, four, ten 714FROM tenk1 WHERE unique1 < 10; 715 716SELECT sum(unique1) over (partition by ten 717 order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten 718FROM tenk1 WHERE unique1 < 10; 719 720SELECT sum(unique1) over (partition by ten 721 order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten 722FROM tenk1 WHERE unique1 < 10; 723 724SELECT sum(unique1) over (partition by ten 725 order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten 726FROM tenk1 WHERE unique1 < 10; 727 728select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), 729 lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), 730 nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), 731 salary, enroll_date from empsalary; 732 733select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), 734 lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), 735 salary, enroll_date from empsalary; 736 737select first_value(salary) over(order by enroll_date groups between 1 following and 3 following 738 exclude current row), 739 lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), 740 nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following 741 exclude ties), 742 salary, enroll_date from empsalary; 743 744select last_value(salary) over(order by enroll_date groups between 1 following and 3 following 745 exclude group), 746 lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), 747 salary, enroll_date from empsalary; 748 749-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS 750WITH cte (x) AS ( 751 SELECT * FROM generate_series(1, 35, 2) 752) 753SELECT x, (sum(x) over w) 754FROM cte 755WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); 756 757WITH cte (x) AS ( 758 SELECT * FROM generate_series(1, 35, 2) 759) 760SELECT x, (sum(x) over w) 761FROM cte 762WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); 763 764WITH cte (x) AS ( 765 SELECT * FROM generate_series(1, 35, 2) 766) 767SELECT x, (sum(x) over w) 768FROM cte 769WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); 770 771WITH cte (x) AS ( 772 select 1 union all select 1 union all select 1 union all 773 SELECT * FROM generate_series(5, 49, 2) 774) 775SELECT x, (sum(x) over w) 776FROM cte 777WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); 778 779WITH cte (x) AS ( 780 select 1 union all select 1 union all select 1 union all 781 SELECT * FROM generate_series(5, 49, 2) 782) 783SELECT x, (sum(x) over w) 784FROM cte 785WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); 786 787WITH cte (x) AS ( 788 select 1 union all select 1 union all select 1 union all 789 SELECT * FROM generate_series(5, 49, 2) 790) 791SELECT x, (sum(x) over w) 792FROM cte 793WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); 794 795-- with UNION 796SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; 797 798-- check some degenerate cases 799create temp table t1 (f1 int, f2 int8); 800insert into t1 values (1,1),(1,2),(2,2); 801 802select f1, sum(f1) over (partition by f1 803 range between 1 preceding and 1 following) 804from t1 where f1 = f2; -- error, must have order by 805explain (costs off) 806select f1, sum(f1) over (partition by f1 order by f2 807 range between 1 preceding and 1 following) 808from t1 where f1 = f2; 809select f1, sum(f1) over (partition by f1 order by f2 810 range between 1 preceding and 1 following) 811from t1 where f1 = f2; 812select f1, sum(f1) over (partition by f1, f1 order by f2 813 range between 2 preceding and 1 preceding) 814from t1 where f1 = f2; 815select f1, sum(f1) over (partition by f1, f2 order by f2 816 range between 1 following and 2 following) 817from t1 where f1 = f2; 818 819select f1, sum(f1) over (partition by f1 820 groups between 1 preceding and 1 following) 821from t1 where f1 = f2; -- error, must have order by 822explain (costs off) 823select f1, sum(f1) over (partition by f1 order by f2 824 groups between 1 preceding and 1 following) 825from t1 where f1 = f2; 826select f1, sum(f1) over (partition by f1 order by f2 827 groups between 1 preceding and 1 following) 828from t1 where f1 = f2; 829select f1, sum(f1) over (partition by f1, f1 order by f2 830 groups between 2 preceding and 1 preceding) 831from t1 where f1 = f2; 832select f1, sum(f1) over (partition by f1, f2 order by f2 833 groups between 1 following and 2 following) 834from t1 where f1 = f2; 835 836-- ordering by a non-integer constant is allowed 837SELECT rank() OVER (ORDER BY length('abc')); 838 839-- can't order by another window function 840SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())); 841 842-- some other errors 843SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10; 844 845SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10; 846 847SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1; 848 849SELECT * FROM rank() OVER (ORDER BY random()); 850 851DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10; 852 853DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random()); 854 855SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1); 856 857SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1; 858 859SELECT count() OVER () FROM tenk1; 860 861SELECT generate_series(1, 100) OVER () FROM empsalary; 862 863SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; 864 865SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; 866 867-- filter 868 869SELECT sum(salary), row_number() OVER (ORDER BY depname), sum( 870 sum(salary) FILTER (WHERE enroll_date > '2007-01-01') 871) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", 872 depname 873FROM empsalary GROUP BY depname; 874 875-- Test pushdown of quals into a subquery containing window functions 876 877-- pushdown is safe because all PARTITION BY clauses include depname: 878EXPLAIN (COSTS OFF) 879SELECT * FROM 880 (SELECT depname, 881 sum(salary) OVER (PARTITION BY depname) depsalary, 882 min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary 883 FROM empsalary) emp 884WHERE depname = 'sales'; 885 886-- pushdown is unsafe because there's a PARTITION BY clause without depname: 887EXPLAIN (COSTS OFF) 888SELECT * FROM 889 (SELECT depname, 890 sum(salary) OVER (PARTITION BY enroll_date) enroll_salary, 891 min(salary) OVER (PARTITION BY depname) depminsalary 892 FROM empsalary) emp 893WHERE depname = 'sales'; 894 895-- Test Sort node collapsing 896EXPLAIN (COSTS OFF) 897SELECT * FROM 898 (SELECT depname, 899 sum(salary) OVER (PARTITION BY depname order by empno) depsalary, 900 min(salary) OVER (PARTITION BY depname, empno order by enroll_date) depminsalary 901 FROM empsalary) emp 902WHERE depname = 'sales'; 903 904-- Test Sort node reordering 905EXPLAIN (COSTS OFF) 906SELECT 907 lead(1) OVER (PARTITION BY depname ORDER BY salary, enroll_date), 908 lag(1) OVER (PARTITION BY depname ORDER BY salary,enroll_date,empno) 909FROM empsalary; 910 911-- cleanup 912DROP TABLE empsalary; 913 914-- test user-defined window function with named args and default args 915CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement 916 LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value'; 917 918SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four 919 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; 920 921SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four 922 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; 923 924-- 925-- Test the basic moving-aggregate machinery 926-- 927 928-- create aggregates that record the series of transform calls (these are 929-- intentionally not true inverses) 930 931CREATE FUNCTION logging_sfunc_nonstrict(text, anyelement) RETURNS text AS 932$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$ 933LANGUAGE SQL IMMUTABLE; 934 935CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS 936$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$ 937LANGUAGE SQL IMMUTABLE; 938 939CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS 940$$ SELECT $1 || '-' || quote_nullable($2) $$ 941LANGUAGE SQL IMMUTABLE; 942 943CREATE AGGREGATE logging_agg_nonstrict (anyelement) 944( 945 stype = text, 946 sfunc = logging_sfunc_nonstrict, 947 mstype = text, 948 msfunc = logging_msfunc_nonstrict, 949 minvfunc = logging_minvfunc_nonstrict 950); 951 952CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement) 953( 954 stype = text, 955 sfunc = logging_sfunc_nonstrict, 956 mstype = text, 957 msfunc = logging_msfunc_nonstrict, 958 minvfunc = logging_minvfunc_nonstrict, 959 initcond = 'I', 960 minitcond = 'MI' 961); 962 963CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS 964$$ SELECT $1 || '*' || quote_nullable($2) $$ 965LANGUAGE SQL STRICT IMMUTABLE; 966 967CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS 968$$ SELECT $1 || '+' || quote_nullable($2) $$ 969LANGUAGE SQL STRICT IMMUTABLE; 970 971CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS 972$$ SELECT $1 || '-' || quote_nullable($2) $$ 973LANGUAGE SQL STRICT IMMUTABLE; 974 975CREATE AGGREGATE logging_agg_strict (text) 976( 977 stype = text, 978 sfunc = logging_sfunc_strict, 979 mstype = text, 980 msfunc = logging_msfunc_strict, 981 minvfunc = logging_minvfunc_strict 982); 983 984CREATE AGGREGATE logging_agg_strict_initcond (anyelement) 985( 986 stype = text, 987 sfunc = logging_sfunc_strict, 988 mstype = text, 989 msfunc = logging_msfunc_strict, 990 minvfunc = logging_minvfunc_strict, 991 initcond = 'I', 992 minitcond = 'MI' 993); 994 995-- test strict and non-strict cases 996SELECT 997 p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS row, 998 logging_agg_nonstrict(v) over wnd as nstrict, 999 logging_agg_nonstrict_initcond(v) over wnd as nstrict_init, 1000 logging_agg_strict(v::text) over wnd as strict, 1001 logging_agg_strict_initcond(v) over wnd as strict_init 1002FROM (VALUES 1003 (1, 1, NULL), 1004 (1, 2, 'a'), 1005 (1, 3, 'b'), 1006 (1, 4, NULL), 1007 (1, 5, NULL), 1008 (1, 6, 'c'), 1009 (2, 1, NULL), 1010 (2, 2, 'x'), 1011 (3, 1, 'z') 1012) AS t(p, i, v) 1013WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1014ORDER BY p, i; 1015 1016-- and again, but with filter 1017SELECT 1018 p::text || ',' || i::text || ':' || 1019 CASE WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row, 1020 logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt, 1021 logging_agg_nonstrict_initcond(v) filter(where f) over wnd as nstrict_init_filt, 1022 logging_agg_strict(v::text) filter(where f) over wnd as strict_filt, 1023 logging_agg_strict_initcond(v) filter(where f) over wnd as strict_init_filt 1024FROM (VALUES 1025 (1, 1, true, NULL), 1026 (1, 2, false, 'a'), 1027 (1, 3, true, 'b'), 1028 (1, 4, false, NULL), 1029 (1, 5, false, NULL), 1030 (1, 6, false, 'c'), 1031 (2, 1, false, NULL), 1032 (2, 2, true, 'x'), 1033 (3, 1, true, 'z') 1034) AS t(p, i, f, v) 1035WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1036ORDER BY p, i; 1037 1038-- test that volatile arguments disable moving-aggregate mode 1039SELECT 1040 i::text || ':' || COALESCE(v::text, 'NULL') as row, 1041 logging_agg_strict(v::text) 1042 over wnd as inverse, 1043 logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END) 1044 over wnd as noinverse 1045FROM (VALUES 1046 (1, 'a'), 1047 (2, 'b'), 1048 (3, 'c') 1049) AS t(i, v) 1050WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1051ORDER BY i; 1052 1053SELECT 1054 i::text || ':' || COALESCE(v::text, 'NULL') as row, 1055 logging_agg_strict(v::text) filter(where true) 1056 over wnd as inverse, 1057 logging_agg_strict(v::text) filter(where random() >= 0) 1058 over wnd as noinverse 1059FROM (VALUES 1060 (1, 'a'), 1061 (2, 'b'), 1062 (3, 'c') 1063) AS t(i, v) 1064WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1065ORDER BY i; 1066 1067-- test that non-overlapping windows don't use inverse transitions 1068SELECT 1069 logging_agg_strict(v::text) OVER wnd 1070FROM (VALUES 1071 (1, 'a'), 1072 (2, 'b'), 1073 (3, 'c') 1074) AS t(i, v) 1075WINDOW wnd AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) 1076ORDER BY i; 1077 1078-- test that returning NULL from the inverse transition functions 1079-- restarts the aggregation from scratch. The second aggregate is supposed 1080-- to test cases where only some aggregates restart, the third one checks 1081-- that one aggregate restarting doesn't cause others to restart. 1082 1083CREATE FUNCTION sum_int_randrestart_minvfunc(int4, int4) RETURNS int4 AS 1084$$ SELECT CASE WHEN random() < 0.2 THEN NULL ELSE $1 - $2 END $$ 1085LANGUAGE SQL STRICT; 1086 1087CREATE AGGREGATE sum_int_randomrestart (int4) 1088( 1089 stype = int4, 1090 sfunc = int4pl, 1091 mstype = int4, 1092 msfunc = int4pl, 1093 minvfunc = sum_int_randrestart_minvfunc 1094); 1095 1096WITH 1097vs AS ( 1098 SELECT i, (random() * 100)::int4 AS v 1099 FROM generate_series(1, 100) AS i 1100), 1101sum_following AS ( 1102 SELECT i, SUM(v) OVER 1103 (ORDER BY i DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s 1104 FROM vs 1105) 1106SELECT DISTINCT 1107 sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1, 1108 -sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2, 1109 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 1110FROM vs 1111JOIN sum_following ON sum_following.i = vs.i 1112WINDOW fwd AS ( 1113 ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 1114); 1115 1116-- 1117-- Test various built-in aggregates that have moving-aggregate support 1118-- 1119 1120-- test inverse transition functions handle NULLs properly 1121SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1122 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1123 1124SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1125 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1126 1127SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1128 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1129 1130SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1131 FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v); 1132 1133SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1134 FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); 1135 1136SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1137 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1138 1139SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1140 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1141 1142SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1143 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1144 1145SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1146 FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v); 1147 1148SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1149 FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); 1150 1151SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1152 FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v); 1153 1154SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1155 FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n); 1156 1157SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1158 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1159 1160SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1161 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1162 1163SELECT VAR_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1164 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1165 1166SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1167 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1168 1169SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1170 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1171 1172SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1173 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1174 1175SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1176 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1177 1178SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1179 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1180 1181SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1182 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1183 1184SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1185 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1186 1187SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1188 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1189 1190SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1191 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1192 1193SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1194 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1195 1196SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1197 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1198 1199SELECT STDDEV_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1200 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1201 1202SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1203 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1204 1205SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1206 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1207 1208SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1209 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1210 1211SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1212 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1213 1214SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1215 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1216 1217SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1218 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1219 1220SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1221 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 1222 1223SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1224 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1225 1226SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1227 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1228 1229SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1230 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1231 1232SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 1233 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 1234 1235-- test that inverse transition functions work with various frame options 1236SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) 1237 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1238 1239SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) 1240 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 1241 1242SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 1243 FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v); 1244 1245-- ensure aggregate over numeric properly recovers from NaN values 1246SELECT a, b, 1247 SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 1248FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b); 1249 1250-- It might be tempting for someone to add an inverse trans function for 1251-- float and double precision. This should not be done as it can give incorrect 1252-- results. This test should fail if anyone ever does this without thinking too 1253-- hard about it. 1254SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9') 1255 FROM (VALUES(1,1e20),(2,1)) n(i,n); 1256 1257SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w 1258 FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b) 1259 WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING); 1260 1261-- Tests for problems with failure to walk or mutate expressions 1262-- within window frame clauses. 1263 1264-- test walker (fails with collation error if expressions are not walked) 1265SELECT array_agg(i) OVER w 1266 FROM generate_series(1,5) i 1267WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW); 1268 1269-- test mutator (fails when inlined if expressions are not mutated) 1270CREATE FUNCTION pg_temp.f(group_size BIGINT) RETURNS SETOF integer[] 1271AS $$ 1272 SELECT array_agg(s) OVER w 1273 FROM generate_series(1,5) s 1274 WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING) 1275$$ LANGUAGE SQL STABLE; 1276 1277EXPLAIN (costs off) SELECT * FROM pg_temp.f(2); 1278SELECT * FROM pg_temp.f(2); 1279