Pori_IT

문제

https://school.programmers.co.kr/learn/courses/30/lessons/59413

[문제 포인트 짚기]

포인트는 단 하나. 0시부터 23시까지 이다. 이게 왜 포인트인가?

기존 테이블에 DATETIME은 0~23시의 값을 모두 갖지 않기 때문이다. (예제는 7~19시의 값을 갖고 있다.)

풀이

[RECURSIVE]

0~23시의 값을 만들어내기 위해 어떤 방법이 있을까 찾아보던 중 RECURSIVE에 대해 알게 되었다. 다음 블로그를 참고해서 작성한다.

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

기본적인 문법은 다음과 같다. WITH RECURSIVE > 내부에서 UNION 사용.

WITH RECURSIVE CTE AS (
	SELECT 1 AS n -- 초기 값 설정
    UNION ALL
    SELECT n+1 AS num -- 재귀
    FROM CTE
    WHERE n<3 -- 정지 조건
 )

1. 컬럼 n의 초기값을 설정한다.
2. UNION ALL을 사용!
3. 아래는 재귀 함수를 작성한다.
4. WHERE을 이용해서 종료 조건을 설정

[특징과 조건]

• 메모리 상에 가상의 테이블 저장
• UNION 사용
• 초기값을 위해 최소 1개의 비반복문 사용
• 바깥의 가상 테이블을 참조하는 반복문이 필요
• 반복문은 정지조건이 필수
• 가상의 테이블을 구성하면서 그 자신을 이용해 값을 결정할 때 유용

[진짜 풀이]

순서는 이렇다.

1. 0~23의 시간을 만드는 임시 테이블을 생성

: 방법은 위에 나와있는 RECURSIVE와 동일하다.

WITH RECURSIVE CTE AS (
    select 0 as HOUR
    UNION ALL
    select HOUR+1 as num
    from CTE
     where HOUR+1 <= 23
),

2. ANIMAL_OUTS에서 시간대별 입양 건수 계산 -> 임시테이블사용

: HOUR로 그룹을 묶고 ANIMAL_ID의 수를 계산한다.

CTE2 AS (
    SELECT HOUR(DATETIME) AS HOUR , COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY 1
)

3. 두 테이블 조인 및 비어있는 값은 COALESCE를 이용해서 0으로 출력하기

: CTE를 뒤에 사용해서 RIGHT조인을 사용했는데 왼쪽에 넣고 LEFT 조인을 해도 괜찮다.

여기서는 COUNT가 문제인데 COALESCE를 사용했다. 값이 없다면 0으로 표시되도록 작성했다.

-- 전체 코드
WITH RECURSIVE CTE AS (
    select 0 as HOUR
    UNION ALL
    select HOUR+1 as num
    from CTE
     where HOUR+1 <= 23
),
CTE2 AS (
    SELECT HOUR(DATETIME) AS HOUR , COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY 1
)
select c1.hour, COALESCE(c2.count,0) AS COUNT
FROM CTE2 c2 right JOIN CTE c1
    ON c1.hour = c2.hour

문제

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