Поиск ссылок в темах

Создание сообщества Данные и отчетность
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-запросу

Запрос выполняет следующие операции:

  • Операции соединения (JOIN): Он соединяет таблицу posts с таблицей topics, чтобы учитывать только сообщения, принадлежащие публичным темам. Это делается путем проверки того, что archetype темы не равен private_message.
  • Сопоставление шаблонов: С помощью регулярного выражения извлекаются все URL-адреса из поля cooked каждого сообщения. Это поле содержит HTML-версию содержимого сообщения.
  • Фильтрация:
    • Фильтрация по дате: Включаются только сообщения, созданные в указанном пользователем диапазоне дат (start_date — end_date).
    • Фильтрация ссылок: Опционально, если параметр search установлен в true, учитываются только ссылки, соответствующие link_pattern.
  • Агрегация: Для каждого сообщения запрос подсчитывает количество уникальных ссылок и агрегирует их в массив. Это помогает понять разнообразие ссылок, размещенных в одном сообщении.
  • Вывод: Запрос выводит дату создания сообщения, ID сообщения, ID темы, количество уникальных ссылок и массив этих ссылок.

Параметры

  • 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 лайка