토픽에서 링크 찾기

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

이 Data Explorer SQL 쿼리는 지정된 날짜 범위 내 공개 주제에 포함된 게시글에서 링크를 추출하고 개수를 세도록 설계되었습니다. 또한 지정된 URL 패턴에 기반하여 링크를 선택적으로 검색할 수도 있습니다.

이 쿼리는 공개 토론에서 내부 또는 외부 링크의 사용 방식을 이해하는 것이 중요한 시나리오에서 유용할 수 있습니다.

--[params]
--date :start_date = 2023-12-01
--date :end_date = 2024-01-01
--text :link_pattern = %example.com%
--boolean :search = false

SELECT
  p.created_at,
  p.id AS post_id,
  p.topic_id AS topic_id,
  COUNT(DISTINCT links) AS link_count,
  array_agg(DISTINCT links) AS all_links
FROM
  posts p
JOIN
  topics t ON p.topic_id = t.id
CROSS JOIN LATERAL
  regexp_matches(p.cooked, 'href="((?:http|https)://[^"]+)"', 'g') AS links
WHERE
  p.created_at >= :start_date 
  AND p.created_at <= :end_date 
  AND p.cooked ~ 'href="http[s]?://'
  AND t.archetype <> 'private_message' -- 개인 메시지 제외
  AND (:search = false OR links[1] LIKE :link_pattern) -- 패턴으로 링크 필터링
GROUP BY
  p.id,
  p.topic_id,
  p.post_number
ORDER BY 
  p.created_at ASC

SQL 쿼리 설명

이 쿼리는 다음 작업을 수행합니다:

  • 조인 연산: posts 테이블을 topics 테이블과 조인하여 공개 주제에 속하는 게시글만 고려하도록 합니다. 이는 주제의 archetype이 ‘private_message’가 아닌지 확인함으로써 수행됩니다.
  • 패턴 매칭: 정규 표현식을 사용하여 각 게시글의 cooked 필드에서 모든 URL을 추출합니다. 이 필드는 게시글 콘텐츠의 HTML 렌더링된 버전을 포함합니다.
  • 필터링:
    • 날짜 필터링: 사용자가 지정된 날짜 범위(start_date부터 end_date까지) 내에 생성된 게시글만 포함됩니다.
    • 링크 필터링: 선택적으로, search 매개변수가 true로 설정된 경우 link_pattern과 일치하는 링크만 고려됩니다.
  • 집계: 각 게시글에 대해 쿼리는 고유한 링크를 카운트하고 이를 배열로 집계합니다. 이는 단일 게시글에서 공유된 링크의 다양성을 이해하는 데 도움이 됩니다.
  • 출력: 쿼리는 게시글 생성 날짜, 게시글 ID, 주제 ID, 고유 링크 수, 그리고 해당 링크들의 배열을 출력합니다.

매개변수

  • start_date 및 end_date: 분석할 게시글의 날짜 범위를 정의합니다.
  • link_pattern: 링크를 필터링하기 위한 패턴입니다. search가 true로 설정된 경우 이 패턴을 포함하는 링크만 포함됩니다. :link_pattern 매개변수에서 % 기호는 SQL에서 0개 이상의 문자와 일치하는 와일드카드입니다.
  • search: 링크 패턴 필터링을 활성화하기 위한 불리언 플래그입니다.

결과

  • created_at: 게시글이 생성된 날짜와 시간.
  • post_id: 게시글의 고유 식별자.
  • topic_id: 게시글이 속한 주제의 고유 식별자.
  • link_count: 게시글에서 발견된 고유 링크의 수. 이는 게시글에 포함된 다른 외부 링크의 개수를 나타냅니다.
  • all_links: 게시글에서 발견된 모든 고유 링크의 배열.

결과 예시

created_at post_id topic_id link_count all_links
2023-12-05 14:30:00 10234 543 2 {“http://example.com/page1”, “http://example.com/page2”}
2023-12-12 09:15:00 10567 550 1 {“http://example.com/page3”}
2023-12-20 16:45:00 10894 560 3 {“http://example.com/page1”, “http://example.com/info”, “http://example.com/contact”}
… … … … …
2개의 좋아요