Pori_IT

윈도우 함수를 사용시 함수에 포함시킬 행의 범위를 지정한다. ROWS와 RANGE가 존재한다.

다음 블로그를 참고하여 정리했습니다. https://camel-context.tistory.com/14

Rows  & Range

ROWS : 현재 row 위치에서 물리적인 범위를 지정한다.

RANGE: 현재 row 값을 기준으로 논리적인 범위를 지정.

[UNBOUNDED PRECEDING]

ROW:  현재 행과 현재 행 위에 있는 모든 행들을 함수에 사용한다.

RANGE: 현재 행의 값을 기준으로 이하의 값을 함수에 사용한다.

[숫자 PRECEDING]

ROW: 현재 행과 바로 위의 N개의 행의 값을 함수에 사용한다. -> 숫자를 지정한 만큼 앞의 값을 사용

=> SUM(A) OVER (ORDER BY B ROWS 1 PRECEDING) 이라면 A의 값은 B행으로 나열하였을때
앞의 첫 행의 값을 더해서 값을 산출한다.

[BETWEEN과 함께]

범위를 지정하고 싶다면 BETWEEN을 사용해서 작성할 수 있다.

예를 들어서 현재로부터 6일 이전의 전의 값까지 합하고 싶다면 이런 쿼리문을 작성하면 된다.

SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

문제

각 제품의 평균 판매 가격을 구하시오. 소수점 이하 2자리로 반올림해서 average_price를 구해야한다.

테이블 & 예제

[Price]

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+

[UnitsSold]

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+

[예제]

Input: 
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+
Output: 
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+

풀이

1. purchase_date가 start_date ~ end_date안에 있도록 JOIN하기

이를 위해 BETWEEN A AND B를 사용하였다.

SELECT *
FROM Prices AS p
	LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id
    AND u.purchase_date BETWEEN p.start_date AND p.end_date

2. 수식 삽입

우선 product_id 별로 그룹을 묶어줄것이기 때문에 Group By를 사용하고, 평균 판매 가격을 계산하기 위해서

각 시간대별 (판매가격 * 판매량)의 합 / 전체 판매량 을 적용합니다.

SELECT p.product_id, 
        SUM(p.price * u.units) / SUM(u.units)
        AS average_price
FROM Prices AS p
    LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id 
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id

3. 반올림 및 null 처리

반올림은 자릿수를 맞추어서 ROUND를 적용시켜주었습니다. 이제 null 을 처리해야하는데 3가지 방법이 존재합니다.

1. IFNULL

2. CASE WHEN

3. COALESCE

저는 COALESCE를 사용해주었습니다.

SELECT p.product_id, 
    COALESCE(ROUND(
        SUM(p.price * u.units) / SUM(u.units),
        2),0) 
        AS average_price
FROM Prices AS p
    LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id 
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id