اعثر على الروابط في المواضيع

البيانات والتقارير
1

تم تصميم استعلام SQL هذا لـ Data Explorer لاستخراج وحساب الروابط من المشاركات في المواضيع العامة ضمن نطاق تاريخ محدد. كما يسمح بالبحث الاختياري عن الروابط بناءً على نمط عنوان URL محدد.

يمكن أن يكون هذا الاستعلام مفيدًا في السيناريوهات التي يكون فيها فهم استخدام الروابط الداخلية أو الخارجية في المناقشات العامة أمرًا مهمًا.

--[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' -- استبعاد الرسائل الخاصة
  AND (:search = false OR links[1] LIKE :link_pattern) -- تصفية الروابط حسب النمط
GROUP BY
  p.id,
  p.topic_id,
  p.post_number
ORDER BY 
  p.created_at ASC

شرح استعلام SQL

يقوم الاستعلام بالعمليات التالية:

  • عمليات الربط: يربط جدول posts بجدول topics لضمان النظر فقط في المشاركات التي تنتمي إلى مواضيع عامة. يتم ذلك عن طريق التحقق من أن archetype للموضوع ليس ‘private_message’.
  • مطابقة الأنماط: يستخدم تعبيرًا عاديًا لاستخراج جميع عناوين URL من حقل cooked لكل مشاركة. يحتوي هذا الحقل على النسخة المعروضة بصيغة HTML لمحتوى المشاركة.
  • التصفية:
    • تصفية التاريخ: يتم تضمين المشاركات التي تم إنشاؤها ضمن النطاق الزمني المحدد من قبل المستخدم (start_date إلى end_date) فقط.
    • تصفية الروابط: اختياريًا، إذا تم تعيين المعلمة search إلى true، يتم النظر فقط في الروابط التي تطابق link_pattern.
  • التجميع: لكل مشاركة، يقوم الاستعلام بعد الروابط المميزة وتجميعها في مصفوفة. يساعد هذا في فهم تنوع الروابط المشتركة في مشاركة واحدة.
  • الإخراج: يخرج الاستعلام تاريخ إنشاء المشاركة، ومعرف المشاركة، ومعرف الموضوع، وعدد الروابط الفريدة، ومصفوفة لهذه الروابط.

المعلمات

  • start_date و end_date: يحددان النطاق الزمني للمشاركات المراد تحليلها.
  • link_pattern: نمط لتصفية الروابط. سيتم تضمين الروابط التي تحتوي على هذا النمط فقط إذا تم تعيين search إلى true. في المعلمة :link_pattern، الرموز % هي أحرف بدل في SQL تطابق صفرًا أو أكثر من الأحرف.
  • search: علامة منطقية لتفعيل تصفية نمط الرابط.

النتائج

  • created_at: تاريخ ووقت إنشاء المشاركة.
  • post_id: المعرف الفريد للمشاركة.
  • topic_id: المعرف الفريد للموضوع الذي تنتمي إليه المشاركة.
  • link_count: عدد الروابط الفريدة الموجودة في المشاركة. يشير هذا إلى عدد الروابط الخارجية المختلفة التي تم تضمينها في المشاركة.
  • all_links: مصفوفة بجميع الروابط الفريدة الموجودة في المشاركة.

نتائج مثال

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)