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

user_2228155 0
Viewed 58
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.

window-functions sql-server sql
2 Answers

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.

user_11440350

This is an example of a gaps-and-islands problem.

There are a number of solutions, here is one.

  • We use LAG to get the start of each island, in this case: the previous value is 0
  • We use a running COUNT to get a grouping number for each island (remember that COUNT ignores nulls)
  • Then we simply GROUP BY the grouping number and count up how many rows we have, and exclude all groups with differing numbers
WITH WithGroupStarts AS (
    SELECT *,
        IsStartOfGroup = CASE WHEN LAG(changing, 1, 0) OVER (PARTITION BY ib.product
                                 ORDER BY ib.sortid) = 0 THEN 1 END
    FROM identicalblocks ib
),
WithGroups AS (
    SELECT *,
        GroupId = COUNT(ib.IsStartOfGroup) OVER (PARTITION BY ib.product
                  ORDER BY ib.sortid ROWS UNBOUNDED PRECEDING)
    FROM WithGroupStarts ib
    WHERE ib.changing <> 0
)
SELECT
    ib.Product,
    blocklengths = COUNT(*)
FROM WithGroups ib
GROUP BY
  ib.Product,
  ib.GroupId
HAVING MIN(ib.changing) = MAX(ib.changing);

SQL Fiddle

user_148689970
Related