oracle - Oracle - SQL计算无工作天数

表有如下数据:


LogId JobID LogDate JobStatu


1 1 09/01/2019 active


2 1 09/02/2019 end


3 2 08/03/2019 active


4 2 08/05/2019 suspended


5 2 08/08/2019 active


6 2 08/15/2019 end



谁可以帮我写一个Oracle SQL查询?

时间:

你可以尝试下面-只需将datediff()转换为等价的oracle函数即可

演示


select jobid,sum(datediff(dd,t,logdate)) as totaldays from 


(


select *,lag(logdate) over(partition by jobid order by logid) as t,


lag(jobstatus) over(partition by jobid order by logid) as st


from t1


)A where st='active' or (jobstatus='active' and (st is not null and st<>'suspended'))


group by jobid



你可以在Oracle中使用此代码。

WITH AS中,我提供了你的测试数据(log_table )和日期(dates )表。


with log_table (LogId, JobID, LogDate, JobStatu) as (


 select 1, 1, to_date('09/01/2019', 'MM/DD/YYYY'), 'active' from dual


 union all


 select 2, 1, to_date('09/02/2019', 'MM/DD/YYYY'), 'end' from dual


 union all


 select 3, 2, to_date('08/03/2019', 'MM/DD/YYYY'), 'active' from dual


 union all


 select 4, 2, to_date('08/05/2019', 'MM/DD/YYYY'), 'suspended' from dual


 union all


 select 5, 2, to_date('08/08/2019', 'MM/DD/YYYY'), 'active' from dual


 union all


 select 6, 2, to_date('08/15/2019', 'MM/DD/YYYY'), 'end' from dual


),


dates as (


 select trunc(to_date('31.12.2019', 'DD.MM.YYYY') - (rownum - 1))


 as date_val


 , extract(month from trunc (to_date('31.12.2019', 'DD.MM.YYYY') - (rownum - 1)))


 as month_val


 from dual connect by rownum < 366


)


select dts.month_val as"Month"


 , count(dts.date_val) as"Number of days"


 from dates dts


 where not exists (select 1


 from log_table lt1


 where lt1.JobStatu = 'active'


 and dts.date_val between lt1.LogDate and (select min(LogDate) - 1


 from log_table


 where JobID = lt1.JobID


 and JobStatu in ('end', 'suspended')


 and LogDate > lt1.LogDate)


 and rownum = 1


 )


 group by dts.month_val


 order by dts.month_val asc 




Result:



 | Month | Number of days | 


 ---------------------------


 | 1 | 31 | 


 | 2 | 28 | 


 | 3 | 31 | 


 | 4 | 30 | 


 | 5 | 31 | 


 | 6 | 30 | 


 | 7 | 31 | 


 | 8 | 22 | 


 | 9 | 29 | 


 | 10 | 31 | 


 | 11 | 30 | 


 | 12 | 31 | 



你可以尝试模式匹配sql (MATCH_RECOGNIZE ):


WITH t(LogId, JobID, LogDate, JobStatus) AS


 (SELECT 1, 1, TO_DATE('09/01/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL


 SELECT 2, 1, TO_DATE('09/02/2019','mm/dd/YYYY'), 'end' FROM dual UNION ALL


 SELECT 3, 2, TO_DATE('08/03/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL


 SELECT 4, 2, TO_DATE('08/05/2019','mm/dd/YYYY'), 'suspended' FROM dual UNION ALL


 SELECT 5, 2, TO_DATE('08/08/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL


 SELECT 6, 2, TO_DATE('08/15/2019','mm/dd/YYYY'), 'end' FROM dual UNION ALL


 SELECT 7, 3, TO_DATE('06/01/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL


 SELECT 8, 3, TO_DATE('06/04/2019','mm/dd/YYYY'), 'suspended' FROM dual UNION ALL


 SELECT 9, 3, TO_DATE('06/08/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL


 SELECT 10, 3, TO_DATE('06/12/2019','mm/dd/YYYY'), 'suspended' FROM dual UNION ALL


 SELECT 11, 3, TO_DATE('06/18/2019','mm/dd/YYYY'), 'active' FROM dual UNION ALL


 SELECT 12, 3, TO_DATE('06/25/2019','mm/dd/YYYY'), 'end' FROM dual),


m as 


 (SELECT JobID, ACTIVE_DATE, END_DATE, SUSPEND_DAYS, JobStatus, var_match


 FROM t


 MATCH_RECOGNIZE (


 PARTITION BY JobID


 ORDER BY LogId


 MEASURES 


 CLASSIFIER() AS var_match,


 FINAL FIRST(s_ACTIVE.LogDate) AS ACTIVE_DATE,


 FINAL LAST(s_END.LogDate) AS END_DATE,


 (s_REACTIVE.LogDate - s_SUSPENDED.LogDate)+1 AS SUSPEND_DAYS


 ALL ROWS PER MATCH


 PATTERN ( s_ACTIVE (s_SUSPENDED s_REACTIVE)* s_END )


 DEFINE


 s_ACTIVE AS JobStatus = 'active',


 s_REACTIVE AS JobStatus = 'active',


 s_END AS JobStatus = 'end',


 s_SUSPENDED AS JobStatus = 'suspended' 


 )


 )


SELECT JobID, 


 MIN(ACTIVE_DATE) AS START_DATE, 


 MAX(END_DATE) AS END_DATE, 


 SUM(SUSPEND_DAYS) AS SUSPENDED_DAYS,


 MAX(END_DATE) - MIN(ACTIVE_DATE)+1 - NVL(SUM(SUSPEND_DAYS),0) AS ACTIVE_DAYS


FROM m 


WHERE JobStatus = 'active'


GROUP BY JobID


ORDER BY JobID; 



结果:


+------------------------------------------------------+


|JOBID|START_DATE|END_DATE |SUSPENDED_DAYS|ACTIVE_DAYS|


+------------------------------------------------------+


|1 |01.09.2019|02.09.2019| |2 |


|2 |03.08.2019|15.08.2019|4 |9 |


|3 |01.06.2019|25.06.2019|12 |13 |


+------------------------------------------------------+



...