如果把第一个字段仅仅理解为数字,那么可以这么写的with testT as (select 801 as a,1 as b from dual union all select 802,2 from dual union all select 803,3 from dual union all select 815,3 from dual union all select 816,3 from dual union all select 825,4 from dual union all select 830,4 from dual union all select 831,4 from dual ) select to_char(801+to_number(st.groupflag)*15)||'-'||to_char(801+(to_number(st.groupflag)+1)*15-1),sum(st.b) from ( select t.*,floor((t.a-801)/15) as groupflag from testT t ) st group by st.groupflag order by st.groupflag;
with test as ( select '801' as hhmm, '1' as cnt from dual union select '802' as hhmm, '2' as cnt from dual union select '810' as hhmm, '3' as cnt from dual union select '823' as hhmm, '2' as cnt from dual union select '829' as hhmm, '3' as cnt from dual union select '830' as hhmm, '4' as cnt from dual union select '831' as hhmm, '3' as cnt from dual union select '901' as hhmm, '2' as cnt from dual union select '902' as hhmm, '3' as cnt from dual union select '919' as hhmm, '2' as cnt from dual union select '930' as hhmm, '6' as cnt from dual union select '931' as hhmm, '1' as cnt from dual union select '935' as hhmm, '1' as cnt from dual union select '959' as hhmm, '2' as cnt from dual )SELECT TRUNC(HHMM / 100) ,TRUNC((MOD(HHMM, 100) - 1) / 15) ,sum(CNT) FROM TEST GROUP BY TRUNC(HHMM / 100) ,TRUNC((MOD(HHMM, 100) - 1) / 15) ORDER BY TRUNC(HHMM / 100) ,TRUNC((MOD(HHMM, 100) - 1) / 15)如果第一个字段只是数字的话,这个应该可以。 分组的第二个字段代表的就是4个时段,00-15,16-30,31-45,46-59。或者用case when判断也可以,那样可能代码更容易理解,个人喜好吧
-- 凑个人数 with test as ( select '700' as RowTime, '8' as Count1 from dual unionselect '801' as RowTime, '1' as Count1 from dual union select '802' as RowTime, '2' as Count1 from dual union select '810' as RowTime, '3' as Count1 from dual union select '815' as RowTime, '3' as Count1 from dual union select '816' as RowTime, '3' as Count1 from dual union select '823' as RowTime, '2' as Count1 from dual union select '829' as RowTime, '3' as Count1 from dual union select '830' as RowTime, '4' as Count1 from dual union select '831' as RowTime, '3' as Count1 from dual union select '901' as RowTime, '2' as Count1 from dual union select '902' as RowTime, '3' as Count1 from dual union select '919' as RowTime, '2' as Count1 from dual union select '930' as RowTime, '6' as Count1 from dual union select '931' as RowTime, '1' as Count1 from dual union select '935' as RowTime, '1' as Count1 from dual union select '958' as RowTime, '8' as Count1 from dual union select '959' as RowTime, '2' as Count1 from dual ), b as ( select trunc((substr(RowTime,2,2) -1) / 15) k , substr(RowTime,1,1) hh, Count1 from test ) select hh || ':' || lpad((k * 15 + 1),2,'0') || '-' || decode(k,3,hh+1,hh) || ':'|| lpad(mod((k +1) *15, 60),2,'0') as x , k , sum(Count1) from b group by hh , k order by 1,2
WITH convert_time_count AS ( SELECT SUBSTR(row_time,1,1)||':01--'||SUBSTR(row_time,1,1)||':15' AS period_time,row_time,count1 period_count FROM time_count WHERE SUBSTR(row_time,2) BETWEEN 01 AND 15 UNION ALL SELECT SUBSTR(row_time,1,1)||':16--'||SUBSTR(row_time,1,1)||':30',row_time,count1 FROM time_count WHERE SUBSTR(row_time,2) BETWEEN 16 AND 30 UNION ALL SELECT SUBSTR(row_time,1,1)||':31--'||SUBSTR(row_time,1,1)||':45',row_time,count1 FROM time_count WHERE SUBSTR(row_time,2) BETWEEN 31 AND 45 UNION ALL SELECT SUBSTR(row_time,1,1)||':46--'||SUBSTR(row_time+100,1,2)||':00',row_time,count1 FROM time_count WHERE row_time BETWEEN to_number(SUBSTR(row_time,1,1)||'46') AND to_number(SUBSTR(row_time+100,1,2)||'00') ) SELECT period_time,SUM(period_count) total FROM convert_time_count GROUP BY period_time ORDER BY period_time ASC;适用于任何时间段,前提是hh24
WITH convert_time_count AS ( SELECT DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||':01--'||SUBSTR(row_time,1,1)||':15',4,SUBSTR(row_time,1,2)||':01--'||SUBSTR(row_time,1,2)||':15') AS period_time,row_time,count1 period_count FROM time_count WHERE DECODE(LENGTH(row_time),3,SUBSTR(row_time,2),4,SUBSTR(row_time,3)) BETWEEN 01 AND 15 UNION ALL SELECT DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||':16--'||SUBSTR(row_time,1,1)||':30',4,SUBSTR(row_time,1,2)||':16--'||SUBSTR(row_time,1,2)||':30'), row_time,count1 FROM time_count WHERE DECODE(LENGTH(row_time),3,SUBSTR(row_time,2),4,SUBSTR(row_time,3)) BETWEEN 16 AND 30 UNION ALL SELECT DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||':31--'||SUBSTR(row_time,1,1)||':45',4,SUBSTR(row_time,1,2)||':31--'||SUBSTR(row_time,1,2)||':45'), row_time,count1 FROM time_count WHERE DECODE(LENGTH(row_time),3,SUBSTR(row_time,2),4,SUBSTR(row_time,3)) BETWEEN 31 AND 45 UNION ALL SELECT DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||':46--'||decode(LENGTH(row_time+100),3,SUBSTR(row_time,1,1),4,SUBSTR(row_time+100,1,2))||':00',4,SUBSTR(row_time,1,2)||':46--'||SUBSTR(row_time+100,1,2)||':00'), row_time,count1 FROM time_count WHERE row_time BETWEEN to_number(DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||'46',4,SUBSTR(row_time,1,2)||'46')) AND to_number(DECODE(LENGTH(row_time),3,DECODE(LENGTH(row_time+100),3,SUBSTR(row_time+100,1,1),4,SUBSTR(row_time+100,1,2))||'00',4,SUBSTR(row_time+100,1,2)||'00')) AND DECODE(LENGTH(row_time),4,SUBSTR(row_time,1,2),3,row_time)!=23 UNION ALL SELECT '23:46--00:00',row_time,count1 FROM time_count WHERE row_time>2345 ) SELECT period_time,SUM(period_count) total FROM convert_time_count GROUP BY period_time ORDER BY to_date(SUBSTR(period_time,1,INSTR(period_time,'-',1,1)-1),'hh24:mi')写SQL太麻烦,这个是24小时的,可以测试一下,看看行不行。
(select 801 as a,1 as b from dual
union all
select 802,2 from dual
union all
select 803,3 from dual
union all
select 815,3 from dual
union all
select 816,3 from dual
union all
select 825,4 from dual
union all
select 830,4 from dual
union all
select 831,4 from dual
)
select to_char(801+to_number(st.groupflag)*15)||'-'||to_char(801+(to_number(st.groupflag)+1)*15-1),sum(st.b) from
(
select t.*,floor((t.a-801)/15) as groupflag from testT t
) st
group by st.groupflag
order by st.groupflag;
select '801' as hhmm, '1' as cnt from dual union
select '802' as hhmm, '2' as cnt from dual union
select '810' as hhmm, '3' as cnt from dual union
select '823' as hhmm, '2' as cnt from dual union
select '829' as hhmm, '3' as cnt from dual union
select '830' as hhmm, '4' as cnt from dual union
select '831' as hhmm, '3' as cnt from dual union
select '901' as hhmm, '2' as cnt from dual union
select '902' as hhmm, '3' as cnt from dual union
select '919' as hhmm, '2' as cnt from dual union
select '930' as hhmm, '6' as cnt from dual union
select '931' as hhmm, '1' as cnt from dual union
select '935' as hhmm, '1' as cnt from dual union
select '959' as hhmm, '2' as cnt from dual )SELECT TRUNC(HHMM / 100)
,TRUNC((MOD(HHMM, 100) - 1) / 15)
,sum(CNT)
FROM TEST
GROUP BY TRUNC(HHMM / 100)
,TRUNC((MOD(HHMM, 100) - 1) / 15)
ORDER BY TRUNC(HHMM / 100)
,TRUNC((MOD(HHMM, 100) - 1) / 15)如果第一个字段只是数字的话,这个应该可以。
分组的第二个字段代表的就是4个时段,00-15,16-30,31-45,46-59。或者用case when判断也可以,那样可能代码更容易理解,个人喜好吧
-- 凑个人数
with test as (
select '700' as RowTime, '8' as Count1 from dual unionselect '801' as RowTime, '1' as Count1 from dual union
select '802' as RowTime, '2' as Count1 from dual union
select '810' as RowTime, '3' as Count1 from dual union
select '815' as RowTime, '3' as Count1 from dual union select '816' as RowTime, '3' as Count1 from dual union
select '823' as RowTime, '2' as Count1 from dual union
select '829' as RowTime, '3' as Count1 from dual union
select '830' as RowTime, '4' as Count1 from dual union select '831' as RowTime, '3' as Count1 from dual union select '901' as RowTime, '2' as Count1 from dual union
select '902' as RowTime, '3' as Count1 from dual union select '919' as RowTime, '2' as Count1 from dual union
select '930' as RowTime, '6' as Count1 from dual union select '931' as RowTime, '1' as Count1 from dual union
select '935' as RowTime, '1' as Count1 from dual union select '958' as RowTime, '8' as Count1 from dual union
select '959' as RowTime, '2' as Count1 from dual ),
b as (
select trunc((substr(RowTime,2,2) -1) / 15) k , substr(RowTime,1,1) hh, Count1 from test )
select hh || ':' || lpad((k * 15 + 1),2,'0') || '-' || decode(k,3,hh+1,hh) || ':'|| lpad(mod((k +1) *15, 60),2,'0') as x , k , sum(Count1) from b
group by hh , k
order by 1,2
(
SELECT SUBSTR(row_time,1,1)||':01--'||SUBSTR(row_time,1,1)||':15' AS period_time,row_time,count1 period_count FROM time_count WHERE SUBSTR(row_time,2) BETWEEN 01 AND 15
UNION ALL
SELECT SUBSTR(row_time,1,1)||':16--'||SUBSTR(row_time,1,1)||':30',row_time,count1 FROM time_count WHERE SUBSTR(row_time,2) BETWEEN 16 AND 30
UNION ALL
SELECT SUBSTR(row_time,1,1)||':31--'||SUBSTR(row_time,1,1)||':45',row_time,count1 FROM time_count WHERE SUBSTR(row_time,2) BETWEEN 31 AND 45
UNION ALL
SELECT SUBSTR(row_time,1,1)||':46--'||SUBSTR(row_time+100,1,2)||':00',row_time,count1 FROM time_count
WHERE row_time BETWEEN to_number(SUBSTR(row_time,1,1)||'46') AND to_number(SUBSTR(row_time+100,1,2)||'00')
)
SELECT period_time,SUM(period_count) total FROM convert_time_count GROUP BY period_time ORDER BY period_time ASC;适用于任何时间段,前提是hh24
(
SELECT DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||':01--'||SUBSTR(row_time,1,1)||':15',4,SUBSTR(row_time,1,2)||':01--'||SUBSTR(row_time,1,2)||':15') AS
period_time,row_time,count1 period_count FROM time_count WHERE DECODE(LENGTH(row_time),3,SUBSTR(row_time,2),4,SUBSTR(row_time,3)) BETWEEN 01 AND 15
UNION ALL
SELECT DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||':16--'||SUBSTR(row_time,1,1)||':30',4,SUBSTR(row_time,1,2)||':16--'||SUBSTR(row_time,1,2)||':30'),
row_time,count1 FROM time_count WHERE DECODE(LENGTH(row_time),3,SUBSTR(row_time,2),4,SUBSTR(row_time,3)) BETWEEN 16 AND 30
UNION ALL
SELECT DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||':31--'||SUBSTR(row_time,1,1)||':45',4,SUBSTR(row_time,1,2)||':31--'||SUBSTR(row_time,1,2)||':45'),
row_time,count1 FROM time_count WHERE DECODE(LENGTH(row_time),3,SUBSTR(row_time,2),4,SUBSTR(row_time,3)) BETWEEN 31 AND 45
UNION ALL
SELECT DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||':46--'||decode(LENGTH(row_time+100),3,SUBSTR(row_time,1,1),4,SUBSTR(row_time+100,1,2))||':00',4,SUBSTR(row_time,1,2)||':46--'||SUBSTR(row_time+100,1,2)||':00'),
row_time,count1 FROM time_count WHERE row_time BETWEEN
to_number(DECODE(LENGTH(row_time),3,SUBSTR(row_time,1,1)||'46',4,SUBSTR(row_time,1,2)||'46')) AND
to_number(DECODE(LENGTH(row_time),3,DECODE(LENGTH(row_time+100),3,SUBSTR(row_time+100,1,1),4,SUBSTR(row_time+100,1,2))||'00',4,SUBSTR(row_time+100,1,2)||'00')) AND
DECODE(LENGTH(row_time),4,SUBSTR(row_time,1,2),3,row_time)!=23
UNION ALL
SELECT '23:46--00:00',row_time,count1 FROM time_count WHERE row_time>2345
)
SELECT period_time,SUM(period_count) total FROM convert_time_count GROUP BY period_time ORDER BY to_date(SUBSTR(period_time,1,INSTR(period_time,'-',1,1)-1),'hh24:mi')写SQL太麻烦,这个是24小时的,可以测试一下,看看行不行。