标记记录孤岛

Question

我有这个数据集：

下一个Id过程>//tr>产品2流程A//tr>产品2流程A产品1产品2流程B产品2流程B流程B流程A2021-05-01流程A产品1流程A空<1td>流程C<2021-05>//tr>

Id	上一个Id	产品	日期
1	NULL		产品1	流程A	2021-04-24
2	NULL	3	2021-04-24
3	2	5	2021-04-24
	1		7	流程B	2021-04-26
5	3	6	2021-04-24
	6	5	NULL	2021-04-24
7	4	9	产品1	2021-04-29
9	7	10	产品1
10	9	15	产品1	2021-05-03
15	10	29	2021-05-04
19	15	产品1

您可以使用lag()来确定值的相同位置。然后是累计总和：

select t.*,
1 + sum(case when process = prev_process then 0 else 1 end) over (partition by producct order by id) as tag
from (select t.*,
lag(process) over (partition by product order by id) as prev_process
from t
) t;

这里有一个db<gt；不停摆弄

如果您不必验证prevId和nextId(也就是说，如果您的数据已经正确排序(，您可以尝试以下操作：

WITH cte AS(
SELECT *
, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY [Date]) x
, DENSE_RANK() OVER (PARTITION BY Product, Process ORDER BY [Date]) y
FROM T1
WHERE product = 'Product 1'
),
cteTag AS(
SELECT Id, PrevId, NextId, Product, Process, [Date], x-y AS Tag_
FROM cte
)
SELECT Id, PrevId, NextId, Product, Process, [Date], DENSE_RANK() OVER (PARTITION BY Product ORDER BY Tag_) AS Tag
FROM cteTag
ORDER BY [Date]