我有以下输出
ID Number Speed LAT LONG DATETIME
101 AB01 15 73.066016 33.5840768 9/1/15 23:10
101 AB01 20 73.0619712 33.5871456 9/1/15 23:30
101 AB01 0 73.0722176 33.6007488 9/1/15 23:45
101 AB01 0 73.0722112 33.6007488 9/2/15 02:10
101 AB01 0 73.0722176 33.6007008 9/2/15 02:35
101 AB01 0 73.0722432 33.6007456 9/2/15 04:35
101 AB01 0 73.0721664 33.6007904 9/3/15 12:35
101 AB01 0 73.072192 33.6007488 9/3/15 13:35
101 AB01 0 73.072192 33.6007488 9/4/15 11:35
101 AB01 0 73.072192 33.6007488 9/4/15 14:35
101 AB01 1 73.072192 33.6007488 9/5/15 14:35
但要求输出
ID Number Speed LAT LONG DATETIME
101 AB01 15 73.066016 33.5840768 9/1/15 23:10
101 AB01 20 73.0619712 33.5871456 9/1/15 23:30
101 AB01 0 73.0722176 33.6007488 9/1/15 23:45
101 AB01 0 73.072192 33.6007488 9/4/15 14:35
101 AB01 1 73.072192 33.6007488 9/5/15 14:35
我想跳过额外的0速度值。如果包括这些0,查询响应时间就会增加。代码尝试
SELECT Distinct ID,Number,Speed,LAT,LONG,DATETIME
FROM table
group by D,Number,Speed,LAT,LONG,DATETIME
您可以使用LEAD
, LAG
窗口函数来检测速度不连续(如果这是您实际想要的):
SELECT ID, Number, Speed, LAT, [LONG], [DATETIME]
FROM (
SELECT ID, Number, Speed, LAT, [LONG], [DATETIME],
LEAD(Speed) OVER (PARTITION BY ID, Number
ORDER BY [DATETIME]) AS NextSpeed,
LAG(Speed) OVER (PARTITION BY ID, Number
ORDER BY [DATETIME]) AS PrevSpeed
FROM mytable) AS t
WHERE Speed <> COALESCE(NextSpeed, -1) OR Speed <> COALESCE(PrevSpeed, -1)
演示