logo标签列表

加入重叠的日期范围

sqlsas
13
我需要将表A和表B连接起来创建表C。
表A和表B存储ID的状态标志。状态标志（A_Flag和B_Flag）可能会随时间而改变，因此一个ID可以包含多行，表示该ID状态的历史记录。一个特定ID的标志可以相互独立地改变，这可能导致表A中的一行属于表B中的多行，反之亦然。
生成的表（表C）需要是覆盖IDs生命周期内每个日期的唯一日期范围列表，并且每个日期范围都有A_Flag和B_Flag值。
实际表包含数百个ID，每个ID在每个表中具有不同数量的行。
我可以使用SQL和SAS工具来实现最终结果。
Source - Table A
ID  Start           End     A_Flag
1   01/01/2008  23/03/2008  1
1   23/03/2008  15/06/2008  0
1   15/06/2008  18/08/2008  1

Source - Table B
ID  Start           End     B_Flag
1   19/01/2008  17/02/2008  1
1   17/02/2008  15/06/2008  0
1   15/06/2008  18/08/2008  1

Result - Table C
ID  Start           End  A_Flag B_Flag
1   01/01/2008  19/01/2008  1   0
1   19/01/2008  17/02/2008  1   1
1   17/02/2008  23/03/2008  1   0
1   23/03/2008  15/06/2008  0   0
1   15/06/2008  18/08/2008  1   1
我无法想到只使用标准SQL就能完成这个任务的方法，而且我不懂SAS。如果我知道具体情况，我应该能够编写一个可行的程序。 - Joy
有几个答案都是正确的解决方案，其中一个我以前用过：你需要确定发生某些事情的所有日期，并从这些日期中确定所有范围。然后从这个完整的范围集合中连接回到原始表格，以确定在该特定日期范围内有效的属性。你不需要使用LAG函数，但是通用表达式（with子句）在这里非常方便。 - Colin 't Hart
4个回答
4
我将在SQL中解决此问题，假设您有一个名为lag的函数（SQL Server 2012、Oracle、Postgres、DB2）。 您可以使用相关子查询获得相同的效果。
思路是获取所有不同的时间段。 然后加入原始表以获取标志。
我遇到了上传代码的麻烦，但可以获得大部分内容。 但是，它以开始结束为开头，通过对四个日期进行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
Gordon，谢谢你。我只能通过SAS中的PROC SQL（一个过程）访问SQL语法，因此可以使用相关子查询但不能使用lag函数。如何使用相关子查询完成这个任务？ - geebees
如果您有一个支持lag的底层数据库，比如Oracle，您可以设置为通过模式并输入Oracle语法。这是可能的吗？ - Gordon Linoff
SAS确实有一个滞后函数，我想我得尝试一下才能达到我想要的结果。 - geebees
+1，但是您应该添加缺失的代码。如果我没弄错，with driver as 应该替换为 with startends as (select start from a union select end from a union select start from b union select end from b), driver as。 - MarredCheese
4
你提出的问题可以通过一个SQL语句来解决，而且不需要使用非标准扩展。最重要的是要认识到begin-end对中的日期各代表一个潜在的时间跨度的起始或结束点，在这个时间跨度内flag pair将为true。实际上，一个日期既可以是“begin”，也可以是“end”，任何日期都是一个时间分隔符，它同时结束了前一个时间段并开始了另一个时间段。构建一组最小的时间间隔，并将它们与表连接，以找到每个时间间隔内获得的flags。我已经将你的示例（以及解决方案）添加到我的规范SQL页面中。详细讨论请参见该页面。以下是查询本身。
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
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;
0

这种带有移位和偏移量的顺序处理是 SAS DATA 步骤发挥作用的情况之一。并不是说这个答案很简单，但它比使用 SQL 更加简单，虽然可以使用 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和标志的第一个修改，并将初始值设置为相反的值。
/* 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;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接