求一sql语句, 两条记录间隔15分钟

zywhao 2014-09-10 10:54:00
表结构 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分钟的所有记录。
...全文
656 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
zywhao 2014-09-11
  • 打赏
  • 举报
回复
非常感谢 正确,可用
引用 13 楼 Tiger_Zhao 的回复:
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           3
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 3
zywhao 2014-09-11
  • 打赏
  • 举报
回复
引用 11 楼 Tiger_Zhao 的回复:
你的数据里看不到有前后间隔15分钟的记录,没结果很正常。
我明白了,是我没有表述清楚我的需求, 让大家误解了, 重新上一些数据,好描述

      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
上面符合条件的是 n=1 n=8 n=10 n=25 第一条符合条件,在第一条的基础上,加15分钟,接收时间>00:15:23.000 之后的第一条就是符合条件的,那就是 n=8的那条, 然后是在符合条件的第8条的时间上加15分钟 即接收时间> 00:45:55.000 的第一条也是符合条件的,即n=10 依次论推
zywhao 2014-09-10
  • 打赏
  • 举报
回复
引用 2 楼 hdhai9451 的回复:
declare @dt datetime
set @dt='2014-09-01 10:10:36'
select *
from tb
where 时间 between between @dt and DATEADD(minute,15,@dt)


我不是只要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
  • 打赏
  • 举报
回复
引用 8 楼 Tiger_Zhao 的回复:
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)
              )
谢谢, 试试
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
  • 打赏
  • 举报
回复
引用 4 楼 Tiger_Zhao 的回复:
假定 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)
              )
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)
)

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