如何按时间分档统计数据？

Question

我现在将一天的时间分成24档，没小时为一个档，即表A
start_time  end_time
          0:00          1:00
          1:00          2:00
          2:00          3:00
          3:00          4:00
          4:00          5:00
          5:00          6:00
          ...           ...如果现在有这样一条记录 ：2:20-5:40用户用了100的流量，我要根据表A的时间分档得到如下结果：
       start_time    end_time   liuliang
                 2:00            3:00         25
                 3:00            4:00         25
                 4:00            5:00         25
                 5:00            6:00         25
应该如何实现？

Answer 1

select to_char(trunc(sysdate) + (interval '1' hour) * (level - 1), 'hh24:mi') start_time,
       to_char(trunc(sysdate) + (interval '1' hour) * (level    ), 'hh24:mi') end_time
from dual
connect by level <= 24;不知道樓主的“2:20-5:40用户用了100的流量”從哪裡得來？怎樣分配？

Answer 2

这个“2:20-5:40用户用了100的流量”，我只是举个例子，可以算是从另外一个表得到吧，比如表B如下
id    start_time  end_time   liuliang
1      2:20         3:40       100
2      3:30         4:20        40
我想得到
id    start_time    end_time   liuliang
1      2:00          3:00       50
1      3:00          4:00       50
2      3:00          4:00       20
2      4:00          5:00       20
这样应该就清楚了

Answer 3

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
Connected as scott
 
SQL> select * from t_cal_hour;
 
        ID START_TIME  END_TIME      LIULIANG
---------- ----------- ----------- ----------
         1 2010-10-5 2 2010-10-5 4        120
         2 2010-10-6 3 2010-10-6 4         40
 
