如何为每个岛屿的连续记录编号？

Question

我有一张桌子，看起来像：

group    date        color
  A      1-1-2019      R
  A      1-2-2019      Y
  B      1-1-2019      R
  B      1-2-2019      Y
  B      1-3-2019      Y
  B      1-4-2019      R
  B      1-5-2019      R
  B      1-6-2019      R

它是按组和日期排序的。我想要一个额外的列来显示每个组的 连续 颜色’R’的顺序号。

要求的输出：

group    date        color    rank
  A      1-1-2019      R      1
  A      1-2-2019      Y      null
  B      1-1-2019      R      1
  B      1-2-2019      Y      null
  B      1-3-2019      Y      null
  B      1-4-2019      R      1
  B      1-5-2019      R      2
  B      1-6-2019      R      3

我试图将窗口函数与按组和颜色列进行分区一起使用，但它返回的输出在下面是不正确的。

错误的查询和输出：

SELECT 
    *, 
    RANK() OVER (PARTITION BY group, color order by group, date) as rank
FROM table

group    date        color    rank
  A      1-1-2019      R      1
  A      1-2-2019      Y      null
  B      1-1-2019      R      1
  B      1-2-2019      Y      null
  B      1-3-2019      Y      null
  B      1-4-2019      R      2
  B      1-5-2019      R      3
  B      1-6-2019      R      4

我想知道它是否可以在SQL中使用，还是应该切换到另一种语言（例如Python）？

Answer 1

这是使用窗口功能可以完成的方式。首先，我们创建一个CTE，该CTE具有一个标志，指示新的序列已开始，然后从中生成一个对序列号进行计数的标志。最后，我们对每个序列中的行进行计数以获得排名：

WITH cte AS (SELECT `group`, date, color,
                    COALESCE(color = LAG(color) OVER(ORDER BY `group`, date), 0) AS samecolor
             FROM `table`),
sequences AS (SELECT `group`, date, color,
              SUM(samecolor = 0) OVER (ORDER BY `group`, date) AS seq_num
              FROM cte)
SELECT `group`, date, color,
       ROW_NUMBER() OVER (PARTITION BY seq_num) AS `rank`
FROM sequences
ORDER BY `group`, date

输出：

group   date        color   rank
A       1-1-2019    R       1
A       1-2-2019    Y       1
B       1-1-2019    R       1
B       1-2-2019    Y       1
B       1-3-2019    Y       2
B       1-4-2019    R       1
B       1-5-2019    R       2
B       1-6-2019    R       3

dbfiddle上的演示

请注意Y，如果您希望将值NULL替换rank为此的定义，则此查询还会给出值的排名：

CASE WHEN color = 'Y' THEN NULL
     ELSE ROW_NUMBER() OVER (PARTITION BY seq_num) 
     END AS `rank`