基于不同时间级别的MySQL流行行 [英] MySQL popular rows based on different time levels

问题描述

我有2个表:photo和like.我正在尝试根据一天的喜欢次数获得受欢迎的照片.所以基本上就像今天很受欢迎" .

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'.

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...

所以基本上，这就是说，我们需要获得30个物品.首先，它将尝试根据今天的喜欢人数获得行数.它可以是20、15等任意数字.然后它将获得剩下的行数，总共达到30，但是现在将根据一周内的点赞次数进行排序.

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.

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

感谢您的帮助！

推荐答案

您可以基于CASE计算不同的计数，例如最近30天的赞数，最后一天的计数.上周和过去30天:

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:

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

这篇关于基于不同时间级别的MySQL流行行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！