Как найти диапазоны дат в записях с последовательными датами и повторяющимися данными

Вероятно, для этого есть какое-то простое решение, но я его не вижу. У меня есть таблица с последовательными датами, и я часто дублирую связанные данные для нескольких из этих последовательных дат:

Date       Col1  Col2
5/13/2010  1     A
5/14/2010  1     A
5/15/2010  2     B
5/16/2010  1     A
5/17/2010  1     A
5/18/2010  3     C
5/19/2010  3     C
5/20/2010  3     C

Используя MS T-SQL, я хочу найти даты начала и окончания для каждого запуска различных значений Col1 и Col2:

StartDate  EndDate    Col1  Col2
5/13/2010  5/14/2010  1     A
5/15/2010  5/15/2010  2     B
5/16/2010  5/17/2010  1     A
5/18/2010  5/20/2010  3     C

Предположения: никогда не бывает пропущенных дат. Col1 и Col2 не равны нулю. Любые идеи - желательно, чтобы не использовались курсоры? Большое спасибо, -алан


database sql-server tsql sql-server-2000
person alan s    schedule 15.05.2010    source источник
comment
если вы используете Sql Server 2005+, я бы ответил на этот stackoverflow.com/questions/1610599/   -  person Michael Buen    schedule 15.05.2010

Ответы (2)


arrow_upward
3
arrow_downward

Для SQL 2005+ я думаю, что ниже должно работать

WITH DATES AS
(
   SELECT COL1, COL2, DATE,
      DATEADD(DAY, -1 * ROW_NUMBER() 
      OVER(PARTITION BY COL1, COL2 ORDER BY DATE), DATE) AS GRP
   FROM YOUR_TABLE
)
SELECT COL1, COL2, MIN(DATE) AS STARTDATE, MAX(DATE) AS ENDDATE
FROM DATES
GROUP BY COL1, COL2, GRP

Если у вас есть повторяющиеся записи, используйте DENSE_RANK() вместо ROW_NUMBER().

Для SQL 2000 используется подзапрос и связанный с ним запрос.

SELECT COL1, COL2, MIN(DATE) AS STARTDATE, MAX(DATE) AS ENDDATE
FROM (SELECT COL1, COL2, DATE,
    (SELECT MIN(DATE)
     FROM YOUR_TABLE B
     WHERE B.DATE >= A.DATE AND B.COL1 = A.COL1 AND B.COL2 = A.COL2
           AND NOT EXISTS
           (SELECT *
            FROM YOUR_TABLE C
            WHERE C.COL1 = B.COL1 AND C.COL2 = B.COL2
            AND DATEDIFF(DAY, B.DATE, C.DATE) = 1)
    ) AS GRP
    FROM YOUR_TABLE A
)
GROUP BY COL1, COL2, GRP
person Chris Bednarski    schedule 15.05.2010

arrow_upward
1
arrow_downward

Вот один из подходов с использованием outer apply. Замените @t на имя вашей таблицы.

SELECT    head.date, last.date, head.col1, head.col2
FROM      @t head
OUTER APPLY (
          SELECT TOP 1 *
          FROM @t t
          WHERE t.date < head.date
          ORDER BY t.date desc
          ) prev
OUTER APPLY (
          SELECT TOP 1 *
          FROM @t t
          WHERE t.date > head.date
          AND (t.col1 <> head.col1 or t.col2 <> head.col2)
          ORDER BY t.date
          ) next
OUTER APPLY (
          SELECT TOP 1 *
          FROM @t t
          WHERE (t.date < next.date or next.date is null)
          AND (t.col1 = head.col1 and t.col2 = head.col2)
          ORDER BY t.date
          ) last
WHERE (prev.col1 is null or head.col1 <> prev.col1 or head.col2 <> prev.col2)

Запрос сначала выбирает «главную» строку: строки, которые начинают новую группу col1, col2. Это делается путем поиска «предыдущей» строки и указания, что она должна быть другой в предложении where.

Затем он ищет конец группы col1, col2. Это двухэтапный процесс: сначала ищите первую строку «следующей» группы, а строка перед ней является «последней» строкой.

Date       Col1  Col2
...
5/15/2010  2     B      <-- "prev" row
5/16/2010  1     A      <-- "head" row
5/17/2010  1     A      <-- "last" row
5/18/2010  3     C      <-- "next" row
...

Результат запроса соответствует примеру вывода в вашем вопросе.

person Andomar    schedule 15.05.2010
comment
Хорошее решение. Я должен был упомянуть, что это для старой системы SQL Server 2000. - person alan s; 15.05.2010
comment
Алан, ты должен отметить Андомара как решение, если он тебе подходит. - person Irawan Soetomo; 20.05.2010