sql-server - 选择忽略狭窄时间的行

Question

我有一个表[EventLog]，其中包含由控制门的读卡器记录的读取数据。然而，当持卡人靠近读卡器一段时间时，相同的卡代码[epc]可以被多次读取。

我想在同一个阅读器上显示相同代码的读取，但忽略例如 2 分钟的读取。

示例:事件日志

ID  EPC ReaderID    LogTime
1   1234    1   2016-04-15 12:33:55
2   1234    1   2016-04-15 12:34:05
3   1234    1   2016-04-15 12:34:10
4   4321    2   2016-04-15 12:34:12
5   4321    2   2016-04-15 12:34:14

期望的结果:

ID  EPC ReaderID    LogTime
1   1234    1   2016-04-15 12:33:55
4   4321    2   2016-04-15 12:34:12

我现在使用的是 Windows 函数 LAG 来确定每次读取与前一次读取之间的分钟差异:

SELECT EPC, ReaderName, PersonName, LogTime
FROM (
SELECT EPC, ReaderName, PersonName, LogTime,
    DATEDIFF(MINUTE, LAG(LogTime) OVER (PARTITION BY EPC, ReaderID ORDER BY LogTime), LogTime) diff_prev
FROM EventLog l
    LEFT OUTER JOIN Person p ON p.EPC = l.EPC
    INNER JOIN Reader r ON r.ID = l.ReaderID
) tbl
WHERE diff_prev IS NULL OR diff_prev >= @ignoreMinutes
ORDER BY LogTime

其中@ignoreMinutes是一个参数，指定忽略同一次读取的分钟数。

但是，如果每秒读卡一次，持续 3 小时，则此解决方案不正确。例如:

ID  EPC ReaderID    LogTime     diff_prev
1   1234    1   2016-04-15 12:33:55     NULL
2   1234    1   2016-04-15 12:34:05     0
3   1234    1   2016-04-15 12:34:10     0
4   1234    1   2016-04-15 12:34:32     0
5   1234    1   2016-04-15 12:34:54     0
6   1234    1   2016-04-15 12:35:14     0
7   1234    1   2016-04-15 12:35:34     0
8   1234    1   2016-04-15 12:35:54     0
9   1234    1   2016-04-15 12:36:04     0
10  1234    1   2016-04-15 12:36:15     0
11   4321   2   2016-04-15 12:44:12     NULL
12   4321   2   2016-04-15 12:44:14     0

如您所见，我的解决方案在使用 @ignoreMinutes = 1 执行时，将导致仅选择 2 行 ID = 1, 11，因为其余的都是 diff_prev = 0。但正确的结果集应该是ID = 1, 6, 10, 11

你能帮忙吗？谢谢!

Answer 1

这是我想出的一个“候选”解决方案。至少它在您的最后一个示例中可以正常工作，返回记录 1、6、10、11。

DECLARE @intervalSeconds INT
SET @intervalSeconds = 60;

WITH EL AS
(
    -- Select first record for each EPC, this is the baseline for recursion
    SELECT
        ID,
        EPC,
        LogTime
    FROM EventLog
    WHERE LogTime = (SELECT MIN(LogTime) FROM EventLog IEL WHERE IEL.EPC = EventLog.EPC)
    -- Add following events
    UNION ALL
    SELECT
        ID,
        EPC,
        LogTime
    FROM
    (
        SELECT
            NextEvent.ID,
            NextEvent.EPC,
            NextEvent.LogTime,
            ROW_NUMBER() OVER(PARTITION BY NextEvent.EPC ORDER BY NextEvent.LogTime) eventNumber
        FROM EventLog NextEvent
        JOIN
        (
            SELECT 
                ID,
                ROW_NUMBER() OVER(PARTITION BY EPC ORDER BY LogTime DESC) eventNumber, -- Reverse numbering to get last row by readNumber = 1
                EPC,
                LogTime
            FROM EL -- Recursion
        ) PreviousEvent -- Here we have all already selected events wich we're interested in
            ON PreviousEvent.EPC = NextEvent.EPC
                AND PreviousEvent.eventNumber = 1 -- We need only the last one for each EPC
        WHERE DATEDIFF(SECOND, PreviousEvent.LogTime, NextEvent.LogTime) > @intervalSeconds

    ) NextCandidateEvents -- Here we have all events with desired interval offset for each EPC
    WHERE NextCandidateEvents.eventNumber = 1 -- We need only the first one for each EPC
)
SELECT * FROM EL
ORDER BY EPC, LogTime