mysql - 如何根据时间间隔生成序列号

Question

我有以下数据:

---------------------------------------------------------| IDUSER  | ACCESS_TIME            | IPLOG    | SESSIONX    |---------------------------------------------------------|1        |  2018-02-05 04:01:17   |  1.2.3.4 |             ||1        |  2018-02-05 04:05:00   |  1.2.3.4 |             ||1        |  2018-02-05 04:40:00   |  1.2.3.4 |             ||1        |  2018-02-05 07:00:14   |  1.2.3.4 |             ||1        |  2018-02-05 07:50:14   |  1.2.3.5 |             ||2        |  2018-02-05 08:20:20   |  1.2.3.5 |             ||2        |  2018-02-05 08:35:00   |  1.2.3.5 |             ||2        |  2018-02-05 08:45:20   |  1.2.3.6 |             ||2        |  2018-02-05 09:35:00   |  1.2.3.6 |             |

目标是知道多少次(SESSIONX)基于IDUSER、ACCESS_TIME、IPLOG的用户日志。遵循以下规则:

• 如果新 session 的访问时间间隔超过 30 分钟(> 30 分钟)但具有相同的 iplog，则新 session 会增加 (+1)

• 如果用户使用 iplog 的访问不同(新)，即使在 30 分钟内(<30 分钟)从上一次开始计算新 session (+1)。

如何利用上述规则进行 session 时间顺序的查询？ ，所以输出如下

---------------------------------------------------------| IDUSER  | ACCESS_TIME            | IPLOG    | SESSIONX    |---------------------------------------------------------|1        |  2018-02-05 04:01:17   |  1.2.3.4 | 1            ||1        |  2018-02-05 04:05:00   |  1.2.3.4 | 1            ||1        |  2018-02-05 04:40:00   |  1.2.3.4 | 2            ||1        |  2018-02-05 07:00:14   |  1.2.3.4 | 3            ||1        |  2018-02-05 07:50:14   |  1.2.3.5 | 4            ||2        |  2018-02-05 08:20:20   |  1.2.3.5 | 1            ||2        |  2018-02-05 08:35:00   |  1.2.3.5 | 1            ||2        |  2018-02-05 08:45:20   |  1.2.3.6 | 2            ||2        |  2018-02-05 09:35:00   |  1.2.3.6 | 3            |

Answer 1

SELECT IDUSER,
       ACCESS_TIME,
       IPLOG,
       SESSIONX
FROM
(SELECT 
T.IDUSER,
T.ACCESS_TIME,
T.IPLOG,
TIMESTAMPDIFF(MINUTE,@date,T.ACCESS_TIME),
CASE WHEN @id != T.IDUSER THEN @num := 1 END,
CASE WHEN @iplog != T.IPLOG OR TIMESTAMPDIFF(MINUTE,@date,T.ACCESS_TIME) > 30 
THEN @num := @num + 1 
ELSE
@num
END AS SESSIONX,
@date := T.ACCESS_TIME AS VarDate,
@id := T.IDUSER AS VarIDUSER,
@iplog := T.IPLOG AS VarIPLOG
FROM
Table1 T,(SELECT @num := 1,@date := null,@id := null,@iplog := null) R)T1

输出

IDUSER  ACCESS_TIME         IPLOG     SESSIONX
1   2018-02-05T04:01:17Z    1.2.3.4   1
1   2018-02-05T04:05:00Z    1.2.3.4   1
1   2018-02-05T04:40:00Z    1.2.3.4   2
1   2018-02-05T07:00:14Z    1.2.3.4   3
1   2018-02-05T07:50:14Z    1.2.3.5   4
2   2018-02-05T08:20:20Z    1.2.3.5   1
2   2018-02-05T08:35:00Z    1.2.3.5   1
2   2018-02-05T08:45:20Z    1.2.3.6   2
2   2018-02-05T09:35:00Z    1.2.3.6   3

演示

http://sqlfiddle.com/#!9/44063e/42