SQL count the length of blocks of identical numbers, while ignoring blocks of changing numbers

CREATE TABLE identicalblocks
(
    [sortid] int, 
    [product] varchar(57), 
    [changing] int
)
;

INSERT INTO identicalblocks ([sortid], [product], [changing])
VALUES
(1, 'product a', 0),
(2, 'product a', 3),
(3, 'product a', 0),
(4, 'product a', 7),
(5, 'product a', 7),
(6, 'product a', 7),
(7, 'product a', 7),
(8, 'product a', 0),
(9, 'product a', 0),
(10, 'product a', 1),
(11, 'product a', 3),
(12, 'product a', 1),
(13, 'product a', 0),
(14, 'product a', 0),
(1, 'product b', 0),
(2, 'product b', 2),
(3, 'product b', 2),
(4, 'product b', 2),
(5, 'product b', 3),
(6, 'product b', 0),
(7, 'product b', 0),
(8, 'product b', 12),
(9, 'product b', 12),
(10, 'product b', 0),
(11, 'product b', 0),
(12, 'product b', 0);

I built a sqlfiddle here:
http://sqlfiddle.com/#!18/555ace

The logic that I want to implement is that when the “changing” column switches from 0 to some positive integer, I want to start counting. If at any point the integer changes, I want to abandon counting. I only want to count the block if the integer stays the same until the block ends and goes back to 0.

My desired result would be:

Product	blocklengths
product a	1
product a	4
product b	2

Explanation:

For product a, we count the first “3” as length 1, as it switched from 0 to 3 back to 0.

We next count product a’s block “7” as occurring 4 times in a row, switching from 0.

We skip counting product a’s last block, as it switches from 1 to 3 back to 1.

Our resulting lengths are 1 and 4.

For product b, we skip the first block as it changes from 2 to 3 right before ending back to 0.

Product b’s second block is counted as 12 appears twice without changing, as length 2.

Answer

You can treat this as a gaps-and-islands problem. The simplest method I think is to assign the “islands” by using a cumulative sum of 0 values. Then aggregate and filter:

select product, sum(case when changing <> 0 then 1 else 0 end)
from (select ib.*,
             sum(case when changing = 0 then 1 else 0 end) over (partition by product order by sortid) as grp
      from identicalblocks ib
     ) ib
group by product, grp
having count(distinct changing) = 2;  -- 0 and the other value

Here is a db<>fiddle.

Advertisement

Answer