[리트코드] |1251. Average Selling Price; LEFT JOIN, IFNULL
1. Problem
각 제품의 기간별 가격이 명시된 Prices 테이블과 실제 판매 내역인 UnitsSold 테이블을 결합하여, 제품별 평균 판매 단가를 구해야 한다.
- 핵심 과제: 판매 시점(purchase_date)이 가격 적용 기간(start_date ~ end_date) 사이에 있는 데이터를 정확히 매칭해야 하며, 판매량이 없는 제품은 0으로 표시해야 한다.
2. Solution: 기간 매칭 조인과 가중 평균 계산
BETWEEN을 활용한 조인 조건 설정과 집계 함수를 이용한 가중 평균 식을 작성한다.
3. Takeaway: 쿼리 설계의 정밀도와 예외 처리 (객관적 분석)
- 비등가 조인(Non-Equi Join)의 활용: 단순히 ID로만 조인하는 것이 아니라 purchase_date BETWEEN start_date AND end_date라는 기간 조건을 조인 단계에서 처리함으로써, 집계 전에 데이터의 정합성을 확보했다.
- LEFT JOIN을 통한 행 보존: 문제에서 "판매 기록이 없는 제품도 출력"할 것을 요구했으므로, Prices 테이블을 기준으로 왼쪽 외부 조인을 수행하여 데이터 누락을 방지했다.
- 0으로 나누기(Division by Zero) 방지: 판매가 없는 경우 SUM(u.units)는 NULL이 된다. SQL에서 값 / NULL은 NULL이 되므로, 최종 결과를 IFNULL(..., 0)으로 감싸 문제의 요구사항인 **"판매 기록 없을 시 0"**을 완벽히 충족했다.
Table: Prices
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| start_date | date |
| end_date | date |
| price | int |
+---------------+---------+
(product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the price of the product_id in the period from start_date to end_date.
For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.
Table: UnitsSold
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| purchase_date | date |
| units | int |
+---------------+---------+
This table may contain duplicate rows.
Each row of this table indicates the date, units, and product_id of each product sold.
Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places.
**** If a product does not have any sold units, its average selling price is assumed to be 0. ****
Return the result table in any order.
The result format is in the following example.
Example 1:
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 |
+------------+---------------+
Explanation:
Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96
- Prices 기준으로 LEFT JOIN ---> 판매 기록이 전혀 없는 product_id도 결과에 나와야 함
- 분모가 NULL이 될 수 있으니 0 처리 ---> IFNULL(..., 0) SUM(u.units)가 NULL일 경우 평균 0 처리
SELECT p.product_id, ROUND(IFNULL(SUM(p.price*u.units)/SUM(u.units),0),2) AS average_price
FROM Prices p
LEFT JOIN UnitsSold 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;