with tt as(select 1 id,to_date('2009-10-1 6:34:12','yyyy-mm-dd hh24:mi:ss')start_time,to_date('2009-10-2 22:34:12','yyyy-mm-dd hh24:mi:ss') end_time from dual union all select 2,to_date('2009-10-4 8:12:0','yyyy-mm-dd hh24:mi:ss')start_time,to_date('2009-10-4 20:30:12','yyyy-mm-dd hh24:mi:ss') from dual union all select 3,to_date('2009-10-5 20:12:0','yyyy-mm-dd hh24:mi:ss')start_time,to_date('2009-10-6 7:00:00','yyyy-mm-dd hh24:mi:ss') from dual union all select 4,to_date('2009-10-4 5:12:0','yyyy-mm-dd hh24:mi:ss')start_time,to_date('2009-10-8 20:30:12','yyyy-mm-dd hh24:mi:ss') from dual)select id,start_time,end_time,days, (case when start_time<t+21/24 and e_time>t+6/24 then (case when e_time>t+21/24 then t+21/24 else e_time end)-(case when start_time<t+6/24 then t+6/24 else start_time end) else 0 end +case when e_time>t+30/24 then decode(sign(e_time-t-45/24),1,15/24,e_time-t-30/24) else 0 end)*24+ days*15 高峰时间, (case when e_time>t+21/24 then (case when e_time>t+1 then t+1 else e_time end)-(case when start_time<t+21/24 then t+21/24 else start_time end) else 0 end +case when e_time>t+45/24 then e_time-t-45/24 else 0 end)*24+ days*3 低谷时间, case when start_time<t+21/24 and e_time>t+6/24 then 1 else 0 end+case when e_time>t+30/24 then 1 else 0 end+days 高峰次数, case when e_time>t+21/24 then 1 else 0 end+case when e_time>t+45/24 then 1 else 0 end+days 低谷次数 from( select id,trunc(end_time-start_time) days,start_time,trunc(start_time)t, start_time+mod(end_time-start_time,1) e_time,end_time from tt)单位为小时
用case语句或者是decord()函数都可以实现。
直接根据时间段取高峰次数和低谷次数,然后union不就行啦
[TEST@ora10gr1#2009-11-22/18:37:25] SQL>WITH 表名 AS( 2 select 1 id,to_date('2009-10-1 6:34:12','yyyy-mm-dd hh24:mi:ss')时间字段 from dual union all 3 select 1 ,to_date('2009-10-1 7:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 4 select 1 ,to_date('2009-10-1 8:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 5 select 1 ,to_date('2009-10-1 8:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 6 select 1 ,to_date('2009-10-1 10:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 7 select 1 ,to_date('2009-10-1 20:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 8 select 1 ,to_date('2009-10-1 21:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 9 select 1 ,to_date('2009-10-1 23:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 10 select 1 ,to_date('2009-10-2 6:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 11 select 1 ,to_date('2009-10-2 20:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 12 select 1 ,to_date('2009-10-2 21:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all 13 select 1 ,to_date('2009-10-2 23:34:12','yyyy-mm-dd hh24:mi:ss') from dual 14 ) 15 SELECT SUM(高峰出现最高时-高峰出现最低时)*24 AS "高峰持续时间(单位:小时)", 16 SUM(低谷出现最高时-低谷出现最低时)*24 AS "低谷持续时间(单位:小时)", 17 SUM(高峰出现次数) AS 高峰出现次数, 18 SUM(低谷出现次数) AS 低谷出现次数 19 FROM( 20 SELECT 21 MAX( 22 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2100' AND TO_CHAR(时间字段,'hh24mi')>='0600' THEN 时间字段 ELSE TO_DATE('0001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') END 23 ) AS 高峰出现最高时, 24 MIN( 25 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2100' AND TO_CHAR(时间字段,'hh24mi')>='0600' THEN 时间字段 ELSE TO_DATE('9999-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss') END 26 ) AS 高峰出现最低时, 27 MAX( 28 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2400' AND TO_CHAR(时间字段,'hh24mi')>='2100' THEN 时间字段 ELSE TO_DATE('0001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') END 29 ) AS 低谷出现最高时, 30 MIN( 31 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2400' AND TO_CHAR(时间字段,'hh24mi')>='2100' THEN 时间字段 ELSE TO_DATE('9999-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss') END 32 ) AS 低谷出现最低时, 33 SUM( 34 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2100' AND TO_CHAR(时间字段,'hh24mi')>='0600' THEN 1 ELSE 0 END 35 )AS 高峰出现次数, 36 SUM( 37 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2400' AND TO_CHAR(时间字段,'hh24mi')>='2100' THEN 1 ELSE 0 END 38 )AS 低谷出现次数 39 FROM 表名 40 WHERE 时间字段 BETWEEN to_date('2009-10-1 6:34:12','yyyy-mm-dd hh24:mi:ss') AND to_date('2009-10-2 23:34:12','yyyy-mm-dd hh24:mi:ss') 41 GROUP BY TO_CHAR(时间字段,'yyyy-mm-dd') 42 );高峰持续时间(单位:小时) 低谷持续时间(单位:小时) 高峰出现次数 低谷出现次数 --------------------- --------------------- ------------ ------------ 28 4 8 4-- between 和 and 中间,就是 start_time 和 end_time -- 另外:*24得到的单位是“小时”,你可以根据需要换成你想要的,如 *24*3600 单位是“秒”,什么也不写就是 “天”
end_time 就用2009-09-02 00:00:00
union all select 2,to_date('2009-10-4 8:12:0','yyyy-mm-dd hh24:mi:ss')start_time,to_date('2009-10-4 20:30:12','yyyy-mm-dd hh24:mi:ss') from dual
union all select 3,to_date('2009-10-5 20:12:0','yyyy-mm-dd hh24:mi:ss')start_time,to_date('2009-10-6 7:00:00','yyyy-mm-dd hh24:mi:ss') from dual
union all select 4,to_date('2009-10-4 5:12:0','yyyy-mm-dd hh24:mi:ss')start_time,to_date('2009-10-8 20:30:12','yyyy-mm-dd hh24:mi:ss') from dual)select id,start_time,end_time,days,
(case when start_time<t+21/24 and e_time>t+6/24
then (case when e_time>t+21/24 then t+21/24 else e_time end)-(case when start_time<t+6/24 then t+6/24 else start_time end)
else 0 end
+case when e_time>t+30/24 then decode(sign(e_time-t-45/24),1,15/24,e_time-t-30/24) else 0 end)*24+
days*15 高峰时间,
(case when e_time>t+21/24
then (case when e_time>t+1 then t+1 else e_time end)-(case when start_time<t+21/24 then t+21/24 else start_time end)
else 0 end
+case when e_time>t+45/24 then e_time-t-45/24 else 0 end)*24+
days*3 低谷时间,
case when start_time<t+21/24 and e_time>t+6/24 then 1 else 0 end+case when e_time>t+30/24 then 1 else 0 end+days 高峰次数,
case when e_time>t+21/24 then 1 else 0 end+case when e_time>t+45/24 then 1 else 0 end+days 低谷次数
from(
select id,trunc(end_time-start_time) days,start_time,trunc(start_time)t,
start_time+mod(end_time-start_time,1) e_time,end_time
from tt)单位为小时
[TEST@ora10gr1#2009-11-22/18:37:25] SQL>WITH 表名 AS(
2 select 1 id,to_date('2009-10-1 6:34:12','yyyy-mm-dd hh24:mi:ss')时间字段 from dual union all
3 select 1 ,to_date('2009-10-1 7:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
4 select 1 ,to_date('2009-10-1 8:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
5 select 1 ,to_date('2009-10-1 8:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
6 select 1 ,to_date('2009-10-1 10:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
7 select 1 ,to_date('2009-10-1 20:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
8 select 1 ,to_date('2009-10-1 21:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
9 select 1 ,to_date('2009-10-1 23:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
10 select 1 ,to_date('2009-10-2 6:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
11 select 1 ,to_date('2009-10-2 20:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
12 select 1 ,to_date('2009-10-2 21:34:12','yyyy-mm-dd hh24:mi:ss') from dual union all
13 select 1 ,to_date('2009-10-2 23:34:12','yyyy-mm-dd hh24:mi:ss') from dual
14 )
15 SELECT SUM(高峰出现最高时-高峰出现最低时)*24 AS "高峰持续时间(单位:小时)",
16 SUM(低谷出现最高时-低谷出现最低时)*24 AS "低谷持续时间(单位:小时)",
17 SUM(高峰出现次数) AS 高峰出现次数,
18 SUM(低谷出现次数) AS 低谷出现次数
19 FROM(
20 SELECT
21 MAX(
22 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2100' AND TO_CHAR(时间字段,'hh24mi')>='0600' THEN 时间字段 ELSE TO_DATE('0001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') END
23 ) AS 高峰出现最高时,
24 MIN(
25 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2100' AND TO_CHAR(时间字段,'hh24mi')>='0600' THEN 时间字段 ELSE TO_DATE('9999-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss') END
26 ) AS 高峰出现最低时,
27 MAX(
28 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2400' AND TO_CHAR(时间字段,'hh24mi')>='2100' THEN 时间字段 ELSE TO_DATE('0001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') END
29 ) AS 低谷出现最高时,
30 MIN(
31 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2400' AND TO_CHAR(时间字段,'hh24mi')>='2100' THEN 时间字段 ELSE TO_DATE('9999-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss') END
32 ) AS 低谷出现最低时,
33 SUM(
34 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2100' AND TO_CHAR(时间字段,'hh24mi')>='0600' THEN 1 ELSE 0 END
35 )AS 高峰出现次数,
36 SUM(
37 CASE WHEN TO_CHAR(时间字段,'hh24mi')<'2400' AND TO_CHAR(时间字段,'hh24mi')>='2100' THEN 1 ELSE 0 END
38 )AS 低谷出现次数
39 FROM 表名
40 WHERE 时间字段 BETWEEN to_date('2009-10-1 6:34:12','yyyy-mm-dd hh24:mi:ss') AND to_date('2009-10-2 23:34:12','yyyy-mm-dd hh24:mi:ss')
41 GROUP BY TO_CHAR(时间字段,'yyyy-mm-dd')
42 );高峰持续时间(单位:小时) 低谷持续时间(单位:小时) 高峰出现次数 低谷出现次数
--------------------- --------------------- ------------ ------------
28 4 8 4-- between 和 and 中间,就是 start_time 和 end_time
-- 另外:*24得到的单位是“小时”,你可以根据需要换成你想要的,如 *24*3600 单位是“秒”,什么也不写就是 “天”