大数据面试SQL（十三）：经常去同一家网吧的用户中两人一定认识的组合数

文章目录

经常去同一家网吧的用户中两人一定认识的组合数

一、题目

二、分析

三、SQL实战

四、样例数据参考

---

经常去同一家网吧的用户中两人一定认识的组合数

一、题目

有某城市网吧上网记录表，包含字段：网吧，访客，上线时间，下线时间。

• 规则一：如果两个用户在同一个网吧上线时间或者下线时间间隔在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博客🙉
• 📢停下休息的时候不要忘了别人还在奔跑，希望大家抓紧时间学习，全力奔赴更美好的生活✨