題目：

現(xiàn)有城市網(wǎng)吧訪問數(shù)據(jù)，字段：網(wǎng)吧id潜索，訪客id（身份證號）臭增，上線時間，下線時間
規(guī)則1竹习、如果有兩個用戶在一家網(wǎng)吧的前后上下線時間在10分鐘以內(nèi)誊抛，則兩人可能認識
規(guī)則2、如果這兩個用戶在三家以上網(wǎng)吧出現(xiàn)【規(guī)則1】的情況整陌，則兩人一定認識
需求：
該城市上網(wǎng)用戶中兩人一定認識的組合數(shù)
該題可以選用自己擅長的任何技術(shù)來解決拗窃，可以是JAVA、Python泌辫、C随夸、C++編程語言，也可以是Hadoop震放，Spark大數(shù)據(jù)工具

參考答案：

數(shù)據(jù)庫版本：Server version: 8.0.20 MySQL Community Server - GPL

建表語句

create table test_network_bar_info(
    bar_id int comment '網(wǎng)吧id',
    user_id int comment '訪客id-身份證號',
    login_time timestamp comment '上線時間',
    logout_time timestamp comment '下線時間'
);

數(shù)據(jù)準備

insert into test_network_bar_info values (1,110001,'2020-01-01 12:10:00','2020-01-01 12:30:00');
insert into test_network_bar_info values (1,110001,'2020-01-01 12:35:00','2020-01-01 12:40:00');
insert into test_network_bar_info values (1,110002,'2020-01-01 12:50:00','2020-01-01 12:55:00');
insert into test_network_bar_info values (1,110001,'2020-01-01 13:00:00','2020-01-01 13:10:00');
insert into test_network_bar_info values (1,110003,'2020-01-01 12:15:00','2020-01-01 13:15:00');

insert into test_network_bar_info values (2,110001,'2020-01-02 12:10:00','2020-01-02 12:30:00');
insert into test_network_bar_info values (2,110001,'2020-01-02 12:35:00','2020-01-02 12:40:00');
insert into test_network_bar_info values (2,110001,'2020-01-02 12:50:00','2020-01-02 12:55:00');
insert into test_network_bar_info values (2,110002,'2020-01-02 13:00:00','2020-01-02 13:10:00');

insert into test_network_bar_info values (3,110001,'2020-01-03 12:10:00','2020-01-03 12:30:00');
insert into test_network_bar_info values (3,110003,'2020-01-03 12:15:00','2020-01-03 12:40:00');
insert into test_network_bar_info values (3,110001,'2020-01-03 12:50:00','2020-01-03 12:55:00');
insert into test_network_bar_info values (3,110002,'2020-01-03 13:00:00','2020-01-03 13:10:00');

查詢邏輯

select
    C.user_group,
    COUNT(DISTINCT C.bar_id)
from
(select
    distinct
    A.bar_id,
    case when A.user_id > B.user_id then CONCAT(B.user_id,A.user_id)
    when A.user_id < B.user_id then CONCAT(A.user_id,B.user_id)
    else 'NA' end as user_group
from
(select
    bar_id,
    user_id,
    login_time,
    logout_time
from test_network_bar_info)A
inner join
(select
    bar_id,
    user_id,
    login_time,
    logout_time
from test_network_bar_info)B
on A.bar_id = B.bar_id
and A.user_id <> B.user_id
and (ABS(timestampdiff(second ,A.login_time,B.login_time)) <= 600
         or ABS(timestampdiff(second ,A.logout_time,B.logout_time)) <= 600))C
GROUP BY C.user_group;

https://blog.csdn.net/weixin_43619485/java/article/details/107164729