Improve EXPLAIN to print the grouping columns in Agg and Group nodes.

[postgresql] / src / test / regress / expected / window.out

--
-- WINDOW FUNCTIONS
--
CREATE TEMPORARY TABLE empsalary (
    depname varchar,
    empno bigint,
    salary int,
    enroll_date date
);
INSERT INTO empsalary VALUES
('develop', 10, 5200, '2007-08-01'),
('sales', 1, 5000, '2006-10-01'),
('personnel', 5, 3500, '2007-12-10'),
('sales', 4, 4800, '2007-08-08'),
('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),
('develop', 9, 4500, '2008-01-01'),
('sales', 3, 4800, '2007-08-01'),
('develop', 8, 6000, '2006-10-01'),
('develop', 11, 5200, '2007-08-15');
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
  depname  | empno | salary |  sum  
-----------+-------+--------+-------
 develop   |     7 |   4200 | 25100
 develop   |     9 |   4500 | 25100
 develop   |    11 |   5200 | 25100
 develop   |    10 |   5200 | 25100
 develop   |     8 |   6000 | 25100
 personnel |     5 |   3500 |  7400
 personnel |     2 |   3900 |  7400
 sales     |     3 |   4800 | 14600
 sales     |     4 |   4800 | 14600
 sales     |     1 |   5000 | 14600
(10 rows)

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     7 |   4200 |    1
 develop   |     9 |   4500 |    2
 develop   |    11 |   5200 |    3
 develop   |    10 |   5200 |    3
 develop   |     8 |   6000 |    5
 personnel |     5 |   3500 |    1
 personnel |     2 |   3900 |    2
 sales     |     3 |   4800 |    1
 sales     |     4 |   4800 |    1
 sales     |     1 |   5000 |    3
(10 rows)

-- with GROUP BY
SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1
GROUP BY four, ten ORDER BY four, ten;
 four | ten | sum  |          avg           
------+-----+------+------------------------
    0 |   0 |    0 | 0.00000000000000000000
    0 |   2 |    0 |     2.0000000000000000
    0 |   4 |    0 |     4.0000000000000000
    0 |   6 |    0 |     6.0000000000000000
    0 |   8 |    0 |     8.0000000000000000
    1 |   1 | 2500 | 1.00000000000000000000
    1 |   3 | 2500 |     3.0000000000000000
    1 |   5 | 2500 |     5.0000000000000000
    1 |   7 | 2500 |     7.0000000000000000
    1 |   9 | 2500 |     9.0000000000000000
    2 |   0 | 5000 | 0.00000000000000000000
    2 |   2 | 5000 |     2.0000000000000000
    2 |   4 | 5000 |     4.0000000000000000
    2 |   6 | 5000 |     6.0000000000000000
    2 |   8 | 5000 |     8.0000000000000000
    3 |   1 | 7500 | 1.00000000000000000000
    3 |   3 | 7500 |     3.0000000000000000
    3 |   5 | 7500 |     5.0000000000000000
    3 |   7 | 7500 |     7.0000000000000000
    3 |   9 | 7500 |     9.0000000000000000
(20 rows)

SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
  depname  | empno | salary |  sum  
-----------+-------+--------+-------
 develop   |    11 |   5200 | 25100
 develop   |     7 |   4200 | 25100
 develop   |     9 |   4500 | 25100
 develop   |     8 |   6000 | 25100
 develop   |    10 |   5200 | 25100
 personnel |     5 |   3500 |  7400
 personnel |     2 |   3900 |  7400
 sales     |     3 |   4800 | 14600
 sales     |     1 |   5000 | 14600
 sales     |     4 |   4800 | 14600
(10 rows)

SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     7 |   4200 |    1
 personnel |     5 |   3500 |    1
 sales     |     3 |   4800 |    1
 sales     |     4 |   4800 |    1
 personnel |     2 |   3900 |    2
 develop   |     9 |   4500 |    2
 sales     |     1 |   5000 |    3
 develop   |    11 |   5200 |    3
 develop   |    10 |   5200 |    3
 develop   |     8 |   6000 |    5
(10 rows)

-- empty window specification
SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
 count 
-------
    10
    10
    10
    10
    10
    10
    10
    10
    10
    10
(10 rows)

SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
 count 
-------
    10
    10
    10
    10
    10
    10
    10
    10
    10
    10
(10 rows)

-- no window operation
SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
 four 
------
(0 rows)

-- cumulative aggregate
SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
 sum_1 | ten | four 
-------+-----+------
     0 |   0 |    0
     0 |   0 |    0
     2 |   0 |    2
     3 |   1 |    3
     4 |   1 |    1
     5 |   1 |    1
     3 |   3 |    3
     0 |   4 |    0
     1 |   7 |    1
     1 |   9 |    1
(10 rows)

SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
 row_number 
------------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
(10 rows)

SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
 rank_1 | ten | four 
--------+-----+------
      1 |   0 |    0
      1 |   0 |    0
      3 |   4 |    0
      1 |   1 |    1
      1 |   1 |    1
      3 |   7 |    1
      4 |   9 |    1
      1 |   0 |    2
      1 |   1 |    3
      2 |   3 |    3
(10 rows)

SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 dense_rank | ten | four 
------------+-----+------
          1 |   0 |    0
          1 |   0 |    0
          2 |   4 |    0
          1 |   1 |    1
          1 |   1 |    1
          2 |   7 |    1
          3 |   9 |    1
          1 |   0 |    2
          1 |   1 |    3
          2 |   3 |    3
(10 rows)

SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
   percent_rank    | ten | four 
-------------------+-----+------
                 0 |   0 |    0
                 0 |   0 |    0
                 1 |   4 |    0
                 0 |   1 |    1
                 0 |   1 |    1
 0.666666666666667 |   7 |    1
                 1 |   9 |    1
                 0 |   0 |    2
                 0 |   1 |    3
                 1 |   3 |    3
(10 rows)

SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
     cume_dist     | ten | four 
-------------------+-----+------
 0.666666666666667 |   0 |    0
 0.666666666666667 |   0 |    0
                 1 |   4 |    0
               0.5 |   1 |    1
               0.5 |   1 |    1
              0.75 |   7 |    1
                 1 |   9 |    1
                 1 |   0 |    2
               0.5 |   1 |    3
                 1 |   3 |    3
(10 rows)

SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10;
 ntile | ten | four 
-------+-----+------
     1 |   0 |    0
     1 |   0 |    0
     1 |   0 |    2
     1 |   1 |    1
     2 |   1 |    1
     2 |   1 |    3
     2 |   3 |    3
     3 |   4 |    0
     3 |   7 |    1
     3 |   9 |    1
(10 rows)

SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2;
 ntile | ten | four 
-------+-----+------
       |   0 |    0
       |   0 |    0
(2 rows)

SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lag | ten | four 
-----+-----+------
     |   0 |    0
   0 |   0 |    0
   0 |   4 |    0
     |   1 |    1
   1 |   1 |    1
   1 |   7 |    1
   7 |   9 |    1
     |   0 |    2
     |   1 |    3
   1 |   3 |    3
(10 rows)

SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lag | ten | four 
-----+-----+------
   0 |   0 |    0
   0 |   0 |    0
   4 |   4 |    0
     |   1 |    1
   1 |   1 |    1
   1 |   7 |    1
   7 |   9 |    1
     |   0 |    2
     |   1 |    3
     |   3 |    3
(10 rows)

SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lag | ten | four 
-----+-----+------
   0 |   0 |    0
   0 |   0 |    0
   4 |   4 |    0
   0 |   1 |    1
   1 |   1 |    1
   1 |   7 |    1
   7 |   9 |    1
   0 |   0 |    2
   0 |   1 |    3
   0 |   3 |    3
(10 rows)

SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lead | ten | four 
------+-----+------
    0 |   0 |    0
    4 |   0 |    0
      |   4 |    0
    1 |   1 |    1
    7 |   1 |    1
    9 |   7 |    1
      |   9 |    1
      |   0 |    2
    3 |   1 |    3
      |   3 |    3
(10 rows)

SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lead | ten | four 
------+-----+------
    0 |   0 |    0
    8 |   0 |    0
      |   4 |    0
    2 |   1 |    1
   14 |   1 |    1
   18 |   7 |    1
      |   9 |    1
      |   0 |    2
    6 |   1 |    3
      |   3 |    3
(10 rows)

SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lead | ten | four 
------+-----+------
    0 |   0 |    0
    8 |   0 |    0
   -1 |   4 |    0
    2 |   1 |    1
   14 |   1 |    1
   18 |   7 |    1
   -1 |   9 |    1
   -1 |   0 |    2
    6 |   1 |    3
   -1 |   3 |    3
(10 rows)

SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 first_value | ten | four 
-------------+-----+------
           0 |   0 |    0
           0 |   0 |    0
           0 |   4 |    0
           1 |   1 |    1
           1 |   1 |    1
           1 |   7 |    1
           1 |   9 |    1
           0 |   0 |    2
           1 |   1 |    3
           1 |   3 |    3
(10 rows)

-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window.
SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 last_value | ten | four 
------------+-----+------
          0 |   0 |    0
          0 |   0 |    2
          0 |   0 |    0
          1 |   1 |    1
          1 |   1 |    3
          1 |   1 |    1
          3 |   3 |    3
          0 |   4 |    0
          1 |   7 |    1
          1 |   9 |    1
(10 rows)

SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM
        (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
        ORDER BY four, ten;
 last_value | ten | four 
------------+-----+------
          4 |   0 |    0
          4 |   0 |    0
          4 |   4 |    0
          9 |   1 |    1
          9 |   1 |    1
          9 |   7 |    1
          9 |   9 |    1
          0 |   0 |    2
          3 |   1 |    3
          3 |   3 |    3
(10 rows)

SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four
        FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;
 nth_value | ten | four 
-----------+-----+------
         0 |   0 |    0
         0 |   0 |    0
         0 |   4 |    0
         1 |   1 |    1
         1 |   1 |    1
         1 |   7 |    1
         1 |   9 |    1
           |   0 |    2
           |   1 |    3
           |   3 |    3
(10 rows)

SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
FROM tenk1 GROUP BY ten, two;
 ten | two | gsum  |  wsum  
-----+-----+-------+--------
   0 |   0 | 45000 |  45000
   2 |   0 | 47000 |  92000
   4 |   0 | 49000 | 141000
   6 |   0 | 51000 | 192000
   8 |   0 | 53000 | 245000
   1 |   1 | 46000 |  46000
   3 |   1 | 48000 |  94000
   5 |   1 | 50000 | 144000
   7 |   1 | 52000 | 196000
   9 |   1 | 54000 | 250000
(10 rows)

SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
 count | four 
-------+------
     4 |    1
     4 |    1
     4 |    1
     4 |    1
     2 |    3
     2 |    3
(6 rows)

SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) +
  sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum
  FROM tenk1 WHERE unique2 < 10;
 cntsum 
--------
 22
 22
 87
 24
 24
 82
 92
 51
 92
 136
(10 rows)

-- opexpr with different windows evaluation.
SELECT * FROM(
  SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
    sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
    count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
    sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
    FROM tenk1
)sub
WHERE total <> fourcount + twosum;
 total | fourcount | twosum 
-------+-----------+--------
(0 rows)

SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
          avg           
------------------------
 0.00000000000000000000
 0.00000000000000000000
 0.00000000000000000000
 1.00000000000000000000
 1.00000000000000000000
 1.00000000000000000000
 1.00000000000000000000
     2.0000000000000000
     3.0000000000000000
     3.0000000000000000
(10 rows)

SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);
 ten | two | gsum  |  wsum  
