34,838
社区成员
发帖
与我相关
我的任务
分享求一sql语句， 两条记录间隔15分钟
表结构 ID lon lat recivetime
1 107.223 69.23 2014-09-10 0：0：0
2 107.233 69.43 2014-09-10 0：0：2
3 107.253 69.53 2014-09-10 0：0：4
因为表中数据量比较大 ，每隔2秒会有一条记录，
我需要查询某个时间段内两条记录之间间隔15分钟的所有记录。
1 107.223 69.23 2014-09-10 0：0：0
2 107.233 69.43 2014-09-10 0：0：2
3 107.253 69.53 2014-09-10 0：0：4
因为表中数据量比较大 ，每隔2秒会有一条记录，
我需要查询某个时间段内两条记录之间间隔15分钟的所有记录。
...全文
请发表友善的回复…
发表回复
zywhao 2014-09-11
- 打赏
- 举报
非常感谢 正确，可用
Tiger_Zhao 2014-09-11
- 打赏
- 举报
WITH T(UserID,Longitude,Latitude,receivetime,n) AS (
SELECT 13775221,114.825736666667,30.4989133333333,'2014-09-10 00:00:23.000',1 UNION ALL
SELECT 13775221,114.825508333333,30.4970333333333,'2014-09-10 00:01:24.000',2 UNION ALL
SELECT 13775221,114.825173333333,30.4951566666667,'2014-09-10 00:02:23.000',3 UNION ALL
SELECT 13775221,114.825006666667,30.4942216666667,'2014-09-10 00:02:53.000',4 UNION ALL
SELECT 13775221,114.824853333333,30.4932033333333,'2014-09-10 00:03:25.000',5 UNION ALL
SELECT 13775221,114.82401,30.48173,'2014-09-10 00:09:23.000',6 UNION ALL
SELECT 13775221,114.823893333333,30.4808233333333,'2014-09-10 00:09:53.000',7 UNION ALL
SELECT 13775221,114.828243333333,30.4421616666667,'2014-09-10 00:30:55.000',8 UNION ALL
SELECT 13775221,114.832366666667,30.4359766666667,'2014-09-10 00:34:52.000',9 UNION ALL
SELECT 13775221,115.038436666667,30.394655,'2014-09-10 02:05:19.000',10 UNION ALL
SELECT 13775221,115.040711666667,30.3931133333333,'2014-09-10 02:06:18.000',11 UNION ALL
SELECT 13775221,115.048828333333,30.38732,'2014-09-10 02:09:49.000',12 UNION ALL
SELECT 13775221,115.05123,30.38576,'2014-09-10 02:10:48.000',13 UNION ALL
SELECT 13775221,115.05241,30.3849483333333,'2014-09-10 02:11:17.000',14 UNION ALL
SELECT 13775221,115.055828333333,30.3823633333333,'2014-09-10 02:12:48.000',15 UNION ALL
SELECT 13775221,115.056865,30.38141,'2014-09-10 02:13:18.000',16 UNION ALL
SELECT 13775221,115.05792,30.3804266666667,'2014-09-10 02:13:49.000',17 UNION ALL
SELECT 13775221,115.05881,30.3794766666667,'2014-09-10 02:14:17.000',18 UNION ALL
SELECT 13775221,115.059791666667,30.3783883333333,'2014-09-10 02:14:50.000',19 UNION ALL
SELECT 13775221,115.061503333333,30.37633,'2014-09-10 02:15:48.000',20 UNION ALL
SELECT 13775221,115.063175,30.374175,'2014-09-10 02:16:48.000',21 UNION ALL
SELECT 13775221,115.06407,30.3730833333333,'2014-09-10 02:17:19.000',22 UNION ALL
SELECT 13775221,115.064903333333,30.3721233333333,'2014-09-10 02:17:48.000',23 UNION ALL
SELECT 13775221,115.066736666667,30.37007,'2014-09-10 02:18:48.000',24 UNION ALL
SELECT 13775221,115.066737766667,30.37008,'2014-09-10 02:25:48.000',25 UNION ALL
-- 加几条另外一个 UserID 的记录
SELECT 13775222,114.825736666667,30.4989133333333,'2014-09-10 00:00:23.000',1 UNION ALL
SELECT 13775222,114.825508333333,30.4970333333333,'2014-09-10 00:01:24.000',2 UNION ALL
SELECT 13775222,114.825173333333,30.4951566666667,'2014-09-10 00:20:23.000',3 UNION ALL
SELECT 13775222,114.824853333333,30.4932033333333,'2014-09-10 00:23:25.000',4
),
cte AS (
SELECT *
FROM T
WHERE n=1
UNION ALL
SELECT T.*
FROM cte
JOIN T
ON cte.UserID = T.userID
AND T.receivetime >= DateAdd(minute,15,cte.receivetime)
WHERE EXISTS ( -- 递归不能用 TOP、Min()，只能用前一条记录进行判断
SELECT *
FROM T t0
WHERE t0.UserID = T.UserID
AND t0.n = T.n-1
AND t0.receivetime < DateAdd(minute,15,cte.receivetime)
)
)
SELECT *
FROM cte
ORDER BY UserID, n UserID Longitude Latitude receivetime n
----------- --------------------------------------- --------------------------------------- ----------------------- -----------
13775221 114.825736666667 30.4989133333333 2014-09-10 00:00:23.000 1
13775221 114.828243333333 30.4421616666667 2014-09-10 00:30:55.000 8
13775221 115.038436666667 30.3946550000000 2014-09-10 02:05:19.000 10
13775221 115.066737766667 30.3700800000000 2014-09-10 02:25:48.000 25
13775222 114.825736666667 30.4989133333333 2014-09-10 00:00:23.000 1
13775222 114.825173333333 30.4951566666667 2014-09-10 00:20:23.000 3zywhao 2014-09-11
- 打赏
- 举报
我明白了，是我没有表述清楚我的需求， 让大家误解了，
重新上一些数据，好描述
ID Longitude Latitude receivetime n
13775221 114.825736666667 30.4989133333333 2014-09-10 00:00:23.000 1
13775221 114.825508333333 30.4970333333333 2014-09-10 00:01:24.000 2
13775221 114.825173333333 30.4951566666667 2014-09-10 00:02:23.000 3
13775221 114.825006666667 30.4942216666667 2014-09-10 00:02:53.000 4
13775221 114.824853333333 30.4932033333333 2014-09-10 00:03:25.000 5
13775221 114.82401 30.48173 2014-09-10 00:09:23.000 6
13775221 114.823893333333 30.4808233333333 2014-09-10 00:09:53.000 7
13775221 114.828243333333 30.4421616666667 2014-09-10 00:30:55.000 8
13775221 114.832366666667 30.4359766666667 2014-09-10 00:34:52.000 9
13775221 115.038436666667 30.394655 2014-09-10 02:05:19.000 10
13775221 115.040711666667 30.3931133333333 2014-09-10 02:06:18.000 11
13775221 115.048828333333 30.38732 2014-09-10 02:09:49.000 12
13775221 115.05123 30.38576 2014-09-10 02:10:48.000 13
13775221 115.05241 30.3849483333333 2014-09-10 02:11:17.000 14
13775221 115.055828333333 30.3823633333333 2014-09-10 02:12:48.000 15
13775221 115.056865 30.38141 2014-09-10 02:13:18.000 16
13775221 115.05792 30.3804266666667 2014-09-10 02:13:49.000 17
13775221 115.05881 30.3794766666667 2014-09-10 02:14:17.000 18
13775221 115.059791666667 30.3783883333333 2014-09-10 02:14:50.000 19
13775221 115.061503333333 30.37633 2014-09-10 02:15:48.000 20
13775221 115.063175 30.374175 2014-09-10 02:16:48.000 21
13775221 115.06407 30.3730833333333 2014-09-10 02:17:19.000 22
13775221 115.064903333333 30.3721233333333 2014-09-10 02:17:48.000 23
13775221 115.066736666667 30.37007 2014-09-10 02:18:48.000 24
13775221 115.066737766667 30.37008 2014-09-10 02:25:48.000 25
zywhao 2014-09-10
- 打赏
- 举报
我不是只要15分钟内的记录， 我是需要两条记录之间间隔15分钟
像上面图片中的记录，需要第一行中的数据， 下面这些第12秒，18秒接收到的数据就不需要了， 下一条符合记录的是 大于第一条的时间15分钟以上的一条记录。
Andy__Huang 2014-09-10
- 打赏
- 举报
declare @dt datetime
set @dt='2014-09-01 10:10:36'
select *
from tb
where 时间 between between @dt and DATEADD(minute,15,@dt)zywhao 2014-09-10
- 打赏
- 举报
如果我查询 9月10日的数据， 我只需要查询出以下记录就可以
1 107.223 69.23 2014-09-10 0：0：0
450 107.293 69.93 2014-09-10 0：15：08
750 107.293 69.93 2014-09-10 0：32：03
数据并不一定全是2秒钟一条， 有时可能会中断好长时间。 只要两条记录时间间隔15分钟就可以
Tiger_Zhao 2014-09-10
- 打赏
- 举报
你的数据里看不到有前后间隔15分钟的记录，没结果很正常。
zywhao 2014-09-10
- 打赏
- 举报
WITH T AS (
SELECT UserID, Longitude,Latitude,receivetime,
ROW_NUMBER() OVER (PARTITION BY userid
ORDER BY receivetime
) n
FROM [dbo].[tbLocationHistory20140910] where UserID='0'
)
SELECT t.*
FROM t这是我执行上面的语句查到的我需要查询的设备的所有记录。
但是继续使用上面的语句，没有出来结果
zywhao 2014-09-10
- 打赏
- 举报
谢谢， 试试
Tiger_Zhao 2014-09-10
- 打赏
- 举报
WITH T AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY 设备号
ORDER BY recivetime
) n
FROM test
)
SELECT t.*
FROM t
WHERE EXISTS (SELECT *
FROM t t1
WHERE t1.设备号 = t.设备号
AND t1.n = t.n-1
AND t1.recivetime <= DateAdd(minute,-15,t.recivetime)
)
OR EXISTS (SELECT *
FROM t t2
WHERE t2.设备号 = t.设备号
AND t2.n = t.n+1
AND t2.recivetime >= DateAdd(minute,15,t.recivetime)
)
霜寒月冷 2014-09-10
- 打赏
- 举报
不连续可以先用开窗函数 把test 重新编排一下，按照 设备编号分组，时间降序或者升序排，然后在用4楼的方法！
zywhao 2014-09-10
- 打赏
- 举报
ID号是不连续的，因为有多个设备。
spiritofdragon 2014-09-10
- 打赏
- 举报
--下面是递归写法（遍历浪费太多，因为递归里不让用TOP...只能这么凑合），强烈不建议这么写，你还是自己写个循环吧，性能都比我这写的好。不过还是辛苦写出来的，贴出来给大家看看。
declare @arg date ='2014-9-10';
with data(id,lon,lat,recivetime) as(
select 1,110.1,30,CONVERT(datetime,'2014/9/10 0:4:16') union all
select 2,110.1,30,'2014/9/10 0:4:49' union all
select 3,110.1,30,'2014/9/10 0:5:14' union all
select 4,110.1,30,'2014/9/10 0:12:34' union all
select 5,110.1,30,'2014/9/10 0:17:26' union all
select 6,110.1,30,'2014/9/10 0:17:48' union all
select 7,110.1,30,'2014/9/10 0:18:43' union all
select 8,110.1,30,'2014/9/10 0:19:28' union all
select 9,110.1,30,'2014/9/10 0:19:53' union all
select 10,110.1,30,'2014/9/10 0:19:59' union all
select 11,110.1,30,'2014/9/10 0:20:3' union all
select 12,110.1,30,'2014/9/10 0:24:27' union all
select 13,110.1,30,'2014/9/10 0:25:18' union all
select 14,110.1,30,'2014/9/10 0:27:27' union all
select 15,110.1,30,'2014/9/10 0:28:1' union all
select 16,110.1,30,'2014/9/10 0:30:58' union all
select 17,110.1,30,'2014/9/10 0:31:1' union all
select 18,110.1,30,'2014/9/10 0:31:43' union all
select 19,110.1,30,'2014/9/10 0:31:57' union all
select 20,110.1,30,'2014/9/10 0:33:23' union all
select 21,110.1,30,'2014/9/10 0:35:37' union all
select 22,110.1,30,'2014/9/10 0:37:20' union all
select 23,110.1,30,'2014/9/10 0:37:33' union all
select 24,110.1,30,'2014/9/10 0:37:35' union all
select 25,110.1,30,'2014/9/10 0:38:2' union all
select 26,110.1,30,'2014/9/10 0:38:7' union all
select 27,110.1,30,'2014/9/10 0:39:12' union all
select 28,110.1,30,'2014/9/10 0:39:40' union all
select 29,110.1,30,'2014/9/10 0:42:3' union all
select 30,110.1,30,'2014/9/10 0:43:10' union all
select 31,110.1,30,'2014/9/10 0:43:31' union all
select 32,113.9,30,'2014/9/10 0:46:2' union all
select 33,110.1,30,'2014/9/10 0:46:58' union all
select 34,110.1,30,'2014/9/10 0:46:59' union all
select 35,110.1,30,'2014/9/10 0:48:36' union all
select 36,110.1,30,'2014/9/10 0:49:31' union all
select 37,110.1,30,'2014/9/10 0:51:9' union all
select 38,110.1,30,'2014/9/10 0:53:6' union all
select 39,110.1,30,'2014/9/10 0:54:3' union all
select 40,110.1,30,'2014/9/10 0:54:11' union all
select 41,110.1,30,'2014/9/10 0:55:22' union all
select 42,110.1,30,'2014/9/10 0:55:59' union all
select 43,110.1,30,'2014/9/10 0:56:57' union all
select 44,110.1,30,'2014/9/10 0:57:25' union all
select 45,110.1,30,'2014/9/10 0:58:4' union all
select 46,110.1,30,'2014/9/10 0:58:28' union all
select 47,110.1,30,'2014/9/10 0:58:30' union all
select 48,110.1,30,'2014/9/10 1:2:24' union all
select 49,110.1,30,'2014/9/10 1:3:3' union all
select 50,110.1,30,'2014/9/10 1:3:42' union all
select 51,113.1,29.1,'2014/9/10 1:3:53' union all
select 52,110.1,30,'2014/9/10 1:6:2' union all
select 53,110.1,30,'2014/9/10 1:6:32' union all
select 54,110.1,30,'2014/9/10 1:7:44' union all
select 55,110.1,30,'2014/9/10 1:9:25' union all
select 56,118.2,29,'2014/9/10 1:9:41'
)
,t as (
select *,ROW_NUMBER() over(order by recivetime) sort
from data where CAST(recivetime as date)=@arg
)
,cte as(
select *,1 lvl from t where sort=1
union all
select tt.* ,c1.lvl+1
from cte c1
cross apply(select * from t where t.recivetime>=DATEADD(MINUTE,15,c1.recivetime)) tt
)
select distinct *
from cte c1
where not exists(select 1 from cte c2 where c2.lvl=c1.lvl and c2.sort<c1.sort)
OPTION (MAXRECURSION 96)
Tiger_Zhao 2014-09-10
- 打赏
- 举报
假定 ID 是连续的。
SELECT t.*
FROM test t
WHERE EXISTS (SELECT *
FROM test t1
WHERE t1.id = t.id-1
AND t1.recivetime <= DateAdd(minute,-15,t.recivetime)
)
OR EXISTS (SELECT *
FROM test t2
WHERE t2.id = t.id+1
AND t2.recivetime >= DateAdd(minute,15,t.recivetime)
)
