对同一表 SQL Server 中的连续日期范围进行分组 [英] Group continuous date ranges from same table SQL Server

问题描述

我有以下数据:

CREATE TABLE #Rate
(
    RateId Bigint
    ,PropertyId Bigint
    ,StartDate DATETIME
    ,EndDate DATETIME
)

INSERT INTO #Rate VALUES (100,1000,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (100,1000,'2015-01-12','2015-02-02')
INSERT INTO #Rate VALUES (100,1000,'2015-02-11','2015-02-25')
INSERT INTO #Rate VALUES (100,1002,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (100,1002,'2015-01-12','2015-02-02')
INSERT INTO #Rate VALUES (101,1000,'2015-02-11','2015-02-25')
INSERT INTO #Rate VALUES (101,1000,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (101,1000,'2015-01-12','2015-02-02')

我需要这个结果集

100 1000 '2015-01-01'  '2015-02-02'
100 1000 '2015-02-11'  '2015-02-25'
100 1002 '2015-01-01'  '2015-02-02'
101 1002 '2015-01-01'  '2015-02-02'

我需要按 RateId 和 propertyId 以及连续的日期范围进行分组.我已经使用游标完成了这项工作，但我不想要游标，因为我们有很多记录.

I need to group by RateId and propertyId and continuous date range for this. I have done this using cursor but I don't want cursor because we have lots of records.

如果我们能从中创建视图那就太好了:)

If we can create view out of it that will be great :)

谢谢.

推荐答案

用 2015 更改数据中的所有 2010，您可以预期的实际结果集是

Changing all the 2010 with 2015 in your data the actual resultset you can expect is

RateId               PropertyId           StartDate  EndDate
-------------------- -------------------- ---------- ----------
100                  1000                 2015-01-01 2015-02-02
100                  1000                 2015-02-11 2015-02-25
100                  1002                 2015-01-01 2015-02-02
101                  1000                 2015-01-01 2015-02-02
101                  1000                 2015-02-11 2015-02-25

这个问题非常类似于find多行中连续日期的开始和停止日期，所以我将使用我对该答案的回答作为模板

this question is quite similar to find start and stop date for contiguous dates in multiple rows so I'll use my answer to that one as a template

WITH D AS (
  SELECT RateId, PropertyId, StartDate, EndDate
       , _Id = ROW_NUMBER() OVER (PARTITION BY  RateId, PropertyId 
                                  ORDER BY StartDate, EndDate)
  FROM   #Rate
), N AS (
  SELECT m.RateId, m.PropertyId, m.StartDate, m.EndDate
       , LastStop = p.EndDate 
  FROM   D m
         LEFT JOIN D p ON m.RateID = p.RateId 
                      AND m.PropertyId = p.PropertyId 
                      AND m._Id = p._Id + 1
), B AS (
  SELECT RateId, PropertyId, StartDate, EndDate, LastStop
       , Block = SUM(CASE WHEN LastStop Is Null Then 1
                          WHEN LastStop + 1 < StartDate Then 1
                          ELSE 0
                    END)
                 OVER (PARTITION BY RateId, PropertyId ORDER BY StartDate, EndDate)
  FROM   N
)
SELECT RateId, PropertyId
     , MIN(StartDate) StartDate
     , MAX(EndDate) EndDate
FROM   B
GROUP BY RateId, PropertyId, Block
ORDER BY RateId, PropertyId, Block;

D 生成行计数器以避免使用三角连接.
N 获取同一 RateID, PropertyID 组中每一行的前一个 EndDate.
B 为每个区块生成一个序列号
主查询聚合B中的数据，得到想要的结果集.

D generates a row counter to avoid to use triangular join.
N get the previous EndDate in the same RateID, PropertyID group for every row.
B generate a sequence number for every block
The main query aggregates the data in B to get the wanted resultset.

这篇关于对同一表 SQL Server 中的连续日期范围进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！