-----+-----+-------+--------
   0 |   0 | 45000 |  45000
   2 |   0 | 47000 |  92000
   4 |   0 | 49000 | 141000
   6 |   0 | 51000 | 192000
   8 |   0 | 53000 | 245000
   1 |   1 | 46000 |  46000
   3 |   1 | 48000 |  94000
   5 |   1 | 50000 | 144000
   7 |   1 | 52000 | 196000
   9 |   1 | 54000 | 250000
(10 rows)

-- more than one window with GROUP BY
SELECT sum(salary),
        row_number() OVER (ORDER BY depname),
        sum(sum(salary)) OVER (ORDER BY depname DESC)
FROM empsalary GROUP BY depname;
  sum  | row_number |  sum  
-------+------------+-------
 14600 |          3 | 14600
  7400 |          2 | 22000
 25100 |          1 | 47100
(3 rows)

-- identical windows with different names
SELECT sum(salary) OVER w1, count(*) OVER w2
FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary);
  sum  | count 
-------+-------
  3500 |     1
  7400 |     2
 11600 |     3
 16100 |     4
 25700 |     6
 25700 |     6
 30700 |     7
 41100 |     9
 41100 |     9
 47100 |    10
(10 rows)

-- subplan
SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten)
FROM tenk1 s WHERE unique2 < 10;
 lead 
