按连续日期范围对数值分组的行

Question

考虑一张按照Col1、Col2、Date1、Date2排序的表格T：

Col1    Col2    Date1         Date2          rate
ABC     123     11/4/2014     11/5/2014      -90
ABC     123     11/4/2014     11/6/2014      -55
ABC     123     11/4/2014     11/7/2014      -90
ABC     123     11/4/2014     11/10/2014     -90

我想对数据进行分组以方便审计/减少重复，因此我有以下内容：

Col1    Col2    Date1         start_Date2    end_Date2      rate
ABC     123     11/4/2014     11/5/2014      11/5/2014      -90
ABC     123     11/4/2014     11/6/2014      11/6/2014      -55
ABC     123     11/4/2014     11/7/2014      11/10/2014     -90

如果我可以得到另一列，行号为1 2 3 3（仅需要确保数字不同），那么我可以轻松完成这个任务，然后按照该列进行GROUP BY。

我的查询尝试：

SELECT *, DENSE_RANK() OVER (ORDER BY rate) island
FROM T
ORDER BY Date2

不能提供我所需要的内容:

Col1    Col2    Date1         Date2          rate     island
ABC     123     11/4/2014     11/5/2014      -90      1
ABC     123     11/4/2014     11/6/2014      -55      2
ABC     123     11/4/2014     11/7/2014      -90      1
ABC     123     11/4/2014     11/10/2014     -90      1

我希望查询可以识别第二组-90值作为新的一组，因为它们出现在与另一个rate不同的组之后。

[gaps-and-islands] SQL标签非常有帮助，但我还无法弄清楚如何处理速率恢复到先前值的情况。我应该如何修改我的查询？

- Prashant Kumar

---

你可能会对这个问题的答案感兴趣：如何使用row_number()和dense_rank()解决“间隙和岛屿”问题？ - Evan Carroll

Answer 1

您可以通过使用row_numbers()的不同来识别组。连续的值将具有一个常数。

select col1, col2, date1, min(date2), max(date2), rate
from (select t.*,
             (row_number() over (partition by col1, col2, date1 order by date2) -
              row_number() over (partition by col1, col2, date1, rate order by date2)
             ) as grp
      from table t
     ) t
group by col1, col2, date1, rate, grp

- Gordon Linoff

---

关于此问题的解释，您可能会对使用row_number()和dense_rank()解决“间隔和岛屿”问题的答案感兴趣。 - Evan Carroll