[SQL] 주단위 집계 꼭!! 주의해야할 점 (MySql)

 

쿼리(Query) 작업 시에 주 (Week) 단위로 집계를 해야한다면, 꼭 주의해야할 점이 하나 있다. 

 

그것은 특정 일자가 어느 주에 속하는지에 따라서 다른년도의 주로 잘 못 계산되는 경우가 무척 많기 때문이다.

특히 연의 마지막주와 연의 첫번째 주에서 이 오류를 자주 범하게 된다.

 

주 번호 (week number) 를 계산하는 방식에는 여러 가지가 있는데, 

예를 들어서
매주 월요일이 있는 날짜의 주를 해당 주의 기준으로 본다고 하면, 위 달력에서 22년도 1월 1일과 22년도 1월 2일은 21년도의 52번째 주에 포함이 된다. 즉, 22년 1월 3일부터 22년도 첫번째주 (01주)가 되는 것이다. 

그리고 반대의 경우도 발생할 수 있다. 특정 연도의 12월 30일 또는 31일이 다음 년도의 첫번째 주에 포함되는 경우도 있다.

 

그래서 그게 뭐가 문제인데 ??

 

 

 

라고 한다면 아래의 쿼리문으로 주단위 집계를 한다고 하자.

select date_format(sale_dt, '%Y-%v') as year_week 
	, sum(sale_amount) as amount
from tbl_sale 
where sale_dt between '2021-12-01' and '2022-01-31'
group by 1

 21년 12월부터 22년 1월 까지의 매출을 연도-주단위로 집계를 하는 쿼리문장이다. 

 

그러면 12월 27일 부터 12월 31일 까지는 주 번호가 2021-52 로 

1월 1일부터 1월 2일까지는 주 번호가 2022-52 로 즉, 같은 주인데 연도가 다르고 주번호가 같은 두 개의 주로 나뉘어 집계가 된다.

 

이렇게 되는 이유는 '%Y' 라는 포맷문자는 당연히 해당일자의 연도를 , '%v' 는 해당일자의 주번호를 각각 계산하기 때문에 그렇다.

 

 

그러면 어떻게 해야하는가??

 

좀 복잡해 보일 수는 있지만, case when 을 사용하여

 - 월이 12월인데, 주가 01로 떨어진다면 연도를 하나 더 해주어서 다음 년도의 주로 계산되도록  

 - 월이 01월인데, 주가 50주가 넘는다면 연도를 하나 빼주어서 전 년도의 주로 계산되도록 

맞춰줄 수 있다.

 

set @base_date = '2022-01-01';

select @base_date as base_dt
    ,  date_format(@base_date, '%Y-w%v') as base_weeknum
    , case when month(@base_date) = 1 and cast(date_format(@base_date, '%v') as decimal) > 50 then concat(cast(year(@base_date) - 1 as char) , date_format(@base_date, '-w%v')) 
        when month(@base_date) = 12 and cast(date_format(@base_date, '%v') as decimal) < 2 then concat(cast(year(@base_date) + 1 as char) , date_format(@base_date, '-w%v')) 
        else date_format(@base_date, '%Y-w%v') end as adjusted_weeknum;

 

위 쿼리에서 @base_date 를 연초나 연말의 여러 날짜로 바꿔주면서 실행해 보면,

그냥 base_weeknum 과 연도와 주번호가 잘 조정된 adjusted_weeknum 을 확인 할 수 있다. 

 

평소에는 위의 문제를 잘 인지하지 못하다가 연초 ~ 연말이 이어지는 짧은 기간의 데이터를 주단위로 집계하는 경우에 이게 뭐지?? 하게 된다. 

 

 

---

[추가]

mysql 에서도 date_format 함수의 format string 중 %x 를 사용하면 쉽게 처리할 수 있다.

-- 월요일을 주의 첫날짜로 하는 경우 
select date_format(date'2022-01-02', '%x-w%v') as week1 
	, date_format(date'2022-01-03', '%x-w%v') as week2
    
-- 일요일을 주의 첫날짜로 하는 경우
select date_format(date'2022-01-01', '%X-w%V') as week1
	, date_format(date'2022-01-02', '%X-w%V') as week2

 

 

 

 

[참고]

mysql 의 date_format 함수에 들어가는 format string 은 아래의 글에서 자세히 확인 할 수 있다.

https://walkingfox.tistory.com/145?category=756659 

MySQL 날짜/시간 함수

 

[참고]

presto 에서 주 단위 집계 하는 방법 

https://walkingfox.tistory.com/163

[presto, athena] 주단위 집계 꼭!! 주의해야할 점