我可以在没有游标的情况下编写这个查询吗?

I am trying to find out the number of events that happened within a threshold time of timestamps found in another table for the same category. What is the fastest way to vary delta (in the case given below, delta is 5 minutes)? I just tested an approach using cursor (set a variable to 5 and then keep incrementing and executing the same query) but it is taking 10 seconds for each iteration. In my actual data, number of rows in #EVENTS is approximately equal to 100K and #CHANGES is about 500K.

我试图找出在同一类别的另一个表中找到的时间戳阈值时间内发生的事件的数量。改变增量的最快方法是什么(在下面的例子中，增量是5分钟)?我刚刚用游标测试了一种方法(将变量设置为5，然后继续递增并执行相同的查询)，但是每次迭代都需要10秒。在我的实际数据中，#EVENTS中的行数约为100K， #更改约为500K。

My tables are as follows:

我的表如下:

CREATE TABLE #EVENTS(Category varchar(20), Timestamp datetime)
GO

INSERT INTO #EVENTS VALUES('A', '2013-01-23 05:02:00.000')
INSERT INTO #EVENTS VALUES('A', '2013-01-23 05:04:00.000')
INSERT INTO #EVENTS VALUES('B', '2013-01-23 05:03:00.000')
INSERT INTO #EVENTS VALUES('B', '2013-01-21 05:02:00.000')
GO

CREATE TABLE #CHANGES(Category varchar(10), Timestamp datetime)
GO

INSERT INTO #CHANGES VALUES('A', '2013-01-23 05:00:00.000')
INSERT INTO #CHANGES VALUES('B', '2013-01-21 05:05:00.000')

SELECT *
FROM
(
    SELECT X.Category, X.Timestamp, Y.Timestamp BeforeT, DATEADD(MINUTE, 5, Y.Timestamp) AfterT
    FROM #EVENTS X, #CHANGES Y
    WHERE X.Category = Y.Category
) X
WHERE X.Timestamp BETWEEN BeforeT AND AfterT

DROP TABLE #CHANGES
DROP TABLE #EVENTS
GO

1 个解决方案

#1

Is this what you are looking for? It does a cross join to a CTE that defines the deltas:

这就是你要找的吗?它与CTE交叉连接，定义了delta:

with deltas as (
     select 5 as delta union all
     select 10 union all
     select 20
)
SELECT *
FROM (SELECT e.Category, e.Timestamp, c.Timestamp BeforeT,
             DATEADD(MINUTE, deltas.delta, c.Timestamp) AfterT,
             deltas.delta
     FROM #EVENTS e join
          #CHANGES c
          on e.Category = c.Category cross join
          deltas
    ) X
WHERE X.Timestamp BETWEEN BeforeT AND AfterT

I also fixed your aliases. Queries read much better when the aliases are related to the underlying table name.

我也修改了你的别名。当别名与底层表名相关时，查询读起来会更好。

#1