통계 API를 개발하던중 난관에 부딪히게 되었다. 여러가지 파라미터 조건들 중에 조회 시작일과 조회 종료일이 있는데 조회시작일: 2023-10-15, 조회 종료일: 2023-10-22 인 경우 게시글 작성일이 2023-10-15 ~ 2023-10-22 사이이며 파라미터로 받아온 해시태그를 조회해 알맞은 게시글들의 개수를 뽑아온다. 내가 작성한 코드는
SELECT DATE_TRUNC('DAY', POSTS.CREATED_AT) :: DATE AS DAY
, COUNT(POSTS.ID) AS COUNT_POST
FROM POSTS
INNER JOIN POST_TAG ON POSTS.ID = POST_TAG.POST_ID
INNER JOIN TAGS ON POST_TAG.TAG_ID = TAGS.ID
WHERE TAGS.TAG = '성수맛집' AND POSTS.CREATED_AT >= '2023-10-15' AND POSTS.CREATED_AT <= '2023-10-22'
GROUP BY DATE_TRUNC('DAY', POSTS.CREATED_AT) :: DATE게시글 테이블인 posts, 해시태그 테이블인 tags, 둘의 매핑 테이블인 post_tag 테이블이 있고 3개의 테이블을 조인하여 작성일을 yyyy-MM-dd 형식으로 변환 후 같은 형식으로 GROUP BY한 후 해당 post의 id를 COUNT한다. 실행하면
이렇게 결과값을 가져오고 typeorm querybuilder로 작성한 코드는
const queryBuilder = this.createQueryBuilder('post').innerJoin('post.tags','tag')
.select("TO_CHAR(post.created_at, 'YYYY-MM-DD') AS date")
.addSelect('COUNT(*)', 'count');
.where('tag.tag = :hashtag', { '성수맛집' })
.andWhere('post.createdAt >= :start', { '2023-10-15' })
.andWhere('post.createdAt <= :end', { '2023-10-22' })
.groupBy(`date`)
const posts = await queryBuilder.getRawMany();
마찬가지로 결과값은
[
{date:'2023-10-19', count:2},
{date:'2023-10-20', count:1},
{date:'2023-10-21', count:2},
]이렇게 받아온다. 여기서 문제가 생긴게 15일부터 22일까지 조회하면
[
{date:'2023-10-15', count:0},
{date:'2023-10-16', count:0},
{date:'2023-10-17', count:0},
{date:'2023-10-18', count:0},
{date:'2023-10-19', count:2},
{date:'2023-10-20', count:1},
{date:'2023-10-21', count:2},
{date:'2023-10-22', count:0},
]이렇게 count가 없는 날도 0으로 받아와야하는 상황이다.
또 고민을 하다가 db 쿼리 단에서 해결해보자 해서 다시 짠 sql 코드이다.
WITH DATE_SERIES(SERIAL_DAY) AS (
SELECT GENERATE_SERIES(
'2023-10-15'::DATE,
'2023-10-22'::DATE,
'1 DAY'::INTERVAL)::DATE
)
SELECT A.SERIAL_DAY, CASE WHEN B.COUNT_POST IS NULL THEN 0 ELSE B.COUNT_POST END
FROM DATE_SERIES A
LEFT JOIN (
SELECT DATE_TRUNC('DAY', POSTS.CREATED_AT) :: DATE AS DAY
, COUNT(POSTS.ID) AS COUNT_POST
FROM POSTS
INNER JOIN POST_TAG ON POSTS.ID = POST_TAG.POST_ID
INNER JOIN TAGS ON POST_TAG.TAG_ID = TAGS.ID
WHERE TAGS.TAG = '성수맛집' AND POSTS.CREATED_AT >= '2023-10-15' AND POSTS.CREATED_AT <= '2023-10-22'
GROUP BY DATE_TRUNC('DAY', POSTS.CREATED_AT) :: DATE
) B ON A.SERIAL_DAY = B.DAY이렇게 짜면 DATE_SERIES라는 Date컬럼이 있고 2023-10-15부터 22일까지 raw들이 주르륵 쌓인 임시테이블이 생기게 된다.
그럼 이 임시테이블과 처음 내가 만든 쿼리문을 서브쿼리로 작성하여 left outer join 하게 되면 임시테이블에 있는 날짜들이 있는 날짜를 서브쿼리에서 찾게 되고 서브쿼리에 값이 없는 날짜들은 원래 null이 들어가지만 CASE WHEN B.COUNT_POST IS NULL THEN 0 ELSE B.COUNT_POST END 라는 select문의 case를 통해 Null 이면 0이 들어가게 된다.
이제 쿼리도 작성했으니 이컬 typeormDml querybuilder로 작성해보자!
... 했는데 또 문제가 생겼다.
querybuilder는
WITH DATE_SERIES(SERIAL_DAY) AS (
SELECT GENERATE_SERIES(
'2023-10-15'::DATE,
'2023-10-22'::DATE,
'1 DAY'::INTERVAL)::DATE
)이런 Temporary 임시 테이블을 만드는 기능이 없다고 한다...
또 요청마다 임시테이블을 만드는건 뭔가 비효율적인것 같기도 하고...
또한 같은 시간에 with절(임시 테이블 사용할때 쓰는)을 동시에 돌리면 임시테이블이 견딜수 있는 정도가 넘어가서 느려진다는 글을 봤다.
결국
[
{date:'2023-10-19', count:2},
{date:'2023-10-20', count:1},
{date:'2023-10-21', count:2},
]이런식으로 받아오고 코드딴으로 가공해주기로 한다.
const result = (아까 db 결과값)
const allDays = [];
const currentDate = new Date(start); // Date 타입 깊은 복사
const endDate = new Date(end);
while (currentDate <= endDate) {
// 배열에 yyyy-MM-dd 형식으로 넣어주기
allDays.push(currentDate.toISOString().split('T')[0]);
// 하루씩 더해주기
currentDate.setDate(currentDate.getDate() + 1)
}
// 위에서 만든 모든 일자|매시간 이들어있는 배열(pushDate)을 루프하면서 db에서 가져온 결과의 count가 있는 날짜 찾아 넣어주고 없으면 0 넣어 새로운 배열 반환
const resultWithZeroCount = allDays.map((date) => {
const existingResult = result.find((row) => row.date === date);
return {
date,
count: existingResult ? Number(existingResult.count) : 0,
};
});
return resultWithZeroCount;간단히 해석하면 시작 날짜와 종료일자를 받아와서 반복문으로 시작일자를 하나씩 카운트하면서 yyyy-MM-dd 형식으로 allDays란 배열에 넣어주면서 하루씩 더해준다.
그럼 배열에 2023-10-15부터 2023-10-22까지 모든 날짜가 들어가고 alldays를 map으로 반복문을 돌리면서 해당 날짜를 db에서 받아온 결과값인 result에서 조회해 count값을 가져온다. result에서 찾지 못할 경우 0을 넣어주고 결과를 {date:날짜, count: 개수}의 배열로 반환한다.
