분석 함수 sum() over(partition by ---)

--sum()이 두번 쓰임 이유는??
select
  years
, customer_id
, sub_amt
from (
  select
    to_char(order_date, 'YYYY') years
  , customer_id
  , sum(order_total) sub_amt
  , sum(sum(order_total)) over (partition by  to_char(order_date, 'YYYY'))  total_amt
  from orders
  group by to_char(order_date, 'YYYY'), customer_id
) a
where a.sub_amt > a.total_amt * 0.2; 

 

 

--뒤에 order by가 있을 때랑 없을 때랑 값이 다름.

--partition을 한 후에 정렬을 해야 제대로 값이 들어감.
select
  department_id
, last_name
, salary
, sum(salary) over(partition by department_id order by last_name) cumm_sal

from employees;