加入重叠的日期范围

4 回答

• 3

  假设你有一个名为 lag 的函数（SQL Server 2012，Oracle，Postgres，DB2），我将在SQL中解决这个问题 . 您可以使用相关子查询获得相同的效果 .

  想法是获得所有不同的时间段 . 然后加入到原始表中以获取标志 .

  我无法上传代码，但可以获得大部分代码 . 但是，它从开始结束开始，您通过在一列中执行四个日期的 union （而不是 union all ）来创建：选择a.start作为日期 . 然后用a.end，b.start和b.end联合起来 .

  with driver as (
      select thedate as start, lag(thedate) over (order by thedate) as end
      from startends
     ) 
  
  select startdate, enddate, a.flag, b.flag
  from  driver left outer join
       a
       on a.start >= driver.start and a.end <= driver.end left outer join
       b
       on b.start >= driver.start and b.end <= driver.end
  

  回复于 2024-05-03T23:35:55+08:00
• 0

  您提出的问题可以在一个没有非标准扩展的SQL语句中解决 .

  要认识到的最重要的事情是，开始 - 结束对中的日期每个都代表一个时间 Span 的潜在起点或终点，在此期间标志对将为真 . 实际上，一个日期是"begin"而另一个日期是"end"并不重要;任何日期都是时间分隔符，它同时执行两个操作：它结束前一个时间段并开始另一个时间段 . 构造一组最小时间间隔，并将它们连接到表以查找在每个间隔期间获得的标志 .

  我将您的示例（和解决方案）添加到我的Canonical SQL页面 . 请参阅那里进行详细讨论 . 公平对待SO，这是查询本身

  with D (ID, bound) as (
      select   ID 
         , case T when 's' then StartDate else EndDate end as bound
      from  (
      select ID, StartDate, EndDate from so.A 
      UNION
      select ID, StartDate, EndDate from so.B
      ) as U
      cross join (select 's' as T union select 'e') as T
  )
  select P.*, a.Flag as A_Flag, b.Flag as B_Flag
  from (
      select s.ID, s.bound as StartDate, min(e.bound) as EndDate
      from D as s join D as e 
      on s.ID = e.ID 
      and s.bound < e.bound
      group by s.ID, s.bound
  ) as P
  left join so.A as a
  on  P.ID = a.ID 
  and a.StartDate <= P.StartDate and P.EndDate <= a.EndDate
  left join so.B as b
  on  P.ID = b.ID 
  and b.StartDate <= P.StartDate and P.EndDate <= b.EndDate
  order by P.ID, P.StartDate, P.EndDate
  

  回复于 2024-05-03T23:35:55+08:00
• 0

  一种可能的SAS解决方案是执行部分连接，然后在数据步骤中创建必要的附加行 . 假设tableA具有所有可能的记录，这应该有效;如果不是这种情况（如果tableB可以在tableA之前启动），可能需要一些额外的逻辑来考虑这种可能性（如果first.id和start gt b_start） . 示例数据中不存在的问题可能还需要额外的逻辑 - 我今天早上没有很多时间，并且没有针对示例数据案例之外的任何事情进行调试，但概念应该是显而易见的 .

  data tableA;
  informat start end DDMMYY10.;
  format start end DATE9.;
  input ID  Start           End     A_Flag;
  datalines;
  1   01/01/2008  23/03/2008  1
  1   23/03/2008  15/06/2008  0
  1   15/06/2008  18/08/2008  1
  ;;;;
  run;
  
  data tableB;
  informat start end DDMMYY10.;
  format start end DATE9.;
  input ID  Start           End     B_Flag;
  datalines;
  1   19/01/2008  17/02/2008  1
  1   17/02/2008  15/06/2008  0
  1   15/06/2008  18/08/2008  1
  ;;;;
  run;
  
  
  proc sql;
  create table c_temp as 
      select * from tableA A 
          left join (select id, start as b_start, end as b_end, b_flag from tableB) B
      on A.Id = B.id
      where (A.start le B.b_start and A.end gt B.b_start) or (A.start lt B.b_end and A.end ge B.b_end)
      order by A.ID, A.start, B.b_start;
  quit;
  
  data tableC;
  set c_temp;
  by id start;
  retain b_flag_ret;
  format start_fin end_fin DATE9.;
  if first.id then b_flag_ret=0;
  do until (start=end);
      if (start lt b_start) and first.start then do;
          start_fin=start;
          end_fin=b_start;
          a_flag_fin=a_flag;
          b_flag_fin=b_flag_ret;
          output;
          start=b_start;
      end;    
      else do; *start=b_start;
              start_fin=ifn(start ge b_start, start, b_start);
              end_fin = ifn(b_end le end, b_end, end);
              a_flag_fin=a_flag;
              b_flag_fin=b_flag;
              output;
              start=end; *leave the loop as there will be a later row that matches;
      end;
  end;
  run;
  

  回复于 2024-05-03T23:35:55+08:00
