点号 点名 开始日期 结束日期 时长 次数 机组
A1_1TE02 汽包上壁温1 2008-2-15 0:00:42 2008-2-15 0:01:42 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:00:42 2008-2-15 0:01:42 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:01:45 2008-2-15 0:02:45 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:01:45 2008-2-15 0:02:45 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:02:48 2008-2-15 0:03:48 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:02:48 2008-2-15 0:03:48 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:03:51 2008-2-15 0:04:51 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:03:51 2008-2-15 0:04:51 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:04:55 2008-2-15 0:05:55 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:04:55 2008-2-15 0:05:55 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:05:58 2008-2-15 0:06:58 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:05:58 2008-2-15 0:06:58 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:07:01 2008-2-15 0:08:01 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:07:01 2008-2-15 0:08:01 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:08:04 2008-2-15 0:09:04 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:08:04 2008-2-15 0:09:04 1 1 #1
如何把相同点号且相邻两条数据的开始时间差不大于2分钟的数据取出来,
要删除中间的数据,如上的数据查询统计完后变成这样
点号 点名 开始日期 结束日期 时长 次数 机组
A1_1TE02 汽包上壁温1 2008-2-15 0:00:42 2008-2-15 0:09:04 9 8 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:00:42 2008-2-15 0:09:04 9 8 #1 如果有满足这样的数还要继续。
下面的数据是假想的。
A1_1TE02 汽包上壁温1 2008-2-15 1:01:21 2008-2-15 1:03:24 2 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 2:29:53 2008-2-15 2:39:54 10 9 #1
......
谢谢
A1_1TE02 汽包上壁温1 2008-2-15 0:00:42 2008-2-15 0:01:42 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:00:42 2008-2-15 0:01:42 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:01:45 2008-2-15 0:02:45 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:01:45 2008-2-15 0:02:45 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:02:48 2008-2-15 0:03:48 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:02:48 2008-2-15 0:03:48 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:03:51 2008-2-15 0:04:51 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:03:51 2008-2-15 0:04:51 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:04:55 2008-2-15 0:05:55 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:04:55 2008-2-15 0:05:55 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:05:58 2008-2-15 0:06:58 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:05:58 2008-2-15 0:06:58 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:07:01 2008-2-15 0:08:01 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:07:01 2008-2-15 0:08:01 1 1 #1
A1_1TE02 汽包上壁温1 2008-2-15 0:08:04 2008-2-15 0:09:04 1 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:08:04 2008-2-15 0:09:04 1 1 #1
如何把相同点号且相邻两条数据的开始时间差不大于2分钟的数据取出来,
要删除中间的数据,如上的数据查询统计完后变成这样
点号 点名 开始日期 结束日期 时长 次数 机组
A1_1TE02 汽包上壁温1 2008-2-15 0:00:42 2008-2-15 0:09:04 9 8 #1
A1_1TE03 汽包下壁温1 2008-2-15 0:00:42 2008-2-15 0:09:04 9 8 #1 如果有满足这样的数还要继续。
下面的数据是假想的。
A1_1TE02 汽包上壁温1 2008-2-15 1:01:21 2008-2-15 1:03:24 2 1 #1
A1_1TE03 汽包下壁温1 2008-2-15 2:29:53 2008-2-15 2:39:54 10 9 #1
......
谢谢
其中P_ROW是ROWID
CREATE OR REPLACE FUNCTION F_EXIST(P_ROW IN VARCHAR2) RETURN INT
IS
VCOUNT PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO VCOUNT FROM YTAB WHERE 同点且起始时间差小于2分钟的.
if vcount<2 then --不具有的.
return 0;
else
retrun 1;
end if;
END;
最后在sql中可以如此使用。
select * from Ytab where f_exist(rowid)=0;
大意如此。
case
when next_datenum-cur_datenum>2 then 开始日期
else next_Date
end 结束日期,
case
when next_datenum-cur_datenum>2 then 开始时间
else next_Time
end 结束时间
from
(
select 点号,开始日期,开始时间,
to_date(开始日期||开始时间,'YYYY-MM-DD HH24:MI:SS')*24*3600 cur_datenum,
lead(to_date(开始日期||开始时间,'YYYY-MM-DD HH24:MI:SS')*24*3600) over(PARTITION BY 点号 order by 开始日期 asc) next_datenum,
lead(开始日期) over(PARTITION BY 点号 order by 开始日期 asc) next_Date,
lead(开始时间) over(PARTITION BY 点号 order by 开始日期 asc) next_Time
from tablename
order by 点号 asc
)
where next_datenum-cur_datenum>2这个sql不一定就符合你的需求,但你的需求,改一下这个sql肯定能实现,而且效率高。
A1_1TE02 2008-2-17 4:53:38 2008-2-17 4:54:42 64 1
A1_1TE02 2008-2-17 4:54:42 2008-2-17 4:55:45 63 1
A1_1TE02 2008-2-17 4:55:45 2008-2-17 4:56:49 64 1
A1_1TE02 2008-2-17 4:56:49 2008-2-17 4:57:53 64 1
A1_1TE02 2008-2-17 4:57:53 2008-2-17 4:58:56 63 1
A1_1TE02 2008-2-17 4:58:56 2008-2-17 5:00:00 64 1
A1_1TE02 2008-2-17 5:00:00 2008-2-17 5:01:03 63 1
A1_1TE02 2008-2-17 5:01:03 2008-2-17 5:02:07 64 1
A1_1TE02 2008-2-17 5:02:07 2008-2-17 5:03:11 64 1
A1_1TE02 2008-2-17 5:03:11 2008-2-17 5:04:15 64 1
A1_1TE02 2008-2-17 5:04:15 2008-2-17 5:05:18 63 1
A1_1TE02 2008-2-17 5:05:18 2008-2-17 5:06:22 64 1
A1_1TE02 2008-2-17 5:06:22 2008-2-17 5:07:26 64 1
A1_1TE02 2008-2-17 5:07:26 2008-2-17 5:08:29 63 1
A1_1TE02 2008-2-17 5:08:29 2008-2-17 5:09:33 64 1
A1_1TE02 2008-2-17 5:09:33 2008-2-17 18:04:09 46476 1
A1_1TE02 2008-2-17 18:04:09 2008-2-17 18:09:28 319 1
A1_1TE02 2008-2-17 18:09:28 2008-2-17 18:14:46 318 1
A1_1TE02 2008-2-17 18:14:46 2008-2-17 18:16:54 128 1
A1_1TE02 2008-2-17 18:16:54 2008-2-17 18:17:57 63 1
A1_1TE02 2008-2-17 18:17:57 2008-2-17 18:20:05 128 1
A1_1TE02 2008-2-17 18:20:05 2008-2-17 18:21:09 64 1
A1_1TE02 2008-2-17 18:21:09 2008-2-17 18:24:20 191 1
A1_1TE02 2008-2-17 18:24:20 2008-2-17 18:26:28 128 1
A1_1TE02 2008-2-17 18:26:28 2008-2-17 18:27:32 64 1
A1_1TE02 2008-2-17 18:27:32 2008-2-17 18:28:35 63 1
A1_1TE02 2008-2-17 18:28:35 2008-2-17 18:31:46 191 1
A1_1TE02 2008-2-17 18:31:46 2008-2-17 18:38:09 383 1
A1_1TE02 2008-2-17 18:38:09 2008-2-17 18:39:13 64 1
A1_1TE02 2008-2-17 18:39:13 2008-2-17 18:43:28 255 1
A1_1TE02 2008-2-17 18:43:28 2008-2-17 18:50:55 447 1
A1_1TE02 2008-2-17 18:50:55 2008-2-17 18:56:14 319 1
A1_1TE02 2008-2-17 18:56:14 2008-2-17 18:58:21 127 1
A1_1TE02 2008-2-17 18:58:21 2008-2-17 19:00:29 128 1
A1_1TE02 2008-2-17 19:00:29 2008-2-17 19:01:33 64 1
A1_1TE02 2008-2-17 19:01:33 2008-2-17 19:03:40 127 1
A1_1TE02 2008-2-17 19:03:40 2008-2-17 19:05:48 128 1
A1_1TE02 2008-2-17 19:05:48 2008-2-17 19:07:55 127 1
A1_1TE02 2008-2-17 19:07:55 2008-2-17 19:08:59 64 1
A1_1TE02 2008-2-17 19:08:59 2008-2-17 19:12:09 190 1
A1_1TE02 2008-2-17 19:12:09 2008-2-17 19:15:21 192 1
A1_1TE02 2008-2-17 19:15:21 2008-2-17 19:17:28 127 1
A1_1TE02 2008-2-17 19:17:28 2008-2-17 19:23:51 383 1
A1_1TE02 2008-2-17 19:23:51 2008-2-17 19:32:22 511 1
A1_1TE02 2008-2-17 19:32:22 2008-2-17 19:33:25 63 1
A1_1TE02 2008-2-17 19:33:25 2008-2-17 19:34:29 64 1
A1_1TE02 2008-2-17 19:34:29 2008-2-17 19:37:40 191 1
A1_1TE02 2008-2-17 19:37:40 2008-2-17 19:38:44 64 1
A1_1TE02 2008-2-17 19:38:44 2008-2-17 19:48:18 574 1
A1_1TE02 2008-2-17 19:48:18 2008-2-17 19:49:22 64 1
A1_1TE02 2008-2-17 19:49:22 2008-2-17 19:50:25 63 1
A1_1TE02 2008-2-17 19:50:25 2008-2-17 19:52:34 129 1
A1_1TE02 2008-2-17 19:52:34 2008-2-17 19:54:41 127 1
A1_1TE02 2008-2-17 19:54:41 2008-2-17 19:57:52 191 1
A1_1TE02 2008-2-17 19:57:52 2008-2-17 20:02:07 255 1
A1_1TE02 2008-2-17 20:02:07 2008-2-17 20:06:23 256 1
A1_1TE02 2008-2-17 20:06:23 2008-2-17 20:08:30 127 1
A1_1TE02 2008-2-17 20:08:30 2008-2-17 20:22:20 830 1
A1_1TE02 2008-2-17 20:22:20 2008-2-17 20:25:31 191 1
A1_1TE02 2008-2-17 20:25:31 2008-2-17 20:26:35 64 1
A1_1TE02 2008-2-17 20:26:35 2008-2-17 20:27:39 64 1
A1_1TE02 2008-2-17 20:27:39 2008-2-17 20:30:50 191 1
A1_1TE02 2008-2-17 20:30:50 2008-2-17 20:32:57 127 1
A1_1TE02 2008-2-17 20:32:57 2008-2-17 20:42:31 574 1
A1_1TE02 2008-2-17 20:42:31 2008-2-17 20:43:35 64 1
A1_1TE02 2008-2-17 20:43:35 2008-2-17 20:51:03 448 1
A1_1TE02 2008-2-17 20:51:03 2008-2-17 20:55:18 255 1
A1_1TE02 2008-2-17 20:55:18 2008-2-17 21:01:40 382 1
A1_1TE02 2008-2-17 21:01:40 2008-2-17 21:04:51 191 1
A1_1TE02 2008-2-17 21:04:51 2008-2-17 21:11:14 383 1
A1_1TE02 2008-2-17 21:11:14 2008-2-17 21:15:32 258 1
A1_1TE02 2008-2-17 21:15:32 2008-2-17 21:17:40 128 1
A1_1TE02 2008-2-17 21:17:40 2008-2-17 21:24:03 383 1
A1_1TE02 2008-2-17 21:24:03 2008-2-17 21:25:06 63 1
A1_1TE02 2008-2-17 21:25:06 2008-2-17 21:29:22 256 1
A1_1TE02 2008-2-17 21:29:22 2008-2-17 21:30:26 64 1
A1_1TE02 2008-2-17 21:30:26 2008-2-17 21:32:33 127 1
A1_1TE02 2008-2-17 21:32:33 2008-2-17 22:00:11 1658 1
A1_1TE02 2008-2-17 22:00:11 2008-2-17 23:33:58 5627 1
A1_1TE02 2008-2-17 23:33:58 2008-2-17 23:35:02 64 1
A1_1TE02 2008-2-17 23:35:02 2008-2-17 23:36:06 64 1
A1_1TE02 2008-2-17 23:36:06 2008-2-17 23:37:10 64 1
A1_1TE02 2008-2-17 23:37:10 2008-2-17 23:38:15 65 1A1_1TE03 2008-2-17 5:15:54 2008-2-17 18:46:40 48646 1
A1_1TE03 2008-2-17 18:46:40 2008-2-17 20:23:24 5804 1
A1_1TE03 2008-2-17 20:23:24 2008-2-17 23:29:42 11178 1
A1_1TE03 2008-2-17 23:29:42 2008-2-17 23:30:46 64 1
A1_1TE03 2008-2-17 23:30:46 2008-2-17 23:32:54 128 1
A1_1TE03 2008-2-17 23:32:54 2008-2-17 23:33:58 64 1
A1_1TE03 2008-2-17 23:33:58 2008-2-17 23:35:02 64 1
case
when next_datenum-cur_datenum> 2 then 开始日期
else next_Date
end 结束日期,
case
when next_datenum-cur_datenum> 2 then 开始时间
else next_Time
end 结束时间
from
(
select 点号,开始日期,开始时间,
to_date(开始日期 ¦ ¦开始时间,'YYYY-MM-DD HH24:MI:SS')*24*60 cur_datenum,
lead(to_date(开始日期 ¦ ¦开始时间,'YYYY-MM-DD HH24:MI:SS')*24*3600) over(PARTITION BY 点号 order by 开始日期 asc) next_datenum,
lead(开始日期) over(PARTITION BY 点号 order by 开始日期 asc) next_Date,
lead(开始时间) over(PARTITION BY 点号 order by 开始日期 asc) next_Time
from tablename
order by 点号 asc
)
where next_datenum-cur_datenum> 2
case
when next_datenum-cur_datenum> 2 then 开始日期
else next_Date
end 结束日期,
case
when next_datenum-cur_datenum> 2 then 开始时间
else next_Time
end 结束时间
from
(
select 点号,开始日期,开始时间,
to_date(开始日期 ¦ ¦开始时间,'YYYY-MM-DD HH24:MI:SS')*24*60 cur_datenum,
lead(to_date(开始日期 ¦ ¦开始时间,'YYYY-MM-DD HH24:MI:SS')*24*60) over(PARTITION BY 点号 order by 开始日期 asc) next_datenum,
lead(开始日期) over(PARTITION BY 点号 order by 开始日期 asc) next_Date,
lead(开始时间) over(PARTITION BY 点号 order by 开始日期 asc) next_Time
from tablename
order by 点号 asc
)
where next_datenum-cur_datenum> 2
这样就需要滤去中间的数据。
如:
A1_1TE02 2008-2-17 5:01:03 2008-2-17 5:02:07 64 1
A1_1TE02 2008-2-17 5:02:07 2008-2-17 5:03:11 64 1
A1_1TE02 2008-2-17 5:03:11 2008-2-17 5:04:15 64 1
A1_1TE02 2008-2-17 5:04:15 2008-2-17 5:05:18 63 1
A1_1TE02 2008-2-17 5:05:18 2008-2-17 5:06:22 64 1
A1_1TE02 2008-2-17 5:06:22 2008-2-17 5:07:26 64 1
A1_1TE02 2008-2-17 5:07:26 2008-2-17 5:08:29 63 1
A1_1TE02 2008-2-17 5:08:29 2008-2-17 5:09:33 64 1 A1_1TE02 2008-2-17 5:09:33 2008-2-17 18:04:09 46476 1 变成
A1_1TE02 2008-2-17 5:01:03 2008-2-17 5:09:33 510 1 //注意这里的结束时间和时长。
A1_1TE02 2008-2-17 5:09:33 2008-2-17 18:04:09 46476 1