sql - SQL查询计算每30分钟列出的消耗读数，需要按日期时间戳和仪表序列号DESC排序

Question

嗨堆栈溢出成员:)

---

我需要获取每行向前主动阅读的消耗。将差额放在消费列中。差异是每 30 分钟的间隔。例如; 17897352 - 17897335 = 17 as [consumption/30min] 但我不想按日期分组，但需要按日期时间戳 DESC 排序。

---

示例表内容：

DeviceReadingID MeterRefID  DateTimeStamp   ForwardActiveReading  
284729948   ELON102582  2020-07-01 00:00:00.000 17897352
284756341   ELON102582  2020-06-30 23:30:00.000 17897335
284746613   ELON102582  2020-06-30 23:00:00.000 17897319
284710326   ELON102582  2020-06-30 22:30:00.000 17897303
284743959   ELON102582  2020-06-30 22:00:00.000 17897286
284762422   ELON102582  2020-06-30 21:30:00.000 17897270
284739309   ELON102582  2020-06-30 21:00:00.000 17897253
284758349   ELON102582  2020-06-30 20:30:00.000 17897237
284675646   ELON102582  2020-06-30 20:00:00.000 17897220
284686333   ELON102582  2020-06-30 19:30:00.000 17897204
284559642   ELON102582  2020-06-30 19:00:00.000 17897187
284560601   ELON102582  2020-06-30 18:30:00.000 17897171
284659743   ELON102582  2020-06-30 18:00:00.000 17897154
284657476   ELON102582  2020-06-30 17:30:00.000 17897111
284655871   ELON102582  2020-06-30 17:00:00.000 17897055
284636705   ELON102582  2020-06-30 16:30:00.000 17896998
284653158   ELON102582  2020-06-30 16:00:00.000 17896941
284624220   ELON102582  2020-06-30 15:30:00.000 17896885
284619064   ELON102582  2020-06-30 15:00:00.000 17896828

谢谢，非常感谢您对上述内容的帮助。我似乎无法弄清楚该怎么做

我试图使用下面的例子，但它没有帮助

        WITH    SampleData  AS
    (
            SELECT  [TimeStamp], [MeterID], [Count]
            FROM    (   VALUES
                        ('2013-09-28 00:00:10', 'BA123', 1453.034),
                        ('2013-09-28 00:01:12', 'BA123', 1454.770),
                        ('2013-09-28 00:00:14', 'BB777', 2351.000),
                        ('2013-09-28 23:59:50', 'BA123', 1470.120),
                        ('2013-09-29 00:00:05', 'BA123', 1470.445)
                    )   AS SampleData([TimeStamp], [MeterID], [Count])
    )
    
    
    
    SELECT      MeterId, CAST([TimeStamp] AS datetime) AS [DateConsumption],
                MAX([Count]) - MIN([Count]) AS [CountConsumption]
    FROM        SampleData
    GROUP BY    MeterID, CAST([TimeStamp] AS datetime)
    --WITH ROLLUP

---

我想做一个选择查询，它会给我以下结果：

标签： sqlsql-serverdatetimeselectdifference

Answer 1

此查询将消耗列添加到示例表输出

select *, ForwardActiveReading - lead(ForwardActiveReading, 1, 0) over(order by DateTimeStamp desc)
from yourtable
order by DateTimeStamp desc