postgresql colaesce 함수에 대해 알아봅시다.

 postgresql에는 coalesce 함수가 있습니다. 어떤 함수인지 간단하게 알아보고 간단한 문제로 실습해 보겠습니다.

 

---

 먼저 헷갈릴 법한 것 부터 정리합시다. nullif는 expr1과 expr2가 같으면 null을, 아니면 expr1을 리턴합니다. 위 예제에서는 1과 2가 다릅니다. 따라서, 결과값은 1이 나올 겁니다.

 

 

 정말 1이 나옵니다. 그러면, colaesce는 무슨 함수인가? expr1, expr2, ... , expr(n)이 있을 때, 최초로 null이 아닌 값을 리턴합니다. 이게 무슨 소리인가? 예제를 보겠습니다.

 

 colaesce에 null, 1, 2가 들어와 있습니다. 최초로 null이 아닌 값은 1입니다.

 

 

 따라서 1이 리턴됩니다. 만약에 그런 것이 없다면 어떤 값이 나올까요? 그런 경우에는 null이 떨어집니다. 이런 걸 대체 어디에 썼는가. 어떤 특정 값이 null 값일 때 다른 값으로 치환하기 위해 쓴 경우가 많았습니다. 예를 들자면, 특정한 날짜에 데이터가 없는 경우가 있겠습니다. 문제 상황을 보도록 하겠습니다.

 

---

 app_user 테이블에 있는 데이터를 보겠습니다. 그러면, date_joined라는 필드가 있는데요. 문제는 아래와 같습니다. 2022년 10월 x일에 가입한 유저 수를 날짜별로 뽑고 싶습니다. 당연하게도 가입자가 없으면 0으로 뽑아야 합니다. step by step으로 접근해 보겠습니다. 먼저 2022년 10월 x일을 series로 뽑는 함수가 postgresql에 있습니다.

 

 generate_series라는 함수를 이용하면 되는데요. 2022-10-01부터, 2022-10-31까지 뽑으면 됩니다. step은 '1 days'로 뽑으면 되고요. date 함수를 이용해서, YYYY년 MM월 DD일 꼴로 출력되게 해 보겠습니다.

 

 

 그러면, 2022년 10월 1일부터, 2022년 10월 31일까지가 출력되었다는 것을 알 수 있어요. 이것을 테이블 1이라 하겠습니다.

 

 

 다음에 app_user에 대해서, 가입한 날짜들을 모두 뽑아봅시다. 위와 같이 입력하면 되겠네요.

 

 

 이를 테이블 2라 합시다. 테이블 2에서 date별로 group by하면 "2022-10-17"이 3개 있다. 라는 정보가 나오게 됩니다. 우리는 테이블 1에 있는 내용은 보존해야 하기 때문에, 테이블 1과 테이블 2를 left outer join을 하면 됩니다.

 

 

 date_ten은, 2022년 10월 날짜를 모두 뽑습니다. 그리고, static_data는 유저가 가입한 날짜에 대한 통계 데이터를 가지고 있어요. 예를 들어 "2022-10-17"에 3명이 가입했다는 정보가 들어 있습니다. date_ten과 static_data를 left outer join 하면 된다는 것을 알 수 있어요.

 

 

 요래 쓰면 결과가 어떻게 나오는지 보겠습니다.

 

 

 10월 17일에 대해서 cc는 3이 나옵니다. 해당 날짜가 아닌 경우, cc는 null이 떨어지게 되는데요. 우리는, cc가 null인 경우, 0으로 치환하고 싶습니다. coalesce(cc, 0)은 cc가 null이 아니라면 cc가 나오고, 그렇지 않으면 0이 null값이 아닌 최초 위치이므로 0이 나오게 됩니다. 따라서, 아래와 같이 쓰면, 0명이 가입한 날짜에도 cc가 0으로 출력이 될 겁니다.

 

 

 with절 밑에 있는 쿼리문을 요래 바꿔 보겠습니다. colaesce(cc, 0)이 중요합니다. 결과를 볼까요?

 

 

 2022년 10월 17일에 3명이 가입했다는 정보가 출력됩니다. 2022년 10월 19일에는 0명이 가입했으므로, 0명이 가입했다는 정보가 출력되게 됩니다.