[SQL] 📌 window 함수 range/rows between 알아보기 | leetcode | 1321. Restaurant Growth 예제 풀이

Windw 함수 : Range/Rows between

 

윈도우 함수 기능을 사용할 때 행간의 범위를 지정할 수 있는 함수이다.

예를 들어, 행 간의 sum을 구할 때, [5개 이전행~현재행] 의 합을 구하고 싶을 때 사용할 수 있다.

물론, 그 범위는 자유롭게 지정할 수 있다.

 

Range/Rows between 문법

 

• 함수 over ( ~ range/rows between [범위 1] and [범위 2] )

[단위 지정]

ROWS BETWEEN : 물리적인 단위로 행 집합을 지정, order by에 같은 값이 있어도 별개의 행으로 취급
RANGE BETWEEN : 논리적인 단위로 의해 행 집합을 지정, order by에 같은 값이 있으면 하나의 행으로 취급

 

[범위 지정]
UNBOUNDED PRECEDING : 윈도우 시작 위치가 첫 번째 로우임을 의미 
UNBOUNDED FOLLOWING : 윈도우 마지막 위치가 마지막 로우임을 의미 
[ROW수] PRECEDING : 윈도우 시작 위치가 ROW수만큼 이전이 시작 로우임을 의미 (ex. 6 preceding)
[ROW수] FOLLOWING : 윈도우 마지막 위치가 ROW수만큼 다음이 마지막 로우임을 의미 (ex. 6 following)
CURRENT ROW : 현재 로우까지를 의미

INTERVAL N [시간단위] PRECEDING/FOLLOWING
: range에서만 사용할 수 있으며, 시간 단위로 범위를 지정한다. (아래 예제에서 활용 방법을 더 자세히 알아보겠다)

 

⚠️ 여기서 항상 헷갈리는 것

- 6 preceding (O)   6 row preceding (X)  :  숫자만 !

- interval 6 day preceding (O)  interval 6 days preceding (X)  : 단위에 -s(복수형) 안붙임 !

 

 

문제

 

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

 

쿼리 답안

 

나의 매우 직관적인 답안

with result as (
select
    visited_on,
    amount,
    lag(amount, 1) over (order by visited_on) as a,
    lag(amount, 2) over (order by visited_on) as b,
    lag(amount, 3) over (order by visited_on) as c,
    lag(amount, 4) over (order by visited_on) as d,
    lag(amount, 5) over (order by visited_on) as e,
    lag(amount, 6) over (order by visited_on) as f
from (select visited_on, sum(amount) as amount from customer group by visited_on) as t
)

select visited_on,
    (amount + a + b + c + d + e + f) as amount,
    round((amount + a + b + c + d + e + f) / 7, 2) as average_amount
from result
where visited_on between '2019-01-07' and '2019-01-10'

-- 12분

 

타 블로그 풀이

with result_table as
(SELECT VISITED_ON
	, SUM(AMOUNT) OVER(ORDER BY VISITED_ON RANGE BETWEEN INTERVAL 6 DAY 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) AMOUNT
	FROM CUSTOMER
	GROUP BY VISITED_ON) as t)
    
select *
from result_table
WHERE VISITED_ON >= (SELECT MIN(VISITED_ON) FROM CUSTOMER) + 6 -- 직접 날짜 범위를 지정하는 게 아닌 최소 날짜+6으로 지정
-- 한 테이블 안에 where절로 visited_on에 조건을 걸어버리면 이전 날짜의 데이터를 포함하지 않은 결과가 나온다
ORDER BY VISITED_ON ASC

 

 

문제 풀이

 

interval [날짜 수] day를 사용해서 6일 이전 ~ 현재까지의 날짜 간격을 범위로 지정한다.

sum / avg 를 사용해서 6일 이전 ~ 현재까지의 이동합 / 이동평균을 구할 수 있다!

 

select visited_on,
       amount,
       sum(amount) over (order by visited_on range between interval 6 day preceding and current row) as 'amount'
from (select visited_on, sum(amount) as amount from customer group by visited_on) as t

 

2019-01-01 ~ 2019-01-01 

2019-01-01 ~ 2019-01-02

2019-01-01 ~ 2019-01-03

2019-01-01 ~ 2019-01-04

2019-01-01 ~ 2019-01-05

2019-01-01 ~ 2019-01-06

2019-01-01 ~ 2019-01-07 -- 여기부터 7개행 합 구해짐

2019-01-02 ~ 2019-01-08

2019-01-03 ~ 2019-01-09

2019-01-04~ 2019-01-10

 

이렇게 날짜 범위로 합이 구해진 걸 볼 수 있다.

 

느낀 점

 

정말 새로운 문법은 끝도 없이 나오는구나 ~~

열심히 공부해야겠다 !