如何在PostgreSQL的hstore列中统计键的两两组合出现次数

假设你的表名为osm_data，hstore类型的列名为tags（请根据实际表名和列名替换），可以通过以下SQL语句实现需求：

SELECT
    t1.key AS "Key 1",
    t2.key AS "Key 2",
    COUNT(DISTINCT od.id) AS "Count"
FROM osm_data od
JOIN LATERAL each(od.tags) t1 ON true
JOIN LATERAL each(od.tags) t2 ON true
WHERE t1.key < t2.key
GROUP BY t1.key, t2.key
ORDER BY "Count" DESC, "Key 1", "Key 2";

语句说明： #

1. 拆分Hstore键：使用LATERAL each(od.tags)将每一行的hstore列拆分为多行键值对，确保能单独处理每个键。
2. 避免重复组合：通过t1.key < t2.key的条件，保证每个键对只统计一次（比如surface和highway不会重复出现highway和surface的条目）。
3. 准确计数：COUNT(DISTINCT od.id)确保同一数据行中的所有键组合只被计数一次，避免因自连接导致的重复统计。如果你的表没有主键id，可以用PostgreSQL内置的行标识符ctid替代，即COUNT(DISTINCT od.ctid)。

示例结果匹配： #

针对你提供的示例数据，执行该语句后会得到与预期一致的结果，比如：

内容的提问来源于stack exchange，提问作者denali11