网站开发主要步骤,商城网站建设制作,手机p图软件,网站搜索引擎优化方案的案例注#xff1a;参考文章#xff1a;
SQL之用户中两人一定认识的组合数--HQL面试题36【快手数仓面试题】_sql面试题-快手-CSDN博客文章浏览阅读1.2k次#xff0c;点赞3次#xff0c;收藏12次。目录0 需求分析1 数据准备2 数据分析3 小结0 需求分析设表名#xff1a;table0现…注参考文章
SQL之用户中两人一定认识的组合数--HQL面试题36【快手数仓面试题】_sql面试题-快手-CSDN博客文章浏览阅读1.2k次点赞3次收藏12次。目录0 需求分析1 数据准备2 数据分析3 小结0 需求分析设表名table0现有城市网吧访问数据字段网吧id访客id身份证号上线时间下线时间规则1、如果有两个用户在一家网吧的前后上下线时间在10分钟以内则两人可能认识规则2、如果这两个用户在三家以上网..._sql面试题-快手https://blog.csdn.net/godlovedaniel/article/details/119155757
0 问题描述 现有一张表table21, 里面装载城市网吧访问数据字段网吧id, 访客id(身份证号)上线时间、下线时间 规则1如果有两个用户在一家网吧的前后上线时间在10分钟内则两人可能认识 规则2如果这两个用户在三家以上网吧出现【规则1】的情况则两人一定认识 需求该城市上网用户中两人一定认识的组合数
1 数据准备
create table table21(wid string,uid string,ontime string,offtime string
)
row format delimited fields terminated by \t;insert overwrite table table21 values(1,110001,2020-01-01 11:10:00,2020-01-01 11:15:00)
,(1,110001,2020-01-01 11:18:00,2020-01-01 11:23:00)
,(1,110002,2020-01-01 12:10:00,2020-01-01 13:15:00)
,(1,110001,2020-01-01 12:11:00,2020-01-01 13:10:00)
,(1,110003,2020-01-01 12:15:00,2020-01-01 13:15:00)
,(1,110004,2020-01-01 12:16:00,2020-01-01 13:18:00),(2,110001,2020-01-02 12:10:00,2020-01-02 12:30:00)
,(2,110001,2020-01-02 12:50:00,2020-01-02 13:05:00)
,(2,110002,2020-01-02 12:52:00,2020-01-02 12:55:00)
,(2,110003,2020-01-02 12:58:00,2020-01-02 13:20:00)
,(2,110004,2020-01-02 13:00:00,2020-01-02 13:10:00),(3,110001,2020-01-03 12:10:00,2020-01-03 12:30:00)
,(3,110003,2020-01-03 12:55:00,2020-01-03 13:02:00)
,(3,110001,2020-01-03 12:50:00,2020-01-03 12:55:00)
,(3,110002,2020-01-03 13:00:00,2020-01-03 13:01:00)
,(3,110004,2020-01-03 12:58:00,2020-01-03 13:03:00)
,(3,110002,2020-01-03 13:20:00,2020-01-03 13:25:00);
2 数据分析 根据规则1和规则2求城市上网用户中两人一定认识的组合数就是指两两相识的组合数。对于这种两两组合数一般用自关联通过自关联将尽可能的情况表示出来然后按照条件筛选数据 step1:表自关联计算得到所有相遇的情况(笛卡尔积)
select *
from table21 as t0
join table21 as t1; step2根据规则1得出可能的结果 selectt0.wid as t0_wid,t0.uid as t0_uid,t1.wid as t1_wid,t1.uid as t1_uidfrom table21 as t0join table21 as t1where t0.wid t1.widand (abs(unix_timestamp(t0.ontime, yyyy-MM-dd HH:mm:ss)- unix_timestamp(t1.ontime, yyyy-MM-dd HH:mm:ss)) 600 orabs(unix_timestamp(t0.offtime, yyyy-MM-dd HH:mm:ss)- unix_timestamp(t1.offtime, yyyy-MM-dd HH:mm:ss)) 600)and t0.uid t1.uid 上述代码用到的函数 unix_timestamp(日期转时间戳函数) 语法unix_timestamp(string date) 、unix_timestamp(string date,string pattern) 返回值bigint 说明将格式为yyyy-MM-dd HH:mm:ss的日期 转换成 unix的时间戳。如果转换失败则返回值为0 举例select unix_timestamp(20240201 20:17:11,yyyyMMdd HH:mm:ss) -- 1706825843 abs(unix_timestamp(t0.ontime, yyyy-MM-dd HH:mm:ss) - unix_timestamp(t1.ontime, yyyy-MM-dd HH:mm:ss)) 600 代表的意思是两个用户在一家网吧的前后上线时间在10分钟内10分钟也就是600秒 ps 需要将同一网吧中可能两两相识的人筛选出来所以【用户A、用户B】 与【用户B、用户A】 实际上是一样的只需要选出 t0.uid t1.uid 即可去重取一
step3根据step2可以将同一网吧中可能两两相识的人筛选出来将互相认识的人组合成一个key通过该key来判断该两人是否满足规则2。具体sql如下 selectt0_wid,-- 将可能互相认识的人的uid拼接起来组成key值uuidconcat_ws(~, t0_uid, t1_uid) as uuid
from (selectt0.wid as t0_wid,t0.uid as t0_uid,t1.wid as t1_wid,t1.uid as t1_uidfrom table21 as t0join table21 as t1where t0.wid t1.widand (abs(unix_timestamp(t0.ontime, yyyy-MM-dd HH:mm:ss)- unix_timestamp(t1.ontime, yyyy-MM-dd HH:mm:ss)) 600 orabs(unix_timestamp(t0.offtime, yyyy-MM-dd HH:mm:ss)- unix_timestamp(t1.offtime, yyyy-MM-dd HH:mm:ss)) 600)and t0.uid t1.uid) t2 step4对【两人一定认识】记录进行打标签记为 1
selectuuid,-- 对【两人一定认识】记录进行打标签记为 1if(count(t0_wid) 3,1,0) as flagfrom
(selectt0_wid,-- 将可能互相认识的人的uid拼接起来组成key值uuidconcat_ws(~, t0_uid, t1_uid) as uuid
from (selectt0.wid as t0_wid,t0.uid as t0_uid,t1.wid as t1_wid,t1.uid as t1_uidfrom table21 as t0join table21 as t1where t0.wid t1.widand (abs(unix_timestamp(t0.ontime, yyyy-MM-dd HH:mm:ss)- unix_timestamp(t1.ontime, yyyy-MM-dd HH:mm:ss)) 600 orabs(unix_timestamp(t0.offtime, yyyy-MM-dd HH:mm:ss)- unix_timestamp(t1.offtime, yyyy-MM-dd HH:mm:ss)) 600)and t0.uid t1.uid) t2
)t3
group by uuid; step4计算满足规则1和规则2的记录总数得出结果为6条
selectcount(1) as cnt
from (selectuuid,-- 对【两人一定认识】记录进行打标签记为 1if(count(t0_wid) 3, 1, 0) as flagfrom (selectt0_wid,-- 将可能互相认识的人的uid拼接起来组成key值uuidconcat_ws(~, t0_uid, t1_uid) as uuidfrom (selectt0.wid as t0_wid,t0.uid as t0_uid,t1.wid as t1_wid,t1.uid as t1_uidfrom table21 as t0join table21 as t1where t0.wid t1.widand (abs(unix_timestamp(t0.ontime, yyyy-MM-dd HH:mm:ss)- unix_timestamp(t1.ontime, yyyy-MM-dd HH:mm:ss)) 600 orabs(unix_timestamp(t0.offtime, yyyy-MM-dd HH:mm:ss)- unix_timestamp(t1.offtime, yyyy-MM-dd HH:mm:ss)) 600)and t0.uid t1.uid) t2) t3group by uuid) t4;
3 小结 本案例题型属于“共同xx”例如共同好友、互相认识、共同使用等。遇到这类关键字的时候往往可以采用自关联的方式解决。笛卡尔积“一对多”或者“ 多对一”一般的解题步骤就是通过自关联将所有的组合求解出来然后将符合条件的数据进行过滤即可。