SQL> 
SQL> with aa as (select rownum rn  from dual connect by rownum<=(select max((trunc(end_time,'hh24')-trunc(start_time,'hh24'))*24+ceil(end_time-trunc(end_time,'hh')))-1 from t_cal_hour)),
  2   bb as( select id,trunc(start_time,'hh24')+(rn-1)/24 cal_start, trunc(start_time,'hh24')+rn/24 cal_end,b.start_time,b.end_time,liuliang
  3  from aa a, t_cal_hour b where trunc(b.start_time)+a.rn/24<=trunc(start_time,'hh24')+ceil(end_time-trunc(end_time,'hh'))/24)
  4  select id,cal_start,cal_end,start_time,end_time,liuliang,
  5  liuliang*(case when cal_end-trunc(end_time,'hh24')=1/24 then end_time else cal_end end -case when trunc(cal_start,'hh24')=trunc(start_time,'hh24') then start_time else cal_start end )
  6  /(end_time-start_time)
  7  from bb
  8  order by id,cal_start;
 
        ID CAL_START   CAL_END     START_TIME  END_TIME      LIULIANG LIULIANG*(CASEWHENCAL_END-TRUN
---------- ----------- ----------- ----------- ----------- ---------- ------------------------------
         1 2010-10-5 2 2010-10-5 3 2010-10-5 2 2010-10-5 4        120                             40
         1 2010-10-5 3 2010-10-5 4 2010-10-5 2 2010-10-5 4        120                             60
         1 2010-10-5 4 2010-10-5 5 2010-10-5 2 2010-10-5 4        120                             20
         2 2010-10-6 3 2010-10-6 4 2010-10-6 3 2010-10-6 4         40                             24
         2 2010-10-6 4 2010-10-6 5 2010-10-6 3 2010-10-6 4         40                             16
         2 2010-10-6 5 2010-10-6 6 2010-10-6 3 2010-10-6 4         40                             48
 
6 rows selected
 
SQL> 
SQL> with aa as (select rownum rn  from dual connect by rownum<=(select max((trunc(end_time,'hh24')-trunc(start_time,'hh24'))*24+ceil(end_time-trunc(end_time,'hh')))-1 from t_cal_hour)),
  2   bb as( select id,trunc(start_time,'hh24')+(rn-1)/24 cal_start, trunc(start_time,'hh24')+rn/24 cal_end,b.start_time,b.end_time,liuliang
  3  from aa a, t_cal_hour b where trunc(b.start_time)+a.rn/24<=trunc(start_time,'hh24')+ceil(end_time-trunc(end_time,'hh'))/24)
  4  select id,cal_start,cal_end,
  5  liuliang*(case when cal_end-trunc(end_time,'hh24')=1/24 then end_time else cal_end end -case when trunc(cal_start,'hh24')=trunc(start_time,'hh24') then start_time else cal_start end )
  6  /(end_time-start_time)
  7  from bb
  8  order by id,cal_start;
 
        ID CAL_START   CAL_END     LIULIANG*(CASEWHENCAL_END-TRUN
---------- ----------- ----------- ------------------------------
         1 2010-10-5 2 2010-10-5 3                             40
         1 2010-10-5 3 2010-10-5 4                             60
         1 2010-10-5 4 2010-10-5 5                             20
         2 2010-10-6 3 2010-10-6 4                             24
         2 2010-10-6 4 2010-10-6 5                             16
         2 2010-10-6 5 2010-10-6 6                             48
 
6 rows selected
 
SQL> 

Answer 4

上面写的有点问题.主要是trunc的时候格式有点问题.
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
Connected as scott
 
SQL> select * from t_cal_hour;
 
        ID START_TIME  END_TIME      LIULIANG
---------- ----------- ----------- ----------
         1 2010-10-5 2 2010-10-5 4        120
         2 2010-10-6 3 2010-10-6 4         40
 
SQL> 
SQL> with aa as (select rownum rn  from dual connect by rownum<=(select max((trunc(end_time,'hh24')-trunc(start_time,'hh24'))*24+ceil(end_time-trunc(end_time,'hh')))-1 from t_cal_hour)),
  2   bb as( select id,trunc(start_time,'hh24')+(rn-1)/24 cal_start, trunc(start_time,'hh24')+rn/24 cal_end,b.start_time,b.end_time,liuliang
  3  from aa a, t_cal_hour b where trunc(b.start_time,'hh24')+a.rn/24<=trunc(end_time,'hh24')+ceil(end_time-trunc(end_time,'hh24'))/24)
  4  select id,cal_start,cal_end,
  5  liuliang*(case when cal_end-trunc(end_time,'hh24')=1/24 then end_time else cal_end end -case when trunc(cal_start,'hh24')=trunc(start_time,'hh24') then start_time else cal_start end )
  6  /(end_time-start_time) cal_liuliang,start_time,end_time,liuliang
  7  from bb
  8  order by id,cal_start;
 
        ID CAL_START   CAL_END     CAL_LIULIANG START_TIME  END_TIME      LIULIANG
---------- ----------- ----------- ------------ ----------- ----------- ----------
         1 2010-10-5 2 2010-10-5 3           40 2010-10-5 2 2010-10-5 4        120
         1 2010-10-5 3 2010-10-5 4           60 2010-10-5 2 2010-10-5 4        120
         1 2010-10-5 4 2010-10-5 5           20 2010-10-5 2 2010-10-5 4        120
         2 2010-10-6 3 2010-10-6 4           24 2010-10-6 3 2010-10-6 4         40
         2 2010-10-6 4 2010-10-6 5           16 2010-10-6 3 2010-10-6 4         40
 
SQL> 

Answer 5

SQL> with aa as (select rownum rn  from dual connect by rownum<=(select max((trunc(end_time,'hh24')-trunc(start_time,'hh24'))*24+ceil(end_time-trunc(end_time,'hh')))-1 from t_cal_hour)),
  2   bb as( select id,trunc(start_time,'hh24')+(rn-1)/24 cal_start, trunc(start_time,'hh24')+rn/24 cal_end,b.start_time,b.end_time,liuliang
  3  from aa a, t_cal_hour b where trunc(b.start_time,'hh24')+a.rn/24<=trunc(end_time,'hh24')+ceil(end_time-trunc(end_time,'hh24'))/24)
  4  select id,cal_start,cal_end,start_time,end_time,liuliang,
  5  liuliang/ceil((end_time-trunc(start_time,'hh24'))*24) cal_liuliang
  6  from bb
  7  order by id,cal_start
  8  ;
 
        ID CAL_START   CAL_END     START_TIME  END_TIME      LIULIANG CAL_LIULIANG
---------- ----------- ----------- ----------- ----------- ---------- ------------
         1 2010-10-5 2 2010-10-5 3 2010-10-5 2 2010-10-5 4        120           40
         1 2010-10-5 3 2010-10-5 4 2010-10-5 2 2010-10-5 4        120           40
         1 2010-10-5 4 2010-10-5 5 2010-10-5 2 2010-10-5 4        120           40
         2 2010-10-6 3 2010-10-6 4 2010-10-6 3 2010-10-6 4         40           20
         2 2010-10-6 4 2010-10-6 5 2010-10-6 3 2010-10-6 4         40           20
 
SQL>