用于查找访问者以及日期时间的 SQL 查询

Question

我的访客日志表有 id、访客、部门、vtime 领域。

    id  |   visitor |           Visittime    |     Department_id
    --------------------------------------------------------------
    1         1           2019-05-07 13:53:50           1 
    2         2           2019-05-07 13:56:54           1
    3         1           2019-05-07 14:54:10           3
    4         2           2019-05-08 13:54:49           1
    5         1           2019-05-08 13:58:15           1 
    6         2           2019-05-08 18:54:30           2
    7         1           2019-05-08 18:54:37           2

而且我已经有了以下索引

CREATE INDEX Idx_VisitorLog_Visitor_VisitTime_Includes ON VisitorLog 
    (Visitor, VisitTime) INCLUDE (DepartmentId, ID)

从上表 4 过滤器从用户界面、访客 1 和访客 2 以及访问开始时间和结束时间传递。

什么是部门访客 1 和访客 2 以及需要在 5 分钟内过滤的 VisitTime 差异

输出呼喊是

    id  |   visitor |           Visittime    |     Department_id
    --------------------------------------------------------------
    1         1           2019-05-07 13:53:50           1 
    2         2           2019-05-07 13:56:54           1
    4         2           2019-05-08 13:54:49           1
    5         1           2019-05-08 13:58:15           1 

为此，我使用了以下查询，

;with CTE1 AS(
Select id,visitor,Visittime,department_id from visitorlog where visitor=1
)
,CTE2 AS(
Select id,visitor,Visittime,department_id from visitorlog where visitor=2
)
select * from CTE2 V2
Inner join CTE1 V1 on V2.department_id=V1.department_id and DATEDIFF(minute,V2.Visittime,V1.Visittime)between -5 and 5**

上面的查询花费了太多时间来给出响应。因为在我的表中，有将近 2000 万条记录可用

任何人都可以为我的要求提出正确的方法。
提前致谢

Answer 1

这是一个完全修订的答案，基于您上面的附加信息。

在查看了上面的数据文件和您想要的结果之后，这似乎是提供结果的最简洁的方式。首先，我们需要一个不同的索引:

create index idx_POC_visitorlog on visitorlog
    (visitor, Department_id, Visittime) include(id);

使用此索引，我们可以将查询限制为仅传入的两个 ID。为了模拟这一点，我创建了变量来保存它们的值。此查询返回您要查找的数据。

DECLARE @Visitor1 int = 1,
    @Visitor2 int = 2

;with t as (
    select Department_id,
        dateadd(minute, -5, visittime) as EarlyTime,
        dateadd(minute, 5, Visittime) as LateTime,
        id
    from visitorlog
    where visitor = @Visitor1
    ),
v as (
    select v.id, 
        t.id as tid
    from visitorlog v
    INNER JOIN t 
        ON v.visitor = @Visitor2
        AND v.Department_id = t.Department_id
        and v.Visittime BETWEEN t.EarlyTime and t.LateTime
    )
SELECT *
FROM visitorlog vl
WHERE ID IN (
    SELECT v.id
    FROM v
    UNION
    SELECT v.tid
    FROM v
    )
ORDER BY visittime;