oracle 检测最大日期重叠实体

atmip9wb 于 2022-12-18 发布在 Oracle

关注(0)|答案(3)|浏览(108)

我想找出每个讲师的最大并行能力。如果两个课程持续时间之间有1天的重叠，那么它们被认为是并行的。我想得到F列中的输出。
在Oracle SQL中是否有实现此输出的方法？

创建数据的脚本-

create table instructor_schedule(instructor_id varchar2(5), course_id varchar2(5), course_start_dt date, course_end_dt date);

insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C1', to_date('01-JAN-2022', 'DD-MON-YYYY'), to_date('30-JAN-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C2', to_date('25-DEC-2021', 'DD-MON-YYYY'), to_date('15-JAN-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C3', to_date('25-JAN-2022', 'DD-MON-YYYY'), to_date('05-FEB-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C4', to_date('26-JAN-2022', 'DD-MON-YYYY'), to_date('26-JAN-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C5', to_date('01-MAR-2022', 'DD-MON-YYYY'), to_date('05-MAR-2022', 'DD-MON-YYYY'));

insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I2', 'C1', to_date('20-AUG-2022', 'DD-MON-YYYY'), to_date('22-AUG-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I2', 'C2', to_date('03-SEP-2022', 'DD-MON-YYYY'), to_date('04-SEP-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I2', 'C3', to_date('02-SEP-2022', 'DD-MON-YYYY'), to_date('02-SEP-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I2', 'C4', to_date('01-SEP-2022', 'DD-MON-YYYY'), to_date('05-SEP-2022', 'DD-MON-YYYY'));

oracle

来源：https://stackoverflow.com/questions/74802313/detect-biggest-date-overlap-entity

3条答案

按热度按时间

rvpgvaaj1#

您可以将标量子查询与相交间隔上的条件一起使用：

select
  m.*
  , (
    select count(*)
    from instructor_schedule lkp
    where m.instructor_id = lkp.instructor_id
      and m.course_id != lkp.course_id
      and m.course_start_dt <= lkp.course_end_dt
      and lkp.course_start_dt <= m.course_end_dt
  ) as intersects
from instructor_schedule m

| 讲师ID|课程编号|课程开始日期|课程结束日期|交叉点|
| - ------|- ------|- ------|- ------|- ------|
| I1| C1| 2022年1月1日|2022年1月30日|三个|
| I1| C2|二〇二一年十二月二十五日|2022年1月15日|1个|
| I1| C3| 2022年1月25日|2022年2月5日|第二章|
| I1|四碳|2022年1月26日|2022年1月26日|第二章|
| I1| C5|二○二二年三月一日|2022年3月5日|无|
| I2| C1|二〇二二年八月二十日|二〇二二年八月二十二日|无|
| I2| C2| 2022年9月3日|2022年9月4日|1个|
| I2| C3| 2022年9月2日|2022年9月2日|1个|
| I2|四碳|2022年9月1日|2022年9月5日|第二章|
fiddle

统一采购司

或者，您可以使用单个表扫描和model子句对多行执行计算。下面的cv表示指定维的当前值，括号[...]用于引用维值。

select /*+gather_plan_statistics*/
  instructor_id,
  course_id, course_start_dt, course_end_dt,
  intersects,
  concurrent_courses,
  max_cap_flg
    
from instructor_schedule m
model
  partition by (instructor_id)
  dimension by (course_id, course_start_dt, course_end_dt)
  measures(
    0 as intersects,
    cast(null as varchar(1000)) as concurrent_courses,
    course_id as dummy,
    0 as max_cap_flg
  )
  rules update  sequential order(
    intersects[any, any, any]
      = count(intersects)[
          course_id != cv(course_id),
          course_start_dt <= cv(course_end_dt),
          course_end_dt >= cv(course_start_dt)
        ],
    concurrent_courses[any, any, any]
      = listagg(dummy, ',') within group(order by null)[
          course_id != cv(course_id),
          course_start_dt <= cv(course_end_dt),
          course_end_dt >= cv(course_start_dt)
        ],
    max_cap_flg[any,any,any]
      = case
          when intersects[cv(), cv(), cv()] = max(intersects)[any, any, any]
          then 1
        end
  )

| 讲师ID|课程编号|课程开始日期|课程结束日期|交叉点|并行课程|最大盖法兰|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|
| I1| C1| 2022年1月1日|2022年1月30日|三个|C4、C3、C2| 1个|
| I1| C2|二〇二一年十二月二十五日|2022年1月15日|1个|C1| * 无效 *|
| I1| C3| 2022年1月25日|2022年2月5日|第二章|C4、C1| * 无效 *|
| I1|四碳|2022年1月26日|2022年1月26日|第二章|C3、C1| * 无效 *|
| I1| C5|二○二二年三月一日|2022年3月5日|无| * 无效 | 无效 *|
| I2| C1|二〇二二年八月二十日|二〇二二年八月二十二日|无| * 无效 | 无效 *|
| I2| C2| 2022年9月3日|2022年9月4日|1个|四碳| * 无效 *|
| I2| C3| 2022年9月2日|2022年9月2日|1个|四碳| * 无效 *|
| I2|四碳|2022年9月1日|2022年9月5日|第二章|C3、C2| 1个|
fiddle

