Início / dba / Perguntas / 287027
Accepted
PaxPrz
Asked: 2021-03-15 23:06:30 +0800 CST

Desaninhar ids de arrays com base na prioridade sem duplicação

  • 772

Aqui está o esquema de amostra para meu banco de dados:

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

NOTA: Esses números são na verdade UUIDs no meu banco de dados e nenhum número será repetido na mesma linha.

Eu quero desaninhar a categoria de productive, unproductivee de neutraltal forma que:

  • Nenhum número de 1 a 7 é repetido e
  • Se o número já foi capturado por High Priority não será capturado por Medium e o mesmo vale para Low .

Saída esperada:

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
postgresql greatest-n-per-group

2 respostas

  1. Best Answer

    Isso pode ser definido como o problema de n por grupo disfarçado. Assim DISTINCT ONé meu recurso principal após desaninhar e antes de agregar de volta:

    Para produzir uma linha como na tabela original:

    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;

    Ou com resultado pivotado:

    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<>fique aqui

    Leitura adicional:

    Você não mencionou desempenho nesta questão. Considere a resposta à sua pergunta anterior para isso:

    • 2

  2. Eu resolvi isso relacionalmente desaninhando e atribuindo os pesos conforme sugerido por @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;

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

    Provavelmente pode ser feito de forma mais eficiente.

    • 1

relate perguntas