日期:2014-05-17 浏览次数:20712 次
SQL> create table t( 2 类型 varchar2(5), 3 日期 varchar2(8), 4 操作时间 date, 5 序列 varchar2(2) 6 ); 表已创建。 SQL> insert into t values('type1','20120801',to_date('20120801 13:01:01','yyyymmdd hh24:mi:ss'),'98'); 已创建 1 行。 SQL> insert into t values('type1','20120801',to_date('20120801 13:01:02','yyyymmdd hh24:mi:ss'),'99'); 已创建 1 行。 SQL> insert into t values('type1','20120801',to_date('20120801 13:02:02','yyyymmdd hh24:mi:ss'),'00'); 已创建 1 行。 SQL> insert into t values('type1','20120801',to_date('20120801 13:02:03','yyyymmdd hh24:mi:ss'),'01'); 已创建 1 行。 SQL> insert into t values('type2','20120801',to_date('20120801 13:03:01','yyyymmdd hh24:mi:ss'),'99'); 已创建 1 行。 SQL> insert into t values('type2','20120801',to_date('20120801 13:03:02','yyyymmdd hh24:mi:ss'),'00'); 已创建 1 行。 SQL> insert into t values('type2','20120801',to_date('20120801 13:05:01','yyyymmdd hh24:mi:ss'),'02'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select t.类型,t.日期, 2 case when count(t7.类型)>0 then '否' else '是' end 是否连续, 3 min(t3.开始序列) 开始序列,min(t4.结束序列) 结束序列,count(t.类型) 条数 4 from t 5 left join( 6 select t.类型,t.日期,t.序列 开始序列 7 from t,(select 类型,日期,min(操作时间) 最早操作时间 from t 8 group by 类型,日期) t1 9 where t.类型=t1.类型 and t.日期=t1.日期 and t.操作时间=t1.最早操作时间 10 ) t3 on t.类型=t3.类型 and t.日期=t3.日期 11 left join( 12 select t.类型,t.日期,t.序列 结束序列 13 from t,(select 类型,日期,max(操作时间) 最晚操作时间 from t 14 group by 类型,日期) t2 15 where t.类型=t2.类型 and t.日期=t2.日期 and t.操作时间=t2.最晚操作时间 16 ) t4 on t.类型=t4.类型 and t.日期=t4.日期 17 left join ( 18 select t5.类型,t5.日期 from 19 (select row_number() over(partition by 类型,日期 order by 操作时间) 顺序, 20 类型,日期,操作时间,序列 from t) t5, 21 (select row_number() over(partition by 类型,日期 order by 操作时间) 顺序, 22 类型,日期,操作时间,序列 from t) t6 23 where t5.类型=t6.类型 and t5.日期=t5.日期 and t5.顺序=t6.顺序-1 24 and abs(t6.序列-t5.序列) not in(1,99) 25 ) t7 on t.类型=t7.类型 and t.日期=t7.日期 26 group by t.类型,t.日期; 类型 日期 是否连 开始 结束 条数 ---------- ---------------- ------ ---- ---- ---------- type1 20120801 是 98 01 4 type2 20120801 否 99 02 3
------解决方案--------------------
--单独贴一个sql SELECT 类型, 日期, DECODE(MIN( --取最小值 如果取到的是 1:说明序列是连续的,0:说明序列是不连续的 CASE --如果当前的序列-下一条的序列 不在 (0, 1, 99) 中,说明 序列不是连续的 WHEN ABS(序列 - LEAD_) NOT IN (0, 1, 99) THEN 0 --不连续用0表示 ELSE 1 --连续用1表示 END), 0, '否', -- 0:说明序列是不连续的,转换为"否" '是' -- 1:说明序列是连续的,转换为"是" ) 是否连续, START_V 开始序列, END_V 结束序列, COUNT(1) 条数 FROM (SELECT 类型, 日期, 操作时间, 序列, --按照类型和日期分组、操作时间升序排序,取第一条数据即 开始序列 FIRST_VALUE(序列) OVER(PARTITION BY 类型, 日期 ORDER BY 操作时间) START_V, --按照类型和日期分组、操作时间降序排序,取第一条数据即 结束序列 FIRST_VALUE(序列) OVER(PARTITION BY 类型, 日期 ORDER BY 操作时间 D