postgresql with 여러개 선언해서 사용하는 방법을 알아봅시다.

 postgresql에서 with 절은 임시 결과를 정의할 때 유용하였습니다. 이걸 여러 개를 쓸 수 있을까요? 2023년 6월 1일부터 오늘까지 통계치를 뽑고 싶을 때 유용하게 쓰일 법한 예제를 보면서 간단하게 알아보겠습니다. 

 

---

 테이블 d에는 id, s, created 필드가 있습니다. s와 created는 각각 내용과 생성된 날을 의미합니다. 어떤 레코드들이 있는지 보기 위해, select * from d; 를 입력해 보았습니다.

 

 그랬더니 요래 나오는군요. created의 맨 마지막 부분에 +0900이 되어 있는 것으로 보아, 서울 시간으로 되어 있는 것이 분명하군요. 이제 저는 날짜별로 몇 개의 레코드가 생성되었는지 집계하고 싶습니다. 어떻게 하면 될까요? 날짜별로 집계 하려면 group by 날짜를 하면 되겠습니다. date 함수가 이를 해결해 줍니다. 다음에, 갯수를 출력하는 것은 그냥 간단하게 count 함수로 해 주면 됩니다.

 

 중요한 것은 어느 시간대로 할 것이냐인데요. 서울 시간대로 하겠습니다.

 

 group by date(created at time zone 'Asia/Seoul')을 입력함으로써, 서울 시간 기준 날짜별로 몇 개씩이나 생성되었는지 집계합니다.

 

 결과는 위와 같습니다. 이를 date_static라는 임시 결과로 내보낼 거에요.

 

 이제, 2023년 6월 1일부터 오늘까지의 날짜를 뽑아 봅시다. postgres에는 generate_series 함수가 있어요. 위 쿼리로 2023년 6월 1일부터, 오늘 날짜인 6월 12일까지 뽑게 됩니다. temp_dd:date는 date로 형변환을 합니다.

 

 실행 결과는 위와 같습니다. 이를 date_t라는 이름의 임시 결과로 내보낼 거에요.

 

---

 딱 봐도 두 쿼리. 간단해 보이진 않습니다. with 절을 여러 개 선언하는 방법은 with name1 as (...), name2 as (...) 요래 선언하면 되는데요. , (콤마)로 구분하시면 됩니다. 13번째 줄까지 보겠습니다.

 

 보시면 with date_static as q, date_t as q' 꼴로 되어 있어요. 이는 쿼리 q의 실행 결과는 date_static이라는 임시 결과로, 또 다른 쿼리 q'의 실행 결과는 date_t라는 임시 결과로 내보내겠다는 의미입니다. date_static은 dd와 cc가 있습니다. date_static의 dd는 날짜, 그리고 cc는 날짜별로 생성된 레코드 수를 의미해요. 당연하게도 이 임시로 선언된 date_static과 date_t는 ;가 끝나기 전까지입니다. 만약에 13번째 줄에서 ;를 선언해서 끝내버렸다고 합시다. 그 경우 그 다음에 이어지는 left outer join문에서 date_t와 date_static은 사용하지 못합니다.

 

 다음 date_t의 dd는 2023년 6월 1일부터 오늘 날짜인 2023년 6월 12일을 의미합니다. 우리는 2023년 6월 1일부터 오늘 날짜까지 통계 데이터를 뽑아야 하는데, date_static에는 2023년 6월 1일 이후의 날짜가 없을 수도 있단 말이지요. 따라서, date_t와 date_static을 outer join 합니다. 

 

 그러면 요래 나오는데요. 2023년 6월 1일, 2023년 6월 2일 등은 우측 테이블인 date_static에 없었기 때문입니다. cc가 NULL일 때 0으로 카운트 되게 하기 위해서는 postgres에서는 colaesce를 사용하면 됩니다.

 

 14번째 줄에 select date_t.dd, coalesce(date_static.cc, 0)으로 바꿔 봅시다.

 

 그러면 위와 같이 변경됨을 확인할 수 있습니다.