简化，我有类似的表：
start_date end_date units
06/03/2014 14:00:00 06/03/2014 15:30:00 300
我想要和一直在尝试做的就是找出持续时间（end_date -start_date）。将其分成半小时的间隔，然后在这些间隔之间平均分配单位。
在这种情况下，持续时间为90分钟，间隔3个半小时，这意味着每个间隔100个单位。
我想沿着这些行创建一些东西：
interval_date units
06/03/2014 14:00:00 100
06/03/2014 14:30:00 100
06/03/2014 15:00:00 100
我已经工作了，但是它涉及大量的子查询（可能可以将其清理一点以减少使用）。它还涉及间隔的硬编码列，而不是一个列以容纳不同的间隔。例如，看起来像这样：
id d0000 ... d1400 d1430 d1500 d1530 d1600 ... d2330
1 0 100 100 100 0 0 0
因此，在我想更改间隔持续时间（15分钟，1小时等）的情况下，它不是很灵活的。
任何想法或帮助都将不胜感激。
如果您使用的是Oracle 11gr2，则可以使用递归CTE。
with x(start_date, end_date, units) as (
--select all the records and units per half an hour.
select start_date, end_date, units/((end_date - start_date)*48)
from myt
union all
--iteratively add 30 minutes to start_date till it stays less than end_date
select start_date + interval '30' minute, end_date, units
from x
where start_date + interval '30' minute < end_date
)
select start_date, units
from x
order by 1;
模式：
create table myt(
start_date date,
end_date date,
units number
);
insert into myt values(to_date('06/03/2014 14:00:00','mm/dd/yyyy hh24:mi:ss'),
to_date('06/03/2014 15:30:00','mm/dd/yyyy hh24:mi:ss'),
300
);
insert into myt values(to_date('06/04/2014 11:00:00','mm/dd/yyyy hh24:mi:ss'),
to_date('06/04/2014 15:30:00','mm/dd/yyyy hh24:mi:ss'),
300
);
输出：
START_DATE UNITS
-------------------------- ----------
03-JUN-2014 14:00:00 100
03-JUN-2014 14:30:00 100
03-JUN-2014 15:00:00 100
04-JUN-2014 11:00:00 33.3333333
04-JUN-2014 11:30:00 33.3333333
04-JUN-2014 12:00:00 33.3333333
04-JUN-2014 12:30:00 33.3333333
04-JUN-2014 13:00:00 33.3333333
04-JUN-2014 13:30:00 33.3333333
04-JUN-2014 14:00:00 33.3333333
04-JUN-2014 14:30:00 33.3333333
04-JUN-2014 15:00:00 33.3333333
尝试这个：
with t as (
select to_date('06/03/2014 14:00:00', 'dd/mm/yyyy HH24:mi:ss') start_time,
to_date('06/03/2014 15:10:00', 'dd/mm/yyyy HH24:mi:ss') end_time,
300 units
from dual
)
select start_time + (rownum - 1) / 48 start_interval,
units / ceil((end_time - start_time) * 48) units
from t
where start_date <> end_date
connect by level <= ceil((end_time - start_time) * 48);
START_INTERVAL UNITS
-------------- ----------
06.03.2014 14:00:00 100
06.03.2014 14:30:00 100
06.03.2014 15:00:00 100
更新：添加了WHERE条件，如果start_date等于end_date。
此解决方案涵盖了的行数（其他答案仅涵盖一个或许多行的性能问题）。
这就是我尝试的：
with yourTable as (select 1 id,
to_date('06/03/2014 14:00:00','dd/mm/yyyy hh24:mi:ss') start_date,
to_date('06/03/2014 15:30:00','dd/mm/yyyy hh24:mi:ss') end_date,
300 units
from dual
union all
select 2 id,
to_date('05/03/2014 12:00:00','dd/mm/yyyy hh24:mi:ss') start_date,
to_date('06/03/2014 23:30:15','dd/mm/yyyy hh24:mi:ss') end_date,
1000 units
from dual),
setting as (select 1/24/2 /*every half an hour*/ interval from dual),
borders as (select /* +materialize */ min(start_date) - 1 mindate,max(end_date) + 1 maxdate from yourTable),
periods as (select /* +materialize */
mindate + interval*level period_start,
mindate + interval*(level + 1) - 1/24/60/60 /* - 1 sec */ period_end
from borders,setting
connect by level <= (maxdate - mindate)/interval)
select id,
units,
units/count(*) over (partition by id)
from periods p, yourTable y
where y.start_date between p.period_start and p.period_end
or p.period_start between y.start_date and y.end_date;
其中
-
yourTable应该仅用您的表
替换 -
setting我用作设置期间的变量，使用1/24或1/24/4使用15分钟 -
borders是一个最大和最小日期的变量 -
periods是所有需要时期的大部分 - 最后，查询查询
yourTable中所有计算的时期和周期的所有交集
在您的示例中，它返回4行，因为15:30在逻辑上是一个新的时期（15:29:59将作为上一个时期的结束）。但是我认为您可以自己处理此问题：它需要有关您的业务逻辑的其他信息。
请注意，提示/* +materialize */确实很重要，因为没有IT IT Oracle Analyzer喜欢用connect by level"打开" subquies，这使其不可能表现出色。