计算两行之间的时差 [英] Calculate Time Difference Between Two Rows

问题描述

我有一个包含以下内容的表格：

I have a table that contains the following:

DataDate                 Value
2010-03-01 08:31:32.000  100
2010-03-01 08:31:40.000  110
2010-03-01 08:31:42.000  95
2010-03-01 08:31:45.000  101
.                        .
.                        .
.                        .

我需要将值列乘以当前行和上一行之间的时间差，

I need to multiply the value column by the difference in time between the current and previous rows and sum that for the entire day.

我目前设置的数据每10秒钟一次，这使得在查询中进行简单的转换：

I currently have the data set up to come in every 10 seconds which makes for a simple conversion in the query:

SELECT Sum((Value/6) FROM History WHERE DataDate BETWEEN @startDate and @endDate

其中@startDate和@endDate今天的日期为00:00:00和11:59:59。

Where @startDate and @endDate are today's date at 00:00:00 and 11:59:59.

在我设置每10秒收集的数据之前，每当值改变时收集数据。在时间方面没有任何重复条目，最小时间差为1秒。

Before I set the data to be collected every 10 seconds it was collected whenever the Value changed. There aren't any duplicate entries in terms of time, the minimum time difference is 1 second.

如果我不知道读数之间的时间间隔，那么如何设置查询以获取行之间的经过时间？

How can I set up a query to get the elapsed time between rows for the case when I don't know the time interval between readings?

我我使用SQL Server 2005.

I am using SQL Server 2005.

推荐答案

WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY DataDate) AS rn
        FROM    mytable
        )
SELECT  DATEDIFF(second, mc.DataDate, mp.DataDate)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

在SQL Server 2012 +中：

In SQL Server 2012+:

SELECT  DATEDIFF(second, pDataDate, dataDate)
FROM    (
        SELECT  *,
                LAG(dataDate) OVER (ORDER BY dataDate) pDataDate
        FROM    rows
        ) q
WHERE   pDataDate IS NOT NULL

这篇关于计算两行之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！