WITH maco AS ( SELECT ROW_NUMBER() OVER ( ORDER BY vehicleid,date ) AS rowid,* FROM TB_VDRDATA)
SELECT a.vehicleid ,SUM(CASE WHEN a.carspeed - b.carspeed > 10 THEN 1 ELSE 0 END) cnt
FROM maco a inner JOIN maco b ON a.rowid = b.rowid + 1
left join Vehicle v
on a.VehicleID=v.ID
where v.ZoneID='alog-dg'
and a.date>= '2010-12-29 00:00:00' and a.date<= '2011-01-28 23:59:59'
GROUP BY a.vehicleid;
上面的SQL语句是下一条记录减上一条记录的,我现在想变成第3条记录减去第1条记录,第4条记录减去第2条记录,依次类推,应该怎么做呢? 请各位大侠帮帮忙! 小弟感谢!
FROM maco a inner JOIN maco b ON a.rowid = b.rowid + 2
left join Vehicle v on a.VehicleID=v.ID
where v.ZoneID='alog-dg' and a.date>= '2010-12-29 00:00:00' and a.date<= '2011-01-28 23:59:59'
GROUP BY a.vehicleid;
CREATE TABLE [TB_VDRDATATest] (
[carid] [int] NULL, -------车牌
[carspeed] [int] NULL,-------车速
[data] [datetime] NULL,------------时间
[bcms] [float] NULL),-------------状态保持秒数INSERT [TB_VDRDATATest] ([carid],[carspeed],[data],[bcms]) VALUES ( 1,50,'2011-1-13 11:20:30',3)
INSERT [TB_VDRDATATest] ([carid],[carspeed],[data],[bcms]) VALUES ( 1,63,'2011-1-13 11:20:33',5)
INSERT [TB_VDRDATATest] ([carid],[carspeed],[data],[bcms]) VALUES ( 1,78,'2011-1-13 11:20:38',6)
INSERT [TB_VDRDATATest] ([carid],[carspeed],[data],[bcms]) VALUES ( 1,62,'2011-1-13 12:20:44',7)
INSERT [TB_VDRDATATest] ([carid],[carspeed],[data],[bcms]) VALUES ( 1,75,'2011-1-13 12:20:51',6)
INSERT [TB_VDRDATATest] ([carid],[carspeed],[data],[bcms]) VALUES ( 1,90,'2011-1-13 12:20:57',5)数据给出。刚刚我发我的算法有问题, 就是时间不连续的 我也进行了统计,现在想要的结果是在连续时间中的。如果不连接了 就重新开始算。 规则是这样的,第3条减第1条的车速大于12就算1次急加速,第4条减第2条车速大于12也算一次急加速,依次类推
a.rowid = b.rowid + 1
變成a.rowid = b.rowid + 2 不就可以了嗎?