코테 준비 | Restaurant Growth(이동 평균, 누적 합 구하기 - 윈도우함수/연관 서브쿼리/ N 행 이후부터 끝까지 출력하기)

☑️  1321. Restaurant Growth

[문제]

• 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.The result format is in t
• Return the result table ordered by visited_on in ascending order.
• 레스토랑 테이블에서 일주일(6일전-현재) 까지 누적합 및 이동평균 구하기 (+7일 이후부터 출력)

---

[문제 풀이]

내 코드 - 윈도우 함수

WITH daily_sales AS (-- 일별 매출집계
SELECT visited_on
      ,SUM(amount) as daily_amount
FROM CUSTOMER
GROUP BY visited_on
), agg AS (  -- 윈도우로 집계 & 7일 이상부터 출력하기 위한 넘버링
SELECT visited_on
      ,row_number() OVER (ORDER BY visited_on) AS rown
      ,SUM(daily_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount
      ,ROUND(AVG(daily_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) AS average_amount
FROM daily_sales
ORDER BY visited_on
)
SELECT visited_on
        ,amount
        ,average_amount
FROM agg
WHERE rown>=7

 

내 코드 - 연관 서브쿼리

WITH daily_sales AS (-- 날짜의 값이 여러개 일때 먼저 SUM
SELECT visited_on
      ,SUM(amount) as daily_amount
FROM CUSTOMER
GROUP BY visited_on
)
SELECT visited_on
      ,(SELECT SUM(d2.daily_amount) FROM daily_sales d2 WHERE d2.visited_on BETWEEN DATE_SUB(d1.visited_on, INTERVAL 6 DAY) AND d1.visited_on ) AS amount
      ,(SELECT ROUND(AVG(d2.daily_amount),2) FROM daily_sales d2 WHERE d2.visited_on BETWEEN DATE_SUB(d1.visited_on, INTERVAL 6 DAY) AND d1.visited_on ) AS average_amount
FROM daily_sales d1
WHERE visited_on >= (SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)FROM customer)
ORDER BY visited_on

 

이슈 및 해결 과정

1. 일별 매출 집계
   • 일주일간 이동평균, 누적합은 일별로 집계 되어 있어야 행단위 연산 가능 → 먼저 일별 집계 해줌 
   • daily_sales with문 작성 
2. 누적합 , 이동 평균 구하기
   • 윈도우 함수 : sum() over , avg() over 
     
     • 윈도우 프레임으로 rows between 6 preceding and current row 사용 
   • 연관 서브쿼리 :
     • 스칼라 연관 서브쿼리로 where 절에서 필터링 해줌  : WHERE d2.visited_on BETWEEN DATE_SUB(d1.visited_on, INTERVAL 6 DAY) AND d1.visited_on
3. (이슈) N행 이후부터 출력하기 : 7일 이후 부터 출력해야 함  
   • 날짜 순번 매기고 필터링 : 7번째 부터 출력하기 
     • 코드1 에서 ) agg 서브쿼리에서 윈도우 함수 + row_number로 순번 매겨주고
     • 아우터 쿼리 where 절에서 7번째 부터 최종 필터링
   • where 절 서브쿼리로 필터링 :  
     • 코드2) 아우터 쿼리에서 일자 기준을 where 절 서브쿼리로 제한 함 
     • WHERE visited_on >= (SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)FROM customer)
   • limit `offset` 조건으로 필터링
     
     • limit 에서 offset 조건 활용시, 순서 건너뛰고 갯수 필터링 가능함
     • limit offset 6,999 → 즉, 6개 건너뛰고 999번까지 필터링 
     • 단점, 데이터의 갯수가 몇개인지 모르기때문에 데이터가 클경우 최적화 어려울수있음
   • 성능 측면 
     • 리트코드에서 결과값 돌려봤을때, 윈도우 사용 & where 절 서브쿼리로 7일 이상 필터링 햇을때 가장 성능이 좋았음 

한줄 포인트

• 이동평균, 누적합 조회시 → 데이터가 클경우 윈도우 함수 사용이 적절 (but row_number 같은 순번 매기는 함수는 추가 시간 소요) 
• 특정값 이상 출력시 → where 조건으로 필터링 해주는게 성능면에서 우수