I have a table include "ID" and "Values", and wanted to know how many times does value "A" jumped into another values like below
| ID | Values |
|---|---|
| 1 | A |
| 1 | A |
| 1 | A |
| 1 | B |
| 1 | A |
| 1 | B |
| 1 | B |
| 1 | C |
| 1 | C |
| 1 | C |
| 1 | A |
| 2 | A |
| 2 | A |
| 2 | B |
| 2 | A |
| 2 | B |
| 2 | C |
| 2 | B |
Expected Result:
| ID | Values | Desired Output |
|---|---|---|
| 1 | A | 0 |
| 1 | A | 0 |
| 1 | A | 1 |
| 1 | B | 0 |
| 1 | A | 1 |
| 1 | B | 0 |
| 1 | B | 0 |
| 1 | C | 0 |
| 1 | C | 0 |
| 1 | C | 0 |
| 1 | A | 0 |
| 2 | A | 0 |
| 2 | A | 1 |
| 2 | B | 0 |
| 2 | A | 1 |
| 2 | B | 0 |
| 2 | C | 0 |
| 2 | B | 0 |
The final table should be like this:
| ID | Number of Transitions |
|---|---|
| 1 | 2 |
| 2 | 2 |
You just need LEAD() to look at the next value:
select id, values, lead(value) over(partition by id) next_value
from table
Then you can compare next_value with values, and apply an iff(value='A' and next_value!='A', 1, 0).
Then just SUM() or COUNT() and GROUP BY.
You could also treat this as a regexp problem where you want to count how many times a given pattern occurs for each id. The missing piece in your question is -what column dictates the order in which the values appear for each id? You'll need that for either of the solutions
select id, regexp_count(listagg(val,',') within group (order by ordering_col), 'A,[^A]')
from t
group by id;