主页 / dba / 问题 / 287027
Accepted
PaxPrz
Asked: 2021-03-15 23:06:30 +0800 CST

根据优先级从数组中取消嵌套 id，不重复

  • 772

这是我的数据库的示例架构：

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

注意：这些数字实际上是我数据库中的 UUID，在同一行中不会重复任何数字。

我想从 中取消嵌套类别productive，unproductive这样neutral：

  • 从 1 到 7 的数字都不重复，并且
  • 如果该数字已被High Priority 捕获，它将不会被Medium捕获， Low也是如此。

预期输出：

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 个回答

  1. Best Answer

    这可以伪装成问题。DISTINCT ON在取消嵌套之后和聚合回来之前，我的核心功能也是如此：

    要像原始表中那样生成一行：

    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;

    或使用旋转结果：

    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<>在这里摆弄

    进一步阅读：

    你没有在这个问题中提到性能。考虑一下您之前问题的答案：

    • 2

  2. 我已经按照@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;

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

    它可能可以更有效地完成。

    • 1

相关问题