感谢@Ponder Stibbons的回答,我找到了一个合适的选择:
select dt, nvl(24 * sum(nvl2(endtime, least(dt + 1, endtime), dt+1) - greatest(begintime, dt)),0) duration from ttest t right join (select trunc((select min(beginTime) from ttest)) + rownum -1 dt from all_objects where rownum <= sysdate-cast((select min(beginTime) from ttest) as date)) d on begintime < dt + 1 and (dt < endtime or endtime is null) group by dt order by dt
演示
我做了一个正确的加入,列出了从我表中最早的日期到当前日期和总和匹配记录的所有日期。
我的尝试:
select dt, 24 * sum(nvl2(endtime, least(dt + 1, endtime, dt + 1), dt) - nvl2(endtime, greatest(begintime, dt), begintime)) duration from ttest t join (select trunc(nvl(endtime, sysdate)) dt from ttest) d on begintime < dt + 1 and (dt < endtime or endtime is null) group by dt order by dt
的 dbfiddle演示 强>
我用不同的日期进行了自我加入,然后我对你的类似做了总结。空值 endtime 由...处理 nvl2 ,但你可以改成它 case when 。结果:
endtime
nvl2
case when
DT DURATION ----------- ---------- 2019-01-13 14 2019-01-14 33 2019-01-15 10 2019-01-30 314
根据需要,除了最后一行,因为计算是基于sysdate,所以现在是314小时(但你可以改变 sysdate 例如,到任何日期 date '2019-01-17' 如果你想测试)。
sysdate
date '2019-01-17'
的 编辑: 强>
......在这种情况下,我需要一个24小时的记录,24小时,另一个 适用于18-01,24小时等等。
所以你需要日期生成器:
select dt + level - 1 dt from (select trunc(min(endtime)) dt from ttest) connect by dt + level - 1 < sysdate)
加入(稍加修改)之前的查询:
with dates as ( select dt + level - 1 dt from (select trunc(min(endtime)) dt from ttest) connect by dt + level - 1 < sysdate), details as ( select dt, id, begintime, endtime, case when endtime is null then dt + 1 else least(dt + 1, endtime) end t2, greatest(begintime, dt) t1 from ttest t join dates on begintime < dt + 1 and (dt < endtime or endtime is null)) select dt, 24 * sum(t2 - t1) duration from details group by dt order by dt
结果:
DT DURATION ----------- ---------- 2019-01-13 14 2019-01-14 33 2019-01-15 10 2019-01-16 2 2019-01-17 24 2019-01-18 24 ... ... 2019-01-30 24 2019-01-31 24 19 rows selected