sql 服务器 - 将数据集拆分到窗口中，并在 tsql 中从该窗口中获取最小值

Question

我在将数据集拆分为其他几个数据集时遇到问题，以便我可以从中获取最小值。为此，键可以在不同的国家/地区同时使用（国家/地区 ID 在 a 列中）。一个键（图中的b列）可以在一段时间后再次重复使用，在本例中为120天。一个键可以有大约 10 个事件以及日期。

带有示例数据的图片（我已经为每个周期或任何你称之为它们的理论边界着色）

我尝试做的事情是，获取整个集合的第一个日期，对 a 和 b 列进行分区，并使用 datediff() 来获取整个集合的最小日期和每个单独事件之间的天数。 然后使用 dateDiff/120 返回的数字对它们进行分组。我真正想做的是获取每组（而不是整组）的最小日期并基于此进行计算。我真的希望我解释得足够清楚，让你们这些好心的助手理解我所追求的是什么。现在我想起来了..我有点担心，即使我确实得到了单个集合的最小日期，然后将其除以 120，我也会得到不正确的结果。任何建议表示赞赏！

我使用了这样的东西：DATEDIFF(d,min(dates) OVER (PARTITION BY a, b), dates)/120

视觉解释我需要实现的目标以及如何实现。

                sample data                                   desired outcome
|  CountryId  |    Key      |     date     |    |  CountryId  |      Key    |     date     | 
|-------------|-------------|--------------|    |-------------|-------------|--------------|
|  2          |  093123124  |  2015-04-16  |    |  2          |  093123124  |  2015-04-16  |
|  2          |  093123124  |  2015-04-16  |    |  2          |  093123124  |  2015-11-24  |
|  2          |  093123124  |  2015-04-17  |    |  2          |  093123124  |  2016-04-17  |
|  2          |  093123124  |  2015-04-17  |
|  2          |  093123124  |  2015-11-24  |
|  2          |  093123124  |  2015-11-24  |
|  2          |  093123124  |  2015-11-25  |
|  2          |  093123124  |  2015-11-25  |
|  2          |  093123124  |  2015-11-25  |
|  2          |  093123124  |  2016-04-17  |
|  2          |  093123124  |  2016-04-18  |
|  2          |  093123124  |  2016-04-20  |
|  2          |  093123124  |  2016-04-21  |
|  2          |  093123124  |  2016-04-22  |

Answer 1

这似乎是 CTE 的一个案例。下面的代码查找每个集合递归为每个递归添加 120 天。

SET DATEFORMAT YMD
DECLARE @SampleData TABLE(  A   INT,
                            B   NVARCHAR(25),
                            C   DATETIME)

INSERT INTO @SampleData(A, B, C)
VALUES (2,'093123124', CONVERT(DATETIME, '2015-04-16')),
       (2,'093123124', CONVERT(DATETIME, '2015-04-16')),
       (2,'093123124', CONVERT(DATETIME, '2015-04-17')),
       (2,'093123124', CONVERT(DATETIME, '2015-04-17')),
       (2,'093123124', CONVERT(DATETIME, '2015-11-24')),
       (2,'093123124', CONVERT(DATETIME, '2015-11-24')),
       (2,'093123124', CONVERT(DATETIME, '2015-11-25')),
       (2,'093123124', CONVERT(DATETIME, '2015-11-25')),
       (2,'093123124', CONVERT(DATETIME, '2015-11-25')),
       (2,'093123124', CONVERT(DATETIME, '2016-04-17')),
       (2,'093123124', CONVERT(DATETIME, '2016-04-18')),
       (2,'093123124', CONVERT(DATETIME, '2016-04-20')),
       (2,'093123124', CONVERT(DATETIME, '2016-04-21')),
      -- (2,'093123124', CONVERT(DATETIME, '2016-03-24')),
       (2,'093123124', CONVERT(DATETIME, '2016-04-22'));
WITH lvl1
AS
(
    SELECT  A, 
            B, 
            C = MIN(C)
    FROM @SampleData
    GROUP BY    A,
                B
),
lvlOthers
AS
(
    SELECT  A,
            B,
            C
    FROM lvl1
    UNION ALL
    SELECT  S.A,
            S.B,
            C = MIN(S.C) OVER(PARTITION BY S.A, S.B)
    FROM lvlOthers
    INNER JOIN @SampleData AS S ON  lvlOthers.A = S.A
                                AND lvlOthers.B = S.B
                                AND S.C > DATEADD(DAY, 120, lvlOthers.C)
)
SELECT DISTINCT A, B, C
FROM lvlOthers
OPTION (MAXRECURSION 100)

请注意，此代码未针对性能进行微调。事实上，它在大型数据集上的表现会很差。

此外，如果日期范围超过 ~32 年，则应增加最大递归。