4

Here is the sample schema for my database:

Priority  | Productive | UnProductive | Neutral |
-------------------------------------------------
  High    |   [1, 2]   |      []      |  [4, 5] |
  Medium  |   [3, 4]   |    [5, 7]    |   [2]   |
  Low     |     [1]    |    [2, 6]    |    []   |

NOTE: These numbers are actually UUIDs in my database and no numbers will be repeated in the same row.

I want to unnest the category from productive, unproductive and neutral such that:

  • Not any from 1 to 7 numbers are repeated and
  • If the number is already captured by High Priority it will not be captured by Medium and same goes for Low.

Expected output:

Productive: [1, 2, 3]  # no 4 because it have been captured by neutral High priority
UnProductive: [7, 6]  # no 5 and 2 because it have been captured before
Neutral: [4, 5]  # no 2 because it is caputed before
6
  • Unnest all. Add total relative weight (summarize per separate value, High=6, Medium=3, Low=0, Productive=2, UnProductive=1, Neutral=0). Select highest weight per value. Compact, calculate category.
    – Akina
    Mar 15, 2021 at 7:26
  • could you please elaborate or add it as answer @Akina
    – PaxPrz
    Mar 15, 2021 at 7:28
  • 2
    Replace (or add) table-formatted sample data with CREATE TABLE + INSERT INTO scripts. Add expected output formatted as an output table.
    – Akina
    Mar 15, 2021 at 7:32
  • Currently I am trying to do similar like you've just said implementing a temporary table in a function. The issue was that the temporary table was not recognized during the later insert commands. @Akina
    – PaxPrz
    Mar 15, 2021 at 7:33
  • 1
    You can use dbfiddle.uk to show us how you input the data into tables - this will also give us the table DDL - please also add the same information to the question. This looks to be quite a complex and interesting SQL/programming problem - it may require a RECURSIVE CTE unless you are certain that you will only ever have High, Medium and Low priorities and Productive, Unproductive and Neutral states.
    – Vérace
    Mar 15, 2021 at 19:14

2 Answers 2

2

This can be cast as problem in disguise. So DISTINCT ON is my core feature after unnesting and before aggregating back:

To produce one row like in the original table:

WITH cte AS (
   SELECT *, CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END AS prio
   FROM   tbl
   )
SELECT array_agg(id ORDER BY prio) FILTER (WHERE state = 'p') AS productive  -- ORDER BY prio?
     , array_agg(id ORDER BY prio) FILTER (WHERE state = 'u') AS unproductive
     , array_agg(id ORDER BY prio) FILTER (WHERE state = 'n') AS neutral
FROM  (
   SELECT DISTINCT ON (id) *
   FROM (
                SELECT unnest(neutral) AS id, prio, 'n' AS state FROM cte
      UNION ALL SELECT unnest(productive)   , prio, 'p'          FROM cte
      UNION ALL SELECT unnest(unproductive) , prio, 'u'          FROM cte
      ) sub1
   ORDER  BY id, prio, state
   ) sub2;

Or with pivoted result:

WITH cte AS (
   SELECT *, CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END AS prio
   FROM   tbl
   )
SELECT state, array_agg(id ORDER BY prio)  -- ORDER BY prio?
FROM  (
   SELECT DISTINCT ON (id) *
   FROM (
                SELECT unnest(neutral) AS id, prio, 'neutral' AS state FROM cte
      UNION ALL SELECT unnest(productive)   , prio, 'productive'       FROM cte
      UNION ALL SELECT unnest(unproductive) , prio, 'unproductive'     FROM cte
      ) sub1
   ORDER  BY id, prio, state
   ) sub2
GROUP  BY 1;

db<>fiddle here

Further reading:

You did not mention performance in this question. Consider the answer to your previous question for this:

3
  • Brilliant!! <3 Thank you
    – PaxPrz
    Mar 16, 2021 at 2:33
  • If i have All = {1,2,..10} and I want to have a new row/column: Rest = {8,9,10}. (remaning unseen numbers). How can I add them here?
    – PaxPrz
    Mar 16, 2021 at 2:40
  • @Pax: Sorry, I don't understand. Maybe start a new question with all relevant details? Mar 17, 2021 at 5:32
1

I've solved this relationaly by unnesting and assigning the weights as suggested by @Akina.

with weighted as (
  select
    case priority
      when 'High' then 6
      when 'Medium' then 3
      when 'Low' then 0
    end as priority,
    productive,
    unproductive,
    neutral
  from
    data
), unnested as (
  select
    unnest(productive) as id, priority, 2 as category 
  from
    weighted
  union all
  select
    unnest(unproductive) as id, priority, 1 as category 
  from
    weighted
  union all
  select
    unnest(neutral) as id, priority, 0 as category
  from
    weighted
) 
select
  case category
    when 0 then 'Neutral'
    when 1 then 'UnProductive'
    when 2 then 'Productive'
  end,
  array_agg(id)
from unnested x
where not exists (
  select null
  from unnested
  where id = x.id
  and priority + category > x.priority + x.category
)
group by category, 1
order by category desc;

See https://dbfiddle.uk/?rdbms=postgres_13&fiddle=8a54e1f8018c49146faba3e446f7b694

It can probably be done more efficiently.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.