我这个例子是查询连续 24 小时以内,发生错误超过 3 次的地市的 不知道这个是不是满足你的要求 with tbl as ( select '纽约' as sarea, sysdate - 1 as sdate from dual union all select '纽约' as sarea, sysdate - 0.5 from dual union all select '纽约' as sarea, sysdate + 1/24 from dual union all select '华盛顿' as sarea, sysdate - 0.5 from dual union all select '华盛顿' as sarea, sysdate - 0.7 from dual union all select '华盛顿' as sarea, sysdate from dual union all select '迈阿密' as sarea, sysdate - 2 from dual union all select '迈阿密' as sarea, sysdate - 1 from dual union all select '迈阿密' as sarea, sysdate - 0.5 from dual union all select '迈阿密' as sarea, sysdate - 0.6 from dual union all select '迈阿密' as sarea, sysdate - 0.7 from dual union all select '迈阿密' as sarea, sysdate + 0.1 from dual
) select sarea,icount from (select t.*, lag(sdate) over(partition by sarea order by sdate) as lag_date from (select sarea, t1.sdate, (select count(*) from tbl t2 where t1.sarea = t2.sarea and t2.sdate >= t1.sdate and t2.sdate <= t1.sdate + 1) as icount from tbl t1) t where t.icount >= 3) where lag_date is null;
试试这句呢: SELECT DISTINCT CITY FROM (SELECT T1.CITY FROM TB1 T1, (SELECT CITY, ERROR_TIME START_TIME, ERROR_TIME + 1 END_TIME FROM TB1) T2 WHERE T1.CITY = T2.CITY AND T1.ERROR_TIME >= T2.START_TIME AND T1.ERROR_TIME < T2.END_TIME GROUP BY T1.CITY, T1.ERROR_TIME HAVING COUNT(T1.ROWID) > 10)
上面那句不对,这句才是: SELECT DISTINCT CITY FROM (SELECT T2.CITY, T2.START_TIME, T1.ERROR_TIME FROM TB1 T1, (SELECT CITY, ERROR_TIME START_TIME, ERROR_TIME + 5 / (24 * 60 * 60) END_TIME FROM TB1) T2 WHERE T1.CITY = T2.CITY AND T1.ERROR_TIME >= T2.START_TIME AND T1.ERROR_TIME < T2.END_TIME GROUP BY T2.CITY, T2.START_TIME HAVING COUNT(T1.ROWID) > 10)
这样可以吗? select city,err_time from ( select city,err_time --前第10次的错误时间 ,lag(err_time,9) over (partition by city order by err_time) pre_err_time from t1 ) t2 --当前错误时间与前第10次时间差距在10小时以内 where err_time - pre_err_time <= 10/24 order by 1,2
这句,上面那句错看成10小时了: select city,err_time from ( select city,err_time --前第10次的错误时间 ,lag(err_time,9) over (partition by city order by err_time) pre_err_time from t1 ) t2 --当前错误时间与前第10次时间差距在10小时以内 where err_time - pre_err_time <= 1 order by 1,2
不知道这个是不是满足你的要求
with tbl as
(
select '纽约' as sarea, sysdate - 1 as sdate from dual
union all
select '纽约' as sarea, sysdate - 0.5 from dual
union all
select '纽约' as sarea, sysdate + 1/24 from dual
union all
select '华盛顿' as sarea, sysdate - 0.5 from dual
union all
select '华盛顿' as sarea, sysdate - 0.7 from dual
union all
select '华盛顿' as sarea, sysdate from dual
union all
select '迈阿密' as sarea, sysdate - 2 from dual
union all
select '迈阿密' as sarea, sysdate - 1 from dual
union all
select '迈阿密' as sarea, sysdate - 0.5 from dual
union all
select '迈阿密' as sarea, sysdate - 0.6 from dual
union all
select '迈阿密' as sarea, sysdate - 0.7 from dual
union all
select '迈阿密' as sarea, sysdate + 0.1 from dual
)
select sarea,icount
from (select t.*, lag(sdate) over(partition by sarea order by sdate) as lag_date
from (select sarea, t1.sdate, (select count(*) from tbl t2 where t1.sarea = t2.sarea and t2.sdate >= t1.sdate and t2.sdate <= t1.sdate + 1) as icount
from tbl t1) t
where t.icount >= 3)
where lag_date is null;
SELECT DISTINCT CITY
FROM (SELECT T1.CITY
FROM TB1 T1,
(SELECT CITY, ERROR_TIME START_TIME, ERROR_TIME + 1 END_TIME
FROM TB1) T2
WHERE T1.CITY = T2.CITY
AND T1.ERROR_TIME >= T2.START_TIME
AND T1.ERROR_TIME < T2.END_TIME
GROUP BY T1.CITY, T1.ERROR_TIME
HAVING COUNT(T1.ROWID) > 10)
SELECT DISTINCT CITY
FROM (SELECT T2.CITY, T2.START_TIME, T1.ERROR_TIME
FROM TB1 T1,
(SELECT CITY,
ERROR_TIME START_TIME,
ERROR_TIME + 5 / (24 * 60 * 60) END_TIME
FROM TB1) T2
WHERE T1.CITY = T2.CITY
AND T1.ERROR_TIME >= T2.START_TIME
AND T1.ERROR_TIME < T2.END_TIME
GROUP BY T2.CITY, T2.START_TIME
HAVING COUNT(T1.ROWID) > 10)
select city,err_time
from (
select city,err_time
--前第10次的错误时间
,lag(err_time,9) over (partition by city order by err_time) pre_err_time
from t1
) t2
--当前错误时间与前第10次时间差距在10小时以内
where err_time - pre_err_time <= 10/24
order by 1,2
select city,err_time
from (
select city,err_time
--前第10次的错误时间
,lag(err_time,9) over (partition by city order by err_time) pre_err_time
from t1
) t2
--当前错误时间与前第10次时间差距在10小时以内
where err_time - pre_err_time <= 1
order by 1,2