푸는데 꽤 많은 시간이 소요된 문제였습니다..! 결국 ㅜㅜ 힌트를 찾아서 보았네요
📖문제
아래의 테이블에서 볼 수 있듯이, 누적합과 누적평균을 구하는 문제입니다!
어려웠던 점은 그냥 구하는것이 아니라 '6일전 까지'를 기준으로 구해야한다는 점입니다.
TABLE 및 예시
🖥️코드
하나씩 코드를 살펴봅시다!
WITH cte as(
SELECT visited_on
, min(visited_on) over() as first_day
, SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS AMOUNT
, ROUND(SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) / 7, 2) AS AVERAGE_AMOUNT
FROM (SELECT visited_on
, SUM(amount) as amount
FROM CUSTOMER
GROUP BY visited_on) A
)
SELECT visited_on
,amount
,average_amount
FROM CTE
where datediff(visited_on, first_day)>=6
서브쿼리 부분 A
테이블에 visited_on 날짜가 중복되는 경우가 있기 때문에 visited_on 을 기준으로 group by 해준뒤 날짜별 총 amount를 집계해주었습니다.
FROM (SELECT visited_on
, SUM(amount) as amount
FROM CUSTOMER
GROUP BY visited_on) A
누적합
WITH cte as(
SELECT visited_on
, min(visited_on) over() as first_day
, SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS AMOUNT
, ROUND(SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) / 7, 2) AS AVERAGE_AMOUNT
잠깐! 🫥 ROWS RANGE BETWEEN ... AND ?
그 다음 문제의 핵심이 되는 부분입니다. 저도 이번에 해당 문제를 풀면서 새로 공부하게 되었는데요🫠! 바로 누적합이나 평균을 구할 때 자주 쓰이는 ROWS | RANGE BETWEEN ... AND 라는 옵션 입니다.
위 문제의 경우에는 ROWS 와 RANGE 둘 다 정답처리가 되었는데요! 차이점에 대해서 좀 알아두려고 합니다.
먼저, 윈도우 함수의 ROWS와 RANGE는 데이터의 윈도우(범위)를 지정하는 데 사용되는 두 가지 옵션입니다. 이 두 옵션은 데이터의 순서에 따라 어떤 범위의 행을 포함할지를 결정하게 됩니다.
① ROWS
먼저, ROWS 옵션은 행의 개수를 기반으로 기준을 정하는데요. 물리적인 행의 순서를 기준으로 정하는 느낌입니다.
예를 들어, 위 코드를 바로 해석해보면
SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)👉🏻
amount합계를visited_on순서대로 누적해서 구할건데,CURRENT ROW(현재행) 부터6 PRECEDING(이전 6행) 까지 구한다.
- 데이터의 시작부터 현재 행까지의 모든 행을 포함하는 윈도우를 지정하려면
UNBOUNDED
ROWS BETWEENUNBOUNDEDPRECEDING AND CURRENT ROW
② RANGE
반면에 RANGE 옵션은 값의 범위를 기준으로 정합니다. ,
아래처럼 단순히 6개 이전행을 가져오는게 아니라 날짜라는 값의 범위를 기반으로 가져오는것에 차이가 있습니다.
SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW)👉🏻
amount합계를visited_on순서대로 누적해서 구할건데,CURRENT ROW(현재 행) 부터INTERVAL 6 DAY PRECEDING(6일 전) 까지 구한다.
사실 문제의 의도대로라면 RANGE 를 이용하는 것이 맞다고 생각이 드는군요.
예를들어, 아래의 테이블의 경우, 1일 부터 10일 까지 빈 날짜가 없기 때문에 이를 물리적으로 접근 (ROWS)하거나 값(날짜)으로 접근(RANGE)해도 결론적으로 똑같은 결과가 나옵니다. 현재의 문제처럼! 6행 이전을 가져오던 6일 이전을 가져오던 결국 6개인건 똑같으니까요!
| visited_on | amount |
|---|---|
| 2019-01-01 | 100 |
| 2019-01-02 | 110 |
| 2019-01-03 | 120 |
| 2019-01-04 | 130 |
| 2019-01-05 | 110 |
| 2019-01-06 | 140 |
| 2019-01-07 | 150 |
| 2019-01-08 | 80 |
| 2019-01-09 | 110 |
| 2019-01-10 | 280 |
하지만 아래처럼 8, 9 일에 레스토랑이 쉬는날이라 데이터가 비어있었다면? 이는 RANGE로 접근해야 의도대로 원하는 값이 나올 것 입니다.
예를들어, 10일을 기준으로 6일전까지의 amount 합계를 구해야하는데, ROWS 를 이용해서 단순히 물리적인 6개 이전의 행을 가져오면 2일 ~ 10일 까지의 amount를 합치게 됩니다. 10일의 6일전은 4일 까지인데 말이죠.
| visited_on | amount |
|---|---|
| 2019-01-01 | 100 |
| 2019-01-02 | 110 |
| 2019-01-03 | 120 |
| 2019-01-04 | 130 |
| 2019-01-05 | 110 |
| 2019-01-06 | 140 |
| 2019-01-07 | 150 |
| 2019-01-10 | 280 |
ROWS 와 RANGE를 이용했을때 값을 비교해보면 문제가 되는 10일의 AMOUNT 값이 확연히 다른것을 볼 수 있는데요!
range 를 이용한 amount 합계는 '날짜'값을 기준으로 누적합 되었기 때문에 문제의 의도대로 1월 4일 부터 10일까지 집계가 된 것을 볼 수 있습니다.
| visited_on | ROWS_AMOUNT_SUM | RANGE_AMOUNT_SUM |
|---|---|---|
| 2019-01-01 | 100 | 100 |
| 2019-01-02 | 210 | 210 |
| 2019-01-03 | 330 | 330 |
| 2019-01-04 | 460 | 460 |
| 2019-01-05 | 570 | 570 |
| 2019-01-06 | 710 | 710 |
| 2019-01-07 | 860 | 860 |
| 2019-01-10 | 1040 | 810 |
다시 문제로 이어가서.. with 절 서브쿼리로 만들어놓은 CTE 테이블(visited_on 별 누적합을 구한 테이블)에 visited_on 과 first_day의 차이가 6일 이상인 데이터만 필터링하여 가져오면 끝입니다!!
SELECT visited_on
,amount
,average_amount
FROM CTE
where datediff(visited_on, first_day)>=6
