sql-server - SQL按id分组，用其他id分隔

Question

我使用 MS SQL Server 2008 R2。我有一张 table :

    TypeId    EventDate                 Value      1.  1         2013-11-01 10:13:00.000   50  2.  1         2013-11-01 10:15:00.000   10  3.  1         2013-11-01 10:18:00.000   40  4.  2         2013-11-01 10:19:00.000   12  5.  2         2013-11-01 10:21:00.000   545 6.  1         2013-11-01 10:23:00.000   35  7.  1         2013-11-01 10:27:00.000   47 8.  3         2013-11-01 10:30:00.000   39.  4         2013-11-01 10:31:00.000   010. 4         2013-11-01 10:33:00.000   711. 4         2013-11-01 10:38:00.000   3512. 1         2013-11-01 10:41:00.000   91 13. 5         2013-11-01 10:45:00.000   3714. 5         2013-11-01 10:48:00.000   35

我想要基于 TypeId 字段值的连续组，这些组不被其他 TypeId 值分隔(当按 EventDate 排序时)。
在这个例子中，我想要有七个组:

• TypeId=1，记录1-3
• TypeId=2，记录4-5
• TypeId=1，记录6-7
• TypeId=3，记录8
• TypeId=4，记录9-11
• TypeId=1，记录12
• TypeId=5，记录13-14

如果我使用 GROUP BY TypeId，我会得到一组 TypeId=1，由记录 1-3、6-7 和 12 组成

例如，我想为这些组中的任何一个获取最小和最大 EventDate:

TypeId  MinEventDate              MaxEventDate1       2013-11-01 10:13:00.000   2013-11-01 10:18:00.0002       2013-11-01 10:19:00.000   2013-11-01 10:21:00.0001       2013-11-01 10:23:00.000   2013-11-01 10:27:00.0003       2013-11-01 10:30:00.000   2013-11-01 10:30:00.0004       2013-11-01 10:31:00.000   2013-11-01 10:38:00.0001       2013-11-01 10:41:00.000   2013-11-01 10:41:00.0005       2013-11-01 10:45:00.000   2013-11-01 10:48:00.000

Answer 1

这是一个适用于大数据集的快速替代方案。 CTE 计算表示 (EventDate) 排序的行号与表示 (TypeId, EventDate) 排序的行号之间的差异，并将该差异称为 grp。在每个连续的 TypeId 段中，差异将是恒定的，并且小于将为下一个连续的 TypeId 段产生的差异。

然后一组 (TypeId, grp) 包含具有相同状态的所有连续行:

; with  CTE as 
        (
        select  TypeId
        ,       EventDate
        ,       row_number() over (order by EventDate)
                    - row_number() over (order by TypeId, EventDate) as grp
        from     dbo.TestTable
        )
select  min(EventDate) as mn
,       max(EventDate) as mx
,       TypeId
from    CTE
group by 
        TypeId
,       grp
order by 
        mn;

See it working at SQLFiddle

有关此方法的更多详细信息，请参阅此 sample chapter of MVP Deep Dives .