Re: Any thoughts on a better approach to this query? - Mailing list pgsql-general

>?Formatted query attached in addition to placing it inline.  The commentary is inline with the query.  Basically I've already solved this problem but was wondering if someone has a different perspective; or simply observations.
>
>TIA,
>
>David J.
>
>/*
>For a given id there are multiple linked values of differing types.
>Types "A" and "B" are important and, if present, should be explicitly assigned.
>It is possible that more than one link is associated with a given type.
>If either A or B is lacking an explicit value it is assigned a value
>from:
>1. any extra As that are present
>2. any extra Bs that are present
>3. any extra non-A/B values that are present
>
>The final result contains values for A and B and
>and array of values for whatever links went unused.
>*/
>WITH demo AS (
>    -- A sample record where B needs to be assigned and ends
>    -- up using the excess A
>    SELECT * FROM (
>    VALUES (1,1,'A'), (1,2,'A'), (1,3,'C'), (1,4,'D')
>    ) vals (id, link, type)
>)
>, link_allocation_1 AS (
>    SELECT id,
>
>    -- Grab the first A
>    (SELECT link
>       FROM demo
>      WHERE demo.id = master.id
>        AND type = 'A' LIMIT 1
>    )                                             AS type_a_first_link,
>
>    -- Grab the first B
>    (SELECT link
>       FROM demo
>      WHERE demo.id = master.id
>        AND type = 'B' LIMIT 1
>    )                                             AS type_b_first_link,
>
>    -- Any additional As and Bs are placed into an array
>    -- and appended to an array constructed from all of the non-A/B
>    ARRAY(
>        SELECT link
>          FROM demo
>         WHERE demo.id = master.id
>           AND type = 'A'
>      ORDER BY link
>        OFFSET 1) ||
>    ARRAY(
>        SELECT link
>          FROM demo
>         WHERE demo.id = master.id
>           AND type = 'B'
>      ORDER BY link
>        OFFSET 1) ||
>    ARRAY(
>        SELECT link
>          FROM demo
>         WHERE demo.id = master.id
>           AND type NOT  IN ('A','B')
>      ORDER BY link)                              AS unassigned_links
>    FROM (SELECT DISTINCT id FROM demo) master
>)
>, allocate_unassigned_links AS (
>    SELECT *,
>    -- Determine how many allocations from the "extra" array are required
>    -- so that we can trim slice them out of the final result
>        CASE WHEN type_a_first_link IS NULL
>             THEN 1
>             ELSE 0 END +
>        CASE WHEN type_b_first_link IS NULL
>             THEN 1
>             ELSE 0
>        END                                   AS reassign_count,
>    -- A always gets the first extra if needed
>        CASE WHEN type_a_first_link IS NULL
>            THEN unassigned_links[1]
>            ELSE type_a_first_link
>        END                                   AS actual_a_link,
>    -- B gets the first extra unless A took it in which case it gets the second one
>        CASE WHEN type_b_first_link IS NULL THEN
>             CASE WHEN type_a_first_link IS NOT NULL
>                  THEN unassigned_links[1]
>                  ELSE unassigned_links[2]
>             END
>             ELSE type_b_first_link
>        END                                          AS actual_b_link
>    FROM link_allocation_1
>)
>SELECT id,
>-- For A and B flag is the value was pulled from the extras
>       type_a_first_link IS NULL AS a_link_is_missing,
>       actual_a_link,
>       type_b_first_link IS NULL AS b_link_is_missing,
>       actual_b_link,
>-- Now slice off the first portion of the extras array based upon the assignment count
>       unassigned_links[1+reassign_count:array_length(unassigned_links,1)] AS final_unassigned_links,
>-- output the unsliced array for visual comparison
>       unassigned_links AS pre_allocation_unassigned_links
>  FROM allocate_unassigned_links
>
>
>Output =>
>id
>a_link_is_missing actual_a_link
>b_link_is_missing actual_b_link
>final_unassigned_links pre_allocation_unassigned_links
>1
>False 1
>True 2
>{3,4} {2,3,4}
>

I would do it this way:


-- helper to remove nulls from arrays
-- could be merged into a array_agg_notnulls aggregate function
create function array_not_nulls (a anyarray) returns anyarray as $$
  SELECT array_agg (u) from (select unnest($1) u)foo where u is not null;
$$ language sql immutable strict;

WITH demo AS (
    -- A sample record where B needs to be assigned and ends
    -- up using the excess A
    SELECT * FROM (
    VALUES
    (1,1,'A'), (1,2,'A'), (1,3,'C'), (1,4,'D'),
    (2,1,'e'), (2,2,'B'), (2,3,'C'), (2,4,'D'),
    (3,1,'A'), (3,2,'y'), (3,3,'z'), (3,4,'B'),
    (4,1,'B'), (4,2,'B'), (4,3,'z'), (4,4,'z'), (4,5,'B'),
    (5,1,'x'), (5,2,'y'), (5,3,'z'), (5,4,'q')
    ) vals (id, link, type)
),
PREP AS (
   select  
   id,
   bool_or (type='A') as has_a,
   bool_or (type='B') as has_b,
   count(case when type='A' then 1 end ) as a_ct,
   count(case when type='B' then 1 end ) as b_ct,
   array_not_nulls(array_agg(case when type='A' then link end ))as ar_a,
   array_not_nulls(array_agg(case when type='B' then link end ))as ar_b,
   array_not_nulls(array_agg(case when type NOT IN ('A', 'B') then link end)) as ar_others
   from demo
   GROUP BY id
)
 
SELECT
id,
a_ct,
b_ct,
ar_a,
ar_b,
ar_others,
  coalesce (ar_a[1], case when b_ct > 1 then ar_b[2] else ar_others[1] end ) as link_a,
  coalesce (ar_b[1], case when a_ct > 1 then ar_a[2] when a_ct = 0 then  ar_others[2] else ar_others[1] end) as link_b,

-- unused others
  case when a_ct + b_ct >=2 then  ar_others
       else ar_others[3 - (a_ct + b_ct) : array_length(ar_others,1)]
   end
   ||
 -- unused A & B     
   case when has_a AND has_b then  ar_a[2:a_ct] ||  ar_b[2:b_ct]
       when a_ct > 2  then  ar_a[3:a_ct]
       when b_ct > 2  then  ar_b[3:b_ct]
   end as unused
  FROM PREP
  order by id




regards,

Marc Mamin