DENSE_RANK在两列上，其中一列是不同的值，另一列是重复的

Question

我有以下场景，我正在努力理解如何应用DENSE_RANK()来获得我想要的结果:

<表类> ID 日期 价值 tbody><<tr>11990-05-171.0011991-10-121.0011992-08-011.0011993-07-050.6711994-05-020.6711995-02-011.0011996-03-011.00

您可以使用LAG()窗口函数为每一行获取其先前的值，并使用SUM()窗口函数的条件聚合获得Periods:

SELECT ID, Date, Value,
SUM(CASE WHEN VALUE = prev_value THEN 0 ELSE 1 END) OVER (PARTITION BY ID ORDER BY Date) Period 
FROM (
SELECT *, LAG(Value) OVER (PARTITION BY ID ORDER BY Date) prev_value
FROM tablename
) t
ORDER BY Date;

这就是所谓的缺口和孤岛问题。一种方法是使用几个ROW_NUMBERs将数据分组:

WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [date],[value])-
ROW_NUMBER() OVER (PARTITION BY ID, [value] ORDER BY [date]) AS Grp
FROM (VALUES(1,CONVERT(date,'1990-05-17'),1.00),
(1,CONVERT(date,'1991-10-12'),1.00),
(1,CONVERT(date,'1992-08-01'),1.00),
(1,CONVERT(date,'1993-07-05'),0.67),
(1,CONVERT(date,'1994-05-02'),0.67),
(1,CONVERT(date,'1995-02-01'),1.00),
(1,CONVERT(date,'1996-03-01'),1.00))V(ID,Date,Value))
SELECT ID,
Date,
Value,
DENSE_RANK() OVER (PARTITION BY ID ORDER BY Grp) AS Period
FROM CTE;