# 在主题中查找链接

**URL:** https://meta.discourse.org/t/find-links-in-topics/307181
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2024年五月7日 23:13 UTC](https://meta.discourse.org/t/find-links-in-topics/307181 "2024-05-07T23:13:36Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [2024年五月7日 23:13 UTC](https://meta.discourse.org/t/find-links-in-topics/307181/1 "2024-05-07T23:13:37Z")

</div>

此 [Data Explorer](https://meta.discourse.org/t/32566?silent=true) SQL 查询旨在提取和计算指定日期范围内公共主题帖子中的链接数量。它还允许根据指定的 URL 模式可选地搜索链接。

当了解公共讨论中内部或外部链接的使用情况很重要时，此查询会很有帮助。

```sql
--[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/page1%E2%80%9D), “[http://example.com/page2”](http://example.com/page2%E2%80%9D)} |
| 2023-12-12 09:15:00 | 10567 | 550 | 1 | {“[http://example.com/page3”](http://example.com/page3%E2%80%9D)} |
| 2023-12-20 16:45:00 | 10894 | 560 | 3 | {“[http://example.com/page1”](http://example.com/page1%E2%80%9D), “[http://example.com/info”](http://example.com/info%E2%80%9D), “[http://example.com/contact”](http://example.com/contact%E2%80%9D)} |
| … | … | … | … | … |