[Postgres] 월별/주별/일별 통계 (2)

저번과 이어지는 통계..

- 없는 날짜 JOIN하기

이번에 통계를 내는데 중간 중간에 COUNT가 되지 않는 날짜가 있었다. 예를 들면 주말에 아무도 접속을 안하면 해당

날짜는 테이블에 안들어 가기 때문에 날짜가 붕 떠버리게 되는 것이다. 예를 들면 아래와 같다.

 

중간에 2022-01-01 날짜가 없다.

조건에 해당하는 2022-01-01에 맞는 데이터가 없으니 아예 select문에 잡히지도 않는다.
문제는 COUNT가 없는 날짜도 통계로 보여달라는 요청이었다.
그렇다면 아래와 같이 select를 해야된다.
sumDt : 2022-01-01
sumCt : 0

이 문제를 해결하려면 2가지 방법이 있다.

1. 날짜 참조 테이블을 만들어 join하기
2. 임시 날짜 참조 테이블을 만들어 join하기

쉽게 풀면 그냥 필요한 만큼(예: 3년치 날짜 테이블)의 날짜 테이블을 만들어
join이나 union등을 사용하여 병합하는 것이다.
그럼 있는 날짜는 그대로 나오고 없는 날짜는 참조 테이블에서 가져오는 것이다. * 핵심

이번에는 날짜 참조 테이블을 안만들고 임시 날짜 참조 테이블을 만들어서 해보겠다.

이렇게 하면 참조할 수 있는 임시테이블이 만들어 졌다. 그럼 이제 이 임시테이블과 LEFT OUTER JOIN을 통해 

없는 날짜는 0으로 나올 수 있도록 해보겠다.

 

LEFT OUTER JOIN 의 기본 문법은 다음과 같다.

위 이미지와 같이 2개의 테이블을 두고 LEFT Table을 default로 잡고 RIGHT Table을

조건에 부합하지 않는 행도 포함 시킨다.. 

즉 이미지의 userTable은 무조건 포함하고, gameTable도 가지고 있는 것은 전부 포함하기 때문에 

gameTable에 없는 데이터도 userTable에서 가지고 있다면 다 가져오는 것이다. 이것을 활용하면

우리가 없는 날짜도 "날짜 참조 테이블"을 이용하면 전부 가져 올 수 있다는 이야기이다. 그렇게 해서 만든 쿼리는

다음과 같다.

 

완성된 쿼리

SELECT 해 온 결과를 보면 기존에 없던 2021-01-01도 date를 가져 오게 되었다. 해당 쿼리는 일별 이지만 

generate_series()의 파라메터 값을 1 month, 1 week 으로 변경하면 월별/주별 통계도 뽑아 낼 수 있다!