SQL解决方案用于实时计算从增量值的汇总消耗

Question

i有一个SQL表，其中列每秒具有增量值。我如何确定一个小时的变化或消费价值？

For e.g.
Date            Time                EnergyKWH
2019-02-16      18:00:00.000        43.366
2019-02-16      18:00:01.000        43.367
2019-02-16      18:00:02.000        43.369
.................
............
.........
2019-02-16      19:00:00.000        70.886

（每1秒记录1个记录，1小时内3600个）

从18:00到19:00我如何找到每小时的总消费，并分别计算出所有24小时？（每小时消费将是18:00和19:00值之间的差异）。

Expected Result :
Date            StartTime           EndTime             EnergyKWH
2019-02-16      18:00:00            19:00:00            27.250        (Difference of 70.886-43.366)
2019-02-16      19:00:00            20:00:00            21.561        (Next Value Approximation)

数据以每秒为单位进行，我需要一个可以自动化此过程的解决方案。

我应该考虑在飞行计算上使用视图或cte之类的东西，或者可能会带有计算结果的触发触发器，然后拿到另一个表格，持有我所需的结果集？

请记住，这将是所有日期和全部24小时的持续过程。理想情况下，我希望至少持有计算/结果数据的7天。我的DB版本是MS SQL 2012。

我只需要在18:00和19:00时的值差，而在所有24小时，今天和过去的任何日期中都有类似的差异。

我可以从数学上进行计算，这非常容易，但是随着新数据继续流入？

，将是一种自动化该过程的最佳方法

Answer 1

如果您没有所有数据，则类似的东西：

select t.date, t.time as starttime,
       lead(t.time) over (order by t.date, t.time) as endtime,
       (lead(EnergyKWH) over (order by t.date, t.time) -
        EnergyKWH
       ) as EnergyKWH_diff
from t
where extract(minute from time) = 0
order by t.date, t.time;

这使用ISO/ANSI标准功能。这些可能因数据库而有所不同，但是这个想法是相同的。

在SQL Server中，这将表示为：

select t.date, t.time as starttime,
       lead(t.time) over (order by t.date, t.time) as endtime,
       (lead(EnergyKWH) over (order by t.date, t.time) -
        EnergyKWH
       ) as EnergyKWH_diff
from t
where datepart(minute, time) = 0
order by t.date, t.time;

Answer 2

好吧，所以我有一个可能的解决方案，但是我不想这样做，因为它非常粗糙且非常慢。

它的> 5秒> 7天数据，随着数据的扩展，这将继续增加。

创建视图或CTE有助于提供更好的性能吗？我知道我可以在视图中添加一个额外的索引，但是CTE并不是更好的CTE，因为我递归地击中了表（自加入将替换此嵌套查询）

示例解决方案查询：

SELECT 
    (CASE WHEN ((DATEPART(Minute,p.[Time])) = 0 AND (DATEPART(SECOND,p.Time)) = 0)
                AND (DateAdd(HOUR,1, p.[Time])  IS NOT NULL)
        THEN 
             (Select -1 * (p.EnergyKWH - (Select EnergyKWH from FactPLCDetails p2 WHERE p2.[Time] = (DateAdd(HOUR,1, p.[Time])) AND p.[Date] = p2.[Date]) ))
        END
    )
    as [EnergyDiff],
    p.[Time] As Start_Time,
    (DateAdd(HOUR,1, p.[Time])) as End_Time,
    p.[Date]     
from FactPLCDetails p
ORDER BY [EnergyDiff] DESC