Как выполнить группировку по диапазону дат?

#sql #sql-server-2008 #common-table-expression

Вопрос:

Рассмотрите эту структуру таблицы.

  Key       ID       VISITDATE
 1         1        2011-01-07
 2         1        2011-01-09
 3         2        2011-01-10
 4         1        2011-01-12
 5         3        2011-01-12
 6         1        2011-01-15
 7         2        2011-01-21
 9         1        2011-02-28
 10        2        2011-03-21
 11        1        2011-01-06

Мне нужно получить все идентификаторы, ключ, min (VisitDate), где VisitDate находится в пределах 10 дней?если у вас два посещения в течение 10 дней, в результате должна быть одна строка.

Результат

  KEY      ID        VISITDATE
 11        1         2011-01-06
 3         2         2011-01-10
 5         3         2011-01-12
 7         2         2011-01-21
 9         1         2011-02-28
10         2         2011-03-21

Можно ли это сделать без самостоятельного объединения. у меня есть запрос, который выполняет самосоединение с таблицей по идентификатору и проверяет datediff.is есть лучшее решение? можем ли мы использовать рекурсивный CTE здесь?

Редактировать

Предпочитаете решение, которое может использовать индекс в столбце даты

1. Что делать, если у вас есть визит, подождите 8 дней, посетите снова, подождите 8 дней, посетите снова? Должно быть две группы, верно? Но какие две строки? Существует два способа выполнить группировку (AB)C или A(BC), и вы не указали, какой из них вы хотите. Или я что-то упускаю?

2. Как определяется каждый интервал в 10 дней? Начиная с первого посещения?

3. @Mike да. начиная с первого посещения…

4. @Отметить . да, две группы. мне нужна строка, соответствующая min (Visitdate).начинается с начального (AB)C

5. Хорошо, так что, если кто-то посетит день 1, день 18, день 24? Это 2 группы или 3?

Ответ №1:

Да, CTE будет хорошо работать для этого (все, что со мной, — это CTE в последнее время)…

 ;WITH TenDayVisits
AS (

SELECT 
        ID
        ,MIN(VisitDate) AS VisitDate
    FROM Visits
    GROUP BY ID
    UNION ALL
    SELECT
        t.ID
        ,v.VisitDate
    FROM Visits AS v
    JOIN TenDayVisits AS t ON v.ID = t.ID               
        AND DATEDIFF(dd,t.Visitdate,v.VisitDate) > 10
)

SELECT
    DISTINCT  
    v.[key]
    ,t.id
    ,t.VisitDate
FROM TenDayVisits as T
JOIN Visits AS v ON t.id = v.id
    AND t.VisitDate = v.VisitDate

1. Спасибо. Я добавил DISTINCT, потому что получал некоторые дубликаты обратно… возможно, вы сможете настроить рекурсивную часть CTE, чтобы избавиться от дубликатов, но DISTINCT работает так же хорошо.

2. есть ли способ удалить запросы клавиш (в плане выполнения) для повышения производительности reasons..ie изменение запроса таким образом, чтобы он мог использовать индекс по дате

3. На это трудно ответить, не зная, какие у вас индексы. Если у вас есть кластеризованный индекс на [Key], вы, вероятно, захотите включить его в CTE, чтобы присоединить его обратно к посещениям вместо идентификатора и даты, но это может усложнить сохранение CTE, возвращающего правильные значения. Также есть ошибка, касающаяся DATEDIFF индексов and: connect.microsoft.com/SQLServer/feedback/details/630583 /…

4. извините. похоже, с этим есть проблема. когда у вас есть visitdate, который меньше предыдущих для того же идентификатора в таблице, имеющей более высокий ключ.