主页 / dba / 问题 / 235191
Accepted
SaiKarthikChandra
Asked: 2019-04-19 09:00:21 +0800 CST

仅合并在上一个事件之后立即开始的事件，没有任何间隙

  • 772

我怎样才能只合并随后发生的日期。？

（即，下一个事件在前一个事件完成后立即开始）。

在这里，我们还需要考虑其他一些列来获取输出。

这是示例事件甘特图的附加屏幕截图。 在此处输入图像描述 下表包含事件（分别为 M_ID、Y_ID、T_ID、E_ID、BeginDate、EndDate）

CREATE TABLE #EventTable
(
    M_ID INT,
    Y_ID INT,
    T_ID INT,
    E_ID INT,
    BeginDate DATE,
    EndDate DATE
);

下面的插入语句用于示例数据设置。

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 2, 7, 3, '01 Oct 2017', '31 Oct 2017'),
(135709, 2, 7, 3, '01 Dec 2017', '31 Dec 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 Oct 2017', '31 Oct 2017'),
(134560, 5, 8, 3, '01 Nov 2017', '30 Nov 2017'),
(135678, 3, 6, 2, '01 Jan 2017', '31 Mar 2017'),
(135678, 3, 6, 2, '01 Apr 2017', '30 Jun 2017'),
(135678, 3, 6, 2, '01 Oct 2017', '31 Oct 2017'),
(135678, 3, 6, 2, '01 Nov 2017', '31 Dec 2017'),
(123457, 4, 2, 3, '01 May 2017', '31 Oct 2017');

上面的屏幕截图中提到了所需的输出日期（标记为红色）。 在此处输入图像描述

例子：

对于M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 1;

第 2 场活动在（2017 年 3 月 31 日）第 1 场活动结束后立即开始（2017 年 4 月 1 日）。

因此，在这种情况下，输出应该是第一个事件的开始日期（2017 年 1 月 1 日）和第二个事件的结束日期（2017 年 6 月 30 日）。

同样，对于M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 3;

第 2 场比赛于 2017 年 2 月 1 日开始，紧接着第 1 场比赛于 2017 年 1 月 31 日结束。

第 3 场赛事在（2017 年 4 月 30 日）第二场赛事结束后立即开始（2017 年 4 月 1 日）。

因此，在这种情况下，输出应该是第一个事件的开始日期（2017 年 1 月 1 日）和第三个事件的结束日期（2017 年 6 月 30 日）。

并且由于在第 4 个事件 StartDate 和第 3 个事件 EndDate 之间存在 GAP（即第 4 个事件不会在第 3 个事件之后开始），因此日期应该保持不变。

有人可以帮我吗。？

sql-server gaps-and-islands

2 个回答

  1. Best Answer

    这有点令人费解，但我认为这会得到你想要的结果：

    ;WITH src AS 
(
SELECT et.M_ID
    , et.Y_ID
    , et.T_ID
    , et.E_ID
    , et.BeginDate
    , Marker = CASE WHEN et.BeginDate <= DATEADD(DAY, 1, LAG(et.EndDate, 1, et.BeginDate) OVER (PARTITION BY et.M_ID
            , et.Y_ID
            , et.T_ID
            , et.E_ID
             ORDER BY et.BeginDate))
        THEN 1
        ELSE 0
        END
    , et.EndDate
FROM #EventTable et
)
, src2 AS 
(
SELECT src.*
    , rn1 = ROW_NUMBER() OVER (PARTITION BY src.M_ID
            , src.Y_ID
            , src.T_ID
            , src.E_ID
            , src.Marker
             ORDER BY src.BeginDate)
    , rn2 = ROW_NUMBER() OVER (PARTITION BY src.M_ID
            , src.Y_ID
            , src.T_ID
            , src.E_ID
            , src.Marker
             ORDER BY src.BeginDate DESC)
FROM src
)
SELECT src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , StartDate = MIN(src2.BeginDate)
    , EndDate = MAX(src2.EndDate)
FROM src2
WHERE src2.rn1 = 1
    OR src2.rn2 = 1
GROUP BY src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , src2.Marker
ORDER BY src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , StartDate
    , EndDate;

    输出：

    ╔════════╦══════╦══════╦══════╦════════════╦══════ ══════╗
║ M_ID ║ Y_ID ║ T_ID ║ E_ID ║ StartDate ║ EndDate ║
╠════════╬══════╬══════╬══════╬════════════╬══════ ══════╣
║ 134560 ║ 5 ║ 8 ║ 3 ║ 2017-04-01 ║ 2017-08-31 ║
║ 135709 ║ 2 ║ 7 ║ 1 ║ 2017-01-01 ║ 2017-06-30 ║
║ 135709 ║ 2 ║ 7 ║ 3 ║ 2017-01-01 ║ 2017-05-31 ║
║ 135709 ║ 2 ║ 7 ║ 3 ║ 2017-07-01 ║ 2017-08-31 ║
║ 135709 ║ 5 ║ 8 ║ 1 ║ 2017-02-01 ║ 2017-07-31 ║
╚════════╩══════╩══════╩══════╩════════════╩══════ ══════╝

    该代码使用LAG()聚合来检查下一个逻辑行，以查看开始日期是否早于当前行中的结束日期或与结束日期相邻一天。然后它使用两个ROW_NUMBER()聚合来获取每个组的第一行和最后一行。

    • 2

  2. 如果您使用的是 SQL 2012 或更高版本，则可以使用LEAD函数调用（即LEAD([BeginDate],1) OVER (PARTITION BY [M_ID],[Y_ID],[T_ID],[E_ID]) ORDER BY [BeginDate]). 这样，您可以将当前行EndDate与下一行进行比较BeginDate。

    • 1

相关问题