postgresql行转列
使用场景:报表统计
复杂场景:涉及多维度统计报表,当需要某一维度动态展示在列,此时将该维度行记录转为列记录
结果:原SQL查询的结果集,记录数会产生变化
to do:分组+汇聚+列字段处理
具体方法
- 根据固定行展示维度进行分组(
group by
) - 对行转列维度的数据进行汇聚
- int类型求和:
sum()
; - varchar类型,进行拼接:
array_to_string( ARRAY_AGG ( column_value ), '' )
或者string_agg()
- int类型求和:
- 列字段处理
- 对于已知固定的列名称,可用
case when
语句,或者使用先string_agg()
,再split_part()
- 动态的列名称,可借助程序将字段列名称作为传参进一步处理
- 对于已知固定的列名称,可用
SQL示例模板
with res as (
select
dimension1,
dimension2 ,
string_agg( column_value1 || column_value1 , '') as statistics_value
from
(
select
dimension1,
dimension2,
column_value1,
round( SUM ( coalesce ( column_value1, 0 ) ), 0 ) as num
from
t_1
left join t2 on
t1.column_out_key = t2.column_key
left join t3 on
t1.column_out_key = t3.column_key
group by
dimension1,
dimension2
) aaa
group by
dimension1,
dimension2
order by
aaa.dimension1 desc
)
select
dimension1,
string_agg
(case
when dimension2 = 'dimension2Value1' then statistics_value
else ''
end,
'') as "dimension2Value1",
string_agg
(case
when dimension2 = 'dimension2Value2' then statistics_value
else ''
end,
'') as "dimension2Value2"
from
res
group by
dimension1