使用 SQL 压缩时间段 [英] Condense Time Periods with SQL

问题描述

我有一个大型数据集，就这个问题而言，它有 3 个字段:

I have a large data set which for the purpose of this question has 3 fields:

• 组标识符
• 开始日期
• 迄今为止

在任何给定的行上，From Date 将始终小于 To Date 但在每组中，时间段(没有特定顺序)由日期对可以重叠，一个包含在另一个中，甚至是相同的.

On any given row the From Date will always be less than the To Date but within each group the time periods (which are in no particular order) represented by the date pairs could overlap, be contained one within another, or even be identical.

我想要结束的是一个查询，它将每个组的结果压缩为连续的时间段.例如一个看起来像这样的组:

What I'd like to end up with is a query that condenses the results for each group down to just the continuous periods. For example a group that looks like this:

| Group ID | From Date  | To Date    |
--------------------------------------
| A        | 01/01/2012 | 12/31/2012 |
| A        | 12/01/2013 | 11/30/2014 |
| A        | 01/01/2015 | 12/31/2015 |
| A        | 01/01/2015 | 12/31/2015 |
| A        | 02/01/2015 | 03/31/2015 |
| A        | 01/01/2013 | 12/31/2013 |

会导致:

| Group ID | From Date  | To Date    |
--------------------------------------
| A        | 01/01/2012 | 11/30/2014 |
| A        | 01/01/2015 | 12/31/2015 |

我已经阅读了许多关于日期打包的文章，但我不太明白如何将其应用于我的数据集.

I've read a number of articles on date packing but I can't quite figure out how to apply that to my data set.

如何构建一个可以给我这些结果的查询?

How can construct a query that would give me those results?

推荐答案

《Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions》一书中的解决方案

The solution from book "Microsoft® SQL Server ® 2012 High-Performance T-SQL Using Window Functions"

;with C1 as(
select GroupID, FromDate as ts, +1 as type, 1 as sub
  from dbo.table_name
union all
select GroupID, dateadd(day, +1, ToDate) as ts, -1 as type, 0 as sub
  from dbo.table_name),
C2 as(
select C1.*
     , sum(type) over(partition by GroupID order by ts, type desc
                      rows between unbounded preceding and current row) - sub as cnt
  from C1),
C3 as(
select GroupID, ts, floor((row_number() over(partition by GroupID order by ts) - 1) / 2 + 1) as grpnum
  from C2
  where cnt = 0)

select GroupID, min(ts) as FromDate, dateadd(day, -1, max(ts)) as ToDate
  from C3
  group by GroupID, grpnum;

创建表:

if object_id('table_name') is not null
  drop table table_name
create table table_name(GroupID varchar(100), FromDate datetime,ToDate datetime)
insert into table_name
select 'A', '01/01/2012', '12/31/2012' union all
select 'A', '12/01/2013', '11/30/2014' union all
select 'A', '01/01/2015', '12/31/2015' union all
select 'A', '01/01/2015', '12/31/2015' union all
select 'A', '02/01/2015', '03/31/2015' union all
select 'A', '01/01/2013', '12/31/2013'

这篇关于使用 SQL 压缩时间段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！