时间戳

首页 > 解决方案 > 如何将日期列表“组合”到日期范围

sql - 如何将日期列表“组合”到日期范围

问题描述

如何将日期列表组合成日期范围，我需要将不同行中的一系列天组合成开始日期和结束日期的日期范围。

Create Table #temp_dates
 (Str_Date DateTime,
 End_Date DateTime,
 Rate decimal(10,4))
 ;

 Insert Into #temp_dates
 Values ('2012-01-05 07:55:24.000','2012-03-03 12:25:02.000',0.001),
('2012-03-03 12:25:02.000','2012-03-04 15:28:16.000',0.001),
('2012-03-04 15:28:16.000','2012-08-23 05:14:07.000',0.001),
('2012-08-23 05:14:07.000','2013-04-24 15:04:25.000',0.001),
('2013-04-24 15:04:25.000','2015-04-13 05:37:59.000',0.0015),
('2015-04-13 05:37:59.000','2015-10-11 08:50:24.000',0.0015),
('2015-10-11 08:50:24.000','2016-03-19 23:58:35.000',0.0015),
('2016-03-19 23:58:35.000','2016-03-20 11:07:56.000',0.001),
('2016-03-20 11:07:56.000','2016-04-12 21:05:06.000',0.0015),
('2016-04-12 21:05:06.000','2016-04-12 22:31:41.000',0.001),
('2016-04-12 22:31:41.000','2016-04-20 00:45:32.000',0.0015),
('2016-04-20 00:45:32.000','2016-09-10 19:17:31.000',0.0015)

转动

StartDateTime   EndDateTime Rate
2012-01-05 07:55:24.000 2012-03-03 12:25:02.000 0.001
2012-03-03 12:25:02.000 2012-03-04 15:28:16.000 0.001
2012-03-04 15:28:16.000 2012-08-23 05:14:07.000 0.001
2012-08-23 05:14:07.000 2013-04-24 15:04:25.000 0.001
2013-04-24 15:04:25.000 2015-04-13 05:37:59.000 0.0015
2015-04-13 05:37:59.000 2015-10-11 08:50:24.000 0.0015
2015-10-11 08:50:24.000 2016-03-19 23:58:35.000 0.0015
2016-03-19 23:58:35.000 2016-03-20 11:07:56.000 0.001
2016-03-20 11:07:56.000 2016-04-12 21:05:06.000 0.0015
2016-04-12 21:05:06.000 2016-04-12 22:31:41.000 0.001
2016-04-12 22:31:41.000 2016-04-20 00:45:32.000 0.0015
2016-04-20 00:45:32.000 2016-09-10 19:17:31.000 0.0015

进入

StartDateTime   EndDateTime Rate
2012-01-05 07:55:24.000 2013-04-24 15:04:25.000 0.001
2013-04-24 15:04:25.000 2016-03-19 23:58:35.000 0.0015
2016-03-19 23:58:35.000 2016-03-20 11:07:56.000 0.001
2016-03-20 11:07:56.000 2016-04-12 21:05:06.000 0.0015
2016-04-12 21:05:06.000 2016-04-12 22:31:41.000 0.001
2016-04-12 22:31:41.000 2016-09-10 19:17:31.000 0.0015

标签： sqlsql-servergaps-and-islands

解决方案

这是一种差距和孤岛问题。在这种情况下，用于lag()确定组的开始位置。然后使用这些“开始”的累积和来定义要组合在一起的行。

最后一步是实际的聚合：

select rate, min(str_date), max(end_date)
from (select td.*,
             sum(case when prev_end_date = str_date then 0 else 1 end) over (partition by rate order by str_date) as grp
      from (select td.*,
                   lag(end_date) over (partition by rate order by str_date) as prev_end_date
            from temp_dates td
           ) td
     ) td
group by rate, grp
order by min(str_date);

这是一个 db<>fiddle。

推荐阅读