我意识到这可能是一个有点多余的问题,但我一直在努力遵循我确实找到的一些例子,我想我应该再问一遍,提供关于我的特定场景的细节。
这就是我为什么要使用:
我能够计算出小时数,但我不知道如何进入工作日组件。
发布于 2019-06-04 02:40:18
从周五上午8点到周一上午9点的示例开始:
with dates as (
Select timestamp '2019-05-31 08:00:00' start_date
, timestamp '2019-06-03 09:00:00' end_date
from dual
)
我们需要生成介于两者之间的天数。我们可以使用递归查询来做到这一点:
, recur(start_date, calc_date, end_date) as (
-- Anchor Part
select start_date
, trunc(start_date)
, end_date
from dates
-- Recrusive Part
union all
select start_date
, calc_date+1
, end_date
from recur
where calc_date+1 < end_Date
)
由此,我们需要弄清楚一些事情,例如,calc_day是工作日还是周末,calc_day的开始和结束时间是什么,然后我们可以获取这些值,并使用一些日期算法来找到当天工作的小时数(从我们开始使用时间戳以来,以每天到秒的间隔返回):
, days as (
select calc_date
, case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end isWeekDay
, greatest(start_date, calc_date + interval '8' hour) start_time
, least(end_date, calc_date + interval '16:30' hour to minute) end_time
, least( ( least(end_date, calc_date + interval '16:30' hour to minute)
- greatest(start_date, calc_date + interval '8' hour)
) * case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end
, interval '8' hour
) daily_hrs
from recur
where start_date < (calc_date + interval '16:30' hour to minute)
and (calc_date + interval '8' hour) < end_date
)
请注意,在上面的步骤中,我们将每天的工作时间限制为每天8小时,where子句防止在营业时间之外的开始或结束日期。最后一步是计算小时数的总和。不幸的是,Oracle没有任何本机间隔聚合或分析函数,但我们仍然可以通过将间隔转换为秒,对它们求和,然后将它们转换回间隔来进行管理:
select calc_date
, daily_hrs
, numtodsinterval(sum( extract(hour from daily_hrs)*60*60
+ extract(minute from daily_hrs)*60
+ extract(second from daily_hrs)
) over (order by calc_date)
,'second') run_sum
from days;
我把上面的求和作为一个分析函数,这样我们就可以看到一些中间的数据,但是如果你只想要最终的输出,你可以把查询的最后一部分修改为:
select numtodsinterval(sum( extract(hour from daily_hrs)*60*60
+ extract(minute from daily_hrs)*60
+ extract(second from daily_hrs)
)
,'second') run_sum
这是一个包含整个查询的。请注意,在小提琴中,我已经将数据库会话的NLS_TERRITORY设置更改为美国,以使查询工作,因为每周的第一天是特定于国家的。小提琴中的第二个查询替换了特定于地区的函数:
case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end
使用与位置和语言无关的计算:
case when (mod(mod(calc_date - next_day(date '2019-1-1',to_char(date '2019-01-06','day')),7),6)) != 0 then 1 end
https://stackoverflow.com/questions/56431068
复制相似问题