src/test/regress/sql/window.sql

   1 --
   2 -- WINDOW FUNCTIONS
   3 --
   4
   5 CREATE TEMPORARY TABLE empsalary (
   6     depname varchar,
   7     empno bigint,
   8     salary int,
   9     enroll_date date
  10 );
  11
  12 INSERT 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
  24 SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
  25
  26 SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;
  27
  28 -- with GROUP BY
  29 SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1
  30 GROUP BY four, ten ORDER BY four, ten;
  31
  32 SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
  33
  34 SELECT 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
  37 SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
  38
  39 SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
  40
  41 -- no window operation
  42 SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
  43
  44 -- cumulative aggregate
  45 SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
  46
  47 SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
  48
  49 SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
  50
  51 SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  52
  53 SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  54
  55 SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  56
  57 SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10;
  58
  59 SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2;
  60
  61 SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  62
  63 SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  64
  65 SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  66
  67 SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  68
  69 SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  70
  71 SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  72
  73 SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  74
  75 -- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window.
  76 SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
  77
  78 SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM
  79         (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
  80         ORDER BY four, ten;
  81
  82 SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four
  83         FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;
  84
  85 SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
  86 FROM tenk1 GROUP BY ten, two;
  87
  88 SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
  89
  90 SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) +
  91   sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum
  92   FROM tenk1 WHERE unique2 < 10;
  93
  94 -- opexpr with different windows evaluation.
  95 SELECT * FROM(
  96   SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
  97     sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
  98     count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
  99     sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
 100     FROM tenk1
 101 )sub
 102 WHERE total <> fourcount + twosum;
 103
 104 SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
 105
 106 SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
 107 FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);
 108
 109 -- more than one window with GROUP BY
 110 SELECT sum(salary),
 111         row_number() OVER (ORDER BY depname),
 112         sum(sum(salary)) OVER (ORDER BY depname DESC)
 113 FROM empsalary GROUP BY depname;
 114
 115 -- identical windows with different names
 116 SELECT sum(salary) OVER w1, count(*) OVER w2
 117 FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary);
 118
 119 -- subplan
 120 SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten)
 121 FROM tenk1 s WHERE unique2 < 10;
 122
 123 -- empty table
 124 SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
 125
 126 -- mixture of agg/wfunc in the same window
 127 SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
 128
 129 -- strict aggs
 130 SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM(
 131         SELECT *,
 132                 CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus,
 133                 CASE WHEN
 134                         AVG(salary) OVER (PARTITION BY depname) < salary
 135                 THEN 200 END AS depadj FROM empsalary
 136 )s;
 137
 138 -- test non-default frame specifications
 139 SELECT four, ten,
 140         sum(ten) over (partition by four order by ten),
 141         last_value(ten) over (partition by four order by ten)
 142 FROM (select distinct ten, four from tenk1) ss;
 143
 144 SELECT four, ten,
 145         sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
 146         last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
 147 FROM (select distinct ten, four from tenk1) ss;
 148
 149 SELECT four, ten,
 150         sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
 151         last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
 152 FROM (select distinct ten, four from tenk1) ss;
 153
 154 SELECT four, ten/4 as two,
 155         sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
 156         last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
 157 FROM (select distinct ten, four from tenk1) ss;
 158
 159 SELECT four, ten/4 as two,
 160         sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
 161         last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
 162 FROM (select distinct ten, four from tenk1) ss;
 163
 164 -- with UNION
 165 SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
 166
 167 -- ordering by a non-integer constant is allowed
 168 SELECT rank() OVER (ORDER BY length('abc'));
 169
 170 -- but this draws an error: "ORDER BY 1" means order by first SELECT column
 171 SELECT rank() OVER (ORDER BY 1);
 172
 173 -- some other errors
 174 SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
 175
 176 SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10;
 177
 178 SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
 179
 180 SELECT * FROM rank() OVER (ORDER BY random());
 181
 182 DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;
 183
 184 DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
 185
 186 SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1);
 187
 188 SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
 189
 190 SELECT count() OVER () FROM tenk1;
 191
 192 SELECT generate_series(1, 100) OVER () FROM empsalary;
 193
 194 SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
 195
 196 SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
 197
 198 -- cleanup
 199 DROP TABLE empsalary;