Запрос на объединение непрерывных временных записей

У меня есть таблица вроде этого:

id START_DATE end_date
1 01/01/2011 01/10/2011
2 01/11/2011 01/20/2011
3 01/25/2011 02/01/2011
4 02/10/2011 02/15/2011
5 02/16/2011 02/27/2011

Я хочу объединить записи, где start_date - только на следующий день end_date другой записи: так что конечная запись должна быть примерно такой:

new_id START_DATE end_date
1 01/01/2011 01/20/2011
2 01/25/2011 02/01/2011
3 02/10/2011 02/27/2011

Один из способов, который я знаю для этого, - создать таблицу temp на основе строк с различными строками в виде дат (каждая запись для одной даты, между полным диапазоном дней) и, таким образом, сделать таблицу плоской.

Но должен быть более чистый способ сделать это в одном запросе... например. что-то с помощью row_num?

Спасибо, ребята.

4 ответа

declare @T table
( id int, start_date datetime, end_date datetime
)
insert into @T values
(1, '01/01/2011', '01/10/2011'),
(2, '01/11/2011', '01/20/2011'),
(3, '01/25/2011', '02/01/2011'),
(4, '02/10/2011', '02/15/2011'),
(5, '02/16/2011', '02/27/2011')
select row_number() over(order by min(dt)) as new_id, min(dt) as start_date, max(dt) as end_date
from ( select dateadd(day, N.Number, start_date) as dt, dateadd(day, N.Number - row_number() over(order by dateadd(day, N.Number, start_date)), start_date) as grp from @T inner join master..spt_values as N on N.number between 0 and datediff(day, start_date, end_date) and N.type = 'P' ) as T
group by grp
order by new_id

Вы можете использовать таблицу numbers вместо использования master..spt_values.


Рекурсивное решение:

CREATE TABLE TestData
( Id INT PRIMARY KEY, StartDate DATETIME NOT NULL, EndDate DATETIME NOT NULL
);
SET DATEFORMAT MDY;
INSERT TestData
SELECT 1, '01/01/2011', '01/10/2011'
UNION ALL
SELECT 2, '01/11/2011', '01/20/2011'
UNION ALL
SELECT 3, '01/25/2011', '02/01/2011'
UNION ALL
SELECT 4, '02/10/2011', '02/15/2011'
UNION ALL
SELECT 5, '02/16/2011', '02/27/2011'
UNION ALL
SELECT 6, '02/28/2011', '03/06/2011'
UNION ALL
SELECT 7, '02/28/2011', '03/03/2011'
UNION ALL
SELECT 8, '03/10/2011', '03/18/2011'
UNION ALL
SELECT 9, '03/19/2011', '03/25/2011';
WITH RecursiveCTE
AS
( SELECT t.Id, t.StartDate, t.EndDate ,1 AS GroupID FROM TestData t WHERE t.Id=1 UNION ALL SELECT crt.Id, crt.StartDate, crt.EndDate ,CASE WHEN DATEDIFF(DAY,prev.EndDate,crt.StartDate)=1 THEN prev.GroupID ELSE prev.GroupID+1 END FROM TestData crt JOIN RecursiveCTE prev ON crt.Id-1=prev.Id --WHERE crt.Id > 1
)
SELECT cte.GroupID, MIN(cte.StartDate) AS StartDate, MAX(cte.EndDate) AS EndDate
FROM RecursiveCTE cte
GROUP BY cte.GroupID
ORDER BY cte.GroupID;
DROP TABLE TestData;


Нет, не искал петлю...

Я думаю, это хорошее решение:

взятие всех данных в таблице #temp

SELECT * FROM #temp
SELECT t2.start_date , t1.end_date FROM #temp t1 JOIN #temp t2 ON t1.start_date = DATEADD(DAY,1,t2.end_date)
UNION
SELECT START_DATE,end_date FROM #temp WHERE start_date NOT IN (SELECT t2.START_DATE FROM #temp t1 JOIN #temp t2 ON t1.start_date = DATEADD(DAY,1,t2.end_date))
AND end_date NOT IN (SELECT t1.end_Date FROM #temp t1 JOIN #temp t2 ON t1.start_date = DATEADD(DAY,1,t2.end_date))
DROP TABLE #temp

Пожалуйста, дайте мне знать, если что-то лучше этого.

Спасибо, ребята.


Попробуйте это

Declare @chgRecs Table (updId int primary key not null, delId int not null, endt datetime not null) While Exists (Select * from Table a Where Exists (Select * from table Where start_date = DateAdd(day, 1, a.End_Date))) Begin Insert @chgRecs (updId, delId , endt) Select a.id, b.id, b.End_Date, From table a Where Exists (Select * from table Where start_date = DateAdd(day, 1, a.End_Date))) And Not Exists (Select * from table Where end_Date = DateAdd(day, -1, a.Start_Date))) Delete table Where id In (Select delId from @chgRecs ) Update table set End_Date = u.endt From table t join @chgRecs u On u.updId = t.Id Delete @delRecs End

licensed under cc by-sa 3.0 with attribution.