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