특정 단어가 몇 번 언급되었는지 세기

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

포럼에서 특정 단어 사용 추이를 확인하는 방법에 대해 궁금합니다. 주별 특정 부분 문자열 언급 횟수를 확인할 수 있는 쿼리가 있을까요?

2

ChatGPT의 도움으로 작동하는 코드를 찾았습니다

WITH date_series AS (
  SELECT generate_series(
           DATE_TRUNC('week', MIN(created_at)),  -- 첫 번째 주의 시작
           DATE_TRUNC('week', MAX(created_at)),  -- 마지막 주의 시작
           '1 week'::interval                    -- 주 단위 간격
         ) AS week_start
  FROM posts
),
posts_with_substring AS (
  SELECT
    DATE_TRUNC('week', created_at) AS week_start,
    COUNT(*) AS total_posts,
    SUM((LENGTH(raw) - LENGTH(REPLACE(lower(raw), lower('your_substring'), ''))) / LENGTH('your_substring')) AS substring_count
  FROM
    posts
  WHERE
    raw ILIKE '%test%'
  GROUP BY
    DATE_TRUNC('week', created_at)
)
SELECT
  ds.week_start,
  COALESCE(pws.total_posts, 0) AS total_posts
FROM
  date_series ds
LEFT JOIN
  posts_with_substring pws ON ds.week_start = pws.week_start
ORDER BY
  ds.week_start