postgresql coalesce 함수는 널 값을 다른 값으로 대체하는 역할을 하는 함수입니다. 잠깐. 널 값이 무엇인가? 이 글을 보시면 됩니다. 추가 예제는 다소 복잡합니다. 그러니, left outer join과 with절에 대한 이해가 있어야 합니다. 모르신다면, 그냥 coalesce를 어떤 용도로 쓰는지 짚고 넘어가셔도 됩니다.
- sql null 값과 널 체크 하는 방법을 알아봅시다. 링크
먼저 1번째 인자로는 target 값을 받습니다. 2번째 인자로는 만약 target이 null일 때, 어떤 값으로 바꿀 것인지를 받아요. 쿼리를 보겠습니다.
먼저 coalesce 함수의 1번째 인자에 null을, 2번째 인자에 2를 넣었어요. target이 null입니다. 이럴 때, 2번째 인자로 넘어간 2를 돌려줍니다. 실행 결과를 볼까요?
2가 나옵니다. 왜? 1번째 인자로 넘어간 값이 null 값 이였기 때문입니다. 이제 다음 쿼리를 보겠습니다.
coalesce 함수의 1번째 인자로 3, 2번째 인자로 2가 넘어왔습니다. 이 경우에는 어떻게 될까요? 3은 null이 아닙니다. 따라서, 2가 나오지 않고 그대로 3이 나올 것입니다.
실행 결과는 3이 나옵니다.
정리하면, coalesce(param1, param2) 형태로 들어왔다고 하겠습니다. param1 이 null이면 param2 를 돌려주게 됩니다. 그렇지 않으면 param1 을 돌려주게 됩니다. null 값일 때, 0을 돌려주게 하려면 어떻게 하면 될까요? param2를 0으로 하면 됩니다.
고로, coalesce(null, 0)을 하면, 널 값이기 때문에 0이 나오게 됩니다. 이 함수는 outer join을 할 때에는 심심찮게 써먹기도 합니다. outer join을 할 때 널 값이 많이 나오기 때문입니다.
예제를 하나 보겠습니다. 예제는 2023년 6월 28일부터 2023년 7월 2일까지 등록된 책의 개수를 뽑아내는 것입니다. 등록되지 않은 날에는 0을 뽑아와야 합니다. 등록된 책에 대한 정보는 테이블 a에 있어요.
a 테이블에 있는 내용들을 모두 조회해 보면 위와 같습니다. reg_at이 timezone이 있는 timestamp 형식임에 주의하세요.
이제 각 날짜별로 몇 권의 책이 등록되었는지 카운트 해 보겠습니다. date 함수는 날짜를 뽑아오는 것입니다. 따라서, group by(reg_at)을 해 주면 됩니다. 그리고 집계 함수는 문서의 수를 세는 count(*)로 처리하면 됩니다.
다음에 2023년 6월 28일부터 2023년 7월 2일까지 날짜를 뽑아옵니다. postgres에서는 generate_series를 제공해 줍니다. 이를 이용하면 쉽게 할 수 있어요. 이제, 이 두 테이블을 join 해야 되는데요. 6월 28일부터 7월 2일까지 날짜를 뽑은 것을 왼쪽에 둡니다. count로 집계한 것을 오른쪽에 두고 left outer join을 사용하면 됩니다.
with절로 임시 테이블을 정의한 뒤에 dat_table과 stat_data를 left outer join을 해 주었습니다. cc가 널 값인 게 드문드문 보입니다. 이것은 6월 29일과 7월 1일에 등록된 책이 없기 때문입니다.
coalesce(stat_data.cc, 0)을 봅시다. 아까와 다른 점은 2023년 6월 29일의 cc값이 null값 대신 0이 나왔다는 것입니다. 이는, 널 값이 들어올 때 0을 돌려주게 했기 때문입니다.