날짜 매개변수 추가

커뮤니티 구축 데이터 및 보고
1

특정 기간의 데이터를 추출하고 싶은 경우가 많습니다. 이를 위해 쿼리 자체에 기간을 하드코딩할 수도 있지만, 보고서를 더 유연하게 만들려면 날짜 파라미터를 추가할 수 있습니다:

이 쿼리는 특정 날짜 범위 내에서 생성된 게시물의 총 개수를 카테고리별로 집계하는 데 사용되며(삭제된 게시물과 토픽, 개인 메시지, 카테고리가 없는 토픽은 제외), 카테고리별 총 게시물 수를 기준으로 내림차순으로 정렬합니다.

-- [params]
-- date :start_date -- 범위의 시작 날짜를 위한 파라미터
-- date :end_date -- 범위의 종료 날짜를 위한 파라미터

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- 각 카테고리별 게시물 수를 계산
FROM posts p 
  JOIN topics t ON t.id = p.topic_id 
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- 시작 날짜와 종료 날짜 사이에 생성된 게시물을 필터링 (::date는 created_at 타임스탬프를 날짜로 변환)
  AND t.deleted_at IS NULL 
  AND p.deleted_at IS NULL 
  AND t.archetype <> 'private message' 
  AND p.user_id > 0 
  AND t.category_id IS NOT NULL 
GROUP BY 1 
ORDER BY 2 DESC

string 대신 date 스타일의 파라미터를 사용하면 파라미터 값을 입력하는 것이 훨씬 직관적이 되며, 특히 다른 사람과 공유할 때 유용합니다.

이러한 유형의 쿼리에 대한 또 다른 주목할 만한 팁은 created_at을 날짜로 캐스팅하는 것입니다. 해당 값은 데이터베이스에 타임스탬프 형태로 저장되어 있으므로, 날짜로 캐스팅하지 않으면 :end_date 당일의 결과를 쿼리가 가져오지 못할 수 있습니다.

Explorer 트리에서 어떤 값들이 타임스탬프로 저장되어 있는지 확인할 수 있습니다:

explorer tree - value type
explorer tree - value type278×281 3.18 KB

이 쿼리는 원칙을 시연하기 위한 예시입니다. 유용하게 활용하시길 바라며, 궁금한 점이 있다면 아래에 질문해 주세요. :slight_smile:

2 
PG::SyntaxError: ERROR:  syntax error at or near ":"
LINE 16: WHERE p.created_at::date BETWEEN :start_date AND :end_date -…

:tired_face:

3

아, 그렇군요. 매개변수 옆에 있는 인라인 주석을 싫어하네요.

-- [params]
-- date :start_date 
-- date :end_date 

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- 각 카테고리의 게시글 수를 계산
FROM posts p 
  JOIN topics t ON t.id = p.topic_id 
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- 시작 날짜와 끝 날짜 사이에 생성된 게시글만 필터링 ( ::date는 created_at 타임스탬프를 날짜로 변환)
  AND t.deleted_at IS NULL 
  AND p.deleted_at IS NULL 
  AND t.archetype <> 'private message' 
  AND p.user_id > 0 
  AND t.category_id IS NOT NULL 
GROUP BY 1 
ORDER BY 2 DESC

이 방식이 더 잘 작동하는 것 같습니다. :+1: (처음에는 매개변수 입력 상자를 불러오기 위해 페이지를 새로고침해야 할 수 있습니다)

가이드의 디버깅을 도와주셔서 감사합니다. :slight_smile: :trophy: