[WINDOW] Restaurant Growth

링크

https://leetcode.com/problems/restaurant-growth/description/

문제

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.

The result format is in the following example.

정답

select distinct visited_on,
        sum(amount) over w as amount,
        round((sum(amount) over w)/7, 2) as average_amount
    from customer
    WINDOW w AS ( 
            order by visited_on
            range between interval 6 day PRECEDING and current row
    )
    Limit 6, 999

 

select
t2.visited_on
,sum(c.amount) amount
,round(sum(c.amount)/count(distinct c.visited_on),2) average_amount
from 
(
select visited_on from (
    select
    lead(visited_on,6) over(order by visited_on) as visited_on
    from Customer 
    where visited_on is not null
    group by visited_on
    ) t1
where visited_on is not null
) t2
left join Customer c
on c.visited_on between subdate(t2.visited_on,6) and t2.visited_on 
group by t2.visited_on

해설 1

• 1. SELECT DISTINCT visited_on
  • visited_on: 고객이 방문한 날짜를 나타내는 열입니다.
  • DISTINCT: 중복된 방문 날짜를 제거하고 고유한 날짜만 선택합니다.
• 2. sum(amount) OVER w AS amount
  • sum(amount): 고객 테이블에서 amount 값을 합산하는 집계 함수입니다.
  • OVER w: 윈도우 함수로, w라는 이름의 윈도우 프레임을 정의하여 그 범위 내에서 합계를 계산합니다.
  • 결과: 각 행에 대해 해당 날짜 기준으로 최근 7일 동안의 amount 합계를 반환합니다.
• 3. round((sum(amount) OVER w)/7, 2) AS average_amount
  • 평균 계산: 최근 7일간의 합계를 7로 나누어 일일 평균 금액을 계산합니다.
  • round(..., 2): 소수점 둘째 자리까지 반올림하여 평균 금액을 반환합니다
• 4. WINDOW w AS (...)
  • 윈도우 정의:
    • ORDER BY visited_on: 방문 날짜를 기준으로 정렬합니다.
    • RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW: 현재 행의 날짜를 기준으로 그 이전 6일과 현재 날짜까지의 범위를 설정합니다. 즉, 최근 7일간의 데이터를 다룹니다
• 5. LIMIT 6, 999
  • LIMIT: 쿼리 결과에서 6번째 행부터 시작하여 최대 999개의 행을 반환합니다. 이는 결과에서 처음 5개의 행을 건너뛰고 그 다음부터 데이터를 가져오는 역할을 합니다

해설 2

• 1. SELECT t2.visited_on
  • t2.visited_on: t2라는 임시 테이블에서 각 방문 날짜(visited_on)를 선택합니다. 이 날짜는 메인 쿼리에서 기준이 됩니다.
• 2. SUM(c.amount) AS amount
  • SUM(c.amount): 고객 테이블에서 해당 날짜 범위 내의 금액(amount)을 모두 합산하여 반환합니다. 즉, 각 t2.visited_on 날짜에 대해 최근 7일 동안의 금액 합계를 계산합니다.
• 3. ROUND(SUM(c.amount) / COUNT(DISTINCT c.visited_on), 2) AS average_amount
  • 평균 계산: 각 t2.visited_on 날짜에 대해 최근 7일 동안의 금액 합계를 그 기간 내 고유한 방문 날짜(visited_on)의 개수로 나누어 일일 평균 금액을 계산합니다.
  • ROUND(..., 2): 소수점 둘째 자리까지 반올림하여 평균 금액을 반환합니다.
• 4. 서브쿼리: LEAD(visited_on, 6) OVER (ORDER BY visited_on)
  • 윈도우 함수 LEAD(): 이 함수는 현재 행에서 지정된 오프셋(여기서는 6)만큼 뒤에 있는 값을 가져옵니다.
  • LEAD(visited_on, 6): 현재 행의 visited_on 날짜로부터 6일 뒤의 방문 날짜를 가져옵니다.
  • 목적: 이 함수는 각 행에 대해 그 이후 6번째 행의 방문 날짜를 반환하며, 이를 통해 최근 7일간의 데이터를 처리할 수 있게 합니다.
• 5. 서브쿼리 필터링: WHERE visited_on IS NOT NULL
  • 이 조건은 방문 날짜가 NULL이 아닌 경우에만 데이터를 선택하도록 필터링합니다.
• 6. 외부 서브쿼리: t2
  • 내부 서브쿼리에서 계산된 결과를 다시 한 번 필터링하여, NULL이 아닌 방문 날짜만 선택하고 이를 임시 테이블 t2로 만듭니다.
• 7. LEFT JOIN 및 조건절
  • LEFT JOIN Customer c ON c.visited_on BETWEEN SUBDATE(t2.visited_on, 6) AND t2.visited_on:
    • 이 조건은 t2.visited_on을 기준으로 그 이전 6일과 현재 날짜 사이에 해당하는 모든 고객 데이터를 조인합니다.
    • 즉, 각 기준 날짜(t2.visited_on)에 대해 최근 7일간의 고객 데이터를 가져옵니다.
• 8. GROUP BY t2.visited_on
  • 쿼리는 각 기준 방문 날짜(t2.visited_on)별로 그룹화되어 결과를 반환합니다. 이를 통해 각 기준 날짜마다 합계와 평균이 계산됩니다.