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

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

  •  5
  • 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 年前
        1
  •  4
  •   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)使索引无用。当索引位于原始值上时,您正在更改列的数据(暂时不将其写回表)。

    1. 直接比较能力: t1.value = t2.value
        2
  •  2
  •   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
    

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

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

        3
  •  2
  •   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

        4
  •  0
  •   Alen    14 年前

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

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

        5
  •  0
  •   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年后。请务必仔细记录您的代码。。。。

        6
  •  -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