关于sql server间隔时间段差值和求和？

Question

数据表是记录能耗的，能耗是个累加值，每分钟记录一下，希望可以查到每间隔一定时间（如5分钟）的差值，并对差值求和

创建表格

create table dataEnergy(
id int,
userid varchar(30),
data int,
rdate datetime
)

插入数据

insert into dataEnergy(id,userid,data,rdate) values(1,'1',1,'2021-04-26 16:01:00');
insert into dataEnergy(id,userid,data,rdate) values(2,'1',3,'2021-04-26 16:02:00');
insert into dataEnergy(id,userid,data,rdate) values(3,'1',5,'2021-04-26 16:03:00');
insert into dataEnergy(id,userid,data,rdate) values(4,'1',6,'2021-04-26 16:04:00');
insert into dataEnergy(id,userid,data,rdate) values(5,'1',7,'2021-04-26 16:05:00');
insert into dataEnergy(id,userid,data,rdate) values(6,'1',10,'2021-04-26 16:06:00');
insert into dataEnergy(id,userid,data,rdate) values(7,'1',13,'2021-04-26 16:08:00');
insert into dataEnergy(id,userid,data,rdate) values(8,'1',16,'2021-04-26 16:09:00');
insert into dataEnergy(id,userid,data,rdate) values(9,'1',17,'2021-04-26 16:10:00');
insert into dataEnergy(id,userid,data,rdate) values(10,'1',18,'2021-04-26 16:11:00');
insert into dataEnergy(id,userid,data,rdate) values(11,'1',20,'2021-04-26 16:15:00');
insert into dataEnergy(id,userid,data,rdate) values(12,'1',22,'2021-04-26 16:16:00');
insert into dataEnergy(id,userid,data,rdate) values(13,'1',24,'2021-04-27 16:01:00');
insert into dataEnergy(id,userid,data,rdate) values(14,'1',26,'2021-04-27 16:02:00');
insert into dataEnergy(id,userid,data,rdate) values(15,'1',28,'2021-04-27 16:03:00');
insert into dataEnergy(id,userid,data,rdate) values(16,'1',33,'2021-04-27 16:04:00');
insert into dataEnergy(id,userid,data,rdate) values(17,'1',35,'2021-04-27 16:05:00');
insert into dataEnergy(id,userid,data,rdate) values(18,'1',37,'2021-04-27 16:06:00');
insert into dataEnergy(id,userid,data,rdate) values(19,'1',38,'2021-04-27 16:56:00');
insert into dataEnergy(id,userid,data,rdate) values(20,'1',41,'2021-04-27 16:58:00');
insert into dataEnergy(id,userid,data,rdate) values(21,'1',43,'2021-04-27 17:00:00');
insert into dataEnergy(id,userid,data,rdate) values(22,'1',46,'2021-04-27 17:01:00');
insert into dataEnergy(id,userid,data,rdate) values(23,'1',48,'2021-04-27 17:02:00');
insert into dataEnergy(id,userid,data,rdate) values(24,'1',51,'2021-04-27 17:03:00');
insert into dataEnergy(id,userid,data,rdate) values(25,'1',53,'2021-04-27 17:04:00');
insert into dataEnergy(id,userid,data,rdate) values(26,'1',55,'2021-04-27 17:05:00');
insert into dataEnergy(id,userid,data,rdate) values(27,'1',57,'2021-04-27 17:06:00');
insert into dataEnergy(id,userid,data,rdate) values(28,'1',60,'2021-04-27 17:07:00');

Answer 1

修改了查询。当数据缺失时找上一条记录。

WITH FiveMiniteEnergy AS
(
	SELECT	*, 
			(
				SELECT	TOP 1 FiveMinuteAgo.data
				FROM	dataEnergy As FiveMinuteAgo
				WHERE	FiveMinuteAgo.rdate <= DATEADD(MINUTE, -5, dataEnergy.rdate)
				ORDER BY FiveMinuteAgo.rdate DESC
			) AS FiveMinuteAgoData
	FROM	dataEnergy
	WHERE	DATEPART(MINUTE, dataEnergy.rdate) % 5 = 0
)
SELECT	id,
		rdate,
		data,
		FiveMinuteAgoData,
		data - COALESCE(FiveMinuteAgoData, 0) AS EnergyConsumption
FROM	FiveMiniteEnergy 
ORDER BY rdate
;


// Output
id    rdate    data    FiveMinuteAgoData    EnergyConsumption
5	2021-04-26 16:05:00.000	7	NULL	７
9	2021-04-26 16:10:00.000	17	7	10
11	2021-04-26 16:15:00.000	20	17	3
17	2021-04-27 16:05:00.000	35	22	13
21	2021-04-27 17:00:00.000	43	37	6
26	2021-04-27 17:05:00.000	55	43	12