代码之家 › 专栏 › 技术社区 › automatic

围绕间隙的SQL分组

tsql sql-server-2005 sql-server sql

automatic · 技术社区 · 15 年前

在SQL Server 2005中,我有一个表的数据如下所示:

WTN------------Date  
555-111-1212  2009-01-01  
555-111-1212  2009-01-02  
555-111-1212  2009-01-03  
555-111-1212  2009-01-15  
555-111-1212  2009-01-16  
212-999-5555  2009-01-01  
212-999-5555  2009-01-10  
212-999-5555  2009-01-11

我想从中提取wtn,min(日期),max(日期) 扭曲我想去吗 也打破 每当日期有差距时,从上面的数据来看,我的结果应该是:

WTN------------ MinDate---- MaxDate  
555-111-1212   2009-01-01  2009-01-03  
555-111-1212   2009-01-15  2009-01-16  
212-999-5555   2009-01-01  2009-01-01  
212-999-5555   2009-01-10  2009-01-11

如何在SQL select/group by中执行此操作?
是否可以在没有列出我要在其中标识间隙的值的表或列表的情况下完成此操作(此处为日期)?

3 回复 | 直到 15 年前

Aaron Bertrand 15 年前

为什么每个人都坚决反对用桌子来做这种事?数字表或日历表占用的空间很小,如果引用的足够多的话,可能在内存中。您还可以使用row_number()很容易地即时派生一个数字表。使用数字表有助于理解查询。但这里有一个不那么简单的例子,一个我从拉契夫广场学到的技巧,希望它能有所帮助。

DECLARE @wtns TABLE
(
    WTN    CHAR(12),
    [Date] SMALLDATETIME
);

INSERT @wtns(WTN, [Date])
          SELECT '555-111-1212','2009-01-01'
UNION ALL SELECT '555-111-1212','2009-01-02'
UNION ALL SELECT '555-111-1212','2009-01-03'
UNION ALL SELECT '555-111-1212','2009-01-15'
UNION ALL SELECT '555-111-1212','2009-01-16'
UNION ALL SELECT '212-999-5555','2009-01-01'
UNION ALL SELECT '212-999-5555','2009-01-10' 
UNION ALL SELECT '212-999-5555','2009-01-11';

WITH x AS
(
    SELECT
        [Date],
        wtn,
        part = DATEDIFF(DAY, 0, [Date]) 
        + DENSE_RANK() OVER
        (
            PARTITION BY wtn
            ORDER BY [Date] DESC
        )
    FROM @wtns
)
SELECT 
    WTN, 
    MinDate = MIN([Date]),
    MaxDate = MAX([Date])
FROM
    x
GROUP BY 
    part,
    WTN
ORDER BY
    WTN DESC,
    MaxDate;

Erwin Smout 15 年前

你的问题与区间类型和所谓的压缩正态关系有关。

这些问题在“时间数据和关系模型”中被广泛讨论。

不要期望任何SQL系统真正帮助您解决这些问题。

尽管有一些教程系统,但唯一能为这些问题提供适当支持的DBMS,我知道,是我自己的。没有链接,因为我不想在这里做太多的“插拔”。

Cade Roux 15 年前

你可以用 GROUP BY ,通过检测边界:

WITH    Boundaries
      AS (
          SELECT    m.WTN
                   ,m.Date
                   ,CASE WHEN p.Date IS NULL THEN 1
                         ELSE 0
                    END AS IsStart
                   ,CASE WHEN n.Date IS NULL THEN 1
                         ELSE 0
                    END AS IsEnd
          FROM      so1590166 AS m
          LEFT JOIN so1590166 AS p
                    ON p.WTN = m.WTN
                       AND p.Date = DATEADD(d, -1, m.Date)
          LEFT JOIN so1590166 AS n
                    ON n.WTN = m.WTN
                       AND n.Date = DATEADD(d, 1, m.Date)
          WHERE     p.Date IS NULL
                    OR n.Date IS NULL
         )
SELECT  l.WTN
       ,l.Date AS MinDate
       ,MIN(r.Date) AS MaxDate
FROM    Boundaries l
INNER JOIN Boundaries r
        ON r.WTN = l.WTN
           AND r.Date >= l.Date
           AND l.IsStart = 1
           AND r.IsEnd = 1
GROUP BY l.WTN
       ,l.Date