Trova link negli argomenti

Dati e reportistica
1

Questa query SQL di Data Explorer è progettata per estrarre e contare i link dai post all’interno di argomenti pubblici in un intervallo di date specificato. Consente inoltre la ricerca opzionale di link in base a un modello di URL specificato.

Questa query può essere utile in scenari in cui è importante comprendere l’utilizzo di link interni o esterni nelle discussioni pubbliche.

--[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' -- Escludi messaggi privati
  AND (:search = false OR links[1] LIKE :link_pattern) -- Filtra i link per modello
GROUP BY
  p.id,
  p.topic_id,
  p.post_number
ORDER BY
  p.created_at ASC

Spiegazione della query SQL

La query esegue le seguenti operazioni:

  • Operazioni di Join: Unisce la tabella posts con la tabella topics per garantire che vengano considerati solo i post appartenenti ad argomenti pubblici. Ciò viene fatto verificando che l’archetype dell’argomento non sia ‘private_message’.
  • Corrispondenza di Modelli: Utilizza un’espressione regolare per estrarre tutti gli URL dal campo cooked di ciascun post. Questo campo contiene la versione renderizzata in HTML del contenuto del post.
  • Filtraggio:
    • Filtraggio per Data: Vengono inclusi solo i post creati nell’intervallo di date specificato dall’utente (start_date a end_date).
    • Filtraggio Link: Opzionalmente, se il parametro search è impostato su true, vengono considerati solo i link che corrispondono al link_pattern.
  • Aggregazione: Per ogni post, la query conta i link distinti e li aggrega in un array. Questo aiuta a comprendere la varietà di link condivisi in un singolo post.
  • Output: La query restituisce la data di creazione del post, l’ID del post, l’ID dell’argomento, il conteggio dei link univoci e un array di questi link.

Parametri

  • start_date e end_date: Definiscono l’intervallo di date per i post da analizzare.
  • link_pattern: Un modello per filtrare i link. Solo i link contenenti questo modello verranno inclusi se search è impostato su true. Nel parametro :link_pattern, i simboli % sono caratteri jolly in SQL che corrispondono a zero o più caratteri.
  • search: Un flag booleano per attivare il filtraggio del modello di link.

Risultati

  • created_at: La data e l’ora in cui è stato creato il post.
  • post_id: L’identificatore univoco del post.
  • topic_id: L’identificatore univoco dell’argomento a cui appartiene il post.
  • link_count: Il numero di link univoci trovati nel post. Questo indica quanti diversi link esterni sono stati inclusi nel post.
  • all_links: Un array di tutti i link univoci trovati nel post.

Risultati di Esempio

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 Mi Piace