sql - SQL查询与日期时间一起查找访客
问题描述
我的访客日志表有id、visitor、department、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 以及 VisitTime 差异在 5 分钟内需要过滤
输出喊be
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 万条记录可用
任何人都可以为我的要求提出正确的方法。提前致谢
解决方案
根据您上面的其他信息,这是一个完全修改过的答案。
在查看了上面的数据文件和您想要的结果之后,这似乎是提供结果的最干净的方式。首先,我们需要一个不同的索引:
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;
推荐阅读
- c# - 将所有基本调用包装在派生类型中
- python-3.x - 如何在某些条件下从某些列表中获取最大值?
- node.js - 客户端(react)和服务器(nodejs)在同一个文件夹中时的问题
- javascript - 第二个异步函数取决于第一个
- html - 如何修改引导程序的卡片列?
- javascript - 在 addEventListener 函数中给出时 removeEventListener 不起作用
- c - 我的老师做了这个功能,但我不明白它有什么用
- java - 如何使用列表实现深度优先遍历测试?
- python - 从列表中计算并将输出打印为表格
- r - r - Average timestamps excluding the day