时间的重叠

很多与时间有关的查询都要求标识重叠的时期，下面的示例中，出于统计账单或其他目的，Sessions表跟踪应用程序的用户会话。会话表示接入到互联网的链接，例如一些互联网的供应商按照链接时间收费。该表包含主键（keycol），应用程序名称（app），用户名（usr），开始时间（starttime），结束时间（endtime）

创建表：

USE [tempdb]
GO

/****** Object:  Table [dbo].[Sessions]    Script Date: 08/12/2011 10:56:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Sessions]
    (
      [keycol] [int] IDENTITY(1, 1)
                     NOT NULL ,
      [app] [varchar](10) NOT NULL ,
      [usr] [varchar](10) NOT NULL ,
      [starttime] [datetime] NOT NULL ,
      [endtime] [datetime] NOT NULL ,
      PRIMARY KEY CLUSTERED ( [keycol] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ,
      CONSTRAINT [IX_Sessions] UNIQUE NONCLUSTERED
        ( [app] ASC, [usr] ASC, [starttime] ASC, [endtime] ASC, [keycol] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Sessions]  WITH CHECK ADD CHECK  (([endtime]>[starttime]))
GO

插入数据：

SET IDENTITY_INSERT [Sessions] ON

INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 2 ,
          'app1' ,
          'user1' ,
          '2006/6/12 8:30:00' ,
          '2006/6/12 10:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 4 ,
          'app1' ,
          'user1' ,
          '2006/6/12 9:00:00' ,
          '2006/6/12 9:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 6 ,
          'app1' ,
          'user1' ,
          '2006/6/12 9:15:00' ,
          '2006/6/12 9:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 8 ,
          'app1' ,
          'user1' ,
          '2006/6/12 10:45:00' ,
          '2006/6/12 11:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 3 ,
          'app1' ,
          'user2' ,
          '2006/6/12 8:30:00' ,
          '2006/6/12 8:45:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 5 ,
          'app1' ,
          'user2' ,
          '2006/6/12 9:15:00' ,
          '2006/6/12 10:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 7 ,
          'app1' ,
          'user2' ,
          '2006/6/12 10:30:00' ,
          '2006/6/12 14:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 9 ,
          'app1' ,
          'user2' ,
          '2006/6/12 11:00:00' ,
          '2006/6/12 11:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 10 ,
          'app2' ,
          'user1' ,
          '2006/6/12 8:30:00' ,
          '2006/6/12 8:45:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 12 ,
          'app2' ,
          'user1' ,
          '2006/6/12 11:45:00' ,
          '2006/6/12 12:00:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 14 ,
          'app2' ,
          'user1' ,
          '2006/6/12 12:45:00' ,
          '2006/6/12 13:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 16 ,
          'app2' ,
          'user1' ,
          '2006/6/12 14:00:00' ,
          '2006/6/12 16:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 11 ,
          'app2' ,
          'user2' ,
          '2006/6/12 9:00:00' ,
          '2006/6/12 9:30:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 13 ,
          'app2' ,
          'user2' ,
          '2006/6/12 12:30:00' ,
          '2006/6/12 14:00:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 15 ,
          'app2' ,
          'user2' ,
          '2006/6/12 13:00:00' ,
          '2006/6/12 14:00:00'
        )
INSERT  [Sessions]
        ( [keycol] ,
          [app] ,
          [usr] ,
          [starttime] ,
          [endtime]
        )
VALUES  ( 17 ,
          'app2' ,
          'user2' ,
          '2006/6/12 15:30:00' ,
          '2006/6/12 17:00:00'
        )

SET IDENTITY_INSERT [Sessions] OFF

下面将会讨论三个涉及重叠的技术：标识重叠，分组重叠和最大重叠