Trouver des liens dans les sujets

Données et rapports
1

Cette requête SQL Data Explorer est conçue pour extraire et compter les liens des publications dans des sujets publics sur une période spécifiée. Elle permet également de rechercher des liens facultatifs basés sur un modèle d’URL spécifié.

Cette requête peut être utile dans les scénarios où la compréhension de l’utilisation des liens internes ou externes dans les discussions publiques est 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' -- Exclure les messages privés
  AND (:search = false OR links[1] LIKE :link_pattern) -- Filtrer les liens par modèle
GROUP BY
  p.id,
  p.topic_id,
  p.post_number
ORDER BY 
  p.created_at ASC

Explication de la requête SQL

La requête effectue les opérations suivantes :

  • Opérations de jointure : Elle joint la table posts avec la table topics pour s’assurer que seuls les posts appartenant à des sujets publics sont pris en compte. Ceci est fait en vérifiant que l’archetype du sujet n’est pas ‘private_message’.
  • Correspondance de modèle : Elle utilise une expression régulière pour extraire toutes les URL du champ cooked de chaque post. Ce champ contient la version rendue en HTML du contenu du post.
  • Filtrage :
    • Filtrage par date : Seuls les posts créés dans la plage de dates spécifiée par l’utilisateur (start_date à end_date) sont inclus.
    • Filtrage des liens : Facultativement, si le paramètre search est défini sur true, seuls les liens qui correspondent au link_pattern sont pris en compte.
  • Agrégation : Pour chaque post, la requête compte les liens distincts et les agrège dans un tableau. Cela permet de comprendre la variété des liens partagés dans un seul post.
  • Sortie : La requête affiche la date de création du post, l’ID du post, l’ID du sujet, le nombre de liens uniques et un tableau de ces liens.

Paramètres

  • start_date et end_date : Définissent la plage de dates pour les posts à analyser.
  • link_pattern : Un modèle pour filtrer les liens. Seuls les liens contenant ce modèle seront inclus si search est défini sur true. Dans le paramètre :link_pattern, les symboles % sont des caractères génériques en SQL qui correspondent à zéro ou plusieurs caractères.
  • search : Un indicateur booléen pour activer le filtrage par modèle de lien.

Résultats

  • created_at : La date et l’heure de création du post.
  • post_id : L’identifiant unique du post.
  • topic_id : L’identifiant unique du sujet auquel le post appartient.
  • link_count : Le nombre de liens uniques trouvés dans le post. Cela indique le nombre de liens externes différents inclus dans le post.
  • all_links : Un tableau de tous les liens uniques trouvés dans le post.

Exemple de résultats

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 « J'aime »