postgresql - 在 PostgreSQL 中使用 null 值取消透视

Question

我正在将 Unpivot 从 Oracle 转换为 postgres。我遇到以下问题：在 Oracle Unpivot 中自动删除 null 值，但 postgres 失败。如何使 Postgres 与 Oracle 相同 你能帮帮我吗？

甲骨文：

with detail as (                
    select          
        'A1' as a,      
        'A2' as b,      
        'A3' as c,      
        null as d       
    from dual           
)               
select * from detail unpivot (              
    agg_value for (agg_name) in (           
        a as 'Max Concurrent Users',        
        b as 'Max Concurrent User Time',        
        c as 'Min Concurrent Users',        
        d as 'Min Concurrent User Time'     
    )           
);

结果甲骨文：

Postgres：

WITH detail AS (                        
    select                  
        'A1' as a,              
        'A2' as b,              
        'A3' as c,              
        null as d               
)                       
SELECT * FROM (                     
    SELECT 
        unnest(array['Max Concurrent Users', 'Max Concurrent User Time', 'Min Concurrent Users', 'Min Concurrent User Time']) AS agg_name,
        unnest(array[a, b, c, d]) AS agg_value
    FROM
        detail
) foo;

结果postgres：

标签： postgresql

Answer 1

我更喜欢针对 values 子句的横向连接来在 Postgres 中实现 unpivot：

WITH detail (a,b,c,d) AS (                        
  values ('A1', 'A2', 'A3', null)
)                       
SELECT u.*
FROM detail d
  cross join lateral (
    values 
        (a, 'Max Concurrent Users'),
        (b, 'Max Concurrent User Time'),
        (c, 'Min Concurrent Users'),
        (d, 'Min Concurrent User Time')
  ) as u (col, col_name)
where u.col is not null;  

如果表中的列名是“可读的”，您可以使用 JSON 来简化：

select u.*
from detail d
  cross join jsonb_each_text(to_jsonb(d)) as u(col_name, col);