Postgres：如何在满足条件的情况下计算数组列中的不同元素数量

Question

我有一张类似如下的表格：

ID     array1           array2
7333 | {615593}        | {NULL}
7333 | {2013682}       | {NULL}
7333 | {26573,1508291} | {NULL}
7333 | {1457957}       | {NULL}
7333 | {NULL}          | {1063105}
7333 | {NULL}          | {107978,408431}

我希望能够查询每个edge_id的array1和array2中所有不重复的项。例如：

ID   array1_distinct    array2_distinct
7333 5                  3

我曾尝试过以下类似的方法：

SELECT 
  id, 
  array1_count 
FROM (
  SELECT id,
  COUNT(DISTINCT((CASE WHEN array1 is not null THEN (select unnest(array1)b) END)) as array1_count
  FROM mytable
  GROUP BY id
) totals
limit 1

在使用unnest时，当尝试计算数组中的元素数量时，我经常会遇到"more than one row returned by a subquery used as an expression"和"set-valued function called in context that cannot accept a set"等错误。

- user3302967

Answer 1

select id, count(distinct a1) as a1, count(distinct a2) as a2
from (
    select id, unnest(array1) as a1, unnest(array2) as a2
    from t
) s
group by id

- Clodoaldo Neto

---

太棒了 - 不确定为什么我之前没有做过。谢谢。 - user3302967