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.
我需要将值列乘以当前行和前行之间的时间差,然后对这一项进行一整天的求和。
I currently have the data set up to come in every 10 seconds which makes for a simple conversion in the query:
我目前设置的数据每10秒就会出现一次,这使得查询的转换变得非常简单:
SELECT Sum((Value/6) FROM History WHERE DataDate BETWEEN @startDate and @endDate
Where @startDate and @endDate are today's date at 00:00:00 and 11:59:59.
@startDate和@endDate是今天在00:00:00和11:59:59的日期。
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.
在我将数据设置为每10秒收集一次之前,每当值发生变化时,它都会被收集。时间上没有重复的条目,最小的时间差是1秒。
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?
当我不知道读取之间的时间间隔时,如何设置查询来获取行之间的运行时间?
I am using SQL Server 2005.
我正在使用SQL Server 2005。
2 个解决方案
#1
120
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
In SQL Server 2012+:
在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
#2
2
A little tweak on Quassnoi's query if you prefer not to use a Subselect would be:
如果您不喜欢使用子选择,那么对Quassnoi的查询做一个小小的调整就是:
SELECT
DATEDIFF(second, LAG(dataDate) OVER (ORDER BY dataDate), dataDate)
FROM rows
WHERE LAG(dataDate) OVER (ORDER BY dataDate) IS NOT NULL
#1
120
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
In SQL Server 2012+:
在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
#2
2
A little tweak on Quassnoi's query if you prefer not to use a Subselect would be:
如果您不喜欢使用子选择,那么对Quassnoi的查询做一个小小的调整就是:
SELECT
DATEDIFF(second, LAG(dataDate) OVER (ORDER BY dataDate), dataDate)
FROM rows
WHERE LAG(dataDate) OVER (ORDER BY dataDate) IS NOT NULL