SQL Server-комплексная группировка по пробелам и островам

У меня есть таблица с такими данными 

+-------------+--------------+------------+----------------+
| CustomerSID | StartDateSID | EndDateSID | MarketingOptIn |
+-------------+--------------+------------+----------------+
|       12345 |     20101019 |   20131016 | Y              |
|       12345 |     20131017 |   20140413 | Y              |
|       12345 |     20140414 |   20140817 | N              |
|       12345 |     20140818 |   20141228 | N              |
|       12345 |     20141229 |   20150125 | Y              |
|       12345 |     20150126 |          0 | Y              |
+-------------+--------------+------------+----------------+

Мне нужно создать представление поверх этой таблицы, чтобы данные были отформатированы в формате ниже для флага, в основном длительность, для которой флаг был Y или N. (EndDateSID-0 в данный момент активен, поэтому сегодняшняя дата) 

+-------------+--------------+------------+----------------+
| CustomerSID | StartDateSID | EndDateSID | MarketingOptIn |
+-------------+--------------+------------+----------------+
|       12345 |     20101019 |   20140413 | Y              |
|       12345 |     20140414 |   20141228 | N              |
|       12345 |     20141229 |   20150825 | Y              |
+-------------+--------------+------------+----------------+

Большинство клиентов меняют свой флаг только один раз, поэтому ниже запрос работает:

SELECT 
CH1.CustomerSID
,MIN(CH1.StartDateSID) StartDate
,MAX(ISNULL(NULLIF(CH1.EndDateSID,0),CONVERT(INT, CONVERT(VARCHAR, GETDATE(), 112)))) EndDate
,CH1.MarketingOptIn 
FROM DWH.DimCustomerHistory CH1
GROUP BY CH1.CustomerSID, CH1.MarketingOptIn
ORDER BY CH1.CustomerSID, CH1.MarketingOptIn

Как я могу достичь желаемого результата для клиентов, подобных приведенному выше, имея изменения в флаге больше, чем один раз?

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

2 3
sql sql-server group-by self-join

2 ответа:

Это проблема пробелов и островов. Вам нужно использовать ROW_NUMBER() для определения ваших пробелов, поэтому начальный этап будет следующим: 

SELECT  CustomerSID,
        StartDateSID,
        EndDateSID,
        MarketingOptIn,
        TotalRowNum = ROW_NUMBER() OVER(PARTITION BY CustomerSID ORDER BY StartDateSID),
        RowNumInGroup = ROW_NUMBER() OVER(PARTITION BY CustomerSID, MarketingOptIn ORDER BY StartDateSID),
        GroupID = ROW_NUMBER() OVER(PARTITION BY CustomerSID ORDER BY StartDateSID) -   
                ROW_NUMBER() OVER(PARTITION BY CustomerSID, MarketingOptIn ORDER BY StartDateSID)
FROM    dbo.YourTable;

Вывод: 

CustomerSID StartDateSID    EndDateSID  MarketingOptIn  TotalRowNum RowNumInGroup   GroupID
---------------------------------------------------------------------------------------------------
12345       20101019        20131016    Y               1           1               0
12345       20131017        20140413    Y               2           2               0
12345       20140414        20140817    N               3           1               2
12345       20140818        20141228    N               4           2               2
12345       20141229        20150125    Y               5           3               2
12345       20150126        0           Y               6           4               2
Ключ здесь заключается в том, что, взяв номер строки каждой строки, а также номер строки каждой строки в группе, вы можете получить уникальный идентификатор (GroupID + MarketingOptIn), который идентифицирует каждый из ваших островов. Тогда это просто случай группировки по этому идентификатору при выполнении ваших агрегатов:

ПОЛНАЯ РАБОТА Пример 

DECLARE @T TABLE 
(   
    CustomerSID INT,
    StartDateSID INT,
    EndDateSID INT,
    MarketingOptIn CHAR(1)
)
INSERT @T 
VALUES
    (12345, 20101019, 20131016, 'Y'),
    (12345, 20131017, 20140413, 'Y'),
    (12345, 20140414, 20140817, 'N'),
    (12345, 20140818, 20141228, 'N'),
    (12345, 20141229, 20150125, 'Y'),
    (12345, 20150126, 0, 'Y');


WITH CTE AS
(
    SELECT  CustomerSID,
            StartDateSID,
            EndDateSID,
            MarketingOptIn,
            GroupID = ROW_NUMBER() OVER(PARTITION BY CustomerSID ORDER BY StartDateSID) -   
                    ROW_NUMBER() OVER(PARTITION BY CustomerSID, MarketingOptIn ORDER BY StartDateSID)
    FROM    @T
)
SELECT  CustomerSID, 
        StartDateSID = MIN(StartDateSID),
        EndDateSID = CASE WHEN MIN(EndDateSID) = 0 THEN CONVERT(INT, CONVERT(VARCHAR(8), GETDATE(), 112)) ELSE MAX(EndDateSID) END,
        MarketingOptIn
FROM    CTE
GROUP BY CustomerSID, MarketingOptIn, GroupID
ORDER BY CustomerSID, StartDateSID;
4

Вы можете использовать следующий запрос: 

SELECT CustomerSID,
       MIN(StartDateSID) AS StartDate,
       MAX(ISNULL(NULLIF(EndDateSID,0),
           CONVERT(INT, CONVERT(VARCHAR, GETDATE(), 112)))) AS EndDate,
       MarketingOptIn
FROM (       
  SELECT CustomerSID, StartDateSID, EndDateSID, MarketingOptIn,
         ROW_NUMBER() OVER (ORDER BY StartDateSID) - 
         ROW_NUMBER() OVER (PARTITION BY CustomerSID, MarketingOptIn 
                            ORDER BY StartDateSID) AS grp     
  FROM DimCustomerHistory ) AS t
GROUP BY CustomerSID, MarketingOptIn, grp
ORDER BY StartDate
Вычисляемое поле grpслужит для идентификации последовательных записей , имеющих одинаковое значениеMarketingOptIn.

Используя это поле во внешнем запросе, вы можете легко GROUP BY и применять MIN и MAX агрегатные функции аналогично вашему исходному запросу.

Демо здесь

5