← Back to branch summary

~ubuntu-branches/ubuntu/hardy/postgresql-8.4/hardy-backports

« back to all changes in this revision

Viewing changes to src/test/regress/sql/window.sql

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2009-03-20 12:00:13 UTC
  • Revision ID: james.westby@ubuntu.com-20090320120013-hogj7egc5mjncc5g
Tags: upstream-8.4~0cvs20090328
Import upstream version 8.4~0cvs20090328

Show diffs side-by-side

added added

removed removed

Lines of Context:
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;