sql - 确定连续的日期间隔

Question

我有以下表结构:

id int -- more like a group id, not unique in the table
AddedOn datetime -- when the record was added

对于特定的 id，每天最多有 一条记录。我必须编写一个查询，为每个 id 返回连续的(在日级别)日期间隔。
预期的结果结构是:

id int
StartDate datetime
EndDate datetime

请注意，AddedOn 的时间部分是可用的，但在这里并不重要。

为了更清楚，这里有一些输入数据:

with data as 
(
  select * from
  (
    values
    (0, getdate()), --dummy record used to infer column types

    (1, '20150101'),
    (1, '20150102'),
    (1, '20150104'),
    (1, '20150105'),
    (1, '20150106'),

    (2, '20150101'),
    (2, '20150102'),
    (2, '20150103'),
    (2, '20150104'),
    (2, '20150106'),
    (2, '20150107'),

    (3, '20150101'),
    (3, '20150103'),
    (3, '20150105'),
    (3, '20150106'),
    (3, '20150108'),
    (3, '20150109'),
    (3, '20150110')
  ) as d(id, AddedOn)
  where id > 0 -- exclude dummy record
)
select * from data

和预期的结果:

id      StartDate      EndDate
1       2015-01-01     2015-01-02
1       2015-01-04     2015-01-06

2       2015-01-01     2015-01-04
2       2015-01-06     2015-01-07

3       2015-01-01     2015-01-01
3       2015-01-03     2015-01-03
3       2015-01-05     2015-01-06
3       2015-01-08     2015-01-10

虽然它看起来像一个常见的问题，但我找不到足够类似的问题。此外，我越来越接近解决方案，我会在(如果)它有效时发布它，但我觉得应该有一个更优雅的解决方案。

Answer 1

这里的答案没有任何花哨的加入，而只是使用 group by 和 row_number，这不仅简单而且效率更高。

WITH CTE_dayOfYear
AS
(
    SELECT  id,
            AddedOn,
            DATEDIFF(DAY,'20000101',AddedOn) dyID,
            ROW_NUMBER() OVER (ORDER BY ID,AddedOn) row_num
    FROM data
)

SELECT  ID,
        MIN(AddedOn) StartDate,
        MAX(AddedOn) EndDate,
        dyID-row_num AS groupID
FROM CTE_dayOfYear
GROUP BY ID,dyID - row_num
ORDER BY ID,2,3

逻辑是 dyID 基于日期，因此存在间隙而 row_num 没有间隙。所以每次在dyID中出现一个gap，那么它就会改变row_num和dyID之间的差值。然后我简单地使用该差异作为我的 groupID。