Считайте последовательные месяцы с пробелами

#sql

Вопрос:

Рассмотрим таблицу с двумя столбцами, Номером обращения и Месяцем возникновения:

Номер дела	Месяц Наступления
123	201801
123	201802
123	201803
123	201805
123	201806

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

 SELECT CaseNumber, 
       OccurenceMonth, 
       COUNT(*) OVER(PARTITION BY CaseNumber ORDER BY OccurenceMonth) AS ConsecutiveMonths
FROM table
ORDER BY CaseNumber, OccurrenceMonth;

Это создает таблицу, которая выглядит так:

Номер дела	Месяц Наступления	Месяцы Подряд
123	201801	1
123	201802	2
123	201803	3
123	201805	4
123	201806	5

Однако я бы хотел, чтобы подсчет последовательных месяцев был сброшен между месяцами возникновения 201803 и 201805, чтобы это выглядело так:

Номер дела	Месяц Наступления	Месяцы Подряд
123	201801	1
123	201802	2
123	201803	3
123	201805	1
123	201806	2

Насколько я понимаю, это простое изменение значительно усложнит запрос. Есть какие-нибудь идеи о том, как лучше всего это сделать? Спасибо.

1. Каков тип данных месяца наступления? Также пометьте базу данных, которую вы используете.

2. @GordonLinoff это преобразованная дата-время в формат YYYYMM. СЛЕВА(ПРЕОБРАЗОВАНИЕ(VARCHAR, время появления, 112), 6)

Ответ №1:

Судя по коду в комментарии, вы, похоже, используете SQL Server.

Наблюдение помогает решить эту проблему. Если вы вычтете последовательность чисел (скажем, «номер строки») из каждой строки, то результат будет постоянным там, где месяцы увеличиваются. Вуаля! Мы можем использовать это наблюдение для реализации решения с использованием оконных функций:

 select t.*,
       row_number() over (partition by case_number
                                       dateadd(month, - seqnum, cast(yyyymm   '01' as date))
                          order by yyyymm
                         ) as consecutive_months
from (select t.*,
             row_number() over (partition by case_number order by yyyymm) as seqnum
      from t
     ) t;