[MYSQL] 1321. Restaurant Growth/ lag 행을 통한 유동적인 누적합

>>문제 설명

테이블 : Customer

customer_id : 소비자 식별번호

name : 소비자 이름

visited_on : 방문날짜

amount : 구매량

 

당신은 식당 관리자로써 앞으로의 각 날짜의 소비량을 예측하고자 한다.

예측 소비량은 : (오늘 소비량 + 이전 6일간의 소비량 누적합) /7로 계산한다. (이동평균법)

단, 각 날짜에는 여러 손님이 방문할 수 있다.

 

예시)

1/7일 예측량 : 1/1~1/7일 소비량의 평균 => 122.86

1/8일 예측량 : 1/2~1/8일 소비량의 평균 => 120

1/9일 예측량 : 1/3~1/9일 소비량의 평균 => 120

1/10일 예측량 : 1/4~1/10일 소비량의 평균 => 142.86

 

---

step1.날짜별 소비량 구하기  +num행 생성 

 

자세히 보면 한 날짜에 여러 손님이 방문한 날이 있음

따라서, 날짜를 기준으로 그룹화하여 각 날짜의 소비량을 모두 더한 테이블을 생성해야 함.

 

또한우리가 구해야 하는 테이블은 손님이 방문한 날짜로부터 7일후부터임.

예시에서는 1/1일이 첫방문일이므로 예측량 연산이 가능한 7일 후인 1/7일부터 테이블이 생성됨.

따라서 결과 테이블의 행수를 where 절로 걸러내기 위해 row_number 열을 생성해줌.

 

 

>>쿼리

>> 결과

 

---

step2. 이전 6행과 오늘 amount의 합을 구해줌

예측량 amount = 오늘 amount+ (1일전) amount + ......... + (6일전) amount

이걸 다른 말로 하면

예측량 amount = 오늘 amount+ (1행전) amount + ......... + (6행전) amount

 

따라서 이전행을 조회할 수 있는 lag function이 떠오름

 

=> lag/lead 기능 알아보기

이전/이후 행 조회하는 함수 설명: lag/lead

[Oracle] 오라클 LAG, LEAD 함수 사용법 (이전값, 다음값)

 

아무튼 이전행을 조회하기 위해선

lag(조회하고자 하는 열, 점프 수) over (partition by - order by) 의 식을 따른다.

 

이걸 활용해 식을 작성해보면 다음과 같다.

여기서 ifnull을 한 이유는 연산에 null값이 포함되면 무조건 null이 되기 때문이다.

즉, 6행 전의 값이 없다면 결과는 null처리가 된다.

 

우선 결과값을 차근차근보고자

연산과정에서 null이 되는 것을 방지하기 위해 ifnull을 주었다.

 

>>결과

---

step3 : num값으로 원하는 기간부터 조회

 

이젠 7로 나누어 평균값을 계산해주고

 7번 행부터 조회만 하면 된다.

 

>>결과

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

Restaurant Growth - LeetCode