类型 日期（yyyymmdd） 操作时间（yyyy-mm-dd hh24:mi:ss） 序列 00-99
type1 20120801 20120801 13:01:01 98
type1 20120801 20120801 13:01:02 99
type1 20120801 20120801 13:02:02 00
type1 20120801 20120801 13:02:03 01type2 20120801 20120801 13:03:01 99
type2 20120801 20120801 13:03:02 00
type2 20120801 20120801 13:05:01 02各位高手请指点一下，这样怎么写统计SQL
因为序列为00－99 按操作时间顺序并且“循环”但不会有重复数据。结实现结果类型 日期 是否连续 开始序列 结束序列 条数
type1 20120801 是 98 01 4
type2 20120801 否 99 02 3
type1 20120801 20120801 13:01:01 98
type1 20120801 20120801 13:01:02 99
type1 20120801 20120801 13:02:02 00
type1 20120801 20120801 13:02:03 01type2 20120801 20120801 13:03:01 99
type2 20120801 20120801 13:03:02 00
type2 20120801 20120801 13:05:01 02各位高手请指点一下，这样怎么写统计SQL
因为序列为00－99 按操作时间顺序并且“循环”但不会有重复数据。结实现结果类型 日期 是否连续 开始序列 结束序列 条数
type1 20120801 是 98 01 4
type2 20120801 否 99 02 3
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
type1 20120801 20120801 13:01:02 99
type1 20120801 20120801 13:02:02 00
这两条数据，分析你的意思是 先插入99 然后序列循环重新为 00
但是我也可以认为是 先插入了 00，然后01~98 全部断号了，因为你的时间相同，这是一个缺陷
建议把时间粒度改为 timestamp
简单起见，我直接用1楼的数据了，另外在sql上我加了注释，你仔细看看，我想应该可以弄明白。[TEST@myoracle] SQL>create table t(
2 类型 varchar2(5),
3 日期 varchar2(8),
4 操作时间 date,
5 序列 varchar2(2)
6 );表已创建。[TEST@myoracle] SQL>insert into t values('type1','20120801',to_date('20120801 13:01:01','yyyymmdd hh24:mi:ss'),'98');已创建 1 行。[TEST@myoracle] SQL>insert into t values('type1','20120801',to_date('20120801 13:01:02','yyyymmdd hh24:mi:ss'),'99');已创建 1 行。[TEST@myoracle] SQL>insert into t values('type1','20120801',to_date('20120801 13:02:02','yyyymmdd hh24:mi:ss'),'00');已创建 1 行。[TEST@myoracle] SQL>insert into t values('type1','20120801',to_date('20120801 13:02:03','yyyymmdd hh24:mi:ss'),'01');已创建 1 行。[TEST@myoracle] SQL>insert into t values('type2','20120801',to_date('20120801 13:03:01','yyyymmdd hh24:mi:ss'),'99');已创建 1 行。[TEST@myoracle] SQL>insert into t values('type2','20120801',to_date('20120801 13:03:02','yyyymmdd hh24:mi:ss'),'00');已创建 1 行。[TEST@myoracle] SQL>insert into t values('type2','20120801',to_date('20120801 13:05:01','yyyymmdd hh24:mi:ss'),'02');已创建 1 行。[TEST@myoracle] SQL>commit;提交完成。
[TEST@myoracle] SQL>col 是否连续 format a8
[TEST@myoracle] SQL>col 开始序列 format a8
[TEST@myoracle] SQL>col 结束序列 format a8
[TEST@myoracle] SQL>SELECT 类型,
2 日期,
3 DECODE(MIN( --取最小值 如果取到的是 1：说明序列是连续的，0：说明序列是不连续的
4 CASE
5 --如果当前的序列-下一条的序列 不在 (0, 1, 99) 中，说明 序列不是连续的
6 WHEN ABS(序列 - LEAD_) NOT IN (0, 1, 99) THEN
7 0 --不连续用0表示
8 ELSE
9 1 --连续用1表示
10 END),
11 0,
12 '否', -- 0：说明序列是不连续的，转换为"否"
13 '是' -- 1：说明序列是连续的，转换为"是"
14 ) 是否连续,
15 START_V 开始序列,
16 END_V 结束序列,
17 COUNT(1) 条数
18 FROM (SELECT 类型,
19 日期,
20 操作时间,
21 序列,
22 --按照类型和日期分组、操作时间升序排序，取第一条数据即 开始序列
23 FIRST_VALUE(序列) OVER(PARTITION BY 类型, 日期 ORDER BY 操作时间) START_V,
24 --按照类型和日期分组、操作时间降序排序，取第一条数据即 结束序列
25 FIRST_VALUE(序列) OVER(PARTITION BY 类型, 日期 ORDER BY 操作时间 DESC) END_V,
26 --按照类型和日期分组、操作时间升序排序 取下一条数据的 序列，取不到的取当前条
27 LEAD(序列, 1, 序列) OVER(PARTITION BY 类型, 日期 ORDER BY 操作时间) LEAD_
28 FROM T)
29 GROUP BY 类型, 日期, START_V, END_V --因为相同的[类型, 日期]分组[START_V, END_V]也是相同的，想不明白可以单独执行下内层sql
30 ;类型 日期 是否连续 开始序列 结束序列 条数
----- -------- -------- -------- -------- ----------
type1 20120801 是 98 01 4
type2 20120801 否 99 02 3[TEST@myoracle] SQL>
--单独贴一个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 操作时间 DESC) END_V,
--按照类型和日期分组、操作时间升序排序 取下一条数据的 序列，取不到的取当前条
LEAD(序列, 1, 序列) OVER(PARTITION BY 类型, 日期 ORDER BY 操作时间) LEAD_
FROM T)
GROUP BY 类型, 日期, START_V, END_V --因为相同的[类型, 日期]分组[START_V, END_V]也是相同的，想不明白可以单独执行下内层sql
SELECT 'type1' ttype,'20120801' tdate,to_date('20120801 13:01:01','yyyymmdd hh24:mi:ss') operateDate,'98' seq FROM DUAL UNION ALL
SELECT 'type1' ttype,'20120801' tdate,to_date('20120801 13:01:02','yyyymmdd hh24:mi:ss') operateDate,'99' seq FROM DUAL UNION ALL
SELECT 'type1' ttype,'20120801' tdate,to_date('20120801 13:02:02','yyyymmdd hh24:mi:ss') operateDate,'00' seq FROM DUAL UNION ALL
SELECT 'type1' ttype,'20120801' tdate,to_date('20120801 13:02:03','yyyymmdd hh24:mi:ss') operateDate,'01' seq FROM DUAL UNION ALL
SELECT 'type2' ttype,'20120801' tdate,to_date('20120801 13:01:01','yyyymmdd hh24:mi:ss') operateDate,'99' seq FROM DUAL UNION ALL
SELECT 'type2' ttype,'20120801' tdate,to_date('20120801 13:01:02','yyyymmdd hh24:mi:ss') operateDate,'00' seq FROM DUAL UNION ALL
SELECT 'type2' ttype,'20120801' tdate,to_date('20120801 13:01:03','yyyymmdd hh24:mi:ss') operateDate,'02' seq FROM DUAL UNION ALL
SELECT 'type3' ttype,'20120801' tdate,to_date('20120801 13:01:01','yyyymmdd hh24:mi:ss') operateDate,'00' seq FROM DUAL UNION ALL
SELECT 'type3' ttype,'20120801' tdate,to_date('20120801 13:01:02','yyyymmdd hh24:mi:ss') operateDate,'99' seq FROM DUAL UNION ALL
SELECT 'type4' ttype,'20120801' tdate,to_date('20120801 13:01:01','yyyymmdd hh24:mi:ss') operateDate,'00' seq FROM DUAL UNION ALL
SELECT 'type4' ttype,'20120801' tdate,to_date('20120801 13:01:02','yyyymmdd hh24:mi:ss') operateDate,'99' seq FROM DUAL
)
SELECT m.ttype,
MAX(m.tdate) tdate,
SUM(DECODE(m.offset, 1, 0, -99, 0, 1)) isContinue,
MAX(m.seq) KEEP(DENSE_RANK FIRST ORDER BY m.rn) start_seq,
MAX(m.seq) KEEP(DENSE_RANK LAST ORDER BY m.rn) end_seq,
COUNT(*) total_cnt
FROM (SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY t.ttype ORDER BY t.operateDate) rn,
NVL(seq - LAG(t.seq, 1) OVER(PARTITION BY t.ttype ORDER BY t.operateDate), 1) offset
FROM tb t) m
GROUP BY m.ttypeTTYPE TDATE ISCONTINUE START_SEQ END_SEQ TOTAL_CNT
----- -------- ---------- --------- ------- ----------
type1 20120801 0 98 01 4
type2 20120801 1 99 02 3
type3 20120801 1 00 99 2
type4 20120801 1 00 99 2