赞(0）回复(0）举报 2022-12-18

qcbq4gxm2#

您可以使用outer apply（至少在Oracle的最新版本中）来获取重叠的课程，然后聚合这些课程：

select i1.instructor_id, i1.course_id, i1.course_start_dt, i1.course_end_dt,
  coalesce(
    listagg(i3.course_id, ',') within group (order by i3.course_id),
    'None') as other_ids,
  count(i3.course_id) as other_count
from instructor_schedule i1
outer apply (
  select i2.course_id
  from instructor_schedule i2
  where i2.instructor_id = i1.instructor_id
  and i2.course_id != i1.course_id
  and not (i2.course_start_dt >= i1.course_end_dt
    or i2.course_end_dt <= i1.course_start_dt)
) i3
group by i1.instructor_id, i1.course_id, i1.course_start_dt, i1.course_end_dt
order by instructor_id, course_id

| 讲师ID|课程编号|课程开始日期|课程结束日期|其他_ID|其他_计数|
| - ------|- ------|- ------|- ------|- ------|- ------|
| I1| C1| 2022年1月1日|2022年1月30日|C2、C3、C4炸药|三个|
| I1| C2|二十一年十二月二十五日|二十二年一月十五日|C1| 1个|
| I1| C3| 2022年1月25日|2022年2月5日|C1、C4|第二章|
| I1|四碳|2022年1月26日|2022年1月26日|C1、C3|第二章|
| I1| C5| 2022年3月1日|2022年3月5日|无|无|
| I2| C1| 2022年8月20日|2022年8月22日|无|无|
| I2| C2| 2022年9月3日|2022年9月4日|四碳|1个|
| I2| C3| 2022年9月2日|2022年9月2日|四碳|1个|
| I2|四碳|2022年9月1日|2022年9月5日|C2、C3|第二章|
fiddle
这得到的结果与您所展示的略有不同，但看起来讲师I1的C3和C4应该彼此重叠...

赞(0）回复(0）举报 2022-12-18

g52tjvyc3#

仅使用解析函数和CASE表达式...

SELECT INSTRUCTOR_ID, COURSE_ID, COURSE_START_DT, COURSE_END_DT,
       Nvl(OVERLAPING, 'None') "OVERLAPING", CNT
FROM(   SELECT DISTINCT
            i.INSTRUCTOR_ID, i.COURSE_ID, s.OVR, i.COURSE_START_DT, i.COURSE_END_DT,
            LISTAGG(s.COURSE_ID, ', ') WITHIN GROUP (Order By s.COURSE_ID) OVER(Partition By s.INSTRUCTOR_ID, s.OVR) "OVERLAPING",
            COUNT(DISTINCT s.COURSE_ID) OVER(Partition By s.INSTRUCTOR_ID, s.OVR) "CNT"
        FROM
            INSTRUCTOR_SCHEDULE i
        LEFT JOIN
            (
                Select 
                    s1.INSTRUCTOR_ID, s1.COURSE_ID, s1.COURSE_START_DT, s1.COURSE_END_DT,
                    CASE WHEN s1.COURSE_START_DT Between s2.COURSE_START_DT And s2.COURSE_END_DT OR s2.COURSE_START_DT Between s1.COURSE_START_DT And s1.COURSE_END_DT THEN s2.COURSE_ID END "OVR"
                From INSTRUCTOR_SCHEDULE s1
                Inner Join
                    INSTRUCTOR_SCHEDULE s2 ON(s2.INSTRUCTOR_ID = s1.INSTRUCTOR_ID And s2.COURSE_ID !=  s1.COURSE_ID)
                Where 
                    CASE WHEN s1.COURSE_START_DT Between s2.COURSE_START_DT And s2.COURSE_END_DT OR s2.COURSE_START_DT Between s1.COURSE_START_DT And s1.COURSE_END_DT THEN s2.COURSE_ID END Is Not Null
            ) s ON(s.INSTRUCTOR_ID = i.INSTRUCTOR_ID And s.OVR = i.COURSE_ID)
        ORDER BY i.INSTRUCTOR_ID, i.COURSE_ID
    )
/*  R e s u l t :
INSTRUCTOR_ID COURSE_ID COURSE_START_DT COURSE_END_DT OVERLAPING             CNT
------------- --------- --------------- ------------- --------------- ----------
I1            C1        01-JAN-22       30-JAN-22     C2, C3, C4               3 
I1            C2        25-DEC-21       15-JAN-22     C1                       1 
I1            C3        25-JAN-22       05-FEB-22     C1, C4                   2 
I1            C4        26-JAN-22       26-JAN-22     C1, C3                   2 
I1            C5        01-MAR-22       05-MAR-22     None                     0 
I2            C1        20-AUG-22       22-AUG-22     None                     0 
I2            C2        03-SEP-22       04-SEP-22     C4                       1 
I2            C3        02-SEP-22       02-SEP-22     C4                       1 
I2            C4        01-SEP-22       05-SEP-22     C2, C3                   2 
*/

赞(0）回复(0）举报 2022-12-18

我来回答

oracle 检测最大日期重叠实体

3条答案

相关问题

热门标签

最新问答