UPDATE: I should have mentioned, YES, I have search and attempted to apply many of the numerous examples, however none have given the desired outcome.

I am trying to count the number of consecutive values in a large data set using mysql.

I've tried using partitions however I don't get the correct outcome.

Outcome: using partitions

The outcome I am looking for is

Outcome: desired

Here is a sample from the data set

SQL Fiddle

Can someone point me on how to get the desired outcome ?

CodePudding user response：

This is a classic gaps and island problem. The code should be self-explanatory, but here's the general idea in two steps:

1. Identify for each partition, start of a new block/island by comparing value in current row with the previous row

2. Use a windowed running sum to create the blocks/islands

with cte1 as
(select *, case when flagkey=lag(flagkey) over (partition by supplierkey, attributekey, productkey, locationkey order by datekey) then 0 else 1 end as island_start
 from t),

cte2 as
(select *, sum(island_start) over (partition by supplierkey, attributekey, productkey, locationkey order by datekey) as island
 from cte1)

select *, row_number() over (partition by island order by datekey) as outcome
from cte2