36 lines
1.2 KiB
Text
36 lines
1.2 KiB
Text
|
-- window functions
|
||
|
|
|
||
|
|
-- frame clause:
|
||
|
|
|
||
|
|
RANGE UNBOUNDED PRECEDING EXCLUDE CURRENT ROW,
|
||
|
|
RANGE 10 PRECEDING EXCLUDE GROUP,
|
||
|
|
RANGE CURRENT ROW EXCLUDE TIES,
|
||
|
|
RANGE 10 FOLLOWING EXCLUDE NO OTHERS,
|
||
|
|
RANGE UNBOUNDED FOLLOWING,
|
||
|
|
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW;
|
||
|
|
|
||
|
|
ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW,
|
||
|
|
ROWS 10 PRECEDING EXCLUDE GROUP,
|
||
|
|
ROWS CURRENT ROW EXCLUDE TIES,
|
||
|
|
ROWS 10 FOLLOWING EXCLUDE NO OTHERS,
|
||
|
|
ROWS UNBOUNDED FOLLOWING,
|
||
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW;
|
||
|
|
|
||
|
|
GROUPS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW,
|
||
|
|
GROUPS 10 PRECEDING EXCLUDE GROUP,
|
||
|
|
GROUPS CURRENT ROW EXCLUDE TIES,
|
||
|
|
GROUPS 10 FOLLOWING EXCLUDE NO OTHERS,
|
||
|
|
GROUPS UNBOUNDED FOLLOWING,
|
||
|
|
GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW;
|
||
|
|
|
||
|
|
-- examples
|
||
|
|
|
||
|
|
SELECT string_agg(empno, ',' ORDER BY a) FROM empsalary;
|
||
|
|
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;
|
||
|
|
SELECT count(*) FILTER (WHERE i < 5) FROM generate_series(1,10) AS s(i);
|
||
|
|
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
|
||
|
|
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
|
||
|
|
SELECT sum(salary) OVER w, avg(salary) OVER w
|
||
|
|
FROM empsalary
|
||
|
|
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
|