Encontrar links em tópicos

Dados e relatórios
1

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.

--[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/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 curtidas