-- 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);