Get occurrence count of specific categories in a table-CodePudding

Looking to get the transition count of categories from a table. For Name type B, category transitions from Good to Bad so count is 2. For Name type A, it transitions from Good - Moderate - Good - Moderate - Bad, hence gets a count of 5.

Any help would be appreciated.

This is my input data:

Name	order no	category
A	1	Good
A	2	Good
A	3	MODERATE
A	4	Good
A	5	MODERATE
A	6	Bad
A	7	Bad
B	1	Good
B	2	Good
B	3	Good
B	4	BAD

And this is my desired output:

Name	category_transition_count
A	5
B	2

CodePudding user response：

You could use the lag window function to get the category of the previous row, and then compare it with the current row to see if it changed, and count those occurrences. Note that by definition the lag of the first value is null, which can't be different from the current value. so you'll need to handle that explicitly:

SELECT name, COUNT(changed)   1
FROM   (SELECT name, 
        CASE WHEN category <> LAG(category) OVER (PARTITION BY name ORDER BY order_no ASC) 
             THEN 1 
        END AS changed
FROM   mytable) t
GROUP BY name

SQLFiddle (PostgreSQL) demo

CodePudding user response：

select   name
        ,count(cnt) as category_transition_count
from 
        (select  name
                ,case when category <> lag(category) over(partition by Name order by order_no) or lag(category) over(partition by Name order by order_no) is null then 1 end as cnt
         from t) t
group by name

name	category_transition_count
A	5
B	2

Fiddle