|
>?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 |
