query-complete.sql

explain
with
qup as (
    select
         curr_season -- all xxx_seasosn are always smallint
        ,curr_code-- all xx_code are always varchar(10)
        ,array_agg(id_up order by id_up)||array_fill(0::smallint,array[10]) as mat_arr
        ,array_agg(curr_mat_code order by id_up) as matcode_arr
        ,bit_or(imask) as ibitmask
    from(
        select
             sup_season as curr_season
            ,sup_sa_code as curr_code
            ,sup_mat_code as curr_mat_code
            ,sup_clr_code as curr_clr_code
            ,id_up
            ,coalesce(id_up,-1) as imask
        from only sa_upper
        left join upper_target on up_mat_code=sup_mat_code and id_up <= (512-1-16)
    ) qr
    group by 1,2
)
,qli as (
    select
         curr_season
        ,curr_code
        ,array_agg(id_li order by id_li)||array_fill(0::smallint,array[4]) as mat_arr
        ,array_agg(curr_mat_code order by id_li) as matcode_arr
        ,bit_or(imask) as ibitmask
    from(
        select
             sli_season as curr_season
            ,sli_sa_code as curr_code
            ,sli_mat_code as curr_mat_code
            ,sli_clr_code as curr_clr_code
            ,id_li
            ,coalesce(id_li,-1) as imask
        from only sa_lining
        left join lining_target on li_mat_code=sli_mat_code and id_li <= (128-1-8)
    ) qr
    group by 1,2
)
,qin as (
select
     curr_season
    ,curr_code
    ,array_agg(id_in order by id_in)||array_fill(0::smallint,array[4]) as mat_arr
    ,array_agg(curr_mat_code order by id_in) as matcode_arr
    ,bit_or(imask) as ibitmask
    from(
        select
             sin_season as curr_season
            ,sin_sa_code as curr_code
            ,sin_mat_code as curr_mat_code
            ,sin_clr_code as curr_clr_code
            ,id_in
            ,coalesce(id_in,-1) as imask
        from only sa_insole
        left join insole_target on in_mat_code=sin_mat_code and id_in <= (128-1-8)
    ) qr
    group by 1,2
)
,qou as (
    select
         curr_season
        ,curr_code
        ,array_agg(id_ou order by id_ou)||array_fill(0::smallint,array[6]) as mat_arr
        ,array_agg(curr_mat_code order by id_ou) as matcode_arr
        ,bit_or(imask) as ibitmask
    from(
        select
             sou_season as curr_season
            ,sou_sa_code as curr_code
            ,sou_mat_code as curr_mat_code
            ,sou_clr_code as curr_clr_code
            ,id_ou
            ,coalesce(id_ou,-1) as imask
        from only sa_outsole
        left join outsole_target on ou_mat_code=sou_mat_code and id_ou <= (32-1-2)
    ) qr
    group by 1,2
)
,qupd as (
    select * from (
        select
             qup.curr_season
            ,qup.curr_code
            ,qup.ibitmask|qin.ibitmask|qli.ibitmask|qou.ibitmask as ibitmask
            -- the calculations of new_mat_x are simplified here
            -- in the production version they are a more complex combination of bit masks, bit shifts and bit or of different elements of the arrays
            ,(qup.mat_arr[1]|qli.mat_arr[1]|qin.mat_arr[1]|qou.mat_arr[1])::bigint as new_mat_1
            
            ,(qup.mat_arr[2]|qli.mat_arr[2]|qin.mat_arr[2]|qou.mat_arr[2])::bigint as new_mat_2
            
            ,(qup.mat_arr[3]|qli.mat_arr[3]|qin.mat_arr[3]|qou.mat_arr[3])::bigint as new_mat_3
        
        from qup
        left join qli on (qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and qli.ibitmask>0 and cardinality(qli.mat_arr) <=8)
        left join qin on (qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and qin.ibitmask>0 and cardinality(qin.mat_arr) <=8)
        left join qou on (qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and qou.ibitmask>0 and cardinality(qou.mat_arr) <=11)
        where qup.ibitmask>0 and cardinality(qup.mat_arr) <=21
    )qj
    where ibitmask is not null
)
,qupda as (
select
 qup.curr_season
,qup.curr_code
,repeat('0',64)||
repeat('11',coalesce(cardinality(qou.matcode_arr),0))||repeat('10',coalesce(cardinality(qin.matcode_arr),0))||
repeat('01',coalesce(cardinality(qou.matcode_arr),0))||repeat('00',coalesce(cardinality(qup.matcode_arr),0))||
'00' as curr_mattype_bitmask
,qup.matcode_arr||qli.matcode_arr||qin.matcode_arr||qou.matcode_arr as curr_matcode_arr
from qup
left join qli on qli.curr_season=qup.curr_season and qli.curr_code=qup.curr_code and (qli.ibitmask<0 or cardinality(qli.mat_arr) >8)
left join qin on qin.curr_season=qup.curr_season and qin.curr_code=qup.curr_code and (qin.ibitmask<0 or cardinality(qin.mat_arr) >8)
left join qou on qou.curr_season=qup.curr_season and qou.curr_code=qup.curr_code and (qou.ibitmask<0 or cardinality(qou.mat_arr) >11)
where qup.ibitmask<0 or cardinality(qup.mat_arr) >21
)
select
 curr_season
,curr_code
,new_mat_1
,new_mat_2
,new_mat_3
,NULL::bigint as new_mattype_bitmask
,NULL as new_mat_codes
from qupd
union all
select
 curr_season
,curr_code
,NULL::bigint as new_mat_1
,NULL::bigint as new_mat_2
,NULL::bigint as new_mat_3
,substr(curr_mattype_bitmask,length(curr_mattype_bitmask)-63)::bit(64)::bigint as new_mattype_bitmask
,curr_matcode_arr as new_mat_codes
from qupda
;