Re: Need optimization in query

Hi Team.

I have a use case to get the result as follows:

1. asin_ymm is never null.

2. If there is more than 1 entry for an asin_ymm with both null and non-null submodelId, I should return rows with non-null submodelId only, otherwise if there is no submodelid present for a asin_ymm, then return that row with null submodelid.

3. Also if submodelid is null , assuming fitment_key would always be null in the table.

4. Using that resultset, If there is more than 1 entry for an (asin_ymm ,SubmodelID)  with both null and non-null fitment_key, I should return rows with non-null fitment_key only, otherwise if there is no fitment_key present for a (asin_ymm,submodelId), then return that row with null fitment_key.

5. Using that resultset, i need to return those rows having maximum values in fitment_key(ie. for eg.(out of these two 'A', 'D','1--3-4' and A', 'D','1-2-3-4-7', i should return row having A', 'D','1-2-3-4-7)

create table fitment_records(asin_ymm varchar(50), SubmodelID varchar(50), fitment_key varchar(50));

insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A', null,null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('B', null,null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key ) values('C', null,null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A', 'D','1--3-4');
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('B', 'E','2-3-4-5');
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('B', 'E', null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A', 'F','2-3');
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A', 'E', null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A', 'D', null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A', 'D','1-2-3-4-7');

output should be:

 asin_ymm | submodelid | fitment_key 
----------+------------+------------- A        | D          | 1-2-3-4-7 A        | E          | null A        | F          | 2-3 B        | E          | 2-3-4-5 C        | null       | null

Currently i have written these queries for this usecase. Can we optimise it further? Considering data is in millions

create temporary view tv1 as (SELECT *
FROM fitment_records fr_1
WHERE fitment_key IS NOT NULL OR
(fitment_key IS NULL AND
NOT EXISTS (SELECT 1 FROM fitment_records fr_2
  WHERE fr_2.asin_ymm = fr_1.asin_ymm AND
        fr_2.SubmodelID = fr_1.SubmodelID and
        fr_2.fitment_key IS NOT NULL)));
        
create temporary view tv2 as (select *
FROM tv1 fr_1
WHERE SubmodelID IS NOT NULL OR
(SubmodelID IS NULL AND
NOT EXISTS (SELECT 1 FROM fitment_records fr_2
  WHERE fr_2.asin_ymm = fr_1.asin_ymm AND
        fr_2.SubmodelID IS NOT NULL) ));
        
create temporary view fitment_records_with_fitment_key_size as (
    select asin_ymm, SubmodelID, fitment_key, Array_Length(string_to_array(fitment_key, '-'),1) as fitment_key_size
        from tv2
        where SubmodelID is not null
        and fitment_key is not null
        group by asin_ymm, SubmodelID, fitment_key
);

create temporary view fitment_records_with_fitment_key_max_size as (
    select asin_ymm, SubmodelID, max(fitment_key_size) as max_fitment_key_size
    from fitment_records_with_fitment_key_size
    group by asin_ymm, SubmodelID
);

    select * from tv2 
    except
    select f2.*
        from fitment_records_with_fitment_key_size frws, fitment_records_with_fitment_key_max_size frwms, 
            tv2 f2
        where frws.asin_ymm = frwms.asin_ymm 
            AND frws.SubmodelID = frwms.SubmodelID
            AND frws.fitment_key_size < frwms.max_fitment_key_size 
            AND frws.SubmodelID = f2.SubmodelID
            AND frws.asin_ymm = f2.asin_ymm 
            AND frws.fitment_key = f2.fitment_key;

Thanks & Regards