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

数据表是记录能耗的，能耗是个累加值，每分钟记录一下，希望可以查到每间隔一定时间（如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');

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

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

select sum(data) from dataEnergy;

写了一个查询。我假设了每分钟有数据。但是其实有的分钟数据缺了。还在想怎么弥补。

WITH FiveMiniteEnergy AS
(
	SELECT	*, DATEPART(MINUTE, rdate) AS minute
	FROM	dataEnergy
	WHERE	DATEPART(MINUTE, rdate) % 5 = 0
)
SELECT	FiveMiniteEnergy.id,
		FiveMiniteEnergy.rdate,
		FiveMiniteEnergy.data,
		FiveMinuteAgo.data AS FiveMinuteAgoData,
		FiveMiniteEnergy.data - COALESCE(FiveMinuteAgo.data, 0) AS EnergyConsumption
FROM	FiveMiniteEnergy 
		LEFT OUTER JOIN FiveMiniteEnergy AS FiveMinuteAgo
			ON FiveMiniteEnergy.rdate = DATEADD(MINUTE, 5, FiveMinuteAgo.rdate)
ORDER BY FiveMiniteEnergy.rdate
;


// Output
id    rdate    data    FiveMinuteAgoData    EnergyConsumption
5	2021-04-26 16:05:00.000	7	NULL	7
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	NULL	35
21	2021-04-27 17:00:00.000	43	NULL	43
26	2021-04-27 17:05:00.000	55	43	12

您好，我是有问必答小助手，您的问题已经有小伙伴解答了，您看下是否解决，可以追评进行沟通哦~

如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～

ps:问答VIP仅需29元，即可享受5次/月 有问必答服务，了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632

非常感谢您使用有问必答服务，为了后续更快速的帮您解决问题，现诚邀您参与有问必答体验反馈。您的建议将会运用到我们的产品优化中，希望能得到您的支持与协助！

速戳参与调研>>>https://t.csdnimg.cn/Kf0y