over函数的使用

1.

select e.id, 
       e.depno, 
       e.sal, 
       sum(e.sal) over(partition by depno) as sum
  from emp e;

2.

select e.*,
sum(e.sal) over(partition by depno order by id)as dept_sum_sal
from emp e;

3.

select e.*, rank()
over(order by sal desc nulls last)
as dept_sum_s from emp e;

4.

select e.*,
       sum(e.sal)
       over(order by l_date range interval '0' month preceding) as dept_sum_sa
  from emp e;
 
  5.

select e.*,
       sum(e.sal) over(order by id rows between 1 preceding and 0 following) as dept_sum_sa
  from emp e;

6.

select e.*,
       sum(e.sal) over(/*partition by depno */order by id rows
       between current row and unbounded following) as dept_sum_sal
  from emp e;