4 CREATE TEMPORARY TABLE empsalary (
10 INSERT INTO empsalary VALUES
11 ('develop', 10, 5200, '2007-08-01'),
12 ('sales', 1, 5000, '2006-10-01'),
13 ('personnel', 5, 3500, '2007-12-10'),
14 ('sales', 4, 4800, '2007-08-08'),
15 ('personnel', 2, 3900, '2006-12-23'),
16 ('develop', 7, 4200, '2008-01-01'),
17 ('develop', 9, 4500, '2008-01-01'),
18 ('sales', 3, 4800, '2007-08-01'),
19 ('develop', 8, 6000, '2006-10-01'),
20 ('develop', 11, 5200, '2007-08-15');
21 SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
22 depname | empno | salary | sum
23 -----------+-------+--------+-------
24 develop | 7 | 4200 | 25100
25 develop | 9 | 4500 | 25100
26 develop | 11 | 5200 | 25100
27 develop | 10 | 5200 | 25100
28 develop | 8 | 6000 | 25100
29 personnel | 5 | 3500 | 7400
30 personnel | 2 | 3900 | 7400
31 sales | 3 | 4800 | 14600
32 sales | 4 | 4800 | 14600
33 sales | 1 | 5000 | 14600
36 SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;
37 depname | empno | salary | rank
38 -----------+-------+--------+------
39 develop | 7 | 4200 | 1
40 develop | 9 | 4500 | 2
41 develop | 11 | 5200 | 3
42 develop | 10 | 5200 | 3
43 develop | 8 | 6000 | 5
44 personnel | 5 | 3500 | 1
45 personnel | 2 | 3900 | 2
52 SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1
53 GROUP BY four, ten ORDER BY four, ten;
54 four | ten | sum | avg
55 ------+-----+------+------------------------
56 0 | 0 | 0 | 0.00000000000000000000
57 0 | 2 | 0 | 2.0000000000000000
58 0 | 4 | 0 | 4.0000000000000000
59 0 | 6 | 0 | 6.0000000000000000
60 0 | 8 | 0 | 8.0000000000000000
61 1 | 1 | 2500 | 1.00000000000000000000
62 1 | 3 | 2500 | 3.0000000000000000
63 1 | 5 | 2500 | 5.0000000000000000
64 1 | 7 | 2500 | 7.0000000000000000
65 1 | 9 | 2500 | 9.0000000000000000
66 2 | 0 | 5000 | 0.00000000000000000000
67 2 | 2 | 5000 | 2.0000000000000000
68 2 | 4 | 5000 | 4.0000000000000000
69 2 | 6 | 5000 | 6.0000000000000000
70 2 | 8 | 5000 | 8.0000000000000000
71 3 | 1 | 7500 | 1.00000000000000000000
72 3 | 3 | 7500 | 3.0000000000000000
73 3 | 5 | 7500 | 5.0000000000000000
74 3 | 7 | 7500 | 7.0000000000000000
75 3 | 9 | 7500 | 9.0000000000000000
78 SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
79 depname | empno | salary | sum
80 -----------+-------+--------+-------
81 develop | 11 | 5200 | 25100
82 develop | 7 | 4200 | 25100
83 develop | 9 | 4500 | 25100
84 develop | 8 | 6000 | 25100
85 develop | 10 | 5200 | 25100
86 personnel | 5 | 3500 | 7400
87 personnel | 2 | 3900 | 7400
88 sales | 3 | 4800 | 14600
89 sales | 1 | 5000 | 14600
90 sales | 4 | 4800 | 14600
93 SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;
94 depname | empno | salary | rank
95 -----------+-------+--------+------
96 develop | 7 | 4200 | 1
97 personnel | 5 | 3500 | 1
100 personnel | 2 | 3900 | 2
101 develop | 9 | 4500 | 2
103 develop | 11 | 5200 | 3
104 develop | 10 | 5200 | 3
105 develop | 8 | 6000 | 5
108 -- empty window specification
109 SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
124 SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
139 -- no window operation
140 SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
145 -- cumulative aggregate
146 SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
161 SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
176 SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
178 --------+-----+------
191 SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
192 dense_rank | ten | four
193 ------------+-----+------
206 SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
207 percent_rank | ten | four
208 -------------------+-----+------
214 0.666666666666667 | 7 | 1
221 SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
222 cume_dist | ten | four
223 -------------------+-----+------
224 0.666666666666667 | 0 | 0
225 0.666666666666667 | 0 | 0
236 SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10;
251 SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2;
258 SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
273 SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
288 SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
303 SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
318 SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
333 SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
348 SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
349 first_value | ten | four
350 -------------+-----+------
363 -- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window.
364 SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
365 last_value | ten | four
366 ------------+-----+------
379 SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM
380 (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
382 last_value | ten | four
383 ------------+-----+------
396 SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four
397 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;
398 nth_value | ten | four
399 -----------+-----+------
412 SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
413 FROM tenk1 GROUP BY ten, two;
414 ten | two | gsum | wsum
415 -----+-----+-------+--------
416 0 | 0 | 45000 | 45000
417 2 | 0 | 47000 | 92000
418 4 | 0 | 49000 | 141000
419 6 | 0 | 51000 | 192000
420 8 | 0 | 53000 | 245000
421 1 | 1 | 46000 | 46000
422 3 | 1 | 48000 | 94000
423 5 | 1 | 50000 | 144000
424 7 | 1 | 52000 | 196000
425 9 | 1 | 54000 | 250000
428 SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
439 SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) +
440 sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum
441 FROM tenk1 WHERE unique2 < 10;
456 -- opexpr with different windows evaluation.
458 SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
459 sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
460 count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
461 sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
464 WHERE total <> fourcount + twosum;
465 total | fourcount | twosum
466 -------+-----------+--------
469 SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
471 ------------------------
472 0.00000000000000000000
473 0.00000000000000000000
474 0.00000000000000000000
475 1.00000000000000000000
476 1.00000000000000000000
477 1.00000000000000000000
478 1.00000000000000000000
484 SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
485 FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);
486 ten | two | gsum | wsum
487 -----+-----+-------+--------
488 0 | 0 | 45000 | 45000
489 2 | 0 | 47000 | 92000
490 4 | 0 | 49000 | 141000
491 6 | 0 | 51000 | 192000
492 8 | 0 | 53000 | 245000
493 1 | 1 | 46000 | 46000
494 3 | 1 | 48000 | 94000
495 5 | 1 | 50000 | 144000
496 7 | 1 | 52000 | 196000
497 9 | 1 | 54000 | 250000
500 -- more than one window with GROUP BY
502 row_number() OVER (ORDER BY depname),
503 sum(sum(salary)) OVER (ORDER BY depname DESC)
504 FROM empsalary GROUP BY depname;
505 sum | row_number | sum
506 -------+------------+-------
512 -- identical windows with different names
513 SELECT sum(salary) OVER w1, count(*) OVER w2
514 FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary);
530 SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten)
531 FROM tenk1 s WHERE unique2 < 10;
547 SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
552 -- mixture of agg/wfunc in the same window
553 SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
569 SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM(
571 CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus,
573 AVG(salary) OVER (PARTITION BY depname) < salary
574 THEN 200 END AS depadj FROM empsalary
576 empno | depname | salary | bonus | depadj | min | max
577 -------+-----------+--------+-------+--------+------+-----
578 1 | sales | 5000 | 1000 | 200 | 1000 | 200
579 2 | personnel | 3900 | 1000 | 200 | 1000 | 200
580 3 | sales | 4800 | 500 | | 500 | 200
581 4 | sales | 4800 | 500 | | 500 | 200
582 5 | personnel | 3500 | 500 | | 500 | 200
583 7 | develop | 4200 | | | 500 | 200
584 8 | develop | 6000 | 1000 | 200 | 500 | 200
585 9 | develop | 4500 | | | 500 | 200
586 10 | develop | 5200 | 500 | 200 | 500 | 200
587 11 | develop | 5200 | 500 | 200 | 500 | 200
590 -- window function over ungrouped agg over empty row set (bug before 9.1)
591 SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42;
597 -- window function with ORDER BY an expression involving aggregates (9.1 bug)
599 sum(unique1) + sum(unique2) as res,
600 rank() over (order by sum(unique1) + sum(unique2)) as rank
602 group by ten order by ten;
604 -----+----------+------
617 -- window and aggregate with GROUP BY expression (9.2 bug)
619 select first_value(max(x)) over (), y
620 from (select unique1 as x, ten+four as y from tenk1) ss
623 -------------------------------
629 -- test non-default frame specifications
631 sum(ten) over (partition by four order by ten),
632 last_value(ten) over (partition by four order by ten)
633 FROM (select distinct ten, four from tenk1) ss;
634 four | ten | sum | last_value
635 ------+-----+-----+------------
659 sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
660 last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
661 FROM (select distinct ten, four from tenk1) ss;
662 four | ten | sum | last_value
663 ------+-----+-----+------------
687 sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
688 last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
689 FROM (select distinct ten, four from tenk1) ss;
690 four | ten | sum | last_value
691 ------+-----+-----+------------
714 SELECT four, ten/4 as two,
715 sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
716 last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
717 FROM (select distinct ten, four from tenk1) ss;
718 four | two | sum | last_value
719 ------+-----+-----+------------
742 SELECT four, ten/4 as two,
743 sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
744 last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
745 FROM (select distinct ten, four from tenk1) ss;
746 four | two | sum | last_value
747 ------+-----+-----+------------
770 SELECT sum(unique1) over (order by four range between current row and unbounded following),
772 FROM tenk1 WHERE unique1 < 10;
774 -----+---------+------
787 SELECT sum(unique1) over (rows between current row and unbounded following),
789 FROM tenk1 WHERE unique1 < 10;
791 -----+---------+------
804 SELECT sum(unique1) over (rows between 2 preceding and 2 following),
806 FROM tenk1 WHERE unique1 < 10;
808 -----+---------+------
821 SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
823 FROM tenk1 WHERE unique1 < 10;
825 -----+---------+------
838 SELECT sum(unique1) over (rows between 1 following and 3 following),
840 FROM tenk1 WHERE unique1 < 10;
842 -----+---------+------
855 SELECT sum(unique1) over (rows between unbounded preceding and 1 following),
857 FROM tenk1 WHERE unique1 < 10;
859 -----+---------+------
872 SELECT sum(unique1) over (w range between current row and unbounded following),
874 FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
876 -----+---------+------
889 -- fail: not implemented yet
890 SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding),
892 FROM tenk1 WHERE unique1 < 10;
893 ERROR: RANGE PRECEDING is only supported with UNBOUNDED
894 LINE 1: SELECT sum(unique1) over (order by four range between 2::int...
896 SELECT first_value(unique1) over w,
897 nth_value(unique1, 2) over w AS nth_2,
898 last_value(unique1) over w, unique1, four
899 FROM tenk1 WHERE unique1 < 10
900 WINDOW w AS (order by four range between current row and unbounded following);
901 first_value | nth_2 | last_value | unique1 | four
902 -------------+-------+------------+---------+------
915 SELECT sum(unique1) over
917 rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING),
919 FROM tenk1 WHERE unique1 < 10;
934 CREATE TEMP VIEW v_window AS
935 SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows
936 FROM generate_series(1, 10) i;
937 SELECT * FROM v_window;
952 SELECT pg_get_viewdef('v_window');
954 ---------------------------------------------------------------------------------------
956 sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+
957 FROM generate_series(1, 10) i(i);
961 SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
966 -- ordering by a non-integer constant is allowed
967 SELECT rank() OVER (ORDER BY length('abc'));
973 -- can't order by another window function
974 SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random()));
975 ERROR: window functions are not allowed in window definitions
976 LINE 1: SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())...
979 SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
980 ERROR: window functions are not allowed in WHERE
981 LINE 1: SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY sa...
983 SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10;
984 ERROR: window functions are not allowed in JOIN conditions
985 LINE 1: SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVE...
987 SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
988 ERROR: window functions are not allowed in GROUP BY
989 LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO...
991 SELECT * FROM rank() OVER (ORDER BY random());
992 ERROR: syntax error at or near "ORDER"
993 LINE 1: SELECT * FROM rank() OVER (ORDER BY random());
995 DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;
996 ERROR: window functions are not allowed in WHERE
997 LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())...
999 DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
1000 ERROR: window functions are not allowed in RETURNING
1001 LINE 1: DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random...
1003 SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1);
1004 ERROR: window "w" is already defined
1005 LINE 1: ...w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY ...
1007 SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
1008 ERROR: syntax error at or near "ORDER"
1009 LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
1011 SELECT count() OVER () FROM tenk1;
1012 ERROR: count(*) must be used to call a parameterless aggregate function
1013 LINE 1: SELECT count() OVER () FROM tenk1;
1015 SELECT generate_series(1, 100) OVER () FROM empsalary;
1016 ERROR: OVER specified, but generate_series is not a window function nor an aggregate function
1017 LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
1019 SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
1020 ERROR: argument of ntile must be greater than zero
1021 SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
1022 ERROR: argument of nth_value must be greater than zero
1024 SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
1025 sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
1026 ) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum",
1028 FROM empsalary GROUP BY depname;
1029 sum | row_number | filtered_sum | depname
1030 -------+------------+--------------+-----------
1032 7400 | 2 | 3500 | personnel
1033 25100 | 1 | 22600 | develop
1037 DROP TABLE empsalary;
1038 -- test user-defined window function with named args and default args
1039 CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement
1040 LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value';
1041 SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four
1042 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
1043 nth_value_def | ten | four
1044 ---------------+-----+------
1057 SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four
1058 FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
1059 nth_value_def | ten | four
1060 ---------------+-----+------