代码之家 › 专栏 › 技术社区 › Michael J Swart

当时间不接近另一个表的时间时,SQLServer可以有效地过滤行

tsql sql-server-2005 sql-server sql

Michael J Swart · 技术社区 · 14 年前

我有两个表,在一个表中查找时间列所在的行不接近另一个表的时间列中的任何值。(Near定义为一分钟内)。

下面是一个代码示例:

create table temp1
(
    id int identity primary key,
    value datetime not null 
)
GO

create index ix_temp1 on temp1(value, id);
GO

set nocount on
insert temp1 (value) values (DATEADD(second, rand() * 1000000, '20100101'))
GO 15000

表temp2的设置相同:

create table temp2
(
    id int identity primary key,
    value datetime not null 
)
GO

create index ix_temp2 on temp2(value, id);
GO

set nocount on
insert temp2 (value) values (DATEADD(second, rand() * 1000000, '20100101'))
GO 15000

这是我第一次尝试(效率很低)

SELECT t1.id, t1.value
FROM temp1 t1
LEFT JOIN temp2 t2
    ON t1.value between DATEADD(MINUTE, -1, t2.value) and DATEADD(MINUTE, 1, t2.value)
WHERE t2.value is null

我在寻找更有效的方法。将考虑所有解决方案(新索引、SSIS解决方案、CLR解决方案、临时表、游标等)

6 回复 | 直到 14 年前

OMG Ponies 14 年前

this link for details .

SELECT t1.id,
       t1.value
  FROM temp1 t1
 WHERE NOT EXISTS(SELECT NULL
                    FROM temp2 t2
                   WHERE t2.value BETWEEN DATEADD(MINUTE, -1, t1.value)  
                                      AND DATEADD(MINUTE, 1, t1.value))

…仍然存在一个问题,即函数的使用(即:DATEADD)使索引无用。当索引位于原始值上时,您正在更改列的数据(暂时不将其写回表)。

直接比较能力: t1.value = t2.value

Nathan Wheeler 14 年前

这似乎做得很快:

SELECT t.id,
       t.value
FROM 
(
   SELECT t1.id, 
          t1.value, 
          (SELECT MIN(temp2.value) FROM temp2 WHERE temp2.value >= t1.value) as theNext, 
          (SELECT MAX(temp2.value) FROM temp2 WHERE temp2.value <= t1.value) as thePrev
   FROM temp1 t1
) t 
WHERE DATEDIFF(second, t.value, t.theNext) > 60 
  AND DATEDIFF(second, t.thePrev, t.value) > 60

而且不需要对表进行任何重组。

一定要用秒来比较,因为分钟是四舍五入的。这在我的机器上运行不到一秒钟,使用您的表创建规范。

Community CDub 5 年前

答案重写

对于原始查询,将连接条件从

LEFT JOIN temp2 t2
 ON t1.value BETWEEN DATEADD(MINUTE, -1, t2.value) AND DATEADD(MINUTE, 1, t2.value)

到

LEFT JOIN temp2 t2
 ON t2.value BETWEEN DATEADD(MINUTE, -1, t1.value) AND DATEADD(MINUTE, 1, t1.value)

有很大的不同。

在这两种情况下,它都有一个temp1上的扫描作为嵌套循环的外部输入迭代器。但是,对于第一种情况,temp2上的条件是不可搜索的,因此需要进行扫描每行对索引进行范围搜索以检索匹配的行。

然而 Not Exists 解决方案符合 @OMG's answer more efficient in SQL Server

执行计划:

ExecutionPlans http://img812.imageshack.us/img812/457/executionplans.jpg

Alen 14 年前

我的第一个建议是把这个交给一个开发人员,让他们用C或C编写一个算法#

否则,这里有一个想法。获取表中的原始数据,并在正负一分钟内创建新行。如果你用秒的话可能会有很多数据。然后将它与第二个表中的数据进行比较

John Murdoch 14 年前

我通过将DateTime值转换为自2000年1月1日以来的整数分钟数,并将该值写入数据库表中的一列来处理类似的问题。因此(在您的情况下)该表如下所示:

create table temp2
(
    id int identity primary key,
    timeValue int not null
)

要与此表进行比较,只需将比较值转换为整数分钟数(我为此使用了一个用户定义的函数)并进行比较。

DECLARE @newTime int;
SET @newTime = dbo.fnGetComparisonTime(@DateTimeValue)

SELECT id, timeValue 
FROM temp2
WHERE timeValue NOT BETWEEN (@newTime - 1) AND @newTime;

以及将时间转换为整数分钟的函数?

CREATE FUNCTION dbo.fnGetComparisonTime
    (
        @DateTimeValue datetime
    )
RETURNS int
AS
BEGIN
    -- Declarations
    DECLARE @Output int
    DECLARE @StartDate datetime

    SET @StartDate = '2000-01-01 00:00:00'
    SET @Output = DATEDIFF(minute, @StartDate, @ReportDateTime)    

    -- And we're done!
    RETURN @Output

END

当然,您可以使用SELECT语句来获得所需的结果。将DateTime值转换为分钟比直接用日期处理要快得多。

你可能会问——这有没有千年虫的问题?(毕竟,在31^2-1分钟的时候,你的时间就用完了。)是的——大约7000年后。请务必仔细记录您的代码。。。。

-1

Ryan Cooper 14 年前

;带时间(ID,表1时间,表2时间) 作为 ( 选择t1.id,t1.value作为表1\u时间,t2.value作为表2\u时间从temp1 t1 内部连接temp2 t2 ON YEAR(t1.value)=YEAR(t2.value) 和天(t1.值)=天(t2.值)

选择交易代码, 表1\u时间从时间其中DATEDIFF(ss,Table1\u Time,Table2\u Time)<61