sqlamazon-web-servicesamazon-redshift

How to count from 0 and onward for every new ID?


I constructed the following query and it gives me the following output...

with tb1 as
(
    select  id
        ,row_number() over(order by id)-1 as lag_0
    from id_store
),
tb2 as
(
    select *
        ,lag(lag_0) over(order by id) as lag_1
    from tb1 
),
tb3 as
(
    select *
        ,lag(lag_1) over(order by id) as lag_2
    from tb2
),
tb4 as
(
    select *
        ,lag(lag_2) over(order by id) as lag_3
    from tb3
)
    select *
         ,lag(lag_3) over(order by id) as lag_4
    from tb4
id lag_0 lag_1 lag_2 lag_3 lag_4
213 0 NULL NULL NULL NULL
215 1 0 NULL NULL NULL
217 2 1 0 NULL NULL
313 3 2 1 0 NULL
315 4 3 2 1 0

The desired Output is what I would like. I only added five ids to keeps things simple. I tried a cross join as well but I got some strange results.

id group_name ord_num
213 lag_0 0
215 lag_0 1
217 lag_0 2
313 lag_0 3
315 lag_0 4
213 lag_1 NULL
215 lag_1 0
217 lag_1 1
313 lag_1 2
315 lag_1 3
213 lag_2 NULL
215 lag_2 NULL
217 lag_2 0
313 lag_2 1
315 lag_2 2
213 lag_3 NULL
215 lag_3 NULL
217 lag_3 NULL
313 lag_3 0
315 lag_3 1
213 lag_4 NULL
215 lag_4 NULL
217 lag_4 NULL
313 lag_4 NULL
315 lag_4 0

Solution

  • --      S a m p l e    D a t a : 
    Create Table id_store (id Int);
    Insert Into id_store 
    VALUES (213), (215), (217), (313), (315);
    

    One option could be using recursive CTE to generate rows and prepare data for fetching the expected result.

    WITH 
      Recursive tb1 (pass, id, group_name, ord, rn) AS
        ( Select 1, id, 'lag_0' as group_name, 0 as ord, 
                 Row_NUmber() Over(Order By id) as rn
          From id_store 
         UNION ALL 
          Select pass + 1, id, Concat('lag_', pass), 
                 Case When rn > ord + 1 then ord + 1 End, rn
          From   tb1
          Where  pass < ( Select Count(id) From id_store )
        )
    Select id, group_name, rn - 1 - ord as ord_num
    From   tb1;
    

    R e s u l t :

    id group_name ord_num
    213 lag_0 0
    215 lag_0 1
    217 lag_0 2
    313 lag_0 3
    315 lag_0 4
    213 lag_1 null
    215 lag_1 0
    217 lag_1 1
    313 lag_1 2
    315 lag_1 3
    213 lag_2 null
    215 lag_2 null
    217 lag_2 0
    313 lag_2 1
    315 lag_2 2
    213 lag_3 null
    215 lag_3 null
    217 lag_3 null
    313 lag_3 0
    315 lag_3 1
    213 lag_4 null
    215 lag_4 null
    217 lag_4 null
    313 lag_4 null
    315 lag_4 0

    fiddle