MYSQL如何在过去两天内显示最受欢迎的项目

时间：2021-01-09 00:52:48

I tried to show the most popular items in the last two days, but this view is letting in items that happened over two days ago.

我试图在过去两天显示最受欢迎的项目,但这个视图是让两天前发生的事情。

It is made to find the most popular in the last two days (maybe 20-30 items) and fill the remaining ones with random items (need 1000 items on the view at all times)

这是为了找到过去两天中最受欢迎的(可能是20-30项)并用随机物品填充剩余的物品(在视图中始终需要1000个物品)

How can I fix this?

我怎样才能解决这个问题?

Thank you

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `XX`@`XX` 
    SQL SECURITY DEFINER
VIEW `trending` AS
    select 
        `question`.`name` AS `name`,
        `question`.`questionUrl` AS `questionUrl`,
        `question`.`miRating` AS `miRating`,
        `question`.`imageUrl` AS `imageUrl`,
        `question`.`miThumbnail` AS `miThumbnail`,
        `question`.`foundOn` AS `foundOn`,
        `question`.`myId` AS `myId`
    from
        (`question`
        join `feed` ON ((`question`.`myId` = `feed`.`question_id`)))
    group by `question`.`name`
    order by (`feed`.`timeStamp` >= (now() - interval 1 day)) desc , 
    (`feed`.`question_id` is not null) desc , 
    (((`question`.`likesCount` * 0.8) + (`question`.`commentsCount` * 0.6)) + ((`question`.`sharesCount` * 1) / 2.4)) desc
    limit 0 , 1000

1 个解决方案

#1

1  

The problem is that you are grouping by question_name, but you have a lot of other columns in the query, in both the select and the order by. MySQL chooses arbitrary values for these. One way to fix this is by using only the maximum of the time condition in the order by clause:

问题是您是按question_name进行分组,但是在select和order by中,查询中有很多其他列。 MySQL为这些选择任意值。解决此问题的一种方法是仅使用order by子句中的最大时间条件:

select q.`name` AS `name`, q.`questionUrl` AS `questionUrl`, q.`miRating` AS `miRating`,
       q.`imageUrl` AS `imageUrl`, q.`miThumbnail` AS `miThumbnail`,
       q.`foundOn` AS `foundOn`, q.`myId` AS `myId`
from `question` q join
     `feed` f
      ON q.`myId` = f.`question_id`
group by q.`name`
order by (max(f.`timeStamp`) >= (now() - interval 1 day)) desc , 
         (f.`question_id` is not null) desc , 
         (((q.`likesCount` * 0.8) + (q.`commentsCount` * 0.6)) + ((q.`sharesCount` * 1) / 2.4)) desc
limit 0 , 1000

#1

1  

The problem is that you are grouping by question_name, but you have a lot of other columns in the query, in both the select and the order by. MySQL chooses arbitrary values for these. One way to fix this is by using only the maximum of the time condition in the order by clause:

问题是您是按question_name进行分组,但是在select和order by中,查询中有很多其他列。 MySQL为这些选择任意值。解决此问题的一种方法是仅使用order by子句中的最大时间条件:

select q.`name` AS `name`, q.`questionUrl` AS `questionUrl`, q.`miRating` AS `miRating`,
       q.`imageUrl` AS `imageUrl`, q.`miThumbnail` AS `miThumbnail`,
       q.`foundOn` AS `foundOn`, q.`myId` AS `myId`
from `question` q join
     `feed` f
      ON q.`myId` = f.`question_id`
group by q.`name`
order by (max(f.`timeStamp`) >= (now() - interval 1 day)) desc , 
         (f.`question_id` is not null) desc , 
         (((q.`likesCount` * 0.8) + (q.`commentsCount` * 0.6)) + ((q.`sharesCount` * 1) / 2.4)) desc
limit 0 , 1000
标签：

相关文章