SQL : Window 함수, LEAD/LAG 함수

🍀 1. Window 함수

---

특정 구간, 기간이라고 생각하면 됨.
데이터를 조회할 때 통계적인 개념이 들어간다.

• SELECT 구문에서 사용되며 분석 구간을 변동시키는 역할. EX)누적합
• SUM(COLUMN1) OVER(PARTITION BY COLUMN2 ORDER BY COLUMN3)
  AS NEW_COLUMN

PARTITION BY : GROUP BY와 비슷한 역할로, 그룹별로 누적 합계를 구할 수 있다. GROUP BY는 집계 결과로 조회가 되는 반면 PARTITION BY는 본래의 TABLE 그대로 출력.

• 생략 가능
  
  
  ORDER BY : 계산을 하는 순서를 정해준다.
• 생략하면 누적 합이 계산되지 않는다

SELECT customernumber, paymentdate, amount, 
SUM(amount) OVER(PARTITION BY customernumber
ORDER BY paymentdate) AS total_amount
FROM payments;

• 2,3번째 줄 : sum(amount) amount를 더한다. over 윈도우 함수구나. partition by customernumber 고객번호로 파티션을 나눈다. order by paymentdate 지불날짜 순서대로.
• customernnumber 가 같은 것(partition by)만 누적합이 적용됨.

SELECT customernumber, paymentdate, amount,
SUM(amount) OVER(PARTITION BY customernumber)
AS total_amount
FROM payments;

• ORDER BY paymentdate 생략 시, 누적 합계 순서가 없음.
• 따라서 그냥 다 더함(누적합이 계산되지 않음)

SELECT customernumber, paymentdate, amount,
SUM(amount) OVER(ORDER BY paymentdate) AS total_amount
FROM payments;

• PARTITION BY 생략 시, 그룹화가 사라짐
• 누적합은 계산됨

실습

❔ 문제 1 : orderdetails 테이블에서 각 주문별로 주문된 제품의 평균 수량(quantityOrdered)을 계산하라.

SELECT orderNumber, productCode,
AVG(quantityOrdered) OVER(PARTITION BY orderNumber)
AS AVG_quantity
FROM orderdetails;

• GROUP BY를 쓰면 집계됨
• 누적 평균이 아님. avg를 쓰면 됨.

❔ 문제 2 : orders 테이블에서 각 고객별로 주문 날짜에 따라서 지금까지의 주문 횟수를 계산하라.

SELECT customerNumber, orderNumber, orderDate,
COUNT(orderNumber) OVER(PARTITION BY customerNumber
ORDER BY orderdate) AS order_count
FROM orders;

• 실무에서 가장 많이 사용하게 될 데이터 중 하나.
• 특정 고객의 주문 빈도가 점점 많아지고 있는가? 또는 적어지고 있는가? 또는 특정 월에 몰려있는가?

🍀 2. LEAD/LAG 함수

---

LEAD : 다음 행 데이터를 가져온다
LAG : 이전 행 데이터를 가져온다
증가분을 보고싶을 때 많이 사용함

SELECT orderNumber, customerNumber, orderDate,
LAG(orderDate) OVER (PARTITION BY customerNumber ORDER BY orderDate)
AS prev_order_date,
LEAD(orderDate) OVER (PARTITION BY customerNumber ORDER BY orderDate)
AS next_order_date
FROM orders;

• orderDate의 이전 행을 가져온 것을 prev_order_date라고 별칭한다
• orderDate의 다음 행을 가져온 것을 next_order_date라고 별칭한다

SELECT orderNumber, customerNumber, orderDate,
LAG(orderDate) OVER (ORDER BY orderDate) AS prev_order_date,
LEAD(orderDate) OVER (ORDER BY orderDate) AS next_order_date
FROM orders;

• partition by를 사용하지 않음 (order by만 사용)
• orderDate를 순서대로 정렬

SELECT orderNumber, customerNumber, orderDate,
LAG(orderDate) OVER (PARTITION by customerNumber) AS prev_order_date,
LEAD(orderDate) OVER (PARTITION BY customerNumber) AS next_order_date
FROM orders;

• order by를 사용하지 않음. 이 데이터 자체에 내림차순 정렬이 되어있음
• 보통 날짜 데이터는 정렬되어 저장되기 때문에,,

실습

❔ 문제 3 : orderdetails 테이블에서 각 제품 코드별로 주문된 수량(orderNumber)을 기준으로 정렬했을 때, 주문 수량의 증분을 계산하라.

SELECT orderNumber, productCode, quantityOrdered,
	quantityOrdered - LAG(quantityOrdered) OVER (PARTITION BY productCode ORDER BY orderNumber) AS quantity_difference
FROM orderdetails;

• orderNumber을 기준으로 정렬을 하고, 주문별로 몇 개씩 주문 수량이 바뀌는 지 계산하는 문제.
• 각 제품 코드별로 : Group by라고 생각할 수 있지만, 쭉 나열해야 하기 때문에 partiton by를 사용해야 함