Finde Links in Themen

Daten & Berichterstattung
1

Diese Data Explorer SQL-Abfrage wurde entwickelt, um Links aus Beiträgen in öffentlichen Themen über einen bestimmten Zeitraum zu extrahieren und zu zählen. Sie ermöglicht auch die optionale Suche nach Links basierend auf einem angegebenen URL-Muster.

Diese Abfrage kann in Szenarien hilfreich sein, in denen das Verständnis der Nutzung interner oder externer Links in öffentlichen Diskussionen wichtig ist.

--[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' -- Private Nachrichten ausschließen
  AND (:search = false OR links[1] LIKE :link_pattern) -- Links nach Muster filtern
GROUP BY
  p.id,
  p.topic_id,
  p.post_number
ORDER BY
  p.created_at ASC

Erklärung der SQL-Abfrage

Die Abfrage führt die folgenden Operationen durch:

  • Join-Operationen: Sie verbindet die posts-Tabelle mit der topics-Tabelle, um sicherzustellen, dass nur Beiträge aus öffentlichen Themen berücksichtigt werden. Dies geschieht durch die Prüfung, ob die archetype des Themas nicht ‘private_message’ ist.
  • Mustererkennung: Sie verwendet einen regulären Ausdruck, um alle URLs aus dem cooked-Feld jedes Beitrags zu extrahieren. Dieses Feld enthält die HTML-gerenderte Version des Beitragsinhalts.
  • Filterung:
    • Datumsfilterung: Es werden nur Beiträge einbezogen, die innerhalb des vom Benutzer angegebenen Datumsbereichs (start_date bis end_date) erstellt wurden.
    • Linkfilterung: Optional, wenn der Parameter search auf true gesetzt ist, werden nur Links berücksichtigt, die dem link_pattern entsprechen.
  • Aggregation: Für jeden Beitrag zählt die Abfrage die eindeutigen Links und aggregiert sie in einem Array. Dies hilft, die Vielfalt der in einem einzelnen Beitrag geteilten Links zu verstehen.
  • Ausgabe: Die Abfrage gibt das Erstellungsdatum des Beitrags, die Beitrags-ID, die Themen-ID, die Anzahl der eindeutigen Links und ein Array dieser Links aus.

Parameter

  • start_date und end_date: Definieren den Datumsbereich für die zu analysierenden Beiträge.
  • link_pattern: Ein Muster zum Filtern der Links. Nur Links, die dieses Muster enthalten, werden berücksichtigt, wenn search auf true gesetzt ist. Im Parameter :link_pattern sind die %-Symbole Platzhalter in SQL, die null oder mehr Zeichen entsprechen.
  • search: Ein boolesches Flag, um die Filterung nach Linkmuster zu aktivieren.

Ergebnisse

  • created_at: Das Datum und die Uhrzeit, zu der der Beitrag erstellt wurde.
  • post_id: Die eindeutige Kennung des Beitrags.
  • topic_id: Die eindeutige Kennung des Themas, zu dem der Beitrag gehört.
  • link_count: Die Anzahl der im Beitrag gefundenen eindeutigen Links. Dies gibt an, wie viele verschiedene externe Links im Beitrag enthalten waren.
  • all_links: Ein Array aller im Beitrag gefundenen eindeutigen Links.

Beispielergebnisse

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 „Gefällt mir“