Solution using Hive window functions. I used your data to test it, remove your_table CTE and use your table instead. The result is as expected.
with your_table as (--remove this CTE, use your table instead
select stack(14,
'242539622', 1,'A',
'242539622', 2,'A',
'242539622', 3,'A',
'242539622', 4,'B',
'242539622', 5,'B',
'242539622', 6,'C',
'242539622', 7,'D',
'242539622', 8,'D',
'242539622', 9,'D',
'242539622',10,'B',
'242539622',11,'B',
'242539622',12,'D',
'242539622',13,'D',
'242539622',14,'F'
) as (id,sequencenumber,appname)
) --remove this CTE, use your table instead
select id,sequencenumber,appname,
case when sum(new_grp_flag) over(partition by id, group_name) = 1 then appname --only one group of consequent runs exists (like A)
else
nvl(concat(group_name, '_',
sum(new_grp_flag) over(partition by id, group_name order by sequencenumber) --rolling sum of new_group_flag
),appname)
end appname_c
from
(
select id,sequencenumber,appname,
case when appname=prev_appname or appname=next_appname then appname end group_name, --identify group of the same app
case when appname<>prev_appname or prev_appname is null then 1 end new_grp_flag --one 1 per each group
from
(
select id,sequencenumber,appname,
lag(appname) over(partition by id order by sequencenumber) prev_appname, --need these columns
lead(appname) over(partition by id order by sequencenumber) next_appname --to identify groups of records w same app
from your_table --replace with your table
)s
)s
order by id,sequencenumber
;
Result:
OK
id sequencenumber appname appname_c
242539622 1 A A
242539622 2 A A
242539622 3 A A
242539622 4 B B_1
242539622 5 B B_1
242539622 6 C C
242539622 7 D D_1
242539622 8 D D_1
242539622 9 D D_1
242539622 10 B B_2
242539622 11 B B_2
242539622 12 D D_2
242539622 13 D D_2
242539622 14 F F
Time taken: 232.319 seconds, Fetched: 14 row(s)
