SQL面试之用户中两人一定认识的组合数

2021-12-12 15:40:11

需求分析

设表名：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

码农公寓

相关文章