postgresql group by week 하는 방법을 알아봅시다.

 postgresql에서 week 별로 통계치를 내라는 요구사항이 들어왔습니다. 물론 몇 번째 week인지도 표시되어야 하고요. week의 시작은 월요일, 끝나는 요일은 일요일이라 해 보겠습니다. 월이나 일 등은 date에서 substring으로 뽑아내도 되기 때문에 그리 어렵지 않습니다. 그런데, week는 yyyy-mm-dd 꼴에서 바로 알 수 있는 방법이 없습니다. 계산을 해서 알아내야 합니다.

 

 그런데, 그걸 위해서 함수를 만들어서 계산하기에는 매우 복잡할 듯 합니다. 이미, 제가 그러한 문제를 출제 했는데요. 하나는 일본 노래들과 프로듀스 48이 예제에 쫙 깔린 것이였습니다. 까다롭다고 평가받았습니다. c++, java, python도 그런 판에 sql은 어떨까요? 직접 함수를 구현한다면 까다롭지 않을까요?

 

---

 다행히도, 직접 날짜가 들어오면 week를 뽑아내는 방법이 있습니다. 문서에 따르면 to_char의 format에서 IW와 WW가 week와 관련된 format임을 알 수 있습니다.

 

 

 'YYYY IW'는 년도와 주를 나타내는데요. 첫 번째 Thursday가 나타나는 주가 1번째 주입니다. 예를 들어 2022년의 1번째 주는 2022년의 첫 번째 목요일이 속한 월 ~ 일요일을 의미합니다.

 

 

 2022년 10월의 데이터를 보겠습니다. 10월 23일이 일요일이고 10월 24일이 월요일입니다.

 

 

 이 42와 43은 일요일과 월요일의 경계상에 있습니다. 월요일이 되면, 이전 날보다 iw가 1이 증가합니다.

 

 

 이제 이 데이터를 봅시다. 2022년 1월 2일은 일요일이고 1월 3일은 월요일입니다. 그리고, 2022년에 목요일이 처음 나타나는 날짜는 2022년 1월 5일인데요. 1월 5일이 속한 주가 2022년 1월 3일 ~ 2022년 1월 9일입니다.

 

 

 이제 'YYYY IW'를 이용해서 몇 년도의 몇 번째 주인지 뽑아보겠습니다. 2022년 1월 2일은 2022년의 첫 번째 목요일이 속한 주보다는 앞에 있으므로, 50보다는 큰 값이 나옵니다. 반면, 2022년 1월 3일은 1월 5일에 속한 주이므로 1번째 주가 됩니다.

 

 

 위 결과를 보면 2022 52, 2022 01이 출력됨을 알 수 있습니다. 한 눈에 봐도 올바른 데이터가 아닌데요. 이는 start of year, end of year의 근처이기 때문입니다. 이 경우에 대해서 각별히 조심해야 합니다. YYYY와 IW같이 그냥 그레고리안 데이터와 ISO 데이터를 혼합해서 format에 쓰는 것은 절대 권장되지 않습니다.

 

 

 'WW' 라는 옵션도 있습니다. 이건 어떨까요? 'WW'의 경우, 1월 1일을 1번째 주의 1번째 날이라고 잡습니다. 2022년 1월 1일이 토요일이니까, 토요일을 1번째 날로, 금요일을 마지막 날로 잡게 됩니다.

 

 

 2022년 10월 28일은 금요일이고 10월 29일은 토요일입니다. 'WW'가 출력된 값을 보면, 2022년 10월 28일은 43, 2022년 10월 29일은 44로 출력이 됩니다. 요구 사항과 맞지 않음을 알 수 있어요.

 

 

---

 어떻게 해야 할까요? IW 형식으로 출력해야 한다는 건 맞아 보입니다. 연도에 대해서는 어떻게 해야 할까요? 마찬가지로 ISO 형식에 맞춰야 하는데요. 문서에 따르면 IYYY입니다.

 

 

 2022년 1월 2일이 일요일이였고 1월 3일이 월요일이였습니다. 'IYYY IW' 형식으로 주었을 때 어떻게 출력되나 봅시다.

 

 

 2021 52, 2022 01로 출력됩니다. 올바르게 출력됨을 알 수 있어요.

 

 

 또 다른 코너 케이스인 2020년 1월 1일 근처입니다. 이 경우, 2020년의 1번째 목요일이 1월 2일이므로, 2019년 12월 30일부터 2020년 1월 5일까지가 2020년의 1번째 주로 잡히게 됩니다. 정말 그런지 테스트를 해 보겠습니다.

 

 

 2019년 12월 29일, 2019년 12월 30일, 2020년 1월 5일, 2020년 1월 6일에 대해서 'IYYY IW' 형식으로 출력한 결과는 밑에 있습니다.

 

 

 2019년 12월 29일은 2019 52, 2019년 12월 30일과 2020년 1월 5일은 2020 01로, 1월 6일은 2020 02로 표기됨을 알 수 있습니다. 정리하면, week로 group by를 하기 위해서, to_char 함수를 쓰면 되는데요. 1번째 인자에 date를, 2번째 인자에는 format text를 받습니다. 이 format text에 'IYYY IW'를 넣으면, 몇 년도의 몇 번째 주인지가 나오게 됩니다.

 

 몇 번째 주인지를 판단하는 기준은, 해당 년도의 1번째 목요일이 속한 주가 1번째 주가 됩니다. "week"를 기준으로 date_trunc를 이용해도 주별로 group by를 할 수 있지만, 몇 번째 주인지는 나타나지 않습니다. 해당 날짜가 속한 월요일이 리턴되기 때문입니다.

 

 

 그러면 group by는 어떻게 하면 될까요? to_char(date, 'IYYY IW')를 기준으로 하면 됩니다. 위 쿼리는 주별로 몇 명이 가입했는지를 app_user에서 뽑아내는 예제입니다. date_joined는 가입 일자가 들어가 있어요.

 

 실행 결과는 위와 같습니다.