MySQL流行的行基于不同的时间级别

I have 2 tables: photo and like. I am trying to get popular photos based on number of likes in a day. So basically something like 'popular today'.

我有2张桌子：照片等。我试图在一天内根据喜欢的数量获得热门照片。所以基本上就像'今天流行'。

SELECT
  p.id AS id, COUNT(li.id) AS total_likes \
FROM `photo` p \
  LEFT JOIN `like` li \
    ON p.id = li.photo_id \
WHERE
  li.date > DATE_SUB(CURDATE(), INTERVAL 1 DAY) \
GROUP BY \
  p.id

This works well when there are enough number of likes today. But if there are no likes in the past day, it will return no records.

当今天有足够数量的喜欢时，这很有效。但如果过去一天没有喜欢，它将不会返回任何记录。

I also want to change this a little. Is it possible to make levels? For example:
Rank photos based on multiple days:

我也想稍微改变一下。是否有可能达到水平？例如：根据多天排名照片：

1. Get photos based on how many likes today
2. Get photos based on how many likes for last week
and so on...

So basically what this does is, let's say we need to get 30 items. First it will try to get rows based on how many likes there were today. It may be any number 20, 15 etc. Then it will get the remaining rows needed to get to a total of 30 but now will sort based on how many likes in a week.

所以基本上它的作用是，假设我们需要得到30个项目。首先，它会尝试根据今天有多少喜欢来获取行。它可能是任何数字20,15等。然后它将获得总共30所需的剩余行，但现在将根据一周中有多少喜欢排序。

So something like:
SELECT FROM photo SORT BY likes today, likes in a week ...

Thanks for your help!

谢谢你的帮助！

3 个解决方案

#1

You can calculate different counts based on CASE, e.g. the likes of the last 30 days with counts for last day. last week and last 30 days:

您可以根据CASE计算不同的计数，例如过去30天的喜欢与最后一天的计数。上周和过去30天：

SELECT
  p.id AS id
  ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN li.id END) AS daily_likes
  ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN li.id END) AS weekly_likes
  ,COUNT(li.id) AS total_likes
FROM `photo` p
JOIN `LIKE` li
    ON p.id = li.photo_id
WHERE
  li.DATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
  p.id
ORDER BY daily_likes DESC, weekly_likes DESC, total_likes DESC
LIMIT 30

I don't know on which definition your limit is based on, it might be something like

我不知道你的限制基于哪个定义，它可能是类似的

SELECT *
FROM
 (
   SELECT
     p.id AS id
     ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN li.id END) AS daily_likes
     ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN li.id END) AS weekly_likes
     ,COUNT(li.id) AS total_likes
   FROM `photo` p
   JOIN `LIKE` li
       ON p.id = li.photo_id
   WHERE
     li.DATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
   GROUP BY
     p.id
  ) AS dt
ORDER BY
   case when daily_likes > 20 then daily_likes else 0 end desc,
   case when weekly_likes > 100 then weekly_likes else 0 end desc,
   total_likes DESC
LIMIT 30

#2

Have you tried UNION?

你试过UNION吗？

technet.microsoft

Techonthenet.com

Wikipedia

and NOT EXISTS

而不是存在

MySQL

MySQL的

With UNION you can join select several select statements, as long the columns are the same

使用UNION，您可以连接选择多个select语句，只要列相同即可

From the top of my head, something like

从我的头顶，像

(select p.id as id,
       count(li.id) as total_likes
  from photo p,
       like li
 where p.id = li.photo_id
   and li.date > date_sub(curdate(), interval 1 day)
   and rownum <= 10
 order by total_likes
 group by p.id)
 union
(select p.id as id,
       count(li.id) as total_likes
  from photo p,
       like li
 where p.id = li.photo_id
   and li.date > date_sub(curdate(), interval 7 day)
   and rownum <= 10
   and not exists (select p.id as id,
                    count(li.id) as total_likes
                     from photo p,
                          like li
                    where p.id = li.photo_id
                      and li.date > date_sub(curdate(), interval 1 day)
                      and rownum <= 10
                    order by total_likes
                    group by p.id)
 order by total_likes
 group by p.id)

Haven't tested this, but guess that something like this would do the trick

没有测试过这个，但是猜测这样的事情就可以了

#3

You can compute the number of weeks elapsed between today and the 'like' date and group by them.

您可以计算今天和“喜欢”日期之间经过的周数，并按他们分组。

SELECT
  p.id AS id, COUNT(li.id) AS total_likes, period \
FROM `photo` p \
  LEFT JOIN `like` li \
  ON p.id = li.photo_id \
WHERE
  DATEDIFF(li.date, now())+7)%7 AS period \
GROUP BY \
  p.id, period
ORDER by period, total_likes DESC 
LIMI 30

#1