Гэпы и острова? Или не?

У меня есть следующая таблица:

;with data as (
select '508325'  as [customer], 'G61' as [demo_given],
        cast('2015-1-1' as date) as [date_of_demo]
union all select '508325', 'G61', cast('2015-3-1' as date) 
union all select '508325', 'G61',cast('2015-3-15' as date)
union all select '508325', 'G61',cast('2015-3-16' as date)
union all select '508325', 'G61',cast('2015-3-17' as date)
union all select '508325', 'G61',cast('2015-6-1' as date)
union all select '508325', 'G61',cast('2015-8-1' as date)
union all select '508325', 'G61',cast('2015-9-1' as date)
union all select '508325', 'G61',cast('2015-9-1' as date)
union all select '508325', 'G61',cast('2015-12-1' as date)
)

На одного клиента может быть предоставлено только 3 демонстрации в течение 4 месяцев. Первый период начинается с момента предоставления первой демо-версии и заканчивается через 4 месяца.

Если количество демонстраций за этот период превышает 3, мне нужны даты демонстраций 4 и более поздних версий в течение этого 4-месячного периода. (в этом примере это будут 2015-3-16 и 2015-3-17)

Следующий период начинается с даты первой демонстрации, предоставленной после первых четырех месяцев. Поэтому мне нужно подсчитать количество демонстраций в период с 2015-6-1 до 2015-9-30 и вернуть даты возможных «избыточных» демонстраций, указанных в этот период.

Как мне это сделать?


sql-server tsql sql-server-2012 gaps-and-islands
person Henrov    schedule 29.10.2015    source источник

Ответы (1)


arrow_upward
2
arrow_downward

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

  • tally - простая числовая таблица, которую вы можете использовать любым методом (рекурсивный cte, фиктивная таблица, табличная функция,...)
  • min_date_per_customer - получить первую дату демонстрации для каждого клиента
  • date_ranges - генерировать диапазоны, добавляя 4 месяца к min_date
  • final - соединить data с date_ranges, сгенерировать номер строки
  • main query - отфильтровать демо, которые являются 4-м, 5-м, 6-м, ... в определенный период

Код:

WITH tally(N) AS (
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM sys.all_columns a CROSS JOIN sys.all_columns b
), min_date_per_customer AS (
  SELECT customer, MIN(date_of_demo) AS min_date
  FROM #data
  GROUP BY customer
), date_ranges AS (
  SELECT t.N, mdpc.customer
      ,[date_start] = DATEADD(m, 4 * (t.N - 1), min_date)
      ,[date_end]   = DATEADD(m, 4 *t.N, min_date)
  FROM min_date_per_customer mdpc
  CROSS JOIN tally t
  WHERE t.N < 100 -- you can generate as many period as you wish
), final AS (
  SELECT d.customer
       ,d.demo_given
       ,d.date_of_demo
       ,dr.N
       ,rn = ROW_NUMBER() OVER (PARTITION BY dr.customer, dr.N ORDER BY date_of_demo)
  FROM #data d
  JOIN date_ranges dr
    ON d.[date_of_demo] >= dr.date_start
   AND d.[date_of_demo] <= dr.date_end
   AND d.customer = dr.customer
)
SELECT *
FROM final
WHERE rn > 3
ORDER BY customer, date_of_demo;

LiveDemo

Выход:

╔══════════╦════════════╦═════════════════════╦═══╦═════╗
║ customer ║ demo_given ║    date_of_demo     ║ N ║ rn  ║
╠══════════╬════════════╬═════════════════════╬═══╬═════╣
║   508325 ║ G61        ║ 2015-03-16 00:00:00 ║ 1 ║   4 ║
║   508325 ║ G61        ║ 2015-03-17 00:00:00 ║ 1 ║   5 ║
║   508325 ║ G61        ║ 2015-09-01 00:00:00 ║ 2 ║   4 ║
╚══════════╩════════════╩═════════════════════╩═══╩═════╝
person Lukasz Szozda    schedule 31.10.2015