sql - 查找在“%Y-%m”中给出最低平均评分的用户

Question

共有三个表：Movies、Users和Rating具有以下结构：

Movies(id int, title text);
Users(id int, name text);
Rating(movie_id int, user_id int, rating int, created_at date);

我正在尝试使用以下查询查找在 2019 年 5 月给出最低平均评分的用户的名称：

select name 
from users 
where id = 
(
    select user_id from
    (
        select user_id, min(avgrating)
        from 
        (
            select user_id, avg(rating) as avgrating 
            from Rating 
            where strftime('%Y-%m', created_at) = '2019-05'
            group by user_id
        )
    )
);

有两个用户 Ben和Nicole，但上面的查询只返回 Ben。

电影内容：

id  title
1, 'Avengers:Endgame'
2, 'Aladdin'
3, 'Aquaman'

用户内容：

id  name
1, 'Ben'
2, 'Nicole'
3, 'James'
4, 'Tara'

评分内容：

movie_id, user_id, rating, created_at
1,        1,       3,      '2019-05-07 00:10:00'
1,        2,       4,      '2019-04-28 09:00:00'
1,        3,       5,      '2019-05-11 10:40:00'
1,        4,       4,      '2019-05-02 09:30:00'
2,        1,       4,      '2019-06-16 08:00:00'
2,        2,       3,      '2019-05-30 09:00:00'
2,        3,       4,      '2019-06-01 13:50:00'
3,        1,       5,      '2019-01-05 09:55:00'
3,        2,       4,      '2019-01-28 10:00:00'

标签： sqlsqlitedatetimegreatest-n-per-groupwindow-functions

Answer 1

在 SQLite 3.25 或更高版本中，您可以使用rank()：

select name
from (
    select u.name, rank() over(order by r.rating) rn
    from rating r
    inner join users u on u.id = r.user_id
    where r.created_date >= '2019-05-01' and r.created_date < '2019-06-01'
) t
where rn = 1

在早期版本中，我会使用一个子查询来检索该月的最低评分，然后加入评分和用户：

select u.name, rank() over(order by r.rating) rn
from rating r
inner join users u on u.id = r.user_id
where 
    r.created_date >= '2019-05-01' and r.created_date < '2019-06-01'
    and r.rating = (
        select min(r1.rating)
        from rating r1
        where r1.created_date >= '2019-05-01' and r1.created_date < '2019-06-01'
    )