Busca enlaces en los temas

Datos e informes
1

Esta consulta SQL del Explorador de Datos está diseñada para extraer y contar enlaces de publicaciones en temas públicos dentro de un rango de fechas especificado. También permite la búsqueda opcional de enlaces basada en un patrón de URL especificado.

Esta consulta puede ser útil en escenarios donde es importante comprender el uso de enlaces internos o externos en discusiones públicas.

--[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' -- Excluir mensajes privados
  AND (:search = false OR links[1] LIKE :link_pattern) -- Filtrar enlaces por patrón
GROUP BY
  p.id,
  p.topic_id,
  p.post_number
ORDER BY
  p.created_at ASC

Explicación de la consulta SQL

La consulta realiza las siguientes operaciones:

  • Operaciones de unión: Une la tabla posts con la tabla topics para garantizar que solo se consideren las publicaciones pertenecientes a temas públicos. Esto se hace verificando que el archetype del tema no sea ‘private_message’.
  • Coincidencia de patrones: Utiliza una expresión regular para extraer todas las URL del campo cooked de cada publicación. Este campo contiene la versión renderizada en HTML del contenido de la publicación.
  • Filtrado:
    • Filtrado por fecha: Solo se incluyen las publicaciones creadas dentro del rango de fechas especificado por el usuario (start_date a end_date).
    • Filtrado de enlaces: Opcionalmente, si el parámetro search se establece en true, solo se consideran los enlaces que coinciden con el link_pattern.
  • Agregación: Para cada publicación, la consulta cuenta los enlaces distintos y los agrega en una matriz. Esto ayuda a comprender la variedad de enlaces compartidos en una sola publicación.
  • Salida: La consulta genera la fecha de creación de la publicación, el ID de la publicación, el ID del tema, el recuento de enlaces únicos y una matriz de estos enlaces.

Parámetros

  • start_date y end_date: Definen el rango de fechas para las publicaciones que se analizarán.
  • link_pattern: Un patrón para filtrar los enlaces. Solo se incluirán los enlaces que contengan este patrón si search se establece en true. En el parámetro :link_pattern, los símbolos % son comodines en SQL que coinciden con cero o más caracteres.
  • search: Un indicador booleano para activar el filtrado de patrones de enlaces.

Resultados

  • created_at: La fecha y hora en que se creó la publicación.
  • post_id: El identificador único de la publicación.
  • topic_id: El identificador único del tema al que pertenece la publicación.
  • link_count: El número de enlaces únicos encontrados en la publicación. Esto indica cuántos enlaces externos diferentes se incluyeron en la publicación.
  • all_links: Una matriz de todos los enlaces únicos encontrados en la publicación.

Resultados de ejemplo

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 Me gusta