时间戳

首页 > 解决方案 > 用于创建会话 ID 的 SQL 查询

sql - 用于创建会话 ID 的 SQL 查询

问题描述

DeviceID    TimeStamp          EventType
1   2020-01-01 01:00:00.000  Arrival Event
1   2020-01-01 01:00:02.000  Departure Event
1   2020-01-01 02:00:02.000  Arrival Event
1   2020-01-01 02:00:05.000  Departure Event
1   2020-01-01 03:00:05.000  Arrival Event
1   2020-01-01 03:00:05.000  ActivityI
1   2020-01-01 03:00:05.000  ActivityI
1   2020-01-01 03:00:19.000  Departure Event
1   2020-01-01 04:00:19.000  Arrival Event
1   2020-01-01 04:00:20.000  Departure Event
1   2020-01-01 05:00:20.000  Arrival Event
1   2020-01-01 05:00:20.000  ActivityI
1   2020-01-01 05:00:20.000  ActivityI
1   2020-01-01 05:00:30.000  Departure Event
1   2020-01-01 06:00:30.000  Arrival Event
1   2020-01-01 06:00:33.000  Departure Event

我有上面的数据。每个事件都以到达开始，以离开结束。我想为每次到达创建 sessionid/transactionid。我如何在 sql 中做到这一点？

预期产出

DeviceID    TimeStamp          EventType               SessionID
1   2020-01-01 01:00:00.000  Arrival Event                 1
1   2020-01-01 01:00:02.000  Departure Event               1
1   2020-01-01 02:00:02.000  Arrival Event                 2
1   2020-01-01 02:00:05.000  Departure Event               2
1   2020-01-01 03:00:05.000  Arrival Event                 3
1   2020-01-01 03:00:05.000  ActivityI                     3
1   2020-01-01 03:00:05.000  ActivityI                     3
1   2020-01-01 03:00:19.000  Departure Event               3
1   2020-01-01 04:00:19.000  Arrival Event                 4
1   2020-01-01 04:00:20.000  Departure Event               4

提前致谢。

标签： sqlsql-servertsql

解决方案

您可以做一个窗口总和，每次到达时递增 1：

select 
    t.*,
    sum(case when eventType = 'Arrival Event' then 1 else 0 end) 
        over(partition by deviceID order by timestamp) sessionID
from mytable t

推荐阅读