Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE temp TABLE p
(
    id text,
    t text[],
    r integer[],
    PRIMARY KEY (id)
);
insert into p values (
'p1','{{"b",NULL,NULL},{"c","d","e"},{"f",NULL,NULL}}','{{2,NULL,NULL},{11,11,12},{101,NULL,NULL}}');

insert into p values (
'p2','{{"a",NULL},{"b","c"},{"b",NULL}}','{{1,NULL},{1,1},{1,NULL}}'
                     );
insert into p values (
'p3','{{"a",NULL},{"b","c"},{"b",NULL}}','{{1,NULL},{1,1},{1,NULL}}'
                     );
insert into p values (
'p4','{{"b",NULL,NULL,"test"},{"c","d","e","f"},{"f",NULL,NULL, NULL},{"g",NULL,NULL, null}}',
                      '{{2,NULL,NULL,3},{11,11,12,111},{101,NULL,NULL,90},{11,11,121,178}}');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

with cte as (
select p.id, u.t, u.r,u.stage,
       array_length(p.t,2),
--        round(u.stage::numeric/array_length(p.t,2),1),
       case when round(u.stage::numeric/array_length(p.t,2),1) =  floor(round(u.stage::numeric/array_length(p.t,2),1) )
            then  round(u.stage::numeric/array_length(p.t,2),1)::integer else null
        end as case1,
       row_number() over ( order by id, stage) as row_num
from p,
unnest(t, r) with ordinality as u(t, r,stage)),
cte2 as (select *,
       lead(case1,1 ) over ( partition by id order by row_num ) as lead1,
       lead(case1,2) over (partition by id order by row_num) as lead2,
       lead(case1,3) over (partition by id order by row_num) as lead3
from cte
order by row_num)
select id, t,r, stage,row_num,
        case
            when lead1 is null and lead2 is not null and case1 is null then lead2
            when lead2 is null and lead1 is not null and case1 is null then lead1
            when lead1 is null and lead2 is  null and case1 is null then lead3
            when case1 is not null  then case1
        end
from cte2 ;


id
t
r
stage
row_num
case


p1
b
2
1
1
1

p1
null
null
2
2
1

p1
null
null
3
3
1

p1
c
11
4
4
2

p1
d
11
5
5
2

p1
e
12
6
6
2

p1
f
101
7
7
3

p1
null
null
8
8
3

p1
null
null
9
9
3

p2
a
1
1
10
1

p2
null
null
2
11
1

p2
b
1
3
12
2

p2
c
1
4
13
2

p2
b
1
5
14
3

p2
null
null
6
15
3

p3
a
1
1
16
1

p3
null
null
2
17
1

p3
b
1
3
18
2

p3
c
1
4
19
2

p3
b
1
5
20
3

p3
null
null
6
21
3

p4
b
2
1
22
1

p4
null
null
2
23
1

p4
null
null
3
24
1

p4
test
3
4
25
1

p4
c
11
5
26
2

p4
d
11
6
27
2

p4
e
12
7
28
2

p4
f
111
8
29
2

p4
f
101
9
30
3

p4
null
null
10
31
3

p4
null
null
11
32
3

p4
null
90
12
33
3

p4
g
11
13
34
4

p4
null
11
14
35
4

p4
null
121
15
36
4

p4
null
178
16
37
4

id	t	r	stage	row_num	case
p1	b	2	1	1	1
p1	null	null	2	2	1
p1	null	null	3	3	1
p1	c	11	4	4	2
p1	d	11	5	5	2
p1	e	12	6	6	2
p1	f	101	7	7	3
p1	null	null	8	8	3
p1	null	null	9	9	3
p2	a	1	1	10	1
p2	null	null	2	11	1
p2	b	1	3	12	2
p2	c	1	4	13	2
p2	b	1	5	14	3
p2	null	null	6	15	3
p3	a	1	1	16	1
p3	null	null	2	17	1
p3	b	1	3	18	2
p3	c	1	4	19	2
p3	b	1	5	20	3
p3	null	null	6	21	3
p4	b	2	1	22	1
p4	null	null	2	23	1
p4	null	null	3	24	1
p4	test	3	4	25	1
p4	c	11	5	26	2
p4	d	11	6	27	2
p4	e	12	7	28	2
p4	f	111	8	29	2
p4	f	101	9	30	3
p4	null	null	10	31	3
p4	null	null	11	32	3
p4	null	90	12	33	3
p4	g	11	13	34	4
p4	null	11	14	35	4
p4	null	121	15	36	4
p4	null	178	16	37	4