------
    0
    0
    4
    1
    7
    9
     
    0
    3
     
(10 rows)

-- empty table
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
 count 
-------
(0 rows)

-- mixture of agg/wfunc in the same window
SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
  sum  | rank 
-------+------
  6000 |    1
 16400 |    2
 16400 |    2
 20900 |    4
 25100 |    5
  3900 |    1
  7400 |    2
  5000 |    1
 14600 |    2
 14600 |    2
(10 rows)

-- strict aggs
SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM(
        SELECT *,
                CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus,
                CASE WHEN
                        AVG(salary) OVER (PARTITION BY depname) < salary
                THEN 200 END AS depadj FROM empsalary
)s;
 empno |  depname  | salary | bonus | depadj | min  | max 
-------+-----------+--------+-------+--------+------+-----
     1 | sales     |   5000 |  1000 |    200 | 1000 | 200
     2 | personnel |   3900 |  1000 |    200 | 1000 | 200
     3 | sales     |   4800 |   500 |        |  500 | 200
     4 | sales     |   4800 |   500 |        |  500 | 200
     5 | personnel |   3500 |   500 |        |  500 | 200
     7 | develop   |   4200 |       |        |  500 | 200
     8 | develop   |   6000 |  1000 |    200 |  500 | 200
     9 | develop   |   4500 |       |        |  500 | 200
    10 | develop   |   5200 |   500 |    200 |  500 | 200
    11 | develop   |   5200 |   500 |    200 |  500 | 200
