sql 查找某列连续的几个值是否相同

iefus 2013-07-24 01:46:15
我有一个table类似下面

name item value rectime
A 001 0.98 2013/7/24 12:12:30
A 002 0.98 2013/7/23 09:06:50
A 002 0.95 2013/7/23 08:00:00
B 004 0.95 2013/7/24 10:10:30
B 002 0.95 2013/7/24 03:05:00
C 005 0.95 2013/7/24 12:12:30

我想实现 group by name ,order by rectime desc

找出是否有 3个连续的value相同
...全文
185 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
iefus 2013-07-30
引用 9 楼 jascjasc 的回复:
[quote=引用 3 楼 iefus 的回复:] [quote=引用 1 楼 jascjasc 的回复:] 是求出有三个连续的value相同的name吗?
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);
恩,满足条件的话,就返回name 值 错位的方法我也想过 我这边相同的3个值只是个例子,如果变成相同的4个,5个值呢 [/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)*/;
[/quote] 这个不错
回复
jascjasc 2013-07-25
引用 3 楼 iefus 的回复:
[quote=引用 1 楼 jascjasc 的回复:] 是求出有三个连续的value相同的name吗?
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);
恩,满足条件的话,就返回name 值 错位的方法我也想过 我这边相同的3个值只是个例子,如果变成相同的4个,5个值呢 [/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)*/;
回复
iefus 2013-07-24
引用 6 楼 zhaoxiangchong 的回复:
用分析函数,很常见的用法
类似2#么?
回复
yehuan911 2013-07-24
好得很,O(∩_∩)O谢谢
回复
善若止水 2013-07-24
用分析函数,很常见的用法
回复
shy315 2013-07-24
引用 4 楼 shy315 的回复:
写个容易理解的,先找出三次以上,然后判断期间没有不同value

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)
having>=3的逻辑错了,思路仅供参考。
回复
shy315 2013-07-24
写个容易理解的,先找出三次以上,然后判断期间没有不同value

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)
回复
iefus 2013-07-24
引用 1 楼 jascjasc 的回复:
是求出有三个连续的value相同的name吗?
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);
恩,满足条件的话,就返回name 值 错位的方法我也想过 我这边相同的3个值只是个例子,如果变成相同的4个,5个值呢
回复
yinan9 2013-07-24
楼上的不错
回复
jascjasc 2013-07-24
是求出有三个连续的value相同的name吗?
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);
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-07-24 01:46
社区公告
暂无公告