SQL Server DENSE_RANK()
SQL Server DENSE_RANK()
提问于 2017-03-08 03:29:55
我有一个表，其中每个行项目都包含一个unit#、日期戳和床位数。每天为具有床位数的每个单元创建一条记录。
Unit DateTime Beds
----------------------
ICU 2011-03-23 12
ICU 2011-03-24 24
ICU 2011-03-25 24
ICU 2011-03-26 35
ICU 2011-03-27 24
ICU 2011-03-28 24我正在尝试获取数据并创建一个类似下面的表。
Unit Beds StartDate EndDate
------------------------------
ICU 12 2011-03-23 2011-03-23
ICU 24 2011-03-24 2011-03-25
ICU 35 2011-03-26 2011-03-26
ICU 24 2011-03-27 2011-03-28问题是，有24个床位的行是为了获得这些结果而组合起来的。
Unit Beds StartDate EndDate
------------------------------
ICU 12 2011-03-23 2011-03-23
ICU 24 2011-03-24 2011-03-28
ICU 35 2011-03-26 2011-03-26我尝试使用DENSE_RANK分配一个排名，将其用作分组编号来分隔24个床位的实例。我希望grouper值是1,2,2,3,4,4，而grouper值是1,2,2,3,2,2。
SELECT DENSE_RANK() OVER(PARTITION BY Unit ORDER BY Beds) AS Grouper,
Unit, DateTime, Beds
FROM StatsLocation
Grouper Unit DateTime Beds
-------------------------------
1 ICU 2011-03-23 12
2 ICU 2011-03-24 24
2 ICU 2011-03-25 24
3 ICU 2011-03-26 35
2 ICU 2011-03-27 24
2 ICU 2011-03-28 24回答 2
Stack Overflow用户
回答已采纳
发布于 2017-03-08 03:33:42
您可以使用lag检查前一行是否具有相同的beds值，并获得与Grouper列相同的运行总和。
SELECT SUM(COL) OVER(PARTITION BY Unit ORDER BY DateTime) as Grouper,Unit,DateTime,Beds
FROM (
SELECT CASE WHEN lag(beds) OVER(PARTITION BY Unit ORDER BY DateTime)=beds then 0 ELSE 1 END AS col,
Unit, DateTime, Beds
FROM StatsLocation
) X此后，获取开始和结束日期很容易，每组都有min和max。
WITH CTE AS(
SELECT SUM(COL) OVER(PARTITION BY Unit ORDER BY DateTime) as Grouper,Unit,DateTime,Beds
FROM (SELECT CASE WHEN lag(beds) OVER(PARTITION BY Unit ORDER BY DateTime)=beds then 0 ELSE 1 END AS col,
Unit, DateTime, Beds
FROM StatsLocation) t
)
SELECT UNIT,BEDS,MIN(DATETIME) AS STARTDATE,MAX(DATETIME) AS ENDDATE
FROM CTE
GROUP BY UNIT,BEDS,GROUPER如果您不需要grouper列，而只需要开始和结束日期，则可以使用不同的行号来完成此操作。
SELECT UNIT,BEDS,MIN(DATETIME) AS STARTDATE,MAX(DATETIME) AS ENDDATE
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY Unit ORDER BY Dt)
- ROW_NUMBER() OVER(PARTITION BY Unit,Beds ORDER BY Dt) AS Grouper,
Unit, Dt, Beds
FROM StatsLocation) T
GROUP BY UNIT,BEDS,GROUPERStack Overflow用户
发布于 2017-03-08 03:46:54
这是一个缺口和孤岛问题，你可以使用两个row_number()来解决它，如下所示：
select
Unit
, Beds
, StartDate = min(DateTime)
, EndDate = max(DateTime)
from (
select *
, rn_x = row_number() over (partition by unit order by [datetime])
, rn_y = row_number() over (partition by unit, beds order by [datetime])
from t
) as s
group by Unit, Beds, rn_x-rn_y
order by Unit, StartDaterextester演示：http://rextester.com/IJXC7931
返回：
+------+------+------------+------------+
| Unit | Beds | StartDate | EndDate |
+------+------+------------+------------+
| ICU | 12 | 2011-03-23 | 2011-03-23 |
| ICU | 24 | 2011-03-24 | 2011-03-25 |
| ICU | 35 | 2011-03-26 | 2011-03-26 |
| ICU | 24 | 2011-03-27 | 2011-03-28 |
+------+------+------------+------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42656735
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号