PostgreSQL数据转换：实体属性表行转列缺失属性补NULL方案

推荐方案：单次扫描聚合转置 #

这是百万级数据下性能最优的实现方式，仅需扫描一次表即可完成行转列，缺失属性自动补NULL，语法兼容PostgreSQL/MySQL等主流数据库。

前置优化（必做） #

创建联合覆盖索引，避免回表查询，性能可提升10倍以上：

-- 索引顺序按k分组、p匹配、v取值的查询逻辑设计
CREATE INDEX idx_k_p_v ON your_table(k, p, v);

查询语句 #

以提取a、b、c三个属性为例：

SELECT 
    k,
    MAX(CASE WHEN p = 'a' THEN v END) AS a,
    MAX(CASE WHEN p = 'b' THEN v END) AS b,
    MAX(CASE WHEN p = 'c' THEN v END) AS c
FROM your_table
-- 不需要过滤可删除下方WHERE，会返回所有实体k的结果
WHERE p IN ('a', 'b', 'c')
GROUP BY k
ORDER BY k;

如果同一实体k下同一属性p存在多个值，可将MAX替换为字符串拼接函数：

• PostgreSQL 用 STRING_AGG(CASE WHEN p='a' THEN v END, ',')
• MySQL 用 GROUP_CONCAT(CASE WHEN p='a' THEN v END SEPARATOR ',')

可选方案：全实体LEFT JOIN模式 #

如果你需要保留JOIN的写法适配自定义逻辑，可以先获取全量实体k集合，再左连各个属性：

WITH all_entities AS (
    -- 有单独的实体主表直接查主表，性能更高
    SELECT DISTINCT k FROM your_table
)
SELECT 
    ae.k,
    a.v AS a,
    b.v AS b,
    c.v AS c
FROM all_entities ae
LEFT JOIN your_table a ON ae.k = a.k AND a.p = 'a'
LEFT JOIN your_table b ON ae.k = b.k AND b.p = 'b'
LEFT JOIN your_table c ON ae.k = c.k AND c.p = 'c'
GROUP BY ae.k, a.v, b.v, c.v
ORDER BY ae.k;

该方案属性数量较多时JOIN成本会上升，性能低于聚合方案。 #

高频查询优化建议 #

如果该查询是高频使用场景，可提前固化结果：

• PostgreSQL 可创建物化视图，定时刷新，查询直接读物化视图即可
• 其他数据库可创建定时任务，将结果同步到中间宽表，查询直接读宽表，性能可达毫秒级

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