Debian Codegraph @

test/window4.tcl

raw
1 ## 2018 May 19
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 #
12
13 source [file join [file dirname $argv0] pg_common.tcl]
14
15 #=========================================================================
16
17 start_test window4 "2018 June 04"
18 ifcapable !windowfunc
19
20 execsql_test 1.0 {
21 DROP TABLE IF EXISTS t3;
22 CREATE TABLE t3(a TEXT PRIMARY KEY);
23 INSERT INTO t3 VALUES('a'), ('b'), ('c'), ('d'), ('e');
24 INSERT INTO t3 VALUES('f'), ('g'), ('h'), ('i'), ('j');
25 }
26
27 for {set i 1} {$i < 20} {incr i} {
28 execsql_test 1.$i "SELECT a, ntile($i) OVER (ORDER BY a) FROM t3"
29 }
30
31 execsql_test 2.0 {
32 DROP TABLE IF EXISTS t4;
33 CREATE TABLE t4(a INTEGER PRIMARY KEY, b TEXT, c INTEGER);
34 INSERT INTO t4 VALUES(1, 'A', 9);
35 INSERT INTO t4 VALUES(2, 'B', 3);
36 INSERT INTO t4 VALUES(3, 'C', 2);
37 INSERT INTO t4 VALUES(4, 'D', 10);
38 INSERT INTO t4 VALUES(5, 'E', 5);
39 INSERT INTO t4 VALUES(6, 'F', 1);
40 INSERT INTO t4 VALUES(7, 'G', 1);
41 INSERT INTO t4 VALUES(8, 'H', 2);
42 INSERT INTO t4 VALUES(9, 'I', 10);
43 INSERT INTO t4 VALUES(10, 'J', 4);
44 }
45
46 execsql_test 2.1 {
47 SELECT a, nth_value(b, c) OVER (ORDER BY a) FROM t4
48 }
49
50 execsql_test 2.2.1 {
51 SELECT a, lead(b) OVER (ORDER BY a) FROM t4
52 }
53 execsql_test 2.2.2 {
54 SELECT a, lead(b, 2) OVER (ORDER BY a) FROM t4
55 }
56 execsql_test 2.2.3 {
57 SELECT a, lead(b, 3, 'abc') OVER (ORDER BY a) FROM t4
58 }
59
60 execsql_test 2.3.1 {
61 SELECT a, lag(b) OVER (ORDER BY a) FROM t4
62 }
63 execsql_test 2.3.2 {
64 SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
65 }
66 execsql_test 2.3.3 {
67 SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
68 }
69
70 execsql_test 2.4.1 {
71 SELECT string_agg(b, '.') OVER (
72 ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
73 ) FROM t4
74 }
75
76 execsql_test 3.0 {
77 DROP TABLE IF EXISTS t5;
78 CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
79 INSERT INTO t5 VALUES(1, 'A', 'one', 5);
80 INSERT INTO t5 VALUES(2, 'B', 'two', 4);
81 INSERT INTO t5 VALUES(3, 'A', 'three', 3);
82 INSERT INTO t5 VALUES(4, 'B', 'four', 2);
83 INSERT INTO t5 VALUES(5, 'A', 'five', 1);
84 }
85
86 execsql_test 3.1 {
87 SELECT a, nth_value(c, d) OVER (ORDER BY b) FROM t5
88 }
89
90 execsql_test 3.2 {
91 SELECT a, nth_value(c, d) OVER (PARTITION BY b ORDER BY a) FROM t5
92 }
93
94 execsql_test 3.3 {
95 SELECT a, count(*) OVER abc, count(*) OVER def FROM t5
96 WINDOW abc AS (ORDER BY a),
97 def AS (ORDER BY a DESC)
98 ORDER BY a;
99 }
100
101 execsql_test 3.4 {
102 SELECT a, max(a) FILTER (WHERE (a%2)=0) OVER w FROM t5
103 WINDOW w AS (ORDER BY a)
104 }
105
106 execsql_test 3.5.1 {
107 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING)
108 FROM t5
109 }
110 execsql_test 3.5.2 {
111 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
112 FROM t5
113 }
114 execsql_test 3.5.3 {
115 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
116 FROM t5
117 }
118
119 execsql_test 3.6.1 {
120 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
121 FROM t5
122 }
123 execsql_test 3.6.2 {
124 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
125 FROM t5
126 }
127 execsql_test 3.6.3 {
128 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
129 FROM t5
130 }
131
132 ==========
133
134 execsql_test 4.0 {
135 DROP TABLE IF EXISTS ttt;
136 CREATE TABLE ttt(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
137 INSERT INTO ttt VALUES(1, 1, 1);
138 INSERT INTO ttt VALUES(2, 2, 2);
139 INSERT INTO ttt VALUES(3, 3, 3);
140
141 INSERT INTO ttt VALUES(4, 1, 2);
142 INSERT INTO ttt VALUES(5, 2, 3);
143 INSERT INTO ttt VALUES(6, 3, 4);
144
145 INSERT INTO ttt VALUES(7, 1, 3);
146 INSERT INTO ttt VALUES(8, 2, 4);
147 INSERT INTO ttt VALUES(9, 3, 5);
148 }
149
150 execsql_test 4.1 {
151 SELECT max(c), max(b) OVER (ORDER BY b) FROM ttt GROUP BY b;
152 }
153
154 execsql_test 4.2 {
155 SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
156 }
157
158 execsql_test 4.3 {
159 SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
160 }
161
162 execsql_test 4.4 {
163 SELECT sum(b) OVER (
164 ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
165 ) FROM ttt;
166 }
167
168 set lPart [list "PARTITION BY b" "PARTITION BY b, a" "" "PARTITION BY a"]
169 set lOrder [list "ORDER BY a" "ORDER BY a DESC" "" "ORDER BY b, a"]
170 set lRange {
171 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
172 "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
173 "RANGE BETWEEN CURRENT ROW AND CURRENT ROW"
174 "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING"
175 }
176
177 set lRows {
178 "ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING"
179 "ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING"
180 "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING"
181 "ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING"
182 "ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING"
183 }
184
185 set tn 1
186 set SQL {
187 SELECT max(c) OVER ($p1 $o1 $r1),
188 min(c) OVER ($p2 $o2 $r2)
189 FROM ttt ORDER BY a
190 }
191 set SQL2 {
192 SELECT sum(c) OVER ($p1 $o1 $r1),
193 sum(c) OVER ($p2 $o2 $r2)
194 FROM ttt ORDER BY a
195 }
196
197 set o1 [lindex $lOrder 0]
198 set o2 [lindex $lOrder 0]
199 set r1 [lindex $lRange 0]
200 set r2 [lindex $lRange 0]
201 foreach p1 $lPart { foreach p2 $lPart {
202 execsql_test 4.5.$tn.1 [subst $SQL]
203 execsql_test 4.5.$tn.2 [subst $SQL2]
204 incr tn
205 }}
206
207 set o1 [lindex $lOrder 0]
208 set o2 [lindex $lOrder 0]
209 set p1 [lindex $lPart 0]
210 set p2 [lindex $lPart 0]
211 foreach r1 $lRange { foreach r2 $lRange {
212 execsql_test 4.5.$tn.1 [subst $SQL]
213 execsql_test 4.5.$tn.2 [subst $SQL2]
214 incr tn
215 }}
216 foreach r1 $lRows { foreach r2 $lRows {
217 execsql_test 4.5.$tn.1 [subst $SQL]
218 execsql_test 4.5.$tn.2 [subst $SQL2]
219 incr tn
220 }}
221
222 set r1 [lindex $lRange 0]
223 set r2 [lindex $lRange 0]
224 set p1 [lindex $lPart 0]
225 set p2 [lindex $lPart 0]
226 foreach o1 $lOrder { foreach o2 $lOrder {
227 execsql_test 4.5.$tn.1 [subst $SQL]
228 execsql_test 4.5.$tn.2 [subst $SQL2]
229 incr tn
230 }}
231
232 ==========
233
234 execsql_test 7.0 {
235 DROP TABLE IF EXISTS t1;
236 CREATE TABLE t1(x INTEGER, y INTEGER);
237 INSERT INTO t1 VALUES(1, 2);
238 INSERT INTO t1 VALUES(3, 4);
239 INSERT INTO t1 VALUES(5, 6);
240 INSERT INTO t1 VALUES(7, 8);
241 INSERT INTO t1 VALUES(9, 10);
242 }
243
244 execsql_test 7.1 {
245 SELECT lead(y) OVER win FROM t1
246 WINDOW win AS (ORDER BY x)
247 }
248
249 execsql_test 7.2 {
250 SELECT lead(y, 2) OVER win FROM t1
251 WINDOW win AS (ORDER BY x)
252 }
253
254 execsql_test 7.3 {
255 SELECT lead(y, 3, -1) OVER win FROM t1
256 WINDOW win AS (ORDER BY x)
257 }
258
259 execsql_test 7.4 {
260 SELECT
261 lead(y) OVER win, lead(y) OVER win
262 FROM t1
263 WINDOW win AS (ORDER BY x)
264 }
265
266 execsql_test 7.5 {
267 SELECT
268 lead(y) OVER win,
269 lead(y, 2) OVER win,
270 lead(y, 3, -1) OVER win
271 FROM t1
272 WINDOW win AS (ORDER BY x)
273 }
274
275 ==========
276
277 execsql_test 8.0 {
278 DROP TABLE IF EXISTS t1;
279 CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
280 INSERT INTO t1 VALUES(1, 2, 3, 4);
281 INSERT INTO t1 VALUES(5, 6, 7, 8);
282 INSERT INTO t1 VALUES(9, 10, 11, 12);
283 }
284
285 execsql_test 8.1 {
286 SELECT row_number() OVER win,
287 nth_value(d,2) OVER win,
288 lead(d) OVER win
289 FROM t1
290 WINDOW win AS (ORDER BY a)
291 }
292
293 execsql_test 8.2 {
294 SELECT row_number() OVER win,
295 rank() OVER win,
296 dense_rank() OVER win,
297 ntile(2) OVER win,
298 first_value(d) OVER win,
299 last_value(d) OVER win,
300 nth_value(d,2) OVER win,
301 lead(d) OVER win,
302 lag(d) OVER win,
303 max(d) OVER win,
304 min(d) OVER win
305 FROM t1
306 WINDOW win AS (ORDER BY a)
307 }
308
309 ==========
310
311 execsql_test 9.0 {
312 DROP TABLE IF EXISTS t2;
313 CREATE TABLE t2(x INTEGER);
314 INSERT INTO t2 VALUES(1), (1), (1), (4), (4), (6), (7);
315 }
316
317 execsql_test 9.1 {
318 SELECT rank() OVER () FROM t2
319 }
320 execsql_test 9.2 {
321 SELECT dense_rank() OVER (PARTITION BY x) FROM t2
322 }
323 execsql_float_test 9.3 {
324 SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2
325 }
326
327 execsql_test 9.4 {
328 SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
329 }
330
331 execsql_test 9.5 {
332 SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
333 }
334
335 execsql_float_test 9.6 {
336 SELECT percent_rank() OVER () FROM t1
337 }
338
339 execsql_float_test 9.7 {
340 SELECT cume_dist() OVER () FROM t1
341 }
342
343 execsql_test 10.0 {
344 DROP TABLE IF EXISTS t7;
345 CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
346 INSERT INTO t7(id, a, b) VALUES
347 (1, 1, 2), (2, 1, NULL), (3, 1, 4),
348 (4, 3, NULL), (5, 3, 8), (6, 3, 1);
349 }
350 execsql_test 10.1 {
351 SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
352 }
353
354 execsql_test 10.2 {
355 SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
356 }
357 execsql_test 10.3 {
358 SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
359 }
360
361 execsql_test 11.0 {
362 DROP VIEW IF EXISTS v8;
363 DROP TABLE IF EXISTS t8;
364 CREATE TABLE t8(t INT, total INT);
365 INSERT INTO t8 VALUES(0,2);
366 INSERT INTO t8 VALUES(5,1);
367 INSERT INTO t8 VALUES(10,1);
368 }
369
370 execsql_test 11.1 {
371 SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
372 }
373
374 execsql_test 11.2 {
375 CREATE VIEW v8 AS SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
376 }
377
378 execsql_test 11.3 {
379 SELECT * FROM v8;
380 }
381
382 execsql_test 11.4 {
383 SELECT * FROM (
384 SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8
385 ) sub;
386 }
387
388 execsql_test 11.5 {
389 SELECT sum( min(t) ) OVER () FROM t8 GROUP BY total;
390 }
391 execsql_test 11.5 {
392 SELECT sum( max(t) ) OVER () FROM t8 GROUP BY total;
393 }
394
395 execsql_test 11.7 {
396 SELECT sum( min(t) ) OVER () FROM t8;
397 }
398 execsql_test 11.8 {
399 SELECT sum( max(t) ) OVER () FROM t8;
400 }
401
402 execsql_test 12.0 {
403 DROP TABLE IF EXISTS t2;
404 CREATE TABLE t2(a INTEGER);
405 INSERT INTO t2 VALUES(1), (2), (3);
406 }
407
408 execsql_test 12.1 {
409 SELECT (SELECT min(a) OVER ()) FROM t2
410 }
411
412 execsql_float_test 12.2 {
413 SELECT (SELECT avg(a)) FROM t2 ORDER BY 1
414 }
415
416 execsql_float_test 12.3 {
417 SELECT
418 (SELECT avg(a) UNION SELECT min(a) OVER ())
419 FROM t2 GROUP BY a
420 ORDER BY 1
421 }
422
423 finish_test
424