(10 rows)

-- window function over ungrouped agg over empty row set (bug before 9.1)
SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42;
 sum 
-----
   0
(1 row)

-- window function with ORDER BY an expression involving aggregates (9.1 bug)
select ten,
  sum(unique1) + sum(unique2) as res,
  rank() over (order by sum(unique1) + sum(unique2)) as rank
from tenk1
group by ten order by ten;
 ten |   res    | rank 
-----+----------+------
   0 |  9976146 |    4
   1 | 10114187 |    9
   2 | 10059554 |    8
   3 |  9878541 |    1
   4 |  9881005 |    2
   5 |  9981670 |    5
   6 |  9947099 |    3
   7 | 10120309 |   10
   8 |  9991305 |    6
   9 | 10040184 |    7
(10 rows)

-- window and aggregate with GROUP BY expression (9.2 bug)
explain (costs off)
select first_value(max(x)) over (), y
  from (select unique1 as x, ten+four as y from tenk1) ss
  group by y;
                 QUERY PLAN                  
---------------------------------------------
 WindowAgg
   ->  HashAggregate
         Group Key: (tenk1.ten + tenk1.four)
         ->  Seq Scan on tenk1
(4 rows)

-- test non-default frame specifications
SELECT four, ten,
        sum(ten) over (partition by four order by ten),
        last_value(ten) over (partition by four order by ten)
