[LeetCode-SQL 50] 1321. Restaurant Growth

문제

http://leetcode.com/problems/restaurant-growth/description/?envType=study-plan-v2&envId=top-sql-50

설명

각 날짜를 기준으로 6일전까지의 일주일 간의 총 amount와 그 평균을 구하는 문제이다. 행 간 관계를 이용해야하므로 윈도우 함수에서 LAG 함수를 이용하면 된다.

🚀 LAG, LEAD 함수
LAG 함수는 특정 컬럼을 기준으로 직전 m번째 레코드 값을, LEAD 함수는 직후 m번재 레코드 값을 출력해주는 함수이다.
포맷은 다음과 같다.
- LAG(컬럼1,m,n) OVER (ORDER BY 컬럼2) : 컬럼2 기준으로 정렬한 뒤, 컬럼1의 직전 m번째 값 출력
- LEAD(컬럼1,m,n) OVER (ORDER BY 컬럼2) : 컬럼2 기준으로 정렬한 뒤, 컬럼1의 직후 m번째 값 출력

먼저, 각 날짜마다 직전 m번째 날짜의 amount를 출력했다.

# Write your MySQL query statement below
WITH SUM_AMOUNT AS 
    ( select visited_on, sum(amount) as amount_sum
    from Customer
    group by visited_on )

select *, LAG(amount_sum,1) OVER(ORDER BY visited_on) as day_before_1,
LAG(amount_sum,2) OVER(ORDER BY visited_on) as day_before_2,
LAG(amount_sum,3) OVER(ORDER BY visited_on) as day_before_3,
LAG(amount_sum,4) OVER(ORDER BY visited_on) as day_before_4,
LAG(amount_sum,5) OVER(ORDER BY visited_on) as day_before_5,
LAG(amount_sum,6) OVER(ORDER BY visited_on) as day_before_6
from SUM_AMOUNT;

그 후 null이 아닌 날짜 중, 모든 amount를 더해서 평균을 구해줬다.

• null은 비교 시, is 와 is not 키워드를 사용한다.
• 소숫점 계산 시, round(숫자, 몇 번째 반올림) 함수를 사용한다.
• 윈도우 함수 대신, DATE_ADD(날짜, INTERVAL -6) 과 BETWEEN을 사용할 수도 있다.

SELECT c1.visited_on, 
       c2.visited_on AS visited_on_t, 
       c1.amount AS amount_t, 
       c2.amount
FROM customer c1 
INNER JOIN customer c2 
ON c1.visited_on BETWEEN DATE_ADD(c2.visited_on, INTERVAL -6 DAY) AND c2.visited_on;

구현

# Write your MySQL query statement below
WITH SUM_AMOUNT AS 
    ( select visited_on, sum(amount) as amount_sum
    from Customer
    group by visited_on ) ,

    SUM_AMOUNT_UNTIL_BEFORE_6 AS 
    ( select *, LAG(amount_sum,1) OVER(ORDER BY visited_on) as day_before_1,
    LAG(amount_sum,2) OVER(ORDER BY visited_on) as day_before_2,
    LAG(amount_sum,3) OVER(ORDER BY visited_on) as day_before_3,
    LAG(amount_sum,4) OVER(ORDER BY visited_on) as day_before_4,
    LAG(amount_sum,5) OVER(ORDER BY visited_on) as day_before_5,
    LAG(amount_sum,6) OVER(ORDER BY visited_on) as day_before_6
    from SUM_AMOUNT )

select visited_on, 
    amount_sum+day_before_1+day_before_2+day_before_3+day_before_4+day_before_5+day_before_6 as amount,
    round ((amount_sum+day_before_1+day_before_2+day_before_3+day_before_4+day_before_5+day_before_6)/7, 2) as average_amount
from SUM_AMOUNT_UNTIL_BEFORE_6
where day_before_1 is not null 
and day_before_2 is not null 
and day_before_3 is not null 
and day_before_4 is not null 
and day_before_5 is not null 
and day_before_6 is not null;