Post reply

How to mark consequitive groups with non-unique group key

  • June 10, 2021 at 7:54 am

    Go to Answer

    #3894274

    I have a sequence of records with ID and FLAG being a key to identify the group of records, but the FLAG here is a bit, so it is not unique

    declare @T table (
    	ID	int
    ,	TS	date
    ,	FLAG	bit
    );
    insert into @T(ID, TS, FLAG)
    values
    	(1,	'2020-01-01', 0 )
    ,	(1,	'2020-01-02', 0 )
    ,	(1,	'2020-01-03', 0	)
    ,	(1,	'2020-01-04', 1	)
    ,	(1,	'2020-01-05', 1	)
    ,	(1,	'2020-01-06', 1	)
    ,	(1,	'2020-01-07', 0	)

    ,	(2,	'2020-05-01', 0	)
    ,	(2,	'2020-05-05', 1	)
    ,	(2,	'2020-05-15', 0	)
    ,	(2,	'2020-05-20', 1	)
    ,	(2,	'2020-05-25', 1	)
    ,	(2,	'2020-05-30', 0	)

    I need to get this grouping

    ID	TS	FLAG		GRP
    1	2020-01-01	0	1
    1	2020-01-02	0	1
    1	2020-01-03	0	1
    1	2020-01-04	1	2
    1	2020-01-05	1	2
    1	2020-01-06	1	2
    1	2020-01-07	0	3
    2	2020-05-01	0	1
    2	2020-05-05	1	2
    2	2020-05-15	0	3
    2	2020-05-20	1	4
    2	2020-05-25	1	4
    2	2020-05-30	0	5

    I tried this, but it does not work as it repeats GRP for records with different ID+FLAG key

    select ID, TS, FLAG
    ,	row_number() over(order by ID) - row_number() over(partition by FLAG order by TS)	[GRP]
    from @T
    order by ID, TS

    Results are:
    ID	TS	FLAG	GRP
    1	2020-01-01	0	0
    1	2020-01-02	0	0
    1	2020-01-03	0	0
    1	2020-01-04	1	3
    1	2020-01-05	1	3
    1	2020-01-06	1	3
    1	2020-01-07	0	3
    2	2020-05-01	0	3
    2	2020-05-05	1	5
    2	2020-05-15	0	4
    2	2020-05-20	1	6
    2	2020-05-25	1	6
    2	2020-05-30	0	6

    I am OK with group numbers being not sequential, but my problem is for example 1 2020-01-07 0 3 - group 3 includes flag 1 and 0, that is a mistake. Similar problem is for group 6.

    Any hints would be appreciated

  • June 10, 2021 at 8:48 am

    Answer

    #3894305 

    with cte as (
    select ID, TS, FLAG,
           case when lag(flag) over(partition by id order by ID,TS) = flag then 0 else 1 end as isstart
    from @T
    )
    select ID, TS, FLAG,
           sum(isstart) over(order by ID,TS) as GRP
    from cte
    order by ID, TS;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • June 10, 2021 at 4:26 pm

    #3894529

    Thank you, this seems to work for me, appreciate quick reply

  • July 7, 2021 at 8:33 am

    This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply