sql - 对表进行分区以获取日期范围

Question

我有一个用户的历史记录表，我正在尝试查找该用户持有特定用户名的日期范围。该表是一个由触发器填充的审核表，因此每次用户(而不仅仅是用户名)发生更改时，它都会有条目。我可以这样做来获取每行的日期范围:

CREATE TABLE #LoginHistory
(
  LoginHistoryID INT IDENTITY(1,1),
  LoginID INT,
  Username VARCHAR(32),
  StartDate DATETIME
)

INSERT INTO #LoginHistory (LoginID, Username, StartDate) VALUES
(1, 't', '2016-01-01'),
(1, 't', '2016-01-02'),
(1, 't', '2016-01-04'),
(1, 'test', '2016-01-05'),
(2, 't', '2016-01-08'),
(2, 'tom', '2016-01-09'),
(1, 'test', '2016-01-15'),
(1, 't', '2016-02-01')

SELECT
    LoginID,
    Username,
    StartDate,
    EndDate = LEAD(StartDate) OVER (PARTITION BY LoginID ORDER BY StartDate ASC)
FROM #LoginHistory
WHERE LoginID = 1
ORDER BY StartDate ASC

DROP TABLE #LoginHistory

输出:

LoginID  Username  StartDate                EndDate
1        t         2016-01-01 00:00:00.000  2016-01-02 00:00:00.000
1        t         2016-01-02 00:00:00.000  2016-01-04 00:00:00.000
1        t         2016-01-04 00:00:00.000  2016-01-05 00:00:00.000
1        test      2016-01-05 00:00:00.000  2016-01-15 00:00:00.000
1        test      2016-01-15 00:00:00.000  2016-02-01 00:00:00.000
1        t         2016-02-01 00:00:00.000  NULL

但是，我真正想做的是折叠每个用户名持续时间，以便用户持有用户名的每个日期范围都有一行。基本上，我正在寻找这个输出:

LoginID  Username  StartDate                EndDate
1        t         2016-01-01 00:00:00.000  2016-01-05 00:00:00.000
1        test      2016-01-05 00:00:00.000  2016-02-01 00:00:00.000
1        t         2016-02-01 00:00:00.000  NULL

我该如何正确折叠这些行？

Answer 1

您可以使用以下查询:

SELECT LoginID,
       Username,
       MIN(StartDate) AS StartDate,
       MAX(EndDate) AS EndDate
FROM (
  SELECT
      LoginID,
      Username,
      StartDate,
      EndDate = LEAD(StartDate) OVER (PARTITION BY LoginID 
                                      ORDER BY StartDate ASC),
      ROW_NUMBER() OVER (ORDER BY StartDate) -
      ROW_NUMBER() OVER (PARTITION BY LoginID, Username 
                         ORDER BY StartDate) AS grp
  FROM #LoginHistory
  WHERE LoginID = 1) AS t
GROUP BY LoginID, Username, grp
ORDER BY StartDate ASC

grp 可帮助您识别具有相同 LoginID、Username 值的连续行。