Объединение разделенных диапазонов дат в запросе SQL

По моему опыту, мне приходится комбинировать диапазоны при постобработке (не в SQL, а в моем скрипте). Я не уверен, что SQL может это сделать, особенно потому, что вы никогда не можете точно знать, сколько диапазонов дат необходимо связать в каком-либо конкретном случае. Но если это можно сделать, я тоже хотел бы знать.

Обновлено: Мой ответ предполагает, что у вас есть более одного диапазона дат для каждого студента, а не только начало и конец. Если у вас есть только один диапазон дат без пробелов, тогда подойдут другие упомянутые решения.

SELECT StudentID, MIN(startdate) AS startdate, MAX(enddate), field1, field2
FROM tablex
GROUP BY StudentID, field1, field2

Это даст вам результат при условии, что не было разрыва между временным диапазоном ученика.

select StudentID, min(StartDate) StartDate, max(EndDate) EndDate, Field1, Field2 
  from table
 group by StudentID, Field1, Field2

Если решения min () / max () недостаточно хороши (например, если даты не являются смежными, и вы хотите сгруппировать отдельные диапазоны дат отдельно), мне интересно, будет ли работать что-то, использующее предложения Oracle START WITH и CONNECT BY. Что, конечно, не сработает для каждой базы данных.

Обновлено: сделайте еще один набор SQL для доступа. Я тестировал все это, но по частям, потому что не знаю, как сделать несколько операторов одновременно в Access. Поскольку я также не знаю, как делать комментарии, вы можете увидеть комментарии в версии SQL ниже.

select 
studentid, min(startdate) as Starter, max(enddate) as Ender, field1, field2, 
max(startDate) - Min(endDate)  as MaxGap 
into tempIDs
from student 
group by studentid, field1, field2 ;  

delete from tempIDs where MaxGap > 1;

UPDATE student INNER JOIN TempIDs ON Student.studentID = TempIDS.StudentID
SET Student.StartDate = [TempIDs].[Starter],
 Student.EndDate = [TempIDs].[Ender];

Я думаю, что это все, в SQL Server - я не делал этого в Access. Я не тестировал его на сложные условия, такие как перекрытие нескольких записей и т. д., Но это должно помочь вам начать. Он обновляет все повторяющиеся записи с небольшими пробелами, оставляя дополнения в базе данных. В MSDN есть страница по устранению дубликатов: http://support.microsoft.com/kb/139444

select 
studentid, min(startdate) as StartDate, max(enddate) as EndDate, field1, field2, 
datediff(dd, Min(endDate),max(startDate)) as MaxGap 
into #tempIDs
from #student 
group by studentid, field1, field2    

-- Update the relevant records.  Keeps two copies of the massaged record 
-- - extra will need to be deleted.

update #student 
set startdate = #TempIDS.startdate, enddate = #tempIDS.EndDate
from #tempIDS 
where #student.studentid = #TempIDs.StudentID and MaxGap < 2

Следующий код должен работать. Я сделал несколько следующих предположений: диапазоны дат не перекрываются, ни в одном из полей нет значений NULL, а дата начала для данной строки всегда меньше даты окончания. Если ваши данные не соответствуют этим критериям, вам необходимо скорректировать этот метод, но он должен указать вам правильное направление.

Вы можете использовать подзапросы вместо представлений, но это может быть громоздко, поэтому я использовал представления, чтобы сделать код более понятным.

CREATE VIEW dbo.StudentStartDates
AS
    SELECT
        S.StudentID,
        S.StartDate,
        S.Field1,
        S.Field2
    FROM
        dbo.Students S
    LEFT OUTER JOIN dbo.Students PREV ON
        PREV.StudentID = S.StudentID AND
        PREV.Field1 = S.Field1 AND
        PREV.Field2 = S.Field2 AND
        PREV.EndDate = DATEADD(dy, -1, S.StartDate)
    WHERE PREV.StudentID IS NULL
GO

CREATE VIEW dbo.StudentEndDates
AS
    SELECT
        S.StudentID,
        S.EndDate,
        S.Field1,
        S.Field2
    FROM
        dbo.Students S
    LEFT OUTER JOIN dbo.Students NEXT ON
        NEXT.StudentID = S.StudentID AND
        NEXT.Field1 = S.Field1 AND
        NEXT.Field2 = S.Field2 AND
        NEXT.StartDate = DATEADD(dy, 1, S.EndDate)
    WHERE NEXT.StudentID IS NULL
GO


SELECT
    SD.StudentID,
    SD.StartDate,
    ED.EndDate,
    SD.Field1,
    SD.Field2
FROM
    dbo.StudentStartDates SD
