我有一个类似于的SQL表
ID | COL1 | COL2 | SIM
=======================================
1 | A | B | 5
2 | Z | A | 3
3 | C | B | 3.5
4 | B | Z | 0.5
5 | C | Z | 1.1
我正在尝试创建一个查询,对于COL1
ORCOL2
中的每个唯一值,它将聚合相应的COL
列中的值以及SIM
值。期望输出:
ID | AGG_KEY | AGG_IDS | AGG_SIM
========================================================
1 | A | [B, Z] | [5, 3]
2 | B | [A, C, Z] | [5, 3.5, 0.5]
3 | C | [B, Z] | [3.5, 1.1]
4 | Z | [A, B, C] | [3, 0.5, 1.1]
嗯。如果我理解正确,你可以"取消透视"并聚合:
select v.cola, array_agg(v.colb order by t.sim desc), array_agg(t.sim order by t.sim desc)
from t cross join lateral
(values (col1, col2), (col2, col1)) v(cola, colb)
group by v.cola;
这是一个数据库<>不停摆弄