mysql - 上周最受欢迎的帖子

Question

上周我正在尝试制作一个最受欢迎的帖子。我创建了这个 sqlfiddle 为了更好地理解和帮助。

在这个 sqlFiddle 你可以看到结果。但结果是错误的，因为查询会从所有数据中计算最受欢迎的帖子。我想获得上周最受欢迎的帖子。什么是错的，查询中的解决方案是什么。任何人都可以帮助我吗？

SELECT  
     t1.yearweek,
     t1.post_id_fk,
     t1.liked_post_type,
     t1.cnt 
         AS max_count 
     FROM 
         (SELECT 
            YEARWEEK(FROM_UNIXTIME(liked_time)) AS 
          yearweek,
          post_id_fk,
          liked_post_type,
             COUNT(*) AS cnt 
          FROM post_like 
     GROUP BY 
          YEARWEEK(FROM_UNIXTIME(liked_time)),post_id_fk,liked_post_type) t1 
     INNER JOIN 
          (SELECT yearweek, MAX(cnt) AS max_cnt
     FROM  
          (SELECT YEARWEEK(FROM_UNIXTIME(liked_time)) AS 
           yearweek,
           post_id_fk, 
           liked_post_type,
     COUNT(*) AS cnt 
           FROM post_like 
     GROUP BY 
           YEARWEEK(FROM_UNIXTIME(liked_time)),post_id_fk,liked_post_type) t 
     GROUP BY yearweek) t2 ON   
     t1.cnt = t2.max_cnt AND liked_post_type = 'p_image' ORDER BY t2.max_cnt DESC LIMIT 1

数据设置

CREATE TABLE `post_like` (
  `like_id` int(11) NOT NULL,
  `post_id_fk` int(11) NOT NULL,
  `liked_uid_fk` int(11) NOT NULL,
  `liked_post_type` enum('p_text','p_image','p_link','p_video','p_audio','u_following','u_send_friend_request','p_avatar','p_cover','p_gif','p_location','p_watermark','p_which','p_page','p_event','p_blog','p_group') DEFAULT NULL,
  `liked_time` int(11) NOT NULL DEFAULT 1524910573
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Tablo döküm verisi `dot_post_like`
--

INSERT INTO `post_like` (`like_id`, `post_id_fk`, `liked_uid_fk`, `liked_post_type`, `liked_time`) VALUES
(2, 7, 4, 'p_image', 1565082347),
(3, 8, 4, 'p_image', 1565082347),
(4, 5, 4, 'p_image', 1565082347),
(5, 3, 4, 'p_image', 1565082347),
(6, 99, 4, 'p_image', 1565082347),
(7, 12, 4, 'p_image', 1533513600),
(8, 7, 4, 'p_image', 1565082347),
(9, 7, 4, 'p_image', 1568351373),
(10, 7, 4, 'p_image', 1568351373),
(11, 7, 4, 'p_image', 1568351373),
(12, 12, 4, 'p_image', 1533513600),
(13, 12, 4, 'p_image', 1533513600),
(14, 12, 4, 'p_image', 1533513600),
(15, 11, 4, 'p_image', 1568351373),
(16, 12, 4, 'p_image', 1533513600);

# The post_id_fk 12 time is last year not this year. That is why this is wrong result.

Answer 1

您拥有比实际需要更复杂的联接子查询。使用子查询最容易确定上周(有喜欢的)是什么:

SELECT MAX(YEARWEEK(FROM_UNIXTIME(liked_time))) FROM post_like

这可以与 INNER JOIN 连接。在 FROM像这样的条款

SELECT  
     t1.yearweek,
     t1.post_id_fk,
     t1.liked_post_type,
     t1.cnt AS max_count 
FROM 
(
  -- subquery returns ONLY the most recent week
  SELECT
    MAX(YEARWEEK(FROM_UNIXTIME(liked_time))) AS lastweek
  FROM post_like
) lastweek
  -- Joins against subquery that returns likes per week
  INNER JOIN (
  SELECT
    YEARWEEK(FROM_UNIXTIME(liked_time)) AS yearweek,
    post_id_fk,
    liked_post_type,
    COUNT(*) AS cnt
  FROM post_like
  WHERE liked_post_type = 'p_image'
  GROUP BY 
    YEARWEEK(FROM_UNIXTIME(liked_time)),
    post_id_fk,
    liked_post_type
) t1 ON lastweek.lastweek = t1.yearweek
-- Retrieve only the top one
ORDER BY max_count DESC LIMIT 1

example here现在返回 post_id_fk = 7一周内有 3 个赞 201936
将它放在 WHERE IN() 中可以更轻松地完成。但我不确定哪个更有效。

SELECT
  YEARWEEK(FROM_UNIXTIME(liked_time)) AS yearweek,
  post_id_fk,
  liked_post_type,
  COUNT(*) AS cnt
FROM post_like
WHERE
  liked_post_type = 'p_image'
  -- Filter the rows for the most recent week in WHERE
  YEARWEEK(FROM_UNIXTIME(liked_time)) IN (SELECT MAX(YEARWEEK(FROM_UNIXTIME(liked_time))) FROM post_like)
GROUP BY 
  YEARWEEK(FROM_UNIXTIME(liked_time)),
  post_id_fk,
  liked_post_type
ORDER BY cnt DESC LIMIT 1

此 example也返回 post_id_fk = 7上周有 3 个赞。

如果您仍然需要知道该帖子总共收到了多少赞，而不仅仅是上周收到了多少赞，请将其加入一个未由 YEARWEEK() 过滤的完整分组。

SELECT post_id_fk, COUNT(*) as all_likes FROM post_like GROUP BY post_id_fk

这产生:

SELECT
  t_week.yearweek,
  t_week.post_id_fk,
  t_week.liked_post_type,
  -- Likes from last week
  t_week.cnt AS week_like_count,
  -- Likes from all time
  t_total.total_likes
FROM
(
  SELECT
    YEARWEEK(FROM_UNIXTIME(liked_time)) AS yearweek,
    post_id_fk,
    liked_post_type,
    COUNT(*) AS cnt
  FROM post_like
  WHERE
    YEARWEEK(FROM_UNIXTIME(liked_time)) IN (SELECT MAX(YEARWEEK(FROM_UNIXTIME(liked_time))) FROM post_like)
  AND liked_post_type = 'p_image'
  GROUP BY 
    YEARWEEK(FROM_UNIXTIME(liked_time)),
    post_id_fk,
    liked_post_type
) t_week
-- Join on a subquery that returns total likes overall (not just last week)
INNER JOIN (
  SELECT post_id_fk, COUNT(*) AS total_likes FROM post_like GROUP BY post_id_fk
) t_total ON t_week.post_id_fk = t_total.post_id_fk
 ORDER BY cnt DESC LIMIT 1

Example with total likes and last week likes