SQL > LeetCode 1251. Average Selling Price (MySQL)

문제

각 제품의 평균 판매 가격을 구하시오. 소수점 이하 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