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

xn1cxnb4 于 2021-06-25 发布在 Mysql

关注(0)|答案(1)|浏览(415)

我有以下数据：

---------------------------------------------------------
| 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 |             |

目标是根据iduser、access\u time、iplog知道用户日志的次数（sessionx）。遵循以下规则：
如果新会话的访问间隔超过30分钟（>30分钟），但具有相同的iplog，则新会话增加（+1）
如果用户对iplog的访问不同（新），即使与上一次不到30分钟（<30分钟），则计算新会话（+1）。
如何用上述规则查询会话时间序列，所以输出如下

---------------------------------------------------------
| 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            |

sql mysql session sequence

来源：https://stackoverflow.com/questions/49913472/how-to-generate-sequence-numbers-based-on-time-intervals

1条答案

按热度按时间

kokeuurv1#

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号

赞(0）回复(0）举报 2021-06-26

我来回答

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

1条答案

相关问题

热门标签

最新问答