FROM (select distinct ten, four from tenk1) ss;
 four | ten | sum | last_value 
------+-----+-----+------------
    0 |   0 |   0 |          0
    0 |   2 |   2 |          2
    0 |   4 |   6 |          4
    0 |   6 |  12 |          6
    0 |   8 |  20 |          8
    1 |   1 |   1 |          1
    1 |   3 |   4 |          3
    1 |   5 |   9 |          5
    1 |   7 |  16 |          7
    1 |   9 |  25 |          9
    2 |   0 |   0 |          0
    2 |   2 |   2 |          2
    2 |   4 |   6 |          4
    2 |   6 |  12 |          6
    2 |   8 |  20 |          8
    3 |   1 |   1 |          1
    3 |   3 |   4 |          3
    3 |   5 |   9 |          5
    3 |   7 |  16 |          7
    3 |   9 |  25 |          9
(20 rows)

SELECT four, ten,
        sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
        last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
 four | ten | sum | last_value 
------+-----+-----+------------
    0 |   0 |   0 |          0
    0 |   2 |   2 |          2
    0 |   4 |   6 |          4
    0 |   6 |  12 |          6
    0 |   8 |  20 |          8
    1 |   1 |   1 |          1
    1 |   3 |   4 |          3
    1 |   5 |   9 |          5
    1 |   7 |  16 |          7
    1 |   9 |  25 |          9
    2 |   0 |   0 |          0
    2 |   2 |   2 |          2
    2 |   4 |   6 |          4
    2 |   6 |  12 |          6
    2 |   8 |  20 |          8
    3 |   1 |   1 |          1
    3 |   3 |   4 |          3
    3 |   5 |   9 |          5
    3 |   7 |  16 |          7
    3 |   9 |  25 |          9
(20 rows)

SELECT four, ten,
        sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
        last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
FROM (select distinct ten, four from tenk1) ss;
 four | ten | sum | last_value 
------+-----+-----+------------
    0 |   0 |  20 |          8
    0 |   2 |  20 |          8
    0 |   4 |  20 |          8
    0 |   6 |  20 |          8
    0 |   8 |  20 |          8
    1 |   1 |  25 |          9
    1 |   3 |  25 |          9
    1 |   5 |  25 |          9
    1 |   7 |  25 |          9
    1 |   9 |  25 |          9
    2 |   0 |  20 |          8
    2 |   2 |  20 |          8
    2 |   4 |  20 |          8
    2 |   6 |  20 |          8
    2 |   8 |  20 |          8
    3 |   1 |  25 |          9
    3 |   3 |  25 |          9
    3 |   5 |  25 |          9
    3 |   7 |  25 |          9
    3 |   9 |  25 |          9
(20 rows)

SELECT four, ten/4 as two,
        sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
        last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
 four | two | sum | last_value 
------+-----+-----+------------
    0 |   0 |   0 |          0
    0 |   0 |   0 |          0
    0 |   1 |   2 |          1
    0 |   1 |   2 |          1
    0 |   2 |   4 |          2
    1 |   0 |   0 |          0
    1 |   0 |   0 |          0
    1 |   1 |   2 |          1
    1 |   1 |   2 |          1
    1 |   2 |   4 |          2
    2 |   0 |   0 |          0
    2 |   0 |   0 |          0
    2 |   1 |   2 |          1
    2 |   1 |   2 |          1
    2 |   2 |   4 |          2
    3 |   0 |   0 |          0
    3 |   0 |   0 |          0
    3 |   1 |   2 |          1
    3 |   1 |   2 |          1
    3 |   2 |   4 |          2
(20 rows)

SELECT four, ten/4 as two,
        sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
        last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
 four | two | sum | last_value 
