时间戳

首页 > 解决方案 > 如何用相同的ID标记彼此在15秒内的交易？

sql - 如何用相同的ID标记彼此在15秒内的交易？

问题描述

我有一个包含以下列的数据集，例如：

终端ID 交易日期 交易时间
1 2020-10-01 05:49:40
1 2020-10-01 05:49:46
1 2020-10-01 06:20:05
2 2020-10-01 03:31:15
2 2020-10-01 03:31:25
2 2020-10-01 03:31:35

我想创建一个列，它为在同一个 TerminalID 上进行的那些交易提供一个公共 ID，并且彼此在 15 秒内。示例表最终应该是这样的：

终端ID 交易日期 交易时间 交叉ID
1 2020-10-01 05:49:40 1
1 2020-10-01 05:49:46 1
1 2020-10-01 06:20:05 2
2 2020-10-01 03:31:15 3
2 2020-10-01 03:31:25 3
2 2020-10-01 03:31:35 3

前两次交易是在 15 秒内在同一个终端上进行的（他们获得 crossID 1），而第三次终端交易发生的时间要晚得多（所以它获得 crossID 2，并且该终端上 15 秒内没有更多交易） . 最后三笔交易是在前一笔交易的 15 秒内完成的，因此即使在 2 号终端上的第一笔交易和最后一笔交易之间的间隔超过 15 秒，它们也会获得相同的 CrossID，因为它们之间有一个交易连接它们.

我已经使用 Pandas 和 Datetime 在 Python 中编写了一些工作代码，但是我的数据集太大而无法用 Python 加载，所以我需要使用 SQL（我只用于非常基本的查询）。任何帮助将非常感激。

标签： sqlsql-server

解决方案

假设这 15 分钟不跨越午夜，则使用 lag 获取上一次时间，并在差值大于 15 分钟时总结一个标志：

select t.*,
       sum(case when prev_transactionTime > dateadd(minute, -15, transactionTime) then 0 else 1 end) over
           (partition by terminalId order by transactionDate, transactionTime) as crossID
from (select t.*,
             lag(transactionTime) over (partition by terminalId, transactionDate order by transactionTime) as prev_transactionTime
      from t
     ) t;

如果 15 分钟的时间可以超过午夜，那么您应该真正修复数据，因此在一列中包含日期/时间。您可以使用以下方法解决此问题：

select t.*,
       sum(case when prev_transactionDateTime > dateadd(minute, -15, transactionDateTime) then 0 else 1 end) over
           (partition by terminalId order by transactionDateTime) as crossID
from (select t.*,
             lag(v.transactionDateTime) over (partition by t.terminalId order by v.transactionDateTime) as prev_transactionDateTime
      from t cross apply
           (values (convert(datetime, t.transactionDate) + convert(datetime, t.transactionTime))
           ) v(transactionDateTime)
     ) t;

SQL Server 允许datetime将两个值相加，这可能是组合它们的最简单方法。

推荐阅读