SQL: Count rows where column value changed from previous row

Question

Suppose I have Oracle or Postgresql database.

 ID         IdExample  OrderByColumn   What I want 
---------- ---------- ----------      ----------              
   1         1       1300                 1              
   2         1       2450                 1              
   3         2       5000                 2               
   4         2       4800                 2
   5         1       5100                 3
   6         1       6000                 3
   7         4       7000                 4
   8         1       8000                 5

How do count the changes that are in idExample, data is sorted by OrderByColumn I need output new column that is represented by "what I want" pay attention to "1" in IdExample. It repeats but I wants to iterate. The query should execute quickly with the table having tens of thousands of records.

THANKS

Answer 1

You need to use lag and sum analytical function as follows:

Select t.*, 
       sum(case when lg is null or lg <> idexample then 1 else 0 end)
           over (order by id) as result
from
(Select t.*, 
       lag(idexample) over (order by id) as lg
  From your_table t) t