基于值变化的Sql计数条件

Question

我有一个这样的表

对 对对对

试试这样:

SELECT t.date, 
t.test,
sum(t.r) OVER (ORDER BY t.date)
FROM (
SELECT DATE,
test,
CASE WHEN lag(test) OVER (order by date) IS null Then 1
WHEN test <> lag(test) OVER (order by date) Then 1
ELSE 0
END as r
FROM tableA
)t

db小提琴