sql - 循环组模式中的密集排名

Question

说我有一张像

店铺	日期	开了
湾	2022 年 1 月 1 日	真的
湾	2022 年 1 月 2 日	真的
湾	2022 年 1 月 3 日	真的
湾	2022 年 1 月 4 日	错误的
湾	2022 年 1 月 5 日	错误的
湾	2022 年 1 月 6 日	错误的
湾	2022 年 1 月 7 日	真的
湾	2022 年 1 月 8 日	真的
湾	2022 年 1 月 9 日	真的
沃尔玛	2022 年 1 月 7 日	真的
沃尔玛	2022 年 1 月 8 日	错误的
沃尔玛	2022 年 1 月 9 日	真的

我希望他们使用 partition by 并获得组的排名，例如

店铺	日期	开了	团体
湾	2022 年 1 月 1 日	真的	1
湾	2022 年 1 月 2 日	真的	1
湾	2022 年 1 月 3 日	真的	1
湾	2022 年 1 月 4 日	错误的	2
湾	2022 年 1 月 5 日	错误的	2
湾	2022 年 1 月 6 日	错误的	2
湾	2022 年 1 月 7 日	真的	3
湾	2022 年 1 月 8 日	真的	3
湾	2022 年 1 月 9 日	真的	3
沃尔玛	2022 年 1 月 7 日	真的	1
沃尔玛	2022 年 1 月 8 日	错误的	2
沃尔玛	2022 年 1 月 9 日	真的	3

我开始尝试分区store，is_open但真的很困惑在 order by 子句中使用什么，任何帮助将不胜感激。

标签： sqldatabasedatabase-partitioning

Answer 1

这实际上是一个空白和孤岛问题。一种方法使用行号差异方法：

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY store ORDER BY date) rn1,
                ROW_NUMBER() OVER (PARTITION BY store, is_open ORDER BY date) rn2
    FROM yourTable t
),
cte2 AS (
    SELECT t.*, MIN(date) OVER (PARTITION BY store, is_open, rn1 - rn2) AS min_date
    FROM cte t
)

SELECT store, date, is_open,
       DENSE_RANK() OVER (PARTITION BY store ORDER BY rn1 - rn2, min_date) "group"
FROM cte2
ORDER BY store, date;

请注意，我们在这里使用第二个 CTEcte2来查找每个岛屿的最小日期值。这样做是为了将两个岛与不同的is_open值（真/假）区分开来，这些值恰好在行号上有相同的差异。它确保在因行数不同而出现平局的情况下，首先报告较早的岛屿。

演示