hltjszx 2021-05-12 09:15 采纳率: 100%
浏览 58
已采纳

关于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');
  • 写回答

5条回答 默认 最新

  • benbenli 2021-05-12 12:11
    关注

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

    
    
    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	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	22	13
    21	2021-04-27 17:00:00.000	43	37	6
    26	2021-04-27 17:05:00.000	55	43	12
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(4条)

报告相同问题?

悬赏问题

  • ¥15 Delphi image图片缩放问题
  • ¥30 gradle环境下javafx项目如何使用druid连接池
  • ¥15 服务器打印水晶报表问题
  • ¥15 初学者用plt报错,求解答
  • ¥18 深度学习tensorflow1,ssdv1,coco数据集训练一个模型
  • ¥100 关于注册表摄像头和麦克风的问题
  • ¥30 代码本地运行正常,但是TOMCAT部署时闪退
  • ¥15 关于#python#的问题
  • ¥15 主机可以ping通路由器但是连不上网怎么办
  • ¥15 数据库一张以时间排好序的表中,找出多次相邻的那些行