Присоединить перекрывающиеся диапазоны дат

Ответ 1

Я собираюсь решить это в SQL, предположив, что у вас есть функция под названием lag (SQL Server 2012, Oracle, Postgres, DB2). Вы можете получить тот же эффект с коррелированным подзапросом.

Идея состоит в том, чтобы получить все разные периоды времени. Затем присоединитесь к исходным таблицам, чтобы получить флаги.

У меня возникли проблемы с загрузкой кода, но вы можете получить большую часть его. Однако он начинается с начальных концов, которые вы создаете, делая union (not union all) четырех дат в одном столбце: выберите a.start как thedate. Затем он соединяется с 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

Ответ 2

Задача, которую вы поставили, может быть решена в одном выражении SQL без нестандартных расширений.

Самое важное, что нужно признать, это то, что даты в парах начального конца представляют потенциальную начальную или конечную точку временного интервала, в течение которого пара флагов будет истинна. На самом деле не имеет значения, что одна дата - это "начало", другое - "конец"; любая дата - это разделитель времени, который делает оба: он заканчивается за предыдущий период и начинается другое. Постройте набор минимальных временных интервалов и присоедините их к таблицам, чтобы найти флаги, полученные в течение каждого интервала.

Я добавил ваш пример (и решение) на страницу 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

Ответ 3

Одним из возможных решений SAS для этого является выполнение частичного соединения, а затем создание необходимых дополнительных строк на шаге данных. Это должно работать, если таблица А имеет все возможные записи; если это не так (если tableB может запускаться до таблицы A), для рассмотрения этой возможности может потребоваться дополнительная логика (если 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;

Ответ 4

Этот тип последовательной обработки со сдвигами и смещениями является одной из ситуаций, когда светит шаг SAS DATA. Не то, чтобы этот ответ прост, но он проще, чем использование SQL, что можно сделать, но не рассчитано на эту последовательную обработку.

Кроме того, решения, основанные на шаге DATA, имеют тенденцию быть очень эффективными. Это выполняется во времени O (n log n) в теории, но ближе к O (n) на практике и в постоянном пространстве.

Первые два этапа DATA просто загружают данные, слегка модифицированные из ответа Джо, для того, чтобы иметь несколько идентификаторов (иначе синтаксис намного проще) и добавить некоторые угловые случаи, то есть идентификатор, для которого невозможно определить начальный состояние.

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 "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;