INNER JOIN dbo.StudentEndDates ED ON
    ED.StudentID = SD.StudentID AND
    ED.Field1 = SD.Field1 AND
    ED.Field2 = SD.Field2 AND
    ED.EndDate > SD.StartDate AND
    NOT EXISTS (SELECT * FROM dbo.StudentEndDates ED2 WHERE ED2.StudentID = SD.StudentID AND ED2.Field1 = SD.Field1 AND ED2.Field2 = SD.Field2 AND ED2.EndDate < ED.EndDate AND ED2.EndDate > SD.StartDate)
GO

Рассматривали ли вы неэквивалентное соединение? Это выглядело бы примерно так:

SELECT A.StudentID, A.StartDate, A.EndDate, A.Field1, A.Field2
FROM tblEnrollment AS A LEFT JOIN tblEnrollment AS B ON (A.StudentID = B.StudentID) 
   AND (A.EndDate=B.StartDate-1)
WHERE B.StudentID Is Null;

Это дает вам все записи, у которых нет соответствующей записи, которая начинается на следующий день после даты окончания первой записи.

[Предостережение: помните, что вы можете редактировать неэквивалентное соединение только в конструкторе запросов Access в представлении SQL - переключение на представление конструктора может привести к потере соединения (хотя, если вы действительно переключаете, Access сообщает вам о проблеме, и если вы сразу переключаетесь обратно в SQL View, вы его не потеряете)]

Если вы затем ОБЪЕДИНИТЕ это с этим:

SELECT A.StudentID, A.StartDate, B.EndDate, A.Field1, A.Field2
FROM tblEnrollment AS A INNER JOIN tblEnrollment AS B ON (A.StudentID = B.StudentID) 
   AND (A.EndDate= B.StartDate-1)

Он должен дать вам то, что вам нужно, при условии, что одновременно не может быть более двух смежных записей. Я не уверен, как бы вы это сделали, если бы у вас было более двух смежных записей (это может потребовать просмотра StartDate-1 по сравнению с EndDate), но это может помочь вам начать в правильном направлении.

Альтернативный окончательный запрос к тому, который дал Том Х. в принятом ответе:

SELECT
    SD.StudentID,
    SD.StartDate,
    MIN(ED.EndDate),
    SD.Field1,
    SD.Field2
FROM
    dbo.StudentStartDates SD
INNER JOIN dbo.StudentEndDates ED ON
    ED.StudentID = SD.StudentID AND
    ED.Field1 = SD.Field1 AND
    ED.Field2 = SD.Field2 AND
    ED.EndDate > SD.StartDate
GROUP BY
    SD.StudentID, SD.Field1, SD.Field2, SD.StartDate

Это также работало на всех тестовых данных.

Это классическая проблема в SQL (языке), например. рассматривается в книгах Джо Селко «SQL для умных» (глава 23, «Регионы, серии, пробелы, последовательности и серии») и его последней книге «Мышление наборами» (глава 15).

Хотя «весело» исправлять данные во время выполнения с помощью запроса-монстра, для меня это одна из тех ситуаций, которые можно лучше исправить в автономном режиме и процедурно (лично я бы сделал это с формулами в электронной таблице Excel).

Важно установить эффективные ограничения базы данных, чтобы предотвратить повторение перекрывающихся периодов. Опять же, написание последовательных ограничений в SQL - это классика: см. Snodgrass (http://www.cs.arizona.edu/people/rts/tdbbook.pdf). Подсказка для пользователей MS Access: вам нужно использовать ограничения CHECK.

Вот пример с тестовыми данными с использованием синтаксиса SQL Server 2005/2008.

DECLARE @Data TABLE(
    CalendarDate datetime )

INSERT INTO @Data( CalendarDate )
-- range start
SELECT '1 Jan 2010'
UNION ALL SELECT '2 Jan 2010'
UNION ALL SELECT '3 Jan 2010'
-- range start
UNION ALL SELECT '5 Jan 2010'
-- range start
UNION ALL SELECT '7 Jan 2010'
UNION ALL SELECT '8 Jan 2010'
UNION ALL SELECT '9 Jan 2010'
UNION ALL SELECT '10 Jan 2010'

SELECT DateGroup, Min( CalendarDate ) AS StartDate, Max( CalendarDate ) AS EndDate
FROM(   SELECT NextDay.CalendarDate, 
            DateDiff( d, RangeStart.CalendarDate, NextDay.CalendarDate ) - ROW_NUMBER() OVER( ORDER BY NextDay.CalendarDate ) AS DateGroup
        FROM( SELECT Min( CalendarDate ) AS CalendarDate
                FROM @data ) AS RangeStart
            JOIN @data AS NextDay
                ON NextDay.CalendarDate >= RangeStart.CalendarDate ) A
GROUP BY DateGroup