需求分析
设表名:table0
现有城市网吧访问数据,字段:网吧id,访客id(身份证号),上线时间,下线时间:
规则1、如果有两个用户在一家网吧的前后上下线时间在10分钟以内,则两人可能认识
规则2、如果这两个用户在三家以上网吧出现【规则1】的情况,则两人一定认识
需求:
该城市上网用户中两人一定认识的组合数。
数据准备
注:以下使用oracle实现的
DROP TABLE "STARPOWER"."TABLE0"; CREATE TABLE "STARPOWER"."TABLE0" ( "WID" NUMBER, "UUID" VARCHAR2(100 BYTE), "ONTIME" DATE, "OFFTIME" DATE ) TABLESPACE "STARPOWER" LOGGING NOCOMPRESS PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT ) PARALLEL 1 NOCACHE DISABLE ROW MOVEMENT ; -- ---------------------------- -- Records of TABLE0 -- ---------------------------- INSERT INTO "STARPOWER"."TABLE0" VALUES ('1', '110001', TO_DATE('2020-01-01 12:10:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-01 12:30:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('1', '110001', TO_DATE('2020-01-01 12:35:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-01 12:40:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('1', '110002', TO_DATE('2020-01-01 12:50:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-01 12:55:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('1', '110001', TO_DATE('2020-01-01 13:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-01 13:10:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('1', '110003', TO_DATE('2020-01-01 12:15:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-01 13:15:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('2', '110001', TO_DATE('2020-01-02 12:10:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-02 12:30:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('2', '110001', TO_DATE('2020-01-02 12:35:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-02 12:40:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('2', '110002', TO_DATE('2020-01-02 12:50:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-02 12:55:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('2', '110003', TO_DATE('2020-01-02 13:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-02 13:00:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('3', '110001', TO_DATE('2020-01-03 12:10:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-03 12:30:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('3', '110003', TO_DATE('2020-01-03 12:15:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-03 12:40:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('3', '110001', TO_DATE('2020-01-03 12:50:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-03 12:55:00', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO "STARPOWER"."TABLE0" VALUES ('3', '110002', TO_DATE('2020-01-03 13:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-01-03 13:10:00', 'SYYYY-MM-DD HH24:MI:SS'));
数据分析
先理解题意,题中给出了规则1和规则2实际上就是两个条件,需要去创造这两个条件去获取结果。如何创造呢?首先我们看规则1,如果有两个用户在一家网吧的前后上下线时间在10分钟以内,则两人可能认识。意思就是两个用户在一家网吧上线时间和下线时间都不能超过10min钟,那么计算时候就需要取相邻的上线时间和下线时间进行差值计算,算出每个用户针对上一个用户上线时间差和下线时间差,如果上线时间差值和下线时间差值均小于10分钟我们可以判断满足规则1.
最终sql
select count(uuid) as com_cnt from( SELECT uuid, count(1) AS flag FROM ( select wid ,uuid ,lag(uuid,1,'uuidxxxx')over(partition by wid order by ontime) ,to_number(abs(ontime-lag(ontime,1,to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')) over(partition by wid order by ontime)))*1440 ontime_diff ,to_number(abs(offtime-lag(offtime,1,to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')) over(partition by wid order by offtime)))*1440 offtime_diff from table0 )m WHERE ONTIME_diff<=10 AND offtime_diff<=10 --求出上线和下线时间差小于10min GROUP BY uuid --按用户分组统计 )n WHERE flag>=3 --根据规则2判断出现网吧次数大于2的用户
最终结果:
参考原文:https://mp.weixin.qq.com/s/U1YiZF8eRlF7yQtCXxEQzw