SQL Server语言 - 分组与行号-差距和岛屿

Question

我有一个这样的数据表

+-------------+--------------+------------+----------------+
| 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              |
+-------------+--------------+------------+----------------+

我需要在这个表的顶部创建一个视图，以下述格式格式化Flag的数据，基本上是Flag为Y或N的持续时间。(EndDateSID - 0是当前活动的，所以今天的日期)

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

大多数客户只更改他们的Flag一次，因此下面的查询工作:

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

对于像上面那样多次更改标志的客户，我如何实现预期的输出?

Answer 1

您可以使用以下查询:

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聚合函数。

演示

Answer 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;