SQL Server DENSE_RANK()

Question

我有一个表，每个订单项都包含一个单位＃，日期邮票和床计数。每天为每个单元的床数创建记录。

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张床的实例分开。我想让石斑鱼值为1,2,2,3,4,4。相反，石块值为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

Answer 1

您可以使用 lag检查上一行是否具有相同的床位值，并获得与石斑鱼列的运行总和。

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

此后，要获得开始和结束日期，每组的开始和最大都很容易。

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

如果您不需要石块列，而只是开始日期，则可以使用行号的差异来完成。

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,GROUPER

Answer 2

这是一个差距和岛屿问题，您可以使用两个 row_number() s这样解决它：

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, StartDate

rextester演示：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 |
+------+------+------------+------------+