Make geometric sequence from series of bit values

Marek Brojo 21 Reputation points
2020-11-21T11:53:39.857+00:00

I have this table:

declare @Table table (value int)  
insert @Table select 0  
insert @Table select 1  
insert @Table select 1  
insert @Table select 1  
insert @Table select 0  
insert @Table select 1  
insert @Table select 1  

Now, I need to make a Select query, which would add a column. This column will make a geometric sequence once there is a serie of value 1 in column value.

This would be the result:
41616-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 111.8K Reputation points
    2020-11-21T13:03:28.283+00:00

    I think that you need a column that specifies the order of these values, otherwise the order is undefined. The next possible approach uses an additional id column:

    declare @Table table (id int primary key, value int)
    insert @Table select 1, 0
    insert @Table select 3, 1
    insert @Table select 4, 1
    insert @Table select 7, 1
    insert @Table select 8, 0
    insert @Table select 14, 1
    insert @Table select 17, 1
    
    ;
    with Q1 as
    (
        select *, isnull(lag(value) over (order by id), 0) as prev
        from @Table
    ),
    Q2 as
    (
        select *, (select max(id) from Q1 where id <= q.id and prev = 0) as mx
        from Q1 as q
    ),
    Q3 as
    (
        select *, (select count(id) from Q2 where id <= q.id and mx = q.mx) as c
        from Q2 as q
    ) 
    select value, case value when 1 then POWER(2, c) else 0 end as GeometricSequence
    from Q3
    order by id
    

    There are alternatives, though. Maybe show your attempts too.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Marek Brojo 21 Reputation points
    2020-11-21T18:15:50.893+00:00

    taken from my question at stackoverflow.com, solution by Gordon Linoff

    select t.*,
           value * power(2, row_number() over (partition by grp order by id) - 1) as generatedsequence
    from (select t.*, sum(1 - value) over (order by id) as grp
          from @table t
         ) t;
    
    0 comments No comments