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
AND type = 'A' LIMIT 1
) AS type_a_first_link,
-- Grab the first B
(SELECT link
FROM demo
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
AND type = 'A'
ORDER BY link
OFFSET 1) ||
ARRAY(
SELECT link
FROM demo
AND type = 'B'
ORDER BY link
OFFSET 1) ||
ARRAY(
SELECT link
FROM demo
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}
Attachment:
demonstration-two-group-allocation.sql
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
