Блог

У меня есть две таблицы, «просмотр страниц» и «комментарии», в которых хранятся новые посещения веб-страниц и новые комментарии соответственно.

ПРОСМОТР СТРАНИЦ

Комментарии

Я хочу объединить данные из обеих таблиц за последний 1 час для использования в аналитике, чтобы они выглядели как таблица ниже.

ДАННЫЕ СТРАНИЦЫ

 SELECT page_visits.page_id  , COUNT(page_visits.id) AS visitCount  , COALESCE(cmts.cmt_cnt,0) AS commentCount  FROM page_visits LEFT OUTER  JOIN ( SELECT page_id  , COUNT(*) AS cmt_cnt  FROM comments  WHERE created_at gt;= NOW() - INTERVAL '1 HOUR'  GROUP  BY page_id  ) AS cmts  ON cmts.page_id = page_visits.page_id  WHERE page_visits.created_at gt;= NOW() - INTERVAL '1 HOUR' GROUP  BY page_visits.page_id, cmts.cmt_cnt;  

На данный момент у меня есть приведенный выше код, однако он печатает строку только тогда, когда comment_count равен нулю, но он не делает то же самое, когда visit_count равен 0, а comment_count gt; 0. Мой первый вопрос заключается в том, как заставить его печатать, даже если результат visit_count равен 0. Потому что кто-то мог зайти на страницу час назад, но прокомментировать ее только позже.

Во-вторых, я пытаюсь запускать этот код каждый час с использованием pg_cron, и я знаю, что могу запускать функцию непосредственно в планировщике cron, однако я не могу превратить приведенный выше код в рабочую функцию postgres, которая вставляет новую строку в таблицу «page_data» каждый раз, когда она вызывается.

Может ли кто-нибудь помочь мне с этими 2 проблемами? Спасибо.

Рассмотрим полное объединение двух агрегатов

 SELECT page_visits.page_id   , COALESCE(vsts.vst_cnt, 0) AS visitCount   , COALESCE(cmts.cmt_cnt, 0) AS commentCount FROM (  SELECT page_id  , COUNT(*) AS vst_cnt   FROM page_visits   WHERE created_at gt;= NOW() - INTERVAL '1 HOUR'   GROUP BY page_id  ) AS vsts FULL OUTER JOIN (   SELECT page_id   , COUNT(*) AS cmt_cnt   FROM comments  WHERE created_at gt;= NOW() - INTERVAL '1 HOUR'  GROUP BY page_id   ) AS cmts  ON cmts.page_id = vsts.page_id  

В качестве альтернативы можно объединить запрос ОБЪЕДИНЕНИЯ обеих таблиц:

 SELECT page_id  , SUM(vst_n) AS vst_cnt   , SUM(cmt_n) AS cmt_cnt FROM (  SELECT page_id, 1 AS vst_n, 0 AS cmt_n  FROM page_visits   WHERE created_at gt;= NOW() - INTERVAL '1 HOUR'    UNION ALL   SELECT page_id, 0 AS vst_n, 1 AS cmt_n  FROM comments   WHERE created_at gt;= NOW() - INTERVAL '1 HOUR'   ) AS sub GROUP BY page_id  

Что касается последнего вопроса, если я вас понимаю, просто запустите запрос «вставить-выбрать» из приведенного выше запроса. Не совсем уверен , как вы агрегировали created_at , но добавьте MIN или MAX к приведенным выше агрегациям и включите дополнительный столбец ниже:

 INSERT INTO page_data (page_id, visit_count, comment_count) SELECT ...above query...