我有一个表,表里面有一些字段,记录了地市公司发生某些错误的时间,地市发生一次错误即生成一条记录。表结构:地市名称 发生错误的时间我有什么办法可以快速的查找出‘在连续的24小时内发生错误超过10次的地市’?如果有的话,返回地市名称和错误次数,谢谢各位!
解决方案 »
- oracle11g查询操作碰到的一个问题
- oracle 存储过程 中返回记录集判断有无记录集如何处理
- 大表查询优化有哪些方法
- 超复杂的求和;求平均的行转列显示,请高人指点?
- 行列转换问题(一得到答案就给分)
- windows7 32 安装Oracle出错
- 在Oracle中VARCHAR2型字段最多能保存多少个汉字?现在定义VARCHAR2(4000)只能保存1000个汉字?
- 为什么建立数据库不成功!?
- 在ORACLE中外连接(+)是不是等同于MSSQL中的Left join?????
- 求助:sql查询 ora 03113
- oracle语句查询问题
- Oracle 插入数据是报错:ORA-00936 缺失表达式?
不知道这个是不是满足你的要求
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