------+-----+-----+------------
    0 |   0 |   0 |          0
    0 |   0 |   0 |          0
    0 |   1 |   1 |          1
    0 |   1 |   2 |          1
    0 |   2 |   4 |          2
    1 |   0 |   0 |          0
    1 |   0 |   0 |          0
    1 |   1 |   1 |          1
    1 |   1 |   2 |          1
    1 |   2 |   4 |          2
    2 |   0 |   0 |          0
    2 |   0 |   0 |          0
    2 |   1 |   1 |          1
    2 |   1 |   2 |          1
    2 |   2 |   4 |          2
    3 |   0 |   0 |          0
    3 |   0 |   0 |          0
    3 |   1 |   1 |          1
    3 |   1 |   2 |          1
    3 |   2 |   4 |          2
(20 rows)

SELECT sum(unique1) over (order by four range between current row and unbounded following),
        unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
  45 |       0 |    0
  45 |       8 |    0
  45 |       4 |    0
  33 |       5 |    1
  33 |       9 |    1
  33 |       1 |    1
  18 |       6 |    2
  18 |       2 |    2
  10 |       3 |    3
  10 |       7 |    3
(10 rows)

SELECT sum(unique1) over (rows between current row and unbounded following),
        unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
  45 |       4 |    0
  41 |       2 |    2
  39 |       1 |    1
  38 |       6 |    2
  32 |       9 |    1
  23 |       8 |    0
  15 |       5 |    1
  10 |       3 |    3
   7 |       7 |    3
   0 |       0 |    0
(10 rows)

SELECT sum(unique1) over (rows between 2 preceding and 2 following),
        unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
   7 |       4 |    0
  13 |       2 |    2
  22 |       1 |    1
  26 |       6 |    2
  29 |       9 |    1
  31 |       8 |    0
  32 |       5 |    1
  23 |       3 |    3
  15 |       7 |    3
  10 |       0 |    0
(10 rows)

SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
        unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
     |       4 |    0
   4 |       2 |    2
   6 |       1 |    1
   3 |       6 |    2
   7 |       9 |    1
  15 |       8 |    0
  17 |       5 |    1
  13 |       3 |    3
   8 |       7 |    3
  10 |       0 |    0
(10 rows)

SELECT sum(unique1) over (rows between 1 following and 3 following),
        unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
   9 |       4 |    0
  16 |       2 |    2
  23 |       1 |    1
  22 |       6 |    2
  16 |       9 |    1
  15 |       8 |    0
  10 |       5 |    1
   7 |       3 |    3
   0 |       7 |    3
     |       0 |    0
(10 rows)

SELECT sum(unique1) over (rows between unbounded preceding and 1 following),
        unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
   6 |       4 |    0
   7 |       2 |    2
  13 |       1 |    1
  22 |       6 |    2
  30 |       9 |    1
  35 |       8 |    0
  38 |       5 |    1
  45 |       3 |    3
  45 |       7 |    3
  45 |       0 |    0
(10 rows)

SELECT sum(unique1) over (w range between current row and unbounded following),
        unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
 sum | unique1 | four 
-----+---------+------
  45 |       0 |    0
  45 |       8 |    0
  45 |       4 |    0
  33 |       5 |    1
  33 |       9 |    1
  33 |       1 |    1
  18 |       6 |    2
  18 |       2 |    2
  10 |       3 |    3
  10 |       7 |    3
(10 rows)

-- fail: not implemented yet
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding),
        unique1, four
