1.找到破产玩家——交叉连接
label连续两次为1则代表玩家破产
解题思路:
- 使用交叉连接
- 找出连续时间
- 找到label同为1的记录
使用交叉连接
SELECT *
FROM pochan_01 AS A
CROSS JOIN pochan_01 AS B;
找出连续时间
SELECT *
FROM pochan_01 AS A
CROSS JOIN pochan_01 AS B
WHERE (EXTRACT(MINUTE FROM A.start_time) -
EXTRACT(MINUTE FROM B.start_time)) = -1;
找到label同为1的记录
SELECT *
FROM pochan_01 AS A
CROSS JOIN pochan_01 AS B
WHERE (EXTRACT(MINUTE FROM A.start_time) -
EXTRACT(MINUTE FROM B.start_time)) = -1
AND A.label = 1
AND B.label = 1;