我的情况:
我有以下视图来检索数据:
|ID | START_DATE | END_DATE |
|80 | 09-JAN-2013 15:01:52 | 20-SEP-2014 15:01:52 |
|82 | 09-SEP-2014 15:01:52 | 25-SEP-2014 15:01:52 |
我想要的是这样的东西:
|TOTAL_TIME_IN_HOURS| MONTH| YEAR |
| 200 | 01 | 2013 |
| 250 | 02 | 2013 |
| etc..... | etc. | etc..|
| 150 | 09 | 2014 |
一些附加信息:我只能使用select语句,但我可以预先创建视图。这是一个Oracle数据库,所以我不能使用DATEDIFF等MYSQL函数。
我做了以下工作:
SELECT
ID,
SUM(END_TIME - START_TIME) * 24 AS TOTAL_TIME_IN_HOURS,
FROM TABLE_X
WHERE TO_CHAR(START_TIME, 'MM') IN (1,2,3,4,5,6,7,8,9,10,11,12) AND TO_CHAR(START_TIME, 'YYYY') BETWEEN 1965 AND 2050 AND
TO_CHAR(END_TIME, 'MM') IN (1,2,3,4,5,6,7,8,9,10,11,12) AND TO_CHAR(END_TIME, 'YYYY') BETWEEN 1965 AND 2050
GROUP BY PROC_ID, TO_CHAR(START_TIME, 'YYYY'), TO_CHAR(START_TIME, 'MM') ORDER BY ID;
这将返回以下内容:
|ID| TOTAL_TIME_IN_HOURS |
|80| 5000 |
|82| 300 |
(我使用了虚构的结果,因为问题与实际结果无关)
这个逻辑是可以的,因为我只需要开始和结束日期之间的总小时数。。然而,我需要的是从开始日期到结束日期之间每月的总小时数。
我想在视图中添加其他列,如start_month、end_month,start_year和end_year。然而,我在这些选择上遇到了新的问题,比如闰年。。。
我的问题是:有可能达到我想要的结果吗?如果是这样的话,我应该用什么样的逻辑来达到这个结果?(最好是动态查询,这样我就不必输入数百行代码)
另一个递归解决方案,它至少需要Oracle 11gR2:
with t(id, start_date, end_date) as
(select 80, to_date('09/01/2013 15:01:52', 'DD/MM/YYYY HH24:MI:SS'), to_date('20/09/2014 15:01:52', 'DD/MM/YYYY HH24:MI:SS') from dual
union all
select 82, to_date('09/09/2014 15:01:52', 'DD/MM/YYYY HH24:MI:SS'), to_date('25/09/2014 15:01:52', 'DD/MM/YYYY HH24:MI:SS') from dual
)
, t_recur(id, start_date, end_date, month_start_date, month_end_date) as
(select id
, start_date
, end_date
, start_date
, least(add_months(trunc(start_date, 'MM'), 1), end_date)
from t
union all
select id
, start_date
, end_date
, trunc(add_months(month_start_date, 1), 'MM')
, least(add_months(trunc(month_start_date, 'MM'), 2), end_date)
from t_recur
where trunc(add_months(month_start_date, 1), 'MM') < end_date
)
select id
, extract(year from month_start_date) year
, extract(month from month_start_date) month
, (month_end_date - month_start_date) * 24 hours
from t_recur
order by id
, year
, month
具有层次结构查询的更快方法:
with w as
(
select distinct id,
greatest(start_date, trunc(add_months(start_date, level - 1), 'MON')) lim_low,
least(trunc(add_months(start_date, level), 'MON'), end_date) lim_high
from test t
connect by add_months(start_date, level - 1) <= end_date
order by 3, 1
)
select id, lim_low, (lim_high - lim_low) * 24 nb_hours
from w;
试试这个:
with t(ID, START_DATE, END_DATE) as (
select 80, to_date('09/01/2013 15:01:52', 'DD/MM/YYYY HH24:MI:SS'), to_date('20/09/2014 15:01:52', 'DD/MM/YYYY HH24:MI:SS') from dual union all
select 82, to_date('09/09/2014 15:01:52', 'DD/MM/YYYY HH24:MI:SS'), to_date('25/09/2014 15:01:52', 'DD/MM/YYYY HH24:MI:SS') from dual
), t_mon(id, start_date, end_date, lvl, year, month) as (
select id
, start_date
, least(trunc(add_months(start_date, 1), 'MONTH'), end_date)
, 1
, extract(year from start_date)
, extract(month from start_date)
from t
union all
select t.id
, greatest(trunc(add_months(t.start_date, lvl), 'MONTH'), t.start_date)
, least(trunc(add_months(t.start_date, lvl+1), 'MONTH'), t.end_date)
, lvl + 1
, extract(year from greatest(trunc(add_months(t.start_date, lvl), 'MONTH'), t.start_date))
, extract(month from greatest(trunc(add_months(t.start_date, lvl), 'MONTH'), t.start_date))
from t, t_mon
where trunc(add_months(t.start_date, t_mon.lvl), 'MONTH') < t.end_date
), t_corr(id, start_date, end_date, year, month) as (
select unique id, start_date, end_date, year, month
from t_mon
)
select id, year, month, sum(end_date - start_date) * 24 hours
from t_corr
group by id, year, month
order by id, year, month
ID YEAR MONTH HOURS
--------- ---------- ---------- ----------
80 2013 1 536,968889
80 2013 2 672
80 2013 3 744
80 2013 4 720
80 2013 5 744
80 2013 6 720
80 2013 7 744
80 2013 8 744
80 2013 9 720
80 2013 10 744
80 2013 11 720
80 2013 12 744
80 2014 1 744
80 2014 2 672
80 2014 3 744
80 2014 4 720
80 2014 5 744
80 2014 6 720
80 2014 7 744
80 2014 8 744
80 2014 9 471,031111
82 2014 9 384