トピックでリンクを見つける

データとレポート
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のワイルドカードであり、ゼロ個以上の文字に一致します。
  • 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