# Encontrar links em tópicos

**URL:** <https://meta.discourse.org/t/find-links-in-topics/307181>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [Maio 7, 2024, 11:13pm UTC](https://meta.discourse.org/t/find-links-in-topics/307181 "2024-05-07T23:13:36Z")\
**Posts on this page:** 1\
**Showing post:** 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:** [Maio 7, 2024, 11:13pm UTC](https://meta.discourse.org/t/find-links-in-topics/307181/1 "2024-05-07T23:13:37Z")

</div>

Esta consulta SQL do Explorador de Dados foi projetada para extrair e contar links de postagens em tópicos públicos dentro de um intervalo de datas especificado. Ela também permite a pesquisa opcional de links com base em um padrão de URL especificado.

Esta consulta pode ser útil em cenários onde a compreensão do uso de links internos ou externos em discussões públicas é importante.

```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' -- Exclui mensagens privadas
  AND (:search = false OR links[1] LIKE :link_pattern) -- Filtra links por padrão
GROUP BY
  p.id,
  p.topic_id,
  p.post_number
ORDER BY
  p.created_at ASC

```

### Explicação da Consulta SQL

A consulta realiza as seguintes operações:

- **Operações de Junção** : Ela une a tabela `posts` com a tabela `topics` para garantir que apenas postagens pertencentes a tópicos públicos sejam consideradas. Isso é feito verificando que o `archetype` do tópico não seja ‘private\_message’.
- **Correspondência de Padrões** : Utiliza uma expressão regular para extrair todos os URLs do campo `cooked` de cada postagem. Este campo contém a versão renderizada em HTML do conteúdo da postagem.
- **Filtragem** :
  - **Filtragem por Data** : Apenas postagens criadas dentro do intervalo de datas especificado pelo usuário (`start_date` a `end_date`) são incluídas.
  - **Filtragem de Links** : Opcionalmente, se o parâmetro `search` estiver definido como true, apenas os links que correspondem ao `link_pattern` são considerados.

- **Agregação** : Para cada postagem, a consulta conta os links distintos e os agrega em um array. Isso ajuda a entender a variedade de links compartilhados em uma única postagem.
- **Saída** : A consulta exibe a data de criação da postagem, o ID da postagem, o ID do tópico, a contagem de links exclusivos e um array desses links.

### Parâmetros

- `start_date` e `end_date`: Definem o intervalo de datas para as postagens a serem analisadas.
- `link_pattern`: Um padrão para filtrar os links. Apenas links que contêm este padrão serão incluídos se `search` for definido como true. No parâmetro `:link_pattern`, os símbolos `%` são curingas em SQL que correspondem a zero ou mais caracteres.
- `search`: Um sinalizador booleano para ativar a filtragem do padrão de link.

### Resultados

- **`created_at`** : A data e hora em que a postagem foi criada.
- **`post_id`** : O identificador exclusivo da postagem.
- **`topic_id`** : O identificador exclusivo do tópico ao qual a postagem pertence.
- **`link_count`** : O número de links exclusivos encontrados na postagem. Isso indica quantos links externos diferentes foram incluídos na postagem.
- **`all_links`** : Um array de todos os links exclusivos encontrados na postagem.

### Exemplo de Resultados

| 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)} |
| … | … | … | … | … |

---

_[View the full topic](https://meta.discourse.org/t/find-links-in-topics/307181)._