当列更改值时重置的SQL/Bigquery列组或row_number

Question

我有一个sql(bigquery(表，如下所示。

| Name  | DaysToGo |Task    |ID  |
|:-----:|:--------:|:------:|:--:|
| Joe   | 50       | A      | 1  |
| Joe   | 49       | A      | 2  |
| Joe   | 48       | B      | 1  |
| Joe   | 47       | B      | 2  |
| Joe   | 46       | B      | 3  |
| Joe   | 45       | A      | 1  |
| Joe   | 47       | A      | 2  |
| Joe   | 46       | A      | 3  |
| Tim   | 50       | B      | 1  |
| Tim   | 49       | B      | 2  |
| Tim   | 48       | B      | 3  |
| Tim   | 47       | C      | 1  |
| Tim   | 46       | B      | 1  |
| Tim   | 45       | B      | 2  |
| Tim   | 47       | C      | 1  |
| Tim   | 46       | C      | 2  |

我希望根据"任务"列中的更改创建"ID"列，并按名称进行分区。本质上，每次"任务"更改时，"ID"都应该重置为1，并从1重新启动ID计数器。

我尝试了秩、行号甚至滞后函数，但似乎无法找到合适的解决方案。有什么想法吗？

Answer 1

这是一种间隙和孤岛问题。在这种情况下，我建议行数的差异：

select t.*,
row_number() over (partition by name, task, seqnum - seqnum_t
order by daystogo desc
) as id
from (select t.*,
row_number() over (partition by name order by daystogo) as seqnum,
row_number() over (partition by name, task order by daystogo) as seqnum_t
from t
) t;

为什么这样做有点难以解释。如果您盯着子查询的结果看，您将看到两个row_number()值之间的差异如何识别每个人的相邻任务。外部查询使用这个差异为最终结果分配一个新的row_number()。