• 3

  具有移位和偏移的这种类型的顺序处理是SAS DATA步骤闪耀的情况之一 . 并不是说这个答案很简单，但它比使用SQL更简单，可以这样做，但是并没有考虑到这种顺序处理 .

  此外，基于DATA步骤的解决方案往往非常有效 . 这个理论上在时间O（n log n）中运行，但在实践中更接近O（n），并且在恒定空间中 .

  前两个DATA步骤只是加载数据，从Joe的答案略微修改，以具有多个ID（否则语法更容易）并添加一些极端情况，即，无法确定初始状态的ID .

  data tableA;
  informat start end DDMMYY10.;
  format start end DATE9.;
  input ID  Start           End     A_Flag;
  datalines;
  1   01/01/2008  23/03/2008  1
  2   23/03/2008  15/06/2008  0
  2   15/06/2008  18/08/2008  1
  ;;;;
  run;
  
  data tableB;
  informat start end DDMMYY10.;
  format start end DATE9.;
  input ID  Start           End     B_Flag;
  datalines;
  1   19/01/2008  17/02/2008  1
  2   17/02/2008  15/06/2008  0
  4   15/06/2008  18/08/2008  1
  ;;;;
  run;
  

  下一个数据步骤找到每个id和flag的第一个修改，并将初始值设置为与它找到的相反 .

  /* Get initial state by inverting first change */
  data firstA;
      set tableA;
      by id;
      if first.id;
      A_Flag = ~A_Flag;
  run;
  
  data firstB;
      set tableB;
      by id;
      if first.id;
      B_Flag = ~B_Flag;
  run;
  data first;
      merge firstA firstB;
      by id;
  run;
  

  下一个数据步骤将人工“第一”表与其他两个表合并，保留已知的最后状态并丢弃人工初始行 .

  data tableAB (drop=lastA lastB);
     set first tableA tableB;
     by id start;
     retain lastA lastB lastStart;
     if A_flag = . and ~first.id then A_flag = lastA;
     else lastA = A_flag;
     if B_flag = . and ~first.id then B_flag = lastB;
     else lastB = B_flag;
     if ~first.id;  /* drop artificial first row per id */
  run;
  

  上面的步骤几乎可以做到 . 唯一的错误是结束日期是错误的，因为它们是从原始行复制的 . 要解决此问题，请将下一个开头复制到每一行的末尾，除非它是最后一行 . 最简单的方法是通过反向启动对每个id进行排序，回顾一条记录，然后在最后再次升序 .

  /* sort descending to ... */
  proc sort data=tableAB;
     by id descending start;
  run;
  /* ... copy next start to this row's "end" field if not final */
  data tableAB(drop=nextStart);
     set tableAB;
     by id descending start;
     nextStart=lag(start);
     if ~first.id then end=nextStart;
  run;
  
  proc sort data=tableAB;
     by id start;
  run;
  

  回复于 2024-05-03T23:35:55+08:00