回忆是一座桥
却是通往寂寞的牢

SQL案例 - 统计商家不同会员每日访问人次及访问人数

描述

现有某商家用户访问数据及用户会员等级数据,如下所示:

用户访问信息表: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;

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 统计商家不同会员每日访问人次及访问人数
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论