我在 Postgres 中有一個帶有 hstore 列的表，例如：
"surface => concrete, ramp => yes, incline => up, highway => footway"
"surface => asphalt, lit => no, source => survey, incline => 12.6%, highway => footway"
"bicycle => yes, surface => asphalt, highway => footway, segregated => no"
現在我想分析它們之間的關鍵。因此，我想知道整個資料集的鍵成對組合出現的頻率。
結果應如下所示：
| Key 1 | Key 2 | Count |
|---------------------|------------------|------------------|
| surface | source | 1 |
| surface | highway | 3 |
| surface | incline | 2 |
| highway | bicycle | 1 |
.....
uj5u.com熱心網友回復：
使用該函式akeys()將hstore列的所有鍵作為陣列獲取，生成所有不同的陣列元素對，generate_subscripts()并按對對結果進行分組：
select akeys[i] as key1, akeys[j] as key2, count(*)
from my_table
cross join akeys(hstore_col)
cross join generate_subscripts(akeys, 1) as i
cross join generate_subscripts(akeys, 1) as j
where akeys[i] > akeys[j]
group by 1, 2
order by 1 desc, 2 desc
在db<>fiddle中測驗它。
uj5u.com熱心網友回復：
使用 hstoreeach()擴展它，然后加入和聚合：
with expand as (
select id, k, v
from htab
cross join lateral each(hcol) as c(k, v)
), pairings as (
select t1.id, t1.k as key1, t2.k as key2
from expand t1
join expand t2
on t2.id = t1.id
and t2.k < t1.k
)
select key1, key2, count(*)
from pairings
group by key1, key2
order by key1 desc, key2 desc;
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/514691.html