17,086
社区成员
发帖
与我相关
我的任务
分享
with
t as (select 'A' name,001 item,0.98 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 09:06:50','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,004 item,0.95 value,to_date('2013/7/24 10:10:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.98 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'C' name,005 item,0.95 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual),
t1 as (select name,item,value,decode(value,lag(value)over(partition by name order by rectime desc),1,0)is_value,rectime from t),
t2 as (select name,sum(t1.is_value)over(partition by name order by rectime desc
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)sum_is_value from t1) /*查询4个连续的value相同(5个把4 PRECEDING替换成5 PRECEDING)*/
select distinct name
from t2
where t2.sum_is_value>=3 /*查询4个连续的value相同(5个把sum_is_value>=3替换成sum_is_value>=4)*/;
[/quote]
这个不错with
t as (select 'A' name,001 item,0.98 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 09:06:50','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,004 item,0.95 value,to_date('2013/7/24 10:10:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.98 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'C' name,005 item,0.95 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual),
t1 as (select name,item,value,decode(value,lag(value)over(partition by name order by rectime desc),1,0)is_value,rectime from t),
t2 as (select name,sum(t1.is_value)over(partition by name order by rectime desc
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)sum_is_value from t1) /*查询4个连续的value相同(5个把4 PRECEDING替换成5 PRECEDING)*/
select distinct name
from t2
where t2.sum_is_value>=3 /*查询4个连续的value相同(5个把sum_is_value>=3替换成sum_is_value>=4)*/;
select * from table t,
(select name, value, min(rectime) min_time, max(rectime) max_time from table
group by name ,value having count(*)>=3)v
where t.name=v.name and t.value=v.value
and not exists(select 1 from table tt where tt.name=v.name and tt.value<>v.value
and tt.rectime>v.min_time and tt.rectime<v.max_time)
with
t as (select 'A' name,001 item,0.98 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 09:06:50','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'A' name,002 item,0.98 value,to_date('2013/7/23 08:00:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,004 item,0.95 value,to_date('2013/7/24 10:10:30','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'B' name,002 item,0.95 value,to_date('2013/7/24 03:05:00','yyyy/mm/dd hh24:mi:ss')rectime from dual
union all
select 'C' name,005 item,0.95 value,to_date('2013/7/24 12:12:30','yyyy/mm/dd hh24:mi:ss')rectime from dual)
select distinct t1.name
from (select t.*,lag(value)over(partition by name order by rectime desc)lag_value,
lead(value)over(partition by name order by rectime desc)lead_value from t )t1
where t1.value=nvl(lag_value,0) and t1.value=nvl(lead_value,0);