sql - 表中非连续行之间的日期差异

Question

我想从下面的表 1 中取时间差的平均值。这些值不连续，有时时间值会重复，所以我需要 1) 按时间排序，2) 丢弃非唯一值，3) 执行时间差(以毫秒为单位)，然后 4) 平均产生的时间差值。此外，我想 5) 将 datediff 操作限制在选定的时间范围内，例如
WHERE _TimeStamp >= '20091220 11:59:56.1' AND _TimeStamp <= _TimeStamp >= '20091220 11:59:56.8'。我很困惑如何把这一切放在一起!

表格1:
_时间戳
2009-12-20 11:59:56.0
2009-12-20 11:59:56.5
2009-12-20 11:59:56.3
2009-12-20 11:59:56.4
2009-12-20 11:59:56.4
2009-12-20 11:59:56.9

Answer 1

这是一种有效且不难看的方法:

;WITH Time_CTE AS
(
    SELECT
        MIN(_Timestamp) AS dt,
        ROW_NUMBER() OVER (ORDER BY MIN(_Timestamp)) AS RowNum
    FROM Table1
    GROUP BY _Timestamp
)
SELECT
    t1.dt AS StartDate,
    t2.dt AS EndDate,
    DATEDIFF(MS, t1.dt, t2.dt) AS Elapsed
FROM Time_CTE t1
INNER JOIN Time_CTE t2
ON t2.RowNum = t1.RowNum + 1

将从您的示例中为您提供以下输出:

StartDate               | EndDate                 | Elapsed
------------------------+-------------------------+--------
2009-12-20 11:59:56.000 | 2009-12-20 11:59:56.300 | 300
2009-12-20 11:59:56.300 | 2009-12-20 11:59:56.400 | 100
2009-12-20 11:59:56.400 | 2009-12-20 11:59:56.500 | 100
2009-12-20 11:59:56.500 | 2009-12-20 11:59:56.900 | 400

编辑:如果您想限制时间范围，只需添加 WHERE _Timestamp BETWEEN @StartDate AND @EndDate之前 GROUP BY线。

Edit2:如果你想要平均值，那么改变最后的 SELECT t1.dt, ...声明:

SELECT AVG(DATEDIFF(MS, t1.dt, t2.dt))
FROM Time_CTE t1 ... (same as above)