FROM tenk1 WHERE unique1 < 10;
ERROR:  RANGE PRECEDING is only supported with UNBOUNDED
LINE 1: SELECT sum(unique1) over (order by four range between 2::int...
                                                ^
SELECT first_value(unique1) over w,
        nth_value(unique1, 2) over w AS nth_2,
        last_value(unique1) over w, unique1, four
FROM tenk1 WHERE unique1 < 10
WINDOW w AS (order by four range between current row and unbounded following);
 first_value | nth_2 | last_value | unique1 | four 
-------------+-------+------------+---------+------
           0 |     8 |          7 |       0 |    0
           0 |     8 |          7 |       8 |    0
           0 |     8 |          7 |       4 |    0
           5 |     9 |          7 |       5 |    1
           5 |     9 |          7 |       9 |    1
           5 |     9 |          7 |       1 |    1
           6 |     2 |          7 |       6 |    2
           6 |     2 |          7 |       2 |    2
           3 |     7 |          7 |       3 |    3
           3 |     7 |          7 |       7 |    3
(10 rows)

SELECT sum(unique1) over
        (order by unique1
         rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING),
        unique1
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 
-----+---------
   0 |       0
   1 |       1
   3 |       2
   5 |       3
   7 |       4
   9 |       5
  11 |       6
  13 |       7
  15 |       8
  17 |       9
(10 rows)

CREATE TEMP VIEW v_window AS
        SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows
        FROM generate_series(1, 10) i;
SELECT * FROM v_window;
 i  | sum_rows 
----+----------
  1 |        3
  2 |        6
  3 |        9
  4 |       12
  5 |       15
  6 |       18
  7 |       21
  8 |       24
  9 |       27
 10 |       19
(10 rows)

SELECT pg_get_viewdef('v_window');
                                    pg_get_viewdef                                     
---------------------------------------------------------------------------------------
  SELECT i.i,                                                                         +
     sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+
    FROM generate_series(1, 10) i(i);
(1 row)

-- with UNION
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
 count 
-------
(0 rows)

-- ordering by a non-integer constant is allowed
SELECT rank() OVER (ORDER BY length('abc'));
 rank 
------
    1
(1 row)

-- can't order by another window function
SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random()));
ERROR:  window functions are not allowed in window definitions
LINE 1: SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())...
                                     ^
-- some other errors
SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
ERROR:  window functions are not allowed in WHERE
LINE 1: SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY sa...
                                      ^
SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10;
ERROR:  window functions are not allowed in JOIN conditions
LINE 1: SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVE...
                                                    ^
SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
ERROR:  window functions are not allowed in GROUP BY
LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO...
               ^
SELECT * FROM rank() OVER (ORDER BY random());
ERROR:  syntax error at or near "ORDER"
LINE 1: SELECT * FROM rank() OVER (ORDER BY random());
                                   ^
DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;
ERROR:  window functions are not allowed in WHERE
LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())...
                                     ^
DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
ERROR:  window functions are not allowed in RETURNING
LINE 1: DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random...
                                        ^
SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1);
ERROR:  window "w" is already defined
LINE 1: ...w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY ...
                                                             ^
SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
ERROR:  syntax error at or near "ORDER"
LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
                                               ^
SELECT count() OVER () FROM tenk1;
ERROR:  count(*) must be used to call a parameterless aggregate function
LINE 1: SELECT count() OVER () FROM tenk1;
               ^
SELECT generate_series(1, 100) OVER () FROM empsalary;
ERROR:  OVER specified, but generate_series is not a window function nor an aggregate function
LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
               ^
SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR:  argument of ntile must be greater than zero
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR:  argument of nth_value must be greater than zero
-- filter
SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
    sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum",
    depname
FROM empsalary GROUP BY depname;
  sum  | row_number | filtered_sum |  depname  
-------+------------+--------------+-----------
 14600 |          3 |              | sales
  7400 |          2 |         3500 | personnel
 25100 |          1 |        22600 | develop
(3 rows)

-- cleanup
DROP TABLE empsalary;
-- test user-defined window function with named args and default args
CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement
  LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value';
SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four
  FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
 nth_value_def | ten | four 
---------------+-----+------
             0 |   0 |    0
             0 |   0 |    0
             0 |   4 |    0
             1 |   1 |    1
             1 |   1 |    1
             1 |   7 |    1
             1 |   9 |    1
               |   0 |    2
             3 |   1 |    3
             3 |   3 |    3
(10 rows)

SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four
  FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;
 nth_value_def | ten | four 
---------------+-----+------
             0 |   0 |    0
             0 |   0 |    0
             0 |   4 |    0
             1 |   1 |    1
             1 |   1 |    1
             1 |   7 |    1
             1 |   9 |    1
             0 |   0 |    2
             1 |   1 |    3
             1 |   3 |    3
(10 rows)