[문제풀이] Leet code - Restaurant Growth

출처

 

문제

You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).
Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.
Return the result table ordered by visited_on in ascending order.

 

문제에 대한 해석

• average_amount : 7일을 기준으로 매출의 이동평균 계산
• amount : 7일을 기준으로 매출의 누적합
• 단, 이동평균으로 계산되지 않는 날짜는 제외
• 2019-01-10의 날짜는 2개의 데이터 존재 > 2019-01-10의 amount는 합산
• 4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

2019-01-01 ~ 2019-01-10	7일 이동평균으로 계산된 날짜부터(2019-01-07) 집계

 

풀이(MYSQL)

-- 날짜별 총 합을 계산
with base as (
    select visited_on, sum(amount) as 'amount'
    from Customer
    group by visited_on
 ),
-- 누적합과 이동평균
answer as (
    select
    visited_on,
    sum(amount)over(order by visited_on rows between 6 preceding and current row) as 'amount'
    , round(avg(amount)over(order by visited_on rows between 6 preceding and current row),2) as 'average_amount'
    ,row_number()over(order by visited_on) as 'rn'
    from base 
)
-- 6일 이후의 날짜만 출력
select visited_on,amount,average_amount
from answer
where rn >6

 

다른사람 풀이

SELECT 
    visits.visited_on AS visited_on,
    SUM(c.amount) AS amount, 
    ROUND(SUM(c.amount) / 7.0, 2) AS average_amount
FROM (
    SELECT DISTINCT visited_on 
    FROM Customer
    WHERE DATEDIFF(
        visited_on, 
        (SELECT MIN(visited_on) FROM Customer)
    ) >= 6
) visits 
LEFT JOIN Customer c 
ON DATEDIFF(visits.visited_on, c.visited_on) BETWEEN 0 and 6
GROUP BY visits.visited_on
ORDER BY visited_on;

작동순서

💡SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by ->결과반환

1. FROM 절: 
   1. Customer테이블에서 visited_on날짜와 visited_on의 가장 작은 날짜의 일수 차이가 6보다 같거나 클때 고유날짜를 출력하여 visits테이블로 저장 
      1. 즉, 2019-01-07 ~ 2019-01-10까지의 날짜 출력
   2. visits테이블을 기준으로 Customer 테이블과 조인하는데 visits테이블의 날짜(2019-01-07~2019-01-10)와 Customer의 날짜(2019-01-01~2019-01-10)의 차이가 0~6이하일때를 기준으로 함

1. GROUP BY 절: visits의 visited_on를 기준으로 그룹화(2019-01-07~2019-01-10)
2. SELECT 절
   1. customer의 amount의 합을 구해서 amount라고 하고
   2. customer의 amount의 합을 구해서 7로 나누어 average_amount라고 저장
3. ORDER BY 절: visited_on 오름차순

 

이동평균 구하는 방법

1. join + group by를 통한 계산

select
a.visited_on, 
sum(b.amount) as 'amount',
round(avg(b.amount),2) as 'average_amount'
from customer a join customer b 
on b.visited_on between date_sub(a.visited_on, interval 6 day) and a.visited_on
group by a.visited_on
order by a.visited_on

a = b	output

•  b.visited_on between date_sub(a.visited_on, interval 6 day) and a.visited_on
  • b의 visited_on가 a의visited_on에서 6일을 뺀 날짜 안에 있을때 a의 visited_on와 조인
  • DATE_SUB(기준날짜, INTERVAL 기간) :기준날짜에서 기간을 뺀 날짜계산
  • a의 2019-01-01과 b의 2019-01-01를 조인하여 2019-01-01를 출력하고 2019-01-01의 amount의 평균 100 (= 100 / 1)
  • a의 2019-01-02를  b의 2019-01-01과 02를 조인하여 2019-01-02를 출력하고 1일과 2일의 amount의 평균 105 (=(100+110)/2)
  • a의 2019-01-08를  b의 2019-01-02 ~ 2019-01-08를 조인하여 2019-01-08를 출력하고 2019-01-02 ~ 2019-01-08의 amount평균을 계산
• round(avg(b.amount),2) as 'average_amount'
  • b의 amount(즉, a에서 6일뺀 날짜 사이에 있는 수량 들)의 평균을 계산하여 둘째자리까지 계산
•  1-10의 데이터가 2개 있는데 group by 하는 과정에서 생략됨
• > 날짜별로 group by하지 않으면 총 평균인 데이터 하나만 출력됨

> amount의 총 평균은 아님

> 이동평균의 총 평균도 아님..

 

2. 서브쿼리

select
a.visited_on, 
(select round(avg(b.amount),2) 
from customer b 
where b.visited_on between date_sub(a.visited_on, interval 6 day) and a.visited_on) as 'average_amount'
from customer a

날짜별로 그룹화를 하지 않아서 2019-01-10이 모두 출력됨!

 

3. window 함수의 이용

select
visited_on, 
round(avg(amount) over(order by visited_on rows between 6 preceding and current row),2) as 'average_amount'
from customer

 

 

• avg(amount) :이동평균을 계산하기 위해 사용
• over :윈도우 함수 사용 시작
• order by visited_on : 날짜순으로 정렬하여
• rows between 6 preceding and current row : 현재 행과 그 이전의 6개의 행을 포함