问对来自同一表SQL Server的连续日期范围进行分组
EN

Stack Overflow用户

提问于 2014-12-14 16:44:01

回答 2查看 1.7K关注 0票数 2

我有以下数据：

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分组，并为此连续的日期范围。我已经使用游标做到了这一点，但我不想要游标，因为我们有很多记录。

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

谢谢。

sql

sql-server

stored-procedures

sql-server-2008-r2

回答 2

Stack Overflow用户

发布于 2014-12-15 00:05:52

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

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 start and stop date for contiguous dates in multiple rows，所以我将使用我对这个问题的答案作为模板

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中的数据以获得所需的结果集。

票数 2

Stack Overflow用户

发布于 2014-12-14 16:51:28

假设您使用的是SQL Server 2012+，您可以采用以下方法：

查找与上一条记录不重叠的所有记录。它们开始一个范围。
计算在任何给定记录之前的此类记录的数量。它们为范围分配一个常量值。
将其用作分组因子。

该查询如下所示：

select rateid, propertyid, min(startdate) as startdate, max(enddate) as enddate
from (select r.*,
             sum(case when preved < startdate then 1 else 0 end) over (partition by rateid, propertyid order by startdate) as grp
      from (select r.*,
                   lag(enddate) over (partition by rateid, propertyid order by enddate) as preved
            from #Rate r
           ) r
     ) r
group by rateid, propertyid, grp;

编辑：

在SQL Server 2008中，您可以执行类似的操作：

with r as (
      select r.*,
             (case when exists (select 1
                                from #rate r2
                                where r2.rateid = r.rateid and r2.propertyid = r.propertyid and
                                      (r2.startdate <= dateadd(1 day, r.enddate) and
                                       r2.enddate >= r.startdate)
                               ) then 0 else 1 end) as isstart
      from #Rate r join
           #Rate r2
     )
select rateid, propertyid, min(startdate) as startdate, max(enddate) as enddate
from (select r.*,
             (select sum(isstart)
              from r r2
              where r2.rateid = r.rateid and r2.propertyid = r.propertyid
                    r2.startdate <= r.startdate) as grp
      from r
     ) r
group by rateid, propertyid, grp;