情况是这样的:
现有一张设备认证流程表,表里的数据有存这么几个字段:设备编号,认证时间,故障信息。也就是说“故障信息”这个字段是存的一个字符串,是多故障信息组成的,如果设备有故障时就向该表插入一条记录,故障信息就等于该设备上所有故障。所有故障都解决了,再向该表写入一条数据,故障信息就为空。现需要统计发生故障的时间。
设备编号 认证时间 故障信息
AAAAAAA 200703131030 故障1
AAAAAAA 200703131031 故障1故障2
AAAAAAA 200703131035 故障1故障2故障3
AAAAAAA 200703131037 故障1
AAAAAAA 200703131237 null
故障时间及为 200703131237 -200703131030
请问这个统计要怎么写呢?
现有一张设备认证流程表,表里的数据有存这么几个字段:设备编号,认证时间,故障信息。也就是说“故障信息”这个字段是存的一个字符串,是多故障信息组成的,如果设备有故障时就向该表插入一条记录,故障信息就等于该设备上所有故障。所有故障都解决了,再向该表写入一条数据,故障信息就为空。现需要统计发生故障的时间。
设备编号 认证时间 故障信息
AAAAAAA 200703131030 故障1
AAAAAAA 200703131031 故障1故障2
AAAAAAA 200703131035 故障1故障2故障3
AAAAAAA 200703131037 故障1
AAAAAAA 200703131237 null
故障时间及为 200703131237 -200703131030
请问这个统计要怎么写呢?
from table
where 认证时间 between 200703131237 and 200703131030
and 故障信息 is not null;
SQL> select * from test;COL1 COL2 COL3
-------------------- -------------------- ------------------------------------------------------------
AAAAAAA 200703131030 故障1
AAAAAAA 200703131031 故障1故障2
AAAAAAA 200703131035 故障1故障2故障3
AAAAAAA 200703131037 故障1
AAAAAAA 200703131237
AAAAAAA 200703131537 故障1故障2
AAAAAAA 200703131435 故障1
AAAAAAA 200703131637 8 rows selectedSQL>
SQL> select col1, min_col2, max_col2, max_col2 - min_col2
2 from
3 ( select col1, min(to_date(col2, 'yyyymmddhh24mi')) min_col2 , max(to_date(col2, 'yyyymmddhh24mi')) max_col2
4 from ( select col1, col2, col3, rn, rs,
5 decode(col3,null, rn-lag(rs,1,0)over(partition by col1 order by rn, rs)-1 ,rn-rs) rp
6 from ( select col1, col2, col3, row_number()over(partition by col1 order by col2) rn,
7 row_number()over(partition by col1, decode(col3, null, null, 1) order by col2 ) rs
8 from test ) )
9 group by col1, rp )
10 /COL1 MIN_COL2 MAX_COL2 MAX_COL2-MIN_COL2
-------------------- ----------- ----------- -----------------
AAAAAAA 2007-3-13 1 2007-3-13 1 0.084722222222222
AAAAAAA 2007-3-13 1 2007-3-13 1 0.088194444444444
SQL> col max_col2 format a20;
SQL> col col1 format a10;
SQL>
SQL> select col1, min_col2, max_col2, round(max_col2 - min_col2, 2)
2 from
3 ( select col1, min(to_date(col2, 'yyyymmddhh24mi')) min_col2 , max(to_date(col2, 'yyyymmddhh24mi')) max_col2
4 from ( select col1, col2, col3, rn, rs,
5 decode(col3,null, rn-lag(rs,1,0)over(partition by col1 order by rn, rs)-1 ,rn-rs) rp
6 from ( select col1, col2, col3, row_number()over(partition by col1 order by col2) rn,
7 row_number()over(partition by col1, decode(col3, null, null, 1) order by col2 ) rs
8 from test ) )
9 group by col1, rp )
10 /COL1 MIN_COL2 MAX_COL2 ROUND(MAX_COL2-MIN_COL2,2)
---------- -------------------- -------------------- --------------------------
AAAAAAA 2007-3-13 14:35:00 2007-3-13 16:37:00 0.08
AAAAAAA 2007-3-13 10:30:00 2007-3-13 12:37:00 0.09