----- ------- 
| id  | status|
 ----- ------- 
| 01  | open  |
| 02  | close |
| 03  | close |
| 04  | close |
| 05  | open  |
| 06  | open  |
| 07  | open  |
| 08  | close |
| 09  | open  |
| 10  | close |
 ----- ------- 

My expect:

• get a number of records same value adjacent value
• example:

CodePudding user response：

This is a gaps and islands problem, you may use the difference between two row_number functions to define unique groups for consecutive similar 'status' values, then perform aggregation based on that groups. Check the following:

select status, count(*) number
from
(
  select *,
    row_number() over (order by cast(id as signed)) - 
    row_number() over (partition by status order by cast(id as signed)) grp
  from table_name
) T
group by status, grp
order by grp

See a demo.