SQL面试题练习 —— 最后一次登录停留时长

题目来源：腾讯微信。

1 题目

有一张用户登录日志表ods_usr_login_log, 包含user_id（用户id）、ds（登录时间）以及stay_time（停留时长，单位:ms）

问题：计算每个用户每天最后一次登录的停留时长。

样例数据

-- 输入
user_id      ds           stay_time 
001   2024-07-01 10:10:06   10
001   2024-07-01 13:10:06   5
001   2024-07-02 15:10:06   20
-- 输出
user_id      dt      stay_time 
001       2024-07-01       5 
001       2024-07-02       20

2 建表语句

create table ods_usr_login_log (
user_id varchar(20),
ds varchar(20),
stay_time bigint
);
INSERT INTO ods_usr_login_log VALUES 
('001','2024-07-01 10:10:06',10),
('001','2024-07-01 13:10:06',5),
('001','2024-07-02 15:10:06',20),
('002','2024-07-01 16:11:06',15),
('002','2024-07-01 18:11:06',25)
;

3 题解

先按照用户id和日期进行分组，登录时间进行逆序排列，计算对应的排名，然后获取排名为1的数据，即为题目所求

select 
    user_id,
    dt,
    stay_time
from (
    select 
        user_id, 
        str_to_date(ds, '%Y-%m-%d') as dt,
        stay_time,
        row_number() over(partition by user_id, str_to_date(ds, '%Y-%m-%d')  order by ds desc) as rn
    from ods_usr_login_log
) t
where rn = 1
;

执行结果

在 STR_TO_DATE 函数中，日期格式参数 (‘%Y-%m-%d’) 中的字母是区分大小写的。具体来说：

• %Y: 表示四位的年份，例如 2024。
• %m: 表示两位的月份，例如 08 表示八月。
• %d: 表示两位的日期，例如 15 表示十五号。