| blob | 5ef3e6c3175d2e1bb6f2ae174c3e121fdc840eb1 |
1 --
2 -- WINDOW FUNCTIONS
3 --
4 CREATE TEMPORARY TABLE empsalary (
5 depname varchar,
6 empno bigint,
7 salary int,
8 enroll_date date
9 );
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
34 (10 rows)
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
46 sales | 3 | 4800 | 1
47 sales | 4 | 4800 | 1
48 sales | 1 | 5000 | 3
49 (10 rows)
51 -- with GROUP BY
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
76 (20 rows)
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
91 (10 rows)
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
98 sales | 3 | 4800 | 1
99 sales | 4 | 4800 | 1
100 personnel | 2 | 3900 | 2
101 develop | 9 | 4500 | 2
102 sales | 1 | 5000 | 3
103 develop | 11 | 5200 | 3
104 develop | 10 | 5200 | 3
105 develop | 8 | 6000 | 5
106 (10 rows)
108 -- empty window specification
109 SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
110 count
111 -------
112 10
113 10
114 10
115 10
116 10
117 10
118 10
119 10
120 10
121 10
122 (10 rows)
124 SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
125 count
126 -------
127 10
128 10
129 10
130 10
131 10
132 10
133 10
134 10
135 10
136 10
137 (10 rows)
139 -- no window operation
140 SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
141 four
142 ------
143 (0 rows)
145 -- cumulative aggregate
146 SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
147 sum_1 | ten | four
148 -------+-----+------
149 0 | 0 | 0
150 0 | 0 | 0
151 2 | 0 | 2
152 3 | 1 | 3
153 4 | 1 | 1
154 5 | 1 | 1
155 3 | 3 | 3
156 0 | 4 | 0
157 1 | 7 | 1
158 1 | 9 | 1
159 (10 rows)
161 SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
162 row_number
163 ------------
164 1
165 2
166 3
167 4
168 5
169 6
170 7
171 8
172 9
173 10
174 (10 rows)
176 SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
177 rank_1 | ten | four
178 --------+-----+------
179 1 | 0 | 0
180 1 | 0 | 0
181 3 | 4 | 0
182 1 | 1 | 1
183 1 | 1 | 1
184 3 | 7 | 1
185 4 | 9 | 1
186 1 | 0 | 2
187 1 | 1 | 3
188 2 | 3 | 3
189 (10 rows)
191 SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
192 dense_rank | ten | four
193 ------------+-----+------
194 1 | 0 | 0
195 1 | 0 | 0
196 2 | 4 | 0
197 1 | 1 | 1
198 1 | 1 | 1
199 2 | 7 | 1
200 3 | 9 | 1
201 1 | 0 | 2
202 1 | 1 | 3
203 2 | 3 | 3
204 (10 rows)
206 SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
207 percent_rank | ten | four
208 -------------------+-----+------
209 0 | 0 | 0
210 0 | 0 | 0
211 1 | 4 | 0
212 0 | 1 | 1
213 0 | 1 | 1
214 0.666666666666667 | 7 | 1
215 1 | 9 | 1
216 0 | 0 | 2
217 0 | 1 | 3
218 1 | 3 | 3
219 (10 rows)
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
226 1 | 4 | 0
227 0.5 | 1 | 1
228 0.5 | 1 | 1
229 0.75 | 7 | 1
230 1 | 9 | 1
231 1 | 0 | 2
232 0.5 | 1 | 3
233 1 | 3 | 3
234 (10 rows)
236 SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10;
237 ntile | ten | four
238 -------+-----+------
239 1 | 0 | 0
240 1 | 0 | 0
241 1 | 0 | 2
242 1 | 1 | 1
243 2 | 1 | 1
244 2 | 1 | 3
245 2 | 3 | 3
246 3 | 4 | 0
247 3 | 7 | 1
248 3 | 9 | 1
249 (10 rows)
251 SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2;
252 ntile | ten | four
253 -------+-----+------
254 | 0 | 0
255 | 0 | 0
256 (2 rows)
258 SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
259 lag | ten | four
260 -----+-----+------
261 | 0 | 0
262 0 | 0 | 0
263 0 | 4 | 0
264 | 1 | 1
265 1 | 1 | 1
266 1 | 7 | 1
267 7 | 9 | 1
268 | 0 | 2
269 | 1 | 3
270 1 | 3 | 3
271 (10 rows)
273 SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
274 lag | ten | four
275 -----+-----+------
276 0 | 0 | 0
277 0 | 0 | 0
278 4 | 4 | 0
279 | 1 | 1
280 1 | 1 | 1
281 1 | 7 | 1
282 7 | 9 | 1
283 | 0 | 2
284 | 1 | 3
285 | 3 | 3
286 (10 rows)
288 SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
289 lag | ten | four
290 -----+-----+------
291 0 | 0 | 0
292 0 | 0 | 0
293 4 | 4 | 0
294 0 | 1 | 1
295 1 | 1 | 1
296 1 | 7 | 1
297 7 | 9 | 1
298 0 | 0 | 2
299 0 | 1 | 3
300 0 | 3 | 3
301 (10 rows)
303 SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
304 lead | ten | four
305 ------+-----+------
306 0 | 0 | 0
307 4 | 0 | 0
308 | 4 | 0
309 1 | 1 | 1
310 7 | 1 | 1
311 9 | 7 | 1
312 | 9 | 1
313 | 0 | 2
314 3 | 1 | 3
315 | 3 | 3
316 (10 rows)
318 SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
319 lead | ten | four
320 ------+-----+------
321 0 | 0 | 0
322 8 | 0 | 0
323 | 4 | 0
324 2 | 1 | 1
325 14 | 1 | 1
326 18 | 7 | 1
327 | 9 | 1
328 | 0 | 2
329 6 | 1 | 3
330 | 3 | 3
331 (10 rows)
333 SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
334 lead | ten | four
335 ------+-----+------
336 0 | 0 | 0
337 8 | 0 | 0
338 -1 | 4 | 0
339 2 | 1 | 1
340 14 | 1 | 1
341 18 | 7 | 1
342 -1 | 9 | 1
343 -1 | 0 | 2
344 6 | 1 | 3
345 -1 | 3 | 3
346 (10 rows)
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 -------------+-----+------
351 0 | 0 | 0
352 0 | 0 | 0
353 0 | 4 | 0
354 1 | 1 | 1
355 1 | 1 | 1
356 1 | 7 | 1
357 1 | 9 | 1
358 0 | 0 | 2
359 1 | 1 | 3
360 1 | 3 | 3
361 (10 rows)
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 ------------+-----+------
367 0 | 0 | 0
368 0 | 0 | 2
369 0 | 0 | 0
370 1 | 1 | 1
371 1 | 1 | 3
372 1 | 1 | 1
373 3 | 3 | 3
374 0 | 4 | 0
375 1 | 7 | 1
376 1 | 9 | 1
377 (10 rows)
379 SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM
380 (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
381 ORDER BY four, ten;
382 last_value | ten | four
383 ------------+-----+------
384 4 | 0 | 0
385 4 | 0 | 0
386 4 | 4 | 0
387 9 | 1 | 1
388 9 | 1 | 1
389 9 | 7 | 1
390 9 | 9 | 1
391 0 | 0 | 2
392 3 | 1 | 3
393 3 | 3 | 3
394 (10 rows)
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 -----------+-----+------
400 0 | 0 | 0
401 0 | 0 | 0
402 0 | 4 | 0
403 1 | 1 | 1
404 1 | 1 | 1
405 1 | 7 | 1
406 1 | 9 | 1
407 | 0 | 2
408 | 1 | 3
409 | 3 | 3
410 (10 rows)
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
426 (10 rows)
428 SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
429 count | four
430 -------+------
431 4 | 1
432 4 | 1
433 4 | 1
434 4 | 1
435 2 | 3
436 2 | 3
437 (6 rows)
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;
442 cntsum
443 --------
444 22
445 22
446 87
447 24
448 24
449 82
450 92
451 51
452 92
453 136
454 (10 rows)
456 -- opexpr with different windows evaluation.
457 SELECT * FROM(
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
462 FROM tenk1
463 )sub
464 WHERE total <> fourcount + twosum;
465 total | fourcount | twosum
466 -------+-----------+--------
467 (0 rows)
469 SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
470 avg
471 ------------------------
472 0.00000000000000000000
473 0.00000000000000000000
474 0.00000000000000000000
475 1.00000000000000000000
476 1.00000000000000000000
477 1.00000000000000000000
478 1.00000000000000000000
479 2.0000000000000000
480 3.0000000000000000
481 3.0000000000000000
482 (10 rows)
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
498 (10 rows)
500 -- more than one window with GROUP BY
501 SELECT sum(salary),
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 -------+------------+-------
507 14600 | 3 | 14600
508 7400 | 2 | 22000
509 25100 | 1 | 47100
510 (3 rows)
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);
515 sum | count
516 -------+-------
517 3500 | 1
518 7400 | 2
519 11600 | 3
520 16100 | 4
521 25700 | 6
522 25700 | 6
523 30700 | 7
524 41100 | 9
525 41100 | 9
526 47100 | 10
527 (10 rows)
529 -- subplan
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;
532 lead
533 ------
534 0
535 0
536 4
537 1
538 7
539 9
541 0
542 3
544 (10 rows)
546 -- empty table
547 SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
548 count
549 -------
550 (0 rows)
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);
554 sum | rank
555 -------+------
556 6000 | 1
557 16400 | 2
558 16400 | 2
559 20900 | 4
560 25100 | 5
561 3900 | 1
562 7400 | 2
563 5000 | 1
564 14600 | 2
565 14600 | 2
566 (10 rows)
568 -- strict aggs
569 SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM(
570 SELECT *,
571 CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus,
572 CASE WHEN
573 AVG(salary) OVER (PARTITION BY depname) < salary
574 THEN 200 END AS depadj FROM empsalary
575 )s;
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
588 (10 rows)
590 -- test non-default frame specifications
591 SELECT four, ten,
592 sum(ten) over (partition by four order by ten),
593 last_value(ten) over (partition by four order by ten)
594 FROM (select distinct ten, four from tenk1) ss;
595 four | ten | sum | last_value
596 ------+-----+-----+------------
597 0 | 0 | 0 | 0
598 0 | 2 | 2 | 2
599 0 | 4 | 6 | 4
600 0 | 6 | 12 | 6
601 0 | 8 | 20 | 8
602 1 | 1 | 1 | 1
603 1 | 3 | 4 | 3
604 1 | 5 | 9 | 5
605 1 | 7 | 16 | 7
606 1 | 9 | 25 | 9
607 2 | 0 | 0 | 0
608 2 | 2 | 2 | 2
609 2 | 4 | 6 | 4
610 2 | 6 | 12 | 6
611 2 | 8 | 20 | 8
612 3 | 1 | 1 | 1
613 3 | 3 | 4 | 3
614 3 | 5 | 9 | 5
615 3 | 7 | 16 | 7
616 3 | 9 | 25 | 9
617 (20 rows)
619 SELECT four, ten,
620 sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
621 last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
622 FROM (select distinct ten, four from tenk1) ss;
623 four | ten | sum | last_value
624 ------+-----+-----+------------
625 0 | 0 | 0 | 0
626 0 | 2 | 2 | 2
627 0 | 4 | 6 | 4
628 0 | 6 | 12 | 6
629 0 | 8 | 20 | 8
630 1 | 1 | 1 | 1
631 1 | 3 | 4 | 3
632 1 | 5 | 9 | 5
633 1 | 7 | 16 | 7
634 1 | 9 | 25 | 9
635 2 | 0 | 0 | 0
636 2 | 2 | 2 | 2
637 2 | 4 | 6 | 4
638 2 | 6 | 12 | 6
639 2 | 8 | 20 | 8
640 3 | 1 | 1 | 1
641 3 | 3 | 4 | 3
642 3 | 5 | 9 | 5
643 3 | 7 | 16 | 7
644 3 | 9 | 25 | 9
645 (20 rows)
647 SELECT four, ten,
648 sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
649 last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
650 FROM (select distinct ten, four from tenk1) ss;
651 four | ten | sum | last_value
652 ------+-----+-----+------------
653 0 | 0 | 20 | 8
654 0 | 2 | 20 | 8
655 0 | 4 | 20 | 8
656 0 | 6 | 20 | 8
657 0 | 8 | 20 | 8
658 1 | 1 | 25 | 9
659 1 | 3 | 25 | 9
660 1 | 5 | 25 | 9
661 1 | 7 | 25 | 9
662 1 | 9 | 25 | 9
663 2 | 0 | 20 | 8
664 2 | 2 | 20 | 8
665 2 | 4 | 20 | 8
666 2 | 6 | 20 | 8
667 2 | 8 | 20 | 8
668 3 | 1 | 25 | 9
669 3 | 3 | 25 | 9
670 3 | 5 | 25 | 9
671 3 | 7 | 25 | 9
672 3 | 9 | 25 | 9
673 (20 rows)
675 SELECT four, ten/4 as two,
676 sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
677 last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
678 FROM (select distinct ten, four from tenk1) ss;
679 four | two | sum | last_value
680 ------+-----+-----+------------
681 0 | 0 | 0 | 0
682 0 | 0 | 0 | 0
683 0 | 1 | 2 | 1
684 0 | 1 | 2 | 1
685 0 | 2 | 4 | 2
686 1 | 0 | 0 | 0
687 1 | 0 | 0 | 0
688 1 | 1 | 2 | 1
689 1 | 1 | 2 | 1
690 1 | 2 | 4 | 2
691 2 | 0 | 0 | 0
692 2 | 0 | 0 | 0
693 2 | 1 | 2 | 1
694 2 | 1 | 2 | 1
695 2 | 2 | 4 | 2
696 3 | 0 | 0 | 0
697 3 | 0 | 0 | 0
698 3 | 1 | 2 | 1
699 3 | 1 | 2 | 1
700 3 | 2 | 4 | 2
701 (20 rows)
703 SELECT four, ten/4 as two,
704 sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
705 last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
706 FROM (select distinct ten, four from tenk1) ss;
707 four | two | sum | last_value
708 ------+-----+-----+------------
709 0 | 0 | 0 | 0
710 0 | 0 | 0 | 0
711 0 | 1 | 1 | 1
712 0 | 1 | 2 | 1
713 0 | 2 | 4 | 2
714 1 | 0 | 0 | 0
715 1 | 0 | 0 | 0
716 1 | 1 | 1 | 1
717 1 | 1 | 2 | 1
718 1 | 2 | 4 | 2
719 2 | 0 | 0 | 0
720 2 | 0 | 0 | 0
721 2 | 1 | 1 | 1
722 2 | 1 | 2 | 1
723 2 | 2 | 4 | 2
724 3 | 0 | 0 | 0
725 3 | 0 | 0 | 0
726 3 | 1 | 1 | 1
727 3 | 1 | 2 | 1
728 3 | 2 | 4 | 2
729 (20 rows)
731 -- with UNION
732 SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
733 count
734 -------
735 (0 rows)
737 -- ordering by a non-integer constant is allowed
738 SELECT rank() OVER (ORDER BY length('abc'));
739 rank
740 ------
741 1
742 (1 row)
744 -- but this draws an error: "ORDER BY 1" means order by first SELECT column
745 SELECT rank() OVER (ORDER BY 1);
746 ERROR: window functions not allowed in window definition
747 LINE 1: SELECT rank() OVER (ORDER BY 1);
748 ^
749 -- some other errors
750 SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
751 ERROR: window functions not allowed in WHERE clause
752 LINE 1: SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY sa...
753 ^
754 SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10;
755 ERROR: window functions not allowed in JOIN conditions
756 LINE 1: SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVE...
757 ^
758 SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
759 ERROR: window functions not allowed in GROUP BY clause
760 LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO...
761 ^
762 SELECT * FROM rank() OVER (ORDER BY random());
763 ERROR: cannot use window function in function expression in FROM
764 LINE 1: SELECT * FROM rank() OVER (ORDER BY random());
765 ^
766 DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;
767 ERROR: window functions not allowed in WHERE clause
768 LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())...
769 ^
770 DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
771 ERROR: cannot use window function in RETURNING
772 LINE 1: DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random...
773 ^
774 SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1);
775 ERROR: window "w" is already defined
776 LINE 1: ...w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY ...
777 ^
778 SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
779 ERROR: syntax error at or near "ORDER"
780 LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
781 ^
782 SELECT count() OVER () FROM tenk1;
783 ERROR: count(*) must be used to call a parameterless aggregate function
784 LINE 1: SELECT count() OVER () FROM tenk1;
785 ^
786 SELECT generate_series(1, 100) OVER () FROM empsalary;
787 ERROR: OVER specified, but generate_series is not a window function nor an aggregate function
788 LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
789 ^
790 SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
791 ERROR: argument of ntile must be greater than zero
792 SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
793 ERROR: argument of nth_value must be greater than zero
794 -- cleanup
795 DROP TABLE empsalary;