描述
现有某商家用户访问数据如下所示:
用户访问信息表:visit_tb(访问信息id-info_id,用户id-user_id,访问时间-visit_time,离开时间-leave_time)
info_id | user_id | visit_time | leave_time |
---|---|---|---|
911 | 10 | 2022-09-01 08:00:00 | 2022-09-01 09:02:00 |
912 | 11 | 2022-09-01 08:30:00 | 2022-09-01 09:10:00 |
913 | 12 | 2022-09-01 09:50:00 | 2022-09-01 10:12:00 |
914 | 13 | 2022-09-01 11:40:00 | 2022-09-01 12:22:00 |
921 | 11 | 2022-09-02 10:30:00 | 2022-09-02 11:05:00 |
922 | 11 | 2022-09-02 12:00:00 | 2022-09-02 12:02:00 |
923 | 12 | 2022-09-02 11:40:00 | 2022-09-02 13:15:00 |
924 | 13 | 2022-09-02 09:00:00 | 2022-09-02 09:02:00 |
925 | 14 | 2022-09-02 10:00:00 | 2022-09-02 10:40:00 |
931 | 10 | 2022-09-03 09:00:00 | 2022-09-03 09:22:00 |
932 | 11 | 2022-09-03 08:30:00 | 2022-09-03 09:10:00 |
933 | 13 | 2022-09-03 09:00:00 | 2022-09-03 09:32:00 |
该商城这几日推出新的推广活动,用户单次访问时长满10分钟则获得1积分,请查询这几日访问的用户可以获得多少积分?
要求输出:user_id,积分
注:输出结果按照积分降序排序
示例数据结果如下:
user_id | point |
---|---|
11 | 11 |
12 | 11 |
10 | 8 |
13 | 7 |
14 | 4 |
结果解释:
user_id为11的用户分别在0901 08:30、0902 10:30、0902 12:00、0903 08:30访问,
访问时长分别为40、35、2、40分钟,故获得积分为4+3+0+4=11积分;
其他结果同理
示例1
输入:
drop table if exists `visit_tb` ;
CREATE TABLE `visit_tb` (
`info_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`visit_time` datetime NOT NULL,
`leave_time` datetime NOT NULL,
PRIMARY KEY (`info_id`));
INSERT INTO visit_tb VALUES(0911,10,'2022-09-01 08:00:00','2022-09-01 09:02:00');
INSERT INTO visit_tb VALUES(0912,11,'2022-09-01 08:30:00','2022-09-01 09:10:00');
INSERT INTO visit_tb VALUES(0913,12,'2022-09-01 09:50:00','2022-09-01 10:12:00');
INSERT INTO visit_tb VALUES(0914,13,'2022-09-01 11:40:00','2022-09-01 12:22:00');
INSERT INTO visit_tb VALUES(0921,11,'2022-09-02 10:30:00','2022-09-02 11:05:00');
INSERT INTO visit_tb VALUES(0922,11,'2022-09-02 12:00:00','2022-09-02 12:02:00');
INSERT INTO visit_tb VALUES(0923,12,'2022-09-02 11:40:00','2022-09-02 13:15:00');
INSERT INTO visit_tb VALUES(0924,13,'2022-09-02 09:00:00','2022-09-02 09:02:00');
INSERT INTO visit_tb VALUES(0925,14,'2022-09-02 10:00:00','2022-09-02 10:40:00');
INSERT INTO visit_tb VALUES(0931,10,'2022-09-03 09:00:00','2022-09-03 09:22:00');
INSERT INTO visit_tb VALUES(0932,11,'2022-09-03 08:30:00','2022-09-03 09:10:00');
INSERT INTO visit_tb VALUES(0933,13,'2022-09-03 09:00:00','2022-09-03 09:32:00');
输出:
user_id|point
11|11
12|11
10|8
13|7
14|4
答案
解法1:
select
t.user_id,
sum(
floor(
timestampdiff (minute, t.visit_time, t.leave_time) / 10
)
) point
from
visit_tb t
group by
t.user_id
order by
point desc;
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*