[데이터베이스] window 함수

window 함수

• select 구문에서 사용
  • sum(col1) over(partition by col2 order by col3) as new_col
  • partition by : group by랑 비슷하지만, 집계 결과가 아닌 본래 table 그대로 출력
    • partition by는 생략 가능: 순서대로 누적합
  • order by를 생략하면 누적 합계의 순서(1 + 3 -> 4 (+ 3) -> 7 식으로 나타나지 않고 바로 7이 뜸.
• cumtomer number 끼리의 amount를 더할건데, pay_date 오름차순으로 더해가는 것 -> 같은 custom_num의 total_amount
  • 누적합 개념 
• group by는 customer number를 하나로 모아서 집계 출력하지만, partition by는 table 모든 행을 출력

select cus_num, pay_date, amount.
sum(amount) over(partition by cus_num order by pay_date) as total_amount
from payments;

 

• 전체 테이블의 합 구하기 : sum(amount) over() as total_amount
• 고객별로 주문 날짜에 따른 주문 횟수의 누적합을 구하시오

select customernumber, ordernumber, orderdate,

count(ordernumber) over (partition by customernumber order by orderdate) as total_order from orders; 

 

LEAD/LAG(over partition by order by)

• lag: 이전 데이터 (행간)
• lead: 이후 데이터 (행간)
• 다음 주문날이 언제일지 예측할 때 사용, 주문수량의 증분 계산

select ordernumber, productcode, quantityordered, 
	quantityordered - lag(quantityordered) over(partition by productcode order by ordernumbers) as quantity_difference
from orderdetails;

 

순위 함수

• ROW_NUMBER: 중복없이 고유한 순위 부여
• RANK: 중복값에 같은 순위 부여, 중복 숫자만큼 건너뜀
• DENSE_RANK: RANK와 유사하지만, 중복 숫자 건너뛰지 않음
  • SQLd 단골 문제
• First_value():
• Last_value():

select custom_name, creditlimit,
	row_number() over (order by creditlimit ASC) as row_rank,
    rank() over (order by creditlimit ASC) as rank,
    dense_rank() over (order by creditlimit ASC) as dense_rank
from customers
order by creditlimit ASC;

row_number은 같은 값에도 1, 2, 3 순위 매기고 rank는 1, 1, 1, 4, 5 중복값 건너뛰고, dense_rank는 1, 1, 1, 2, 3 순위는 다 부여

 

window frame

범위를 조정하는 함수

• ROW
  행의 개수로 윈도우 프레임 정의
• RANGE
  정렬의 기준이 되는 행의 값을 기준으로 정의
• PRECEDING
  현재 행보다 이전 행
• FOLLOWING
• UNBOUNDED PRECEDING
  현재 파티션의 첫 번째 행부터 현재 행까지의 범위
• UNBOUNDED FOLLOWING
  현재 행부터 현재 파티션의 마지막 행까지의 범위
• CURRENT ROW
  현재 행

 

select a, b, c
	avg(quantityordered) over (order by ordernumber ROWS between 1 PRECEDING and 1 FOLLOWING) as moving_avg_1,
    avg(quantityordered) over (order by ordernumber ROWS between 1 CURRENT ROW and 1 FOLLOWING) as moving_avg_2,
    avg(quantityordered) over (order by ordernumber ROWS between 1 PRECEDING and 1 CURRENT ROW) as moving_avg_3,
    avg(quantityordered) over (order by ordernumber RANGE between 1 PRECEDING and 1 FOLLOWING) as moving_avg_4,
from orderdetails;

• ROWS BETWEEN : 현재 행 기준 앞뒤 1개 행씩 추가해서 범위 지정
• RANGE BETWEEN : 현재 ordernumber 값(order by 값)과 차이가 1이내인 모든 행을 범위로 지정
  •