MySQL如何查询每个用户最高观看分钟数对应的日期

请问如何从watched_time表中查询得到每个用户最高观看分钟数（minutes字段）对应的日期（date字段）？编写对应SQL实现该需求时遇到困难，需要可行的实现方案。

INSERT INTO watched_time (id,user_id, channel_id,minutes,`date`)
VALUES
(1,1,1,100.0,'2021-01-01 00:00:00.0'),
(2,1,1,180.0,'2021-01-02 00:00:00.0'),
(3,1,1,150.0,'2021-01-03 00:00:00.0'),
(4,1,1,110.0,'2021-01-04 00:00:00.0'),
(5,2,1,110.0,'2021-01-04 00:00:00.0'),
(6,2,1,140.0,'2021-01-05 00:00:00.0'),
(7,2,1,190.0,'2021-01-06 00:00:00.0'),
(8,3,1,170.0,'2021-01-01 00:00:00.0'),
(9,3,1,120.0,'2021-01-02 00:00:00.0'),
(10,3,1,130.0,'2021-01-03 00:00:00.0'),
(11,1,2,130.0,'2021-01-03 00:00:00.0'),
(12,2,2,130.0,'2021-01-03 00:00:00.0'),
(13,3,2,125.0,'2021-01-03 00:00:00.0'),
(14,1,2,110.0,'2021-01-05 00:00:00.0'),
(15,1,2,100.0,'2021-01-01 00:00:00.0'),
(16,2,2,120.0,'2021-01-01 00:00:00.0'),
(17,3,2,120.0,'2021-01-01 00:00:00.0');

注意：如果同一用户存在多个日期的观看分钟数并列最高，可按需选择返回单条匹配记录还是全部匹配记录。

方案1：窗口函数（推荐，支持MySQL8.0+、PostgreSQL、SQL Server等现代数据库） #

逻辑清晰，查询性能优秀。

SELECT user_id, minutes, `date`
FROM (
    SELECT 
        user_id,
        minutes,
        `date`,
        -- 按用户分组，按观看分钟数倒序打排名
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY minutes DESC) AS rn
        -- 若需要保留同用户并列最高分钟数的所有日期，将ROW_NUMBER()替换为RANK()即可
    FROM watched_time
) t
WHERE rn = 1;

方案2：关联聚合查询（兼容所有SQL版本，包括MySQL5.x等老版本数据库） #

先聚合算出每个用户的最高观看分钟数，再关联原表取出对应日期。

SELECT w.user_id, w.minutes, w.`date`
FROM watched_time w
INNER JOIN (
    SELECT user_id, MAX(minutes) AS max_minutes
    FROM watched_time
    GROUP BY user_id
) tmp ON w.user_id = tmp.user_id AND w.minutes = tmp.max_minutes;

方案3：DISTINCT ON语法（仅适用于PostgreSQL等支持该语法的数据库） #

写法最简洁，性能优异。

SELECT DISTINCT ON (user_id)
    user_id,
    minutes,
    `date`
FROM watched_time
ORDER BY user_id, minutes DESC;

基于给出的测试数据，正确返回结果如下：

• user_id=1：最高观看分钟数180.0，对应日期2021-01-02
• user_id=2：最高观看分钟数190.0，对应日期2021-01-06
• user_id=3：最高观看分钟数170.0，对应日期2021-01-01

内容的提问来源于stack exchange，提问作者Iago Alcântara