描述
现有某商家用户访问数据及用户会员等级数据,如下所示:
用户访问信息表: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 |
会员等级信息表:uservip_tb(用户id-user_id,会员等级-vip,积分-point)
user_id | vip | point |
---|---|---|
10 | 银卡会员 | 530 |
11 | 银卡会员 | 1555 |
12 | 钻石会员 | 12000 |
13 | 金卡会员 | 6115 |
14 | 普通会员 | 230 |
15 | 银卡会员 | 810 |
16 | 普通会员 | 330 |
根据用户访问数据统计出每日不同会员等级访问人次及访问人数。
要求输出:会员等级、访问人次、访问人数
注:按照访问人次降序排序
示例数据结果如下:
vip | visit_nums | visit_users |
---|---|---|
银卡会员 | 6 | 2 |
金卡会员 | 3 | 1 |
钻石会员 | 2 | 1 |
普通会员 | 1 | 1 |
结果解释:
钻石会员有user_id为12的用户,该用户分别在9月1日、9月2日访问,故访问人次为2、访问人数为1;
其他结果同理。
示例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');
drop table if exists `uservip_tb` ;
CREATE TABLE `uservip_tb` (
`user_id` int(11) NOT NULL,
`vip` varchar(16) NOT NULL,
`point` int(11) NOT NULL,
PRIMARY KEY (`user_id`));
INSERT INTO uservip_tb VALUES(10,'银卡会员',530);
INSERT INTO uservip_tb VALUES(11,'银卡会员',1555);
INSERT INTO uservip_tb VALUES(12,'钻石会员',12000);
INSERT INTO uservip_tb VALUES(13,'金卡会员',6115);
INSERT INTO uservip_tb VALUES(14,'普通会员',230);
INSERT INTO uservip_tb VALUES(15,'银卡会员',810);
INSERT INTO uservip_tb VALUES(16,'普通会员',330);
输出:
vip|visit_nums|visit_users
银卡会员|6|2
金卡会员|3|1
钻石会员|2|1
普通会员|1|1
答案
解法1:
select
u.vip,
sum(datediff(v.leave_time,v.visit_time)+1) visit_nums, -- 针对单次访问涉及多天的情况
count(distinct v.user_id) visit_users
from
visit_tb v
left join uservip_tb u on v.user_id=u.user_id
group by
u.vip
order by visit_nums desc;
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*