Count Number of Consecutive Occurrence of Values in Table

Count Number of Consecutive Occurrence of values in Table

One approach is the difference of row numbers:

select name, count(*) 
from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by name order by id)
             ) as grp
      from t
     ) t
group by grp, name;

The logic is easiest to understand if you run the subquery and look at the values of each row number separately and then look at the difference.

Find the Biggest Number of Consecutive Occurrence of values in Table

You can subtract an enumerated value for each name to get a constant for adjacent values that are the same. The rest is aggregation:

select top (1) name, count(*), min(id), max(id)
from (select l.*,
             row_number() over (partition by name order by id) as seqnum
      from #Launches l
     ) l
group by (id - seqnum), name
order by count(*) desc;

Here is a db<>fiddle.

Count consecutive rows for each customer and value

For gaps and islands solution, for first row_number you need to partition by customer.

SELECT customer,    status, COUNT(*) FROM (
select t.*,
             (row_number() over (partition by customer order by id) -
              row_number() over (partition by customer, status order by id)
             ) as grp
      from tickets t
) X
GROUP BY customer,  status, grp
ORDER BY customer,  max(id)

dbfiddle

Result:

customer    status  count
--------    ------  -----
A           0       4
A           1       3
B           0       2
B           1       1
B           0       2
C           0       1

Count max number of consecutive occurrences of a value in SQL Server

You need a column to specify the ordering. SQL tables represent unordered sets. In the below query, the ? represents this column.

You can use the difference of row numbers to get each winning streak:

select player, count(*) as numwins
from (select t.*,
             row_number() over (partition by player order by ?) as seqnum, 
             row_number() over (partition by player, result order by ?) as seqnum_r 
      from t
     ) t
where result = 'W'
group by player, (seqnum - seqnum_r);

You can then get the maximum:

select player, max(numwins)
from (select player, count(*) as numwins
      from (select t.*,
                   row_number() over (partition by player order by ?) as seqnum, 
                   row_number() over (partition by player, result order by ?) as seqnum_r 
            from t
           ) t
      where result = 'W'
      group by player, (seqnum - seqnum_r)
     ) pw
group by player;

Using R to count consecutive occurrences of a number and how long each occurrence lasts for?

You can use rle to get answer to first part.

dt$count <- with(rle(dt$var), rep(values * cumsum(values & lengths >= 2),lengths))

dt
#  time var count
#1  0.0   1     1
#2  0.3   1     1
#3  0.6   0     0
#4  0.9   0     0
#5  1.2   1     2
#6  1.5   1     2
#7  1.8   0     0

Counting consecutive values using partitioning - gaps and islands?

The following gives your desired results.

Using your step number we can create a sequence from which you can subtract the row-number of each partition to greate a group, then you can aggregate by this group:

with g as (
  select StepNumber, status, 
    Row_Number() over(order by StepNumber) 
    - Row_Number() over(partition by status order by StepNumber) grp
  from t
)
select Row_Number() over(order by grp) [Order],
Status, Count(*) [consecutive counts]
from g
group by Status, grp

---

Related Topics