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 1 preceding), 193 unique1, four 194FROM tenk1 WHERE unique1 < 10; 195 196SELECT sum(unique1) over (rows between 1 following and 3 following), 197 unique1, four 198FROM tenk1 WHERE unique1 < 10; 199 200SELECT sum(unique1) over (rows between unbounded preceding and 1 following), 201 unique1, four 202FROM tenk1 WHERE unique1 < 10; 203 204SELECT sum(unique1) over (w range between current row and unbounded following), 205 unique1, four 206FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 207 208-- fail: not implemented yet 209SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), 210 unique1, four 211FROM tenk1 WHERE unique1 < 10; 212 213SELECT first_value(unique1) over w, 214 nth_value(unique1, 2) over w AS nth_2, 215 last_value(unique1) over w, unique1, four 216FROM tenk1 WHERE unique1 < 10 217WINDOW w AS (order by four range between current row and unbounded following); 218 219SELECT sum(unique1) over 220 (order by unique1 221 rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING), 222 unique1 223FROM tenk1 WHERE unique1 < 10; 224 225CREATE TEMP VIEW v_window AS 226 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows 227 FROM generate_series(1, 10) i; 228 229SELECT * FROM v_window; 230 231SELECT pg_get_viewdef('v_window'); 232 233-- with UNION 234SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; 235 236-- ordering by a non-integer constant is allowed 237SELECT rank() OVER (ORDER BY length('abc')); 238 239-- can't order by another window function 240SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())); 241 242-- some other errors 243SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10; 244 245SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10; 246 247SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1; 248 249SELECT * FROM rank() OVER (ORDER BY random()); 250 251DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10; 252 253DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random()); 254 255SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1); 256 257SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1; 258 259SELECT count() OVER () FROM tenk1; 260 261SELECT generate_series(1, 100) OVER () FROM empsalary; 262 263SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; 264 265SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; 266 267-- filter 268 269SELECT sum(salary), row_number() OVER (ORDER BY depname), sum( 270 sum(salary) FILTER (WHERE enroll_date > '2007-01-01') 271) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", 272 depname 273FROM empsalary GROUP BY depname; 274 275-- Test pushdown of quals into a subquery containing window functions 276 277-- pushdown is safe because all PARTITION BY clauses include depname: 278EXPLAIN (COSTS OFF) 279SELECT * FROM 280 (SELECT depname, 281 sum(salary) OVER (PARTITION BY depname) depsalary, 282 min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary 283 FROM empsalary) emp 284WHERE depname = 'sales'; 285 286-- pushdown is unsafe because there's a PARTITION BY clause without depname: 287EXPLAIN (COSTS OFF) 288SELECT * FROM 289 (SELECT depname, 290 sum(salary) OVER (PARTITION BY enroll_date) enroll_salary, 291 min(salary) OVER (PARTITION BY depname) depminsalary 292 FROM empsalary) emp 293WHERE depname = 'sales'; 294 295-- cleanup 296DROP TABLE empsalary; 297 298-- test user-defined window function with named args and default args 299CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement 300 LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value'; 301 302SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four 303 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; 304 305SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four 306 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; 307 308-- 309-- Test the basic moving-aggregate machinery 310-- 311 312-- create aggregates that record the series of transform calls (these are 313-- intentionally not true inverses) 314 315CREATE FUNCTION logging_sfunc_nonstrict(text, anyelement) RETURNS text AS 316$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$ 317LANGUAGE SQL IMMUTABLE; 318 319CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS 320$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$ 321LANGUAGE SQL IMMUTABLE; 322 323CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS 324$$ SELECT $1 || '-' || quote_nullable($2) $$ 325LANGUAGE SQL IMMUTABLE; 326 327CREATE AGGREGATE logging_agg_nonstrict (anyelement) 328( 329 stype = text, 330 sfunc = logging_sfunc_nonstrict, 331 mstype = text, 332 msfunc = logging_msfunc_nonstrict, 333 minvfunc = logging_minvfunc_nonstrict 334); 335 336CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement) 337( 338 stype = text, 339 sfunc = logging_sfunc_nonstrict, 340 mstype = text, 341 msfunc = logging_msfunc_nonstrict, 342 minvfunc = logging_minvfunc_nonstrict, 343 initcond = 'I', 344 minitcond = 'MI' 345); 346 347CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS 348$$ SELECT $1 || '*' || quote_nullable($2) $$ 349LANGUAGE SQL STRICT IMMUTABLE; 350 351CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS 352$$ SELECT $1 || '+' || quote_nullable($2) $$ 353LANGUAGE SQL STRICT IMMUTABLE; 354 355CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS 356$$ SELECT $1 || '-' || quote_nullable($2) $$ 357LANGUAGE SQL STRICT IMMUTABLE; 358 359CREATE AGGREGATE logging_agg_strict (text) 360( 361 stype = text, 362 sfunc = logging_sfunc_strict, 363 mstype = text, 364 msfunc = logging_msfunc_strict, 365 minvfunc = logging_minvfunc_strict 366); 367 368CREATE AGGREGATE logging_agg_strict_initcond (anyelement) 369( 370 stype = text, 371 sfunc = logging_sfunc_strict, 372 mstype = text, 373 msfunc = logging_msfunc_strict, 374 minvfunc = logging_minvfunc_strict, 375 initcond = 'I', 376 minitcond = 'MI' 377); 378 379-- test strict and non-strict cases 380SELECT 381 p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS row, 382 logging_agg_nonstrict(v) over wnd as nstrict, 383 logging_agg_nonstrict_initcond(v) over wnd as nstrict_init, 384 logging_agg_strict(v::text) over wnd as strict, 385 logging_agg_strict_initcond(v) over wnd as strict_init 386FROM (VALUES 387 (1, 1, NULL), 388 (1, 2, 'a'), 389 (1, 3, 'b'), 390 (1, 4, NULL), 391 (1, 5, NULL), 392 (1, 6, 'c'), 393 (2, 1, NULL), 394 (2, 2, 'x'), 395 (3, 1, 'z') 396) AS t(p, i, v) 397WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 398ORDER BY p, i; 399 400-- and again, but with filter 401SELECT 402 p::text || ',' || i::text || ':' || 403 CASE WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row, 404 logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt, 405 logging_agg_nonstrict_initcond(v) filter(where f) over wnd as nstrict_init_filt, 406 logging_agg_strict(v::text) filter(where f) over wnd as strict_filt, 407 logging_agg_strict_initcond(v) filter(where f) over wnd as strict_init_filt 408FROM (VALUES 409 (1, 1, true, NULL), 410 (1, 2, false, 'a'), 411 (1, 3, true, 'b'), 412 (1, 4, false, NULL), 413 (1, 5, false, NULL), 414 (1, 6, false, 'c'), 415 (2, 1, false, NULL), 416 (2, 2, true, 'x'), 417 (3, 1, true, 'z') 418) AS t(p, i, f, v) 419WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 420ORDER BY p, i; 421 422-- test that volatile arguments disable moving-aggregate mode 423SELECT 424 i::text || ':' || COALESCE(v::text, 'NULL') as row, 425 logging_agg_strict(v::text) 426 over wnd as inverse, 427 logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END) 428 over wnd as noinverse 429FROM (VALUES 430 (1, 'a'), 431 (2, 'b'), 432 (3, 'c') 433) AS t(i, v) 434WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 435ORDER BY i; 436 437SELECT 438 i::text || ':' || COALESCE(v::text, 'NULL') as row, 439 logging_agg_strict(v::text) filter(where true) 440 over wnd as inverse, 441 logging_agg_strict(v::text) filter(where random() >= 0) 442 over wnd as noinverse 443FROM (VALUES 444 (1, 'a'), 445 (2, 'b'), 446 (3, 'c') 447) AS t(i, v) 448WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 449ORDER BY i; 450 451-- test that non-overlapping windows don't use inverse transitions 452SELECT 453 logging_agg_strict(v::text) OVER wnd 454FROM (VALUES 455 (1, 'a'), 456 (2, 'b'), 457 (3, 'c') 458) AS t(i, v) 459WINDOW wnd AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) 460ORDER BY i; 461 462-- test that returning NULL from the inverse transition functions 463-- restarts the aggregation from scratch. The second aggregate is supposed 464-- to test cases where only some aggregates restart, the third one checks 465-- that one aggregate restarting doesn't cause others to restart. 466 467CREATE FUNCTION sum_int_randrestart_minvfunc(int4, int4) RETURNS int4 AS 468$$ SELECT CASE WHEN random() < 0.2 THEN NULL ELSE $1 - $2 END $$ 469LANGUAGE SQL STRICT; 470 471CREATE AGGREGATE sum_int_randomrestart (int4) 472( 473 stype = int4, 474 sfunc = int4pl, 475 mstype = int4, 476 msfunc = int4pl, 477 minvfunc = sum_int_randrestart_minvfunc 478); 479 480WITH 481vs AS ( 482 SELECT i, (random() * 100)::int4 AS v 483 FROM generate_series(1, 100) AS i 484), 485sum_following AS ( 486 SELECT i, SUM(v) OVER 487 (ORDER BY i DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s 488 FROM vs 489) 490SELECT DISTINCT 491 sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1, 492 -sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2, 493 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 494FROM vs 495JOIN sum_following ON sum_following.i = vs.i 496WINDOW fwd AS ( 497 ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 498); 499 500-- 501-- Test various built-in aggregates that have moving-aggregate support 502-- 503 504-- test inverse transition functions handle NULLs properly 505SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 506 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 507 508SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 509 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 510 511SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 512 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 513 514SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 515 FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v); 516 517SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 518 FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); 519 520SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 521 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 522 523SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 524 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 525 526SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 527 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 528 529SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 530 FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v); 531 532SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 533 FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); 534 535SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 536 FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v); 537 538SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 539 FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n); 540 541SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 542 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 543 544SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 545 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 546 547SELECT VAR_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 548 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 549 550SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 551 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 552 553SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 554 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 555 556SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 557 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 558 559SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 560 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 561 562SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 563 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 564 565SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 566 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 567 568SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 569 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 570 571SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 572 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 573 574SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 575 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 576 577SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 578 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 579 580SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 581 FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 582 583SELECT STDDEV_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 584 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 585 586SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 587 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 588 589SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 590 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 591 592SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 593 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 594 595SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 596 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 597 598SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 599 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 600 601SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 602 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 603 604SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 605 FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); 606 607SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 608 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 609 610SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 611 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 612 613SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 614 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 615 616SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 617 FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); 618 619-- test that inverse transition functions work with various frame options 620SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) 621 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 622 623SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) 624 FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); 625 626SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 627 FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v); 628 629-- ensure aggregate over numeric properly recovers from NaN values 630SELECT a, b, 631 SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 632FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b); 633 634-- It might be tempting for someone to add an inverse trans function for 635-- float and double precision. This should not be done as it can give incorrect 636-- results. This test should fail if anyone ever does this without thinking too 637-- hard about it. 638SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9') 639 FROM (VALUES(1,1e20),(2,1)) n(i,n); 640 641SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w 642 FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b) 643 WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING); 644 645-- Tests for problems with failure to walk or mutate expressions 646-- within window frame clauses. 647 648-- test walker (fails with collation error if expressions are not walked) 649SELECT array_agg(i) OVER w 650 FROM generate_series(1,5) i 651WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW); 652 653-- test mutator (fails when inlined if expressions are not mutated) 654CREATE FUNCTION pg_temp.f(group_size BIGINT) RETURNS SETOF integer[] 655AS $$ 656 SELECT array_agg(s) OVER w 657 FROM generate_series(1,5) s 658 WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING) 659$$ LANGUAGE SQL STABLE; 660 661EXPLAIN (costs off) SELECT * FROM pg_temp.f(2); 662SELECT * FROM pg_temp.f(2); 663