我们如何找到postgres中数组类型元素的出现计数到位置映射？
例如：
["A", "B", "C", "D"]
["A", "B", "C", "D"]
["A", "B", "D", "C"]
["A", "D", "C", "B"]
应该屈服
| 元素 | 位置 | 发生次数 |
|---|---|---|
| 一种 | 1个 | 4 |
| 一种 | 2 | 0 |
| 一种 | 3 | 0 |
| 一种 | 4 | 0 |
| 乙 | 1个 | 0 |
| 乙 | 2 | 3 |
| 乙 | 3 | 0 |
| 乙 | 4 | 1个 |
| C | 1个 | 0 |
| C | 2 | 0 |
| C | 3 | 3 |
| C | 4 | 1个 |
| d | 1个 | 0 |
| d | 2 | 1个 |
| d | 3 | 1个 |
| d | 4 | 2 |
我知道array_position可以获得该职位（基于1），但是我对如何实现自己的目标感到困惑？
SQL查询：
create table test (id integer, priorities varchar(100)[]);
insert into test (id, priorities) values (1, '{"A","B","C","D"}');
insert into test (id, priorities) values (2, '{"A","B","C","D"}');
insert into test (id, priorities) values (3, '{"A","D","C","B"}');
insert into test (id, priorities) values (4, '{"A","C","B","D"}');
编辑＃1：我想我可以以某种方式组合unnest with ordinality并group by实现这一目标。
棘手的部分是获取零记录。
为此，创建一个提供所有可能组合的表可能会有所帮助：
SELECT
*
FROM (
SELECT
generate_series(1, max(cardinality(myarray))) idx -- 1
FROM t
) gs
CROSS JOIN ( -- 3
SELECT DISTINCT -- 2
unnest(myarray) val
FROM t
) values
1到最大数组长度的序列。这样可以确保即使长度不同也可以组合CROSS JOIN 他们。现在您有了参考表。在下一步中，您可以使用unnest() WITH ORDINALITY自己的解决方案中提供的。该结果可以LEFT JOIN在参考表上找到。因此，您正在使用预期的非零记录和许多NULL记录创建一个表：
SELECT
values.val, gs.idx,
COUNT(*) FILTER (WHERE s.index IS NOT NULL) -- 1
FROM (
SELECT
generate_series(1, max(cardinality(myarray))) idx
FROM t
) gs
CROSS JOIN (
SELECT DISTINCT
unnest(myarray) val
FROM t
) values
LEFT JOIN (
SELECT
*
FROM t,
unnest(myarray) WITH ORDINALITY as a(val, index)
) s ON s.index = gs.idx AND s.val = values.val
GROUP BY values.val, gs.idx
ORDER BY values.val, gs.idx
最后，您可以将整个jpoin分组。使用FILTERon子句COUNT()可确保仅对那些记录进行计数，而不对进行计数NULL，这意味着：仅对真正出现在数据中的那些组合进行计数。对于NULL记录，过滤器将返回0。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句