文章目录
经常去同一家网吧的用户中两人一定认识的组合数
一、题目
有某城市网吧上网记录表,包含字段:网吧,访客,上线时间,下线时间。
- 规则一:如果两个用户在同一个网吧上线时间或者下线时间间隔在10分钟以内,则两个用户可能认识。
- 规则二:如果两个用户在三家以上的网吧出现过【规则一】可能认识的情况,则两人一定认识。
请计算该城市中经常去同一家网吧的用户中两人一定认识的组合数。
样例数据:
目标结果:
明细分析:
二、分析
1、首先计算可能认识的人,由于所有可能认识的条件必须发生在同一个网吧内,以bar_id进行自关联,然后id要求t1>t2来保证同一个用户和其他的用户只进行一次关联,限定上线时间或者下线时间在10分钟内。
2、计算出可能认识的用户组中,出现的同个网吧的个数。
3、计算网吧个数>=3的组合数。
维度 | 评分 |
---|---|
题目难度 | ⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️ |
三、SQL实战
1、可能认识的记录,按照【规则一】进行条件判断。
规则一:如果两个用户在同一个网吧上线时间或者下线时间间隔在10分钟以内,则两个用户可能认识。
查询语句:
select t1.bar_id,
t1.bar,
t1.user_id,
t1.user_name,
t1.login_time,
t1.logoff_time,
t2.bar_id,
t2.bar,
t2.user_id,
t2.user_name,
t2.login_time,
t2.logoff_time
from (select bar_id,bar,user_id,user_name, login_time, logoff_time
from t2_netbar) t1
left join
(select bar_id,bar, user_id,user_name, login_time, logoff_time
from t2_netbar) t2
on t1.bar_id = t2.bar_id
where t1.user_id < t2.user_id --限定t1的user_id <t2的user_id,防止重复关联和自关联
and (abs(unix_timestamp(t1.login_time, 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(t2.login_time, 'yyyy-MM-dd HH:mm:ss')) < 600-- 上线时间在10min内
or abs(unix_timestamp(t1.logoff_time, 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(t2.logoff_time, 'yyyy-MM-dd HH:mm:ss')) < 600 -- 下线时间在10min内
);
查询结果:
2、按照t1的user_id,t2的user_id 进行分组,统计可能认识的人(符合【规则一】条件的人)在同一网吧上网的次数。
查询语句:
select user_id1,
max(user_name1) as user_name1,
user_id2,
max(user_name2) as user_name2,
count(distinct bar_id) as bar_num
from (
select t1.bar_id,
t1.bar,
t1.user_id as user_id1,
t1.user_name as user_name1,
t1.login_time,
t1.logoff_time,
t2.bar_id,
t2.bar,
t2.user_id as user_id2,
t2.user_name as user_name2,
t2.login_time,
t2.logoff_time
from (select bar_id,bar,user_id,user_name, login_time, logoff_time
from t2_netbar) t1
left join
(select bar_id,bar, user_id,user_name, login_time, logoff_time
from t2_netbar) t2
on t1.bar_id = t2.bar_id
where t1.user_id < t2.user_id --限定t1的user_id <t2的user_id,防止重复关联和自关联
and (abs(unix_timestamp(t1.login_time, 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(t2.login_time, 'yyyy-MM-dd HH:mm:ss')) < 600-- 上线时间在10min内
or abs(unix_timestamp(t1.logoff_time, 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(t2.logoff_time, 'yyyy-MM-dd HH:mm:ss')) < 600 -- 下线时间在10min内
)
) tt
group by user_id1,user_id2;
查询结果:
3、统计最终结果。
查询语句:
select count(1) as group_nums
from (
select user_id1,
max(user_name1) as user_name1,
user_id2,
max(user_name2) as user_name2,
count(distinct bar_id) as bar_num
from (
select t1.bar_id,
t1.bar,
t1.user_id as user_id1,
t1.user_name as user_name1,
t1.login_time,
t1.logoff_time,
t2.bar_id,
t2.bar,
t2.user_id as user_id2,
t2.user_name as user_name2,
t2.login_time,
t2.logoff_time
from (select bar_id,bar,user_id,user_name, login_time, logoff_time
from t2_netbar) t1
left join
(select bar_id,bar, user_id,user_name, login_time, logoff_time
from t2_netbar) t2
on t1.bar_id = t2.bar_id
where t1.user_id < t2.user_id --限定t1的user_id <t2的user_id,防止重复关联和自关联
and (abs(unix_timestamp(t1.login_time, 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(t2.login_time, 'yyyy-MM-dd HH:mm:ss')) < 600-- 上线时间在10min内
or abs(unix_timestamp(t1.logoff_time, 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(t2.logoff_time, 'yyyy-MM-dd HH:mm:ss')) < 600 -- 下线时间在10min内
)
) tt
group by user_id1,user_id2
) t3
where t3.bar_num >= 3;
查询结果:
四、样例数据参考
--建表语句
CREATE TABLE t2_netbar (
bar_id bigint COMMENT '网吧ID',
bar string COMMENT '网吧',
user_id bigint COMMENT '用户ID',
user_name string COMMENT '用户',
login_time string COMMENT '上线时间',
logoff_time string COMMENT '下线时间'
) COMMENT '网吧上下线记录表';
-- 插入数据
insert into t2_netbar(bar_id,bar,user_id,user_name,login_time,logoff_time)
values
(1,'极速网吧',1,'电竞先锋','2024-08-01 09:00:00','2024-08-01 10:00:00'),
(2,'星际网吧',1,'电竞先锋','2024-08-01 11:01:00','2024-08-01 12:00:00'),
(2,'星际网吧',2,'网际游侠','2024-08-01 11:03:00','2024-08-01 14:00:00'),
(3,'网鱼电竞',1,'电竞先锋','2024-08-02 16:01:00','2024-08-02 17:07:00'),
(3,'网鱼电竞',2,'网际游侠','2024-08-02 15:00:00','2024-08-02 17:06:00'),
(4,'风云网吧',1,'电竞先锋','2024-08-03 17:00:00','2024-08-03 19:00:00'),
(4,'风云网吧',2,'网际游侠','2024-08-03 18:00:00','2024-08-03 21:00:00'),
(1,'极速网吧',3,'键盘舞者','2024-08-01 09:04:00','2024-08-01 11:00:00'),
(3,'网鱼电竞',3,'键盘舞者','2024-08-02 20:00:00','2024-08-02 22:00:00'),
(4,'风云网吧',3,'键盘舞者','2024-08-03 18:05:00','2024-08-03 22:00:00'),
(2,'星际网吧',4,'数据猎人','2024-08-01 10:00:00','2024-08-01 12:02:00'),
(3,'网鱼电竞',4,'数据猎人','2024-08-02 16:02:00','2024-08-02 18:00:00'),
(4,'风云网吧',4,'数据猎人','2024-08-03 19:00:00','2024-08-03 18:58:00'),
(2,'星际网吧',5,'虚拟游侠','2024-08-01 10:10:00','2024-08-01 11:00:00'),
(1,'极速网吧',6,'游戏霸主','2024-08-01 10:00:00','2024-08-01 12:00:00');
- 📢博客主页:https://lansonli.blog.csdn.net
- 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正!
- 📢本文由 Lansonli 原创,首发于 CSDN博客🙉
- 📢停下休息的时候不要忘了别人还在奔跑,希望大家抓紧时间学习,全力奔赴更美好的生活✨