sql - 除非有很大的差距,否则如何按时间对行进行分组?
问题描述
问题域:我(嗯,企业)有一个接受公共连接的 wifi 网络。我们想知道每台设备与每个接入点 (AP) 保持连接的时间。这被称为“停留时间”。这个问题很复杂,因为设备可以而且通常确实在白天在 AP 之间移动,并且经常会不止一次地回到其中的许多 AP 上。
我们目前使用 Splunk 作为我们的数据捕获和报告工具,它会自动执行此操作,但我们正在考虑迁移到 AWS,因此需要使用 ETL 和 SQL 的组合重新构建所有内容。
我的数据如下所示:
rowID clientMAC apMAC timeSeen
100 1 a 12:01
101 1 a 12:03
102 1 a 12:05
103 1 b 12:10
104 1 b 12:20
105 2 a 12:20
106 2 a 12:22
107 1 a 13:00
108 1 a 13:02
109 1 a 13:06
110 1 a 13:12
我的挑战是报告每个 clientAP+macAP 示例的持续时间,例如,clientMAC=1
连接到apMAC=a
.
我不能timeSeen
将最初的最终timeSeen
作为clientMAC=1
连接到apMAC=b
中间,因此结果也将包括该连接的时间。
我需要做的简单的英语逻辑是:
对于 和 的每个分组clientMAC
,apMAC
确定所选时间段内的连接持续时间。如果具有相同组合的行之间存在 15 分钟的间隔,则开始新的持续时间计算并关闭旧的持续时间。clientMAC
本质上,在给定中看到的每组给定apMAC
应该是一个单独的“事务”并报告为单行。
所以所需的输出类似于:
clientMAC apMAC Duration
1 a ...
1 b ...
2 a ...
1 a ...
解决方案
一个不使用 LAG() 的版本,因此可以在旧版本的 SQL 上工作(LAG 是 SQL Server 2012 以后的版本),以防万一。我有很多客户仍在使用 SQL Server 2008,并且经常需要适用于旧版本的解决方案,因此其他人可能也需要相同的解决方案!
这个例子包括创建一些测试数据,所以你可以看到它的工作和结果
-- Create a temp table to hold the test data
CREATE TABLE #TestData
(
rowID INT NOT NULL PRIMARY KEY,
clientMAC INT NOT NULL,
apMAC VARCHAR(1) NOT NULL,
timeSeen DATETIME NOT NULL
)
-- Create some test data
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (100, 1, 'a', '2019-Nov-01 12:01:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (101, 1, 'a', '2019-Nov-01 12:02:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (102, 1, 'a', '2019-Nov-01 12:05:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (103, 1, 'b', '2019-Nov-01 12:10:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (104, 1, 'b', '2019-Nov-01 12:20:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (105, 2, 'a', '2019-Nov-01 12:20:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (106, 2, 'a', '2019-Nov-01 12:22:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (107, 1, 'a', '2019-Nov-01 13:00:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (108, 1, 'a', '2019-Nov-01 13:02:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (109, 1, 'a', '2019-Nov-01 13:06:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (110, 1, 'a', '2019-Nov-01 13:12:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (111, 1, 'a', '2019-Nov-01 14:00:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (112, 1, 'a', '2019-Nov-01 14:12:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (113, 1, 'a', '2019-Nov-01 14:14:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (114, 1, 'a', '2019-Nov-01 14:30:00')
INSERT INTO #TestData (rowID, clientMAC, apMAC, timeSeen) VALUES (115, 1, 'a', '2019-Nov-01 14:35:00')
-- Start of Actual Code
-- Store our maximum allowed gap in minuutes into a variable
DECLARE @MaximumGapMinutes INT = 15
-- Create temp table for primary calculated data
CREATE TABLE #DwellTimes (
rowID INT NOT NULL PRIMARY KEY,
clientMAC INT NOT NULL,
apMAC VARCHAR(1) NOT NULL,
timeSeen DATETIME NOT NULL,
lastSeen DATETIME NOT NULL,
DwellTime INT NOT NULL
)
-- Populate temp table
INSERT INTO #DwellTimes
SELECT *, DateDiff(MINUTE, lastSeen, timeSeen) AS DwellTime
FROM (
SELECT *, IsNull((SELECT TOP 1 timeSeen
FROM #TestData TDInner
WHERE TDInner.clientMac = TDMain.clientMac AND TDInner.apMac = TDMain.apMac
AND TDInner.timeSeen < TDMain.timeSeen
ORDER BY timeSeen DESC
), timeSeen) AS lastSeen
FROM #TestData TDMain
) InnerTable
-- Calculate the Dwell Time for visits, counting gaps longer than @MaximumGapMinutes as a new visit
SELECT Min(timeSeen) AS StartTime, clientMac, apMac,
SUM(CASE WHEN DwellTime > @MaximumGapMinutes THEN 0 ELSE DwellTime END) AS DwellTime
FROM (
SELECT *, (SELECT COUNT(*)
FROM #DwellTimes DSub
WHERE DSub.clientMac = DMain.clientMac AND DSub.apMac = DMain.apMac
AND DSub.timeSeen <= DMain.timeSeen AND DSub.DwellTime > 15) AS GapNumber
FROM #DwellTimes DMain
) InnerTable
GROUP BY clientMac, apMac, GapNumber
ORDER BY StartTime, clientMAC, apMAC, DwellTime
-- Clean up after ourselves
DROP TABLE #DwellTimes
-- End of Actual Code
-- Clean up after ourselves
DROP TABLE #TestData
结果:-
解释它是如何工作的。
在实际代码本身中,而不是测试数据准备中,我们要做的第一件事是声明一个变量,以使我们的最大间隔时间仍被视为同一访问的一部分
-- Store our maximum allowed gap in minuutes into a variable
DECLARE @MaximumGapMinutes INT = 15
然后我们创建一个临时表来保存 Dwell Time 计算,并填充它
-- Populate temp table
INSERT INTO #DwellTimes
SELECT *, DateDiff(MINUTE, lastSeen, timeSeen) AS DwellTime
FROM (
SELECT *, IsNull((SELECT TOP 1 timeSeen
FROM #TestData TDInner
WHERE TDInner.clientMac = TDMain.clientMac AND TDInner.apMac = TDMain.apMac
AND TDInner.timeSeen < TDMain.timeSeen
ORDER BY timeSeen DESC
), timeSeen) AS lastSeen
FROM #TestData TDMain
) InnerTable
内部选择查找该 clientMac 和 apMac 的上一个 timeSeen。如果没有看到以前的时间,那么它使用当前 timeSeen(IsNull(subselect, timeSeen) 部分)。
然后,外部选择计算同一 clientMac 和 apMac 的当前 timeSeen 和前一个 (lastSeen) 之间的停留时间。因为如果之前没有访问,我们使用当前 timeSeen 作为 lastSeen,如果这是第一次访问,则 Dwell Time 将为零。
结果存储在#DwellTimes
最后,我们计算实际访问次数和停留时间,将比我们的最大值更长的时间间隔作为新访问。
-- Calculate the Dwell Time for visits, counting gaps longer than @MaximumGapMinutes as a new visit
SELECT Min(timeSeen) AS StartTime, clientMac, apMac,
SUM(CASE WHEN DwellTime > @MaximumGapMinutes THEN 0 ELSE DwellTime END) AS DwellTime
FROM (
SELECT *, (SELECT COUNT(*)
FROM #DwellTimes DSub
WHERE DSub.clientMac = DMain.clientMac AND DSub.apMac = DMain.apMac
AND DSub.timeSeen <= DMain.timeSeen AND DSub.DwellTime > 15) AS GapNumber
FROM #DwellTimes DMain
) InnerTable
GROUP BY clientMac, apMac, GapNumber
ORDER BY StartTime, clientMAC, apMAC, DwellTime
这里的内部选择添加了一个字段GapNumber,我们可以将其用作分组字段。这只是计算有多少以前的记录超过了我们的最大值,包括当前记录。因此,如果当前记录超过最大值,则开始新的访问。
带间隙数的结果
最后,按 clientMac、apMac 和 GapNumber 分组允许我们使用 DwellTime 的总和作为每次访问的 Dwell Time,前提是如果 GapNumber 大于我们的最大值,我们将其设置为 0,因为这将是访问的开始
SUM(CASE WHEN DwellTime > @MaximumGapMinutes THEN 0 ELSE DwellTime END) AS DwellTime
希望这对某人有用!
推荐阅读
- docker - 如何让 Kubernetes 管理我的 Pod 日志文件?
- node.js - NodeJs 故障排除挂起控制台
- php - 从 MySQL INSERT 查询字符串中提取括号内的值
- angularjs - 这里的循环依赖是什么?
- javascript - HTML CSS : 获取 CDN SVG 图片并将它们转换为 PNG
- postgresql - 如何运行 pg_dump?它因“pg_dump: [archiver (db)] query failed: Р?РЁР?Р'”而失败
- android - 从 firebase 数据库中读取特定的子值
- javascript - 为什么简单的javascript字符串比较不起作用?
- julia - 如何在 Mac 中访问 Julia 的包内容?在 Windows 中很容易找到包内容,但在 Mac 中找不到
- jpa - 通过 JPA 自动保留 LocalDateTime