对来自同一表SQL Server的连续日期范围进行分组
对来自同一表SQL Server的连续日期范围进行分组
提问于 2014-12-15 00:44:01
我有以下数据：
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分组，并为此连续的日期范围。我已经使用游标做到了这一点，但我不想要游标，因为我们有很多记录。
如果我们能从中创建视图，那就太好了:)
谢谢。
回答 2
Stack Overflow用户
发布于 2014-12-15 08: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中的数据以获得所需的结果集。
Stack Overflow用户
发布于 2014-12-15 00: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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27471514
复制相关文章
相似问题
腾讯云开发者
