SQL Server - 通过标识汇总日期

Question

SQL Server - 通过标识汇总日期

3

我是使用 SQL Server 2008（如果需要，也可以访问 SQL 2017），我有一个像这样的表：

DECLARE @tbl TABLE (recdate DATE, myflag BIT)

那个表格包含了一个日期范围内的所有行，myflag位会不断变化，类似于这样：

recdate    | myflag
2017-01-01 | 1
2017-01-02 | 1
2017-01-03 | 1
...
2017-04-03 | 1
2017-04-04 | 0
2017-04-05 | 0
..
2017-05-15 | 0
2017-05-16 | 1
etc.

但是我真正需要的是类似于以下的内容

period_from | period_to  | myflag
2017-01-01  | 2017-04-03 | 1
2017-04-04  | 2017-05-15 | 0
2017-05-16  | 2017-05-21 | 1

每次myflag变化时，它都会创建一行新的记录，并将上一行的结束日期设置好（如果这样说可以理解的话）。

我相信有一种非常明显的方法可以做到这一点，但我已经准备好撞头了...我已经反复尝试了选择、子选择、插入和更新临时表，甚至尝试使用游标（我知道！但这是一个一次性的查询）。

- Kevin M

请查看LEAD和LAG。但是您必须超越2008年，因为它直到2012年才被引入。 - Sean Lange

谢谢Sean - 我下面有一个答案，但我一定会去查看，这样我就可以希望学到一些有用的东西！ - Kevin M

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

这是一个间隔和岛屿问题。您可以使用行号的差异来解决此问题：

select min(recdate) as period_from, max(recdate) as period_to, flag
from (select t.*,
             row_number() over (order by recdate) as seqnum,
             row_number() over (partition by flag order by recdate) as seqnum_f
      from @tbl t
     ) t
group by (seqnum - seqnum_f), flag;

为什么这样做有点难以用文字解释。如果运行子查询，您会看到为什么差异对于您要查找的组是恒定的。

如果您的日期是连续的，没有间隙、重复或时间组件，您可以使用稍微简单一些的方法:

select min(recdate) as period_from, max(recdate) as period_to, flag
from (select t.*,
             dateadd(day, 
                     - row_number() over (partition by flag order by recdate
                     recdate
                    ) as grp
      from @tbl t
     ) t
group by grp, flag;

这基本上是与第一个版本相同的逻辑。