在主题中查找链接

数据与报告
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' -- Exclude private messages
  AND (:search = false OR links[1] LIKE :link_pattern) -- Filter links by 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 个赞