Сортировка по просмотрам за день / неделю / месяц — как построить запрос?

Question

[Сергей]

Привет, ребята.

Нужно сделать сортировку постов Wordpress по просмотрам за 24 часа, 7 дней, 30 дней.

Дано:

• Таблица постов WP
  
• Таблица wp_post_views с двумя полями: ID (ID поста) и time (хранит дату и время просмотра)
  

Собственно надо построить запрос SQL, который отсортирует посты из таблицы wp_posts по количеству строк с ID поста в таблице wp_post_views за нужный период.

Есть идеи, как это можно провернуть? Пытался делать через JOIN, GROUP BY и COUNT. Ничего не получилось у меня... В SQL я полный профан.
Заранее спасибо :)

Нашел такое решение:

SELECT `ID`, `post_title` FROM `wp_posts` AS t1
INNER JOIN `wp_post_views` AS t2 USING(`ID`)
WHERE t2.time >= CURDATE() - INTERVAL 30 DAY /* или 1 DAY, или 7 DAY*/
GROUP BY `ID`
ORDER BY COUNT(t2.ID) DESC 
LIMIT 10

Результат выполнения:

Вроде бы работает, но это не точно... Мб кому пригодится :)

Comments

[Сергей]

Поделился. Но я не уверен, что это работает правильно...

Answer 1

[Сергей c0re]

Примерно, как-то так, если не ошибаюсь:

SELECT
      `ID`
    , `post_title`
    , pv30.cnt_month
    , COALESCE(pv7.cnt_week, 0) cnt_week
    , COALESCE(pv1.cnt_day, 0) cnt_day
  FROM `wp_posts` AS p
  INNER JOIN (
    SELECT id, count(id) cnt_month
      FROM `wp_post_views` pv
      WHERE pv.time >= CURDATE() - INTERVAL 30 DAY
      GROUP BY pv.id
    ) AS pv30
    ON pv30.id = p.id
  LEFT JOIN (
    SELECT id, count(id) cnt_week
      FROM `wp_post_views` pv
      WHERE pv.time >= CURDATE() - INTERVAL 7 DAY
      GROUP BY pv.id
    ) AS pv7
    ON pv7.id = p.id
  LEFT JOIN (
    SELECT id, count(id) cnt_day
      FROM `wp_post_views` pv
      WHERE pv.time >= CURDATE() - INTERVAL 1 DAY
      GROUP BY pv.id
    ) AS pv1
    ON pv1.id = p.id
  ORDER BY pv30.cnt_month DESC, pv7.cnt_week DESC, pv1.cnt_day DESC
  LIMIT 10;

Comments

[Сергей]

Надо по отдельности, но спасибо за старания.

Answer 2

[tisprk]

Проверить негде. Рискну предположить следующее:

--24 часа
select * from wp_posts wp
inner join wp_post_views wv on wp.id = wv.id
where time > DATE_SUB(CURDATE(), INTERVAL 24 HOUR);

-- 7 дней
select * from wp_posts wp
inner join wp_post_views wv on wp.id = wv.id
where time > DATE_SUB(CURDATE(), INTERVAL 168 HOUR);

-- 30 дней
select * from wp_posts wp
inner join wp_post_views wv on wp.id = wv.id
where time > DATE_SUB(CURDATE(), INTERVAL 720 HOUR);