GreenPlum之按月份或季度实现行转列

来源：互联网发布：ubuntu 笔记本编辑：程序博客网时间：2024/06/07 13:07

1.参考GreenPlum之生成月份字典表

GreenPlum之生成月份字典表

2.根据Case When语句及GreenPlum中string_agg聚合函数拼凑对应月份或季度周期字段，以下是核心实现sql，读者可根据需求自行修改：

--字段按照月份行转列

if position('yyyy-mm' in in_datecolum)>0 then       out_strsql := '  select   '||case in_matrix   when 1 then 'string_agg('',cast(count(distinct case when 日期字段=''''''||monthid||'''''' then 表名.字段名1 end) as numeric(16,0)) as "''||monthid||''" '','''' order by monthid)'   when 2 then 'string_agg('',sum(case when 日期字段=''''''||monthid||'''''' then 表名.字段名2 else 0 end) as "''||monthid||''" '','''' order by monthid)'   when 3 then 'string_agg('',sum(case when 日期字段=''''''||monthid||'''''' then 表名.字段名3 else 0 end) as "''||monthid||''" '','''' order by monthid)'   when 4 then 'string_agg('',cast(count(distinct case when 日期字段=''''''||monthid||'''''' then 表名.字段名4 end) as numeric(16,0)) as "''||monthid||''" '','''' order by monthid)'     else   'string_agg('',sum(case when 日期字段=''''''||monthid||'''''' then 表名.字段名3 else 0 end) as "''||monthid||''" '','''' order by monthid)'  end   ||'from (  select monthid from monthmapping where onday between '''||in_startdate||''' and '''||in_overdate||''' group by monthid) tab  ';  execute out_strsql into aggstr;

--字段按照季度实现行转列  else   out_strsql := '  select   '||case in_matrix   when 1 then 'string_agg('',cast(count(distinct case when 日期字段=''''''||monthid||'''''' then 表名.字段名1 end) as numeric(16,0)) as "''||monthid||''" '','''' order by monthid)'   when 2 then 'string_agg('',sum(case when 日期字段=''''''||monthid||'''''' then 表名.字段名2 else 0 end) as "''||monthid||''" '','''' order by monthid)'   when 3 then 'string_agg('',sum(case when 日期字段=''''''||monthid||'''''' then 表名.字段名3 else 0 end) as "''||monthid||''" '','''' order by monthid)'   when 4 then 'string_agg('',cast(count(distinct case when 日期字段=''''''||monthid||'''''' then 表名.字段名4 end) as numeric(16,0)) as "''||monthid||''" '','''' order by monthid)'     else   'string_agg('',sum(case when 日期字段=''''''||monthid||'''''' then 表名.字段名3 else 0 end) as "''||monthid||''" '','''' order by monthid)'  end   ||'from (  select substring(onday,1,4)||''-''||EXTRACT(QUARTER from onday)||''-01'' as monthid from monthmapping where onday between '''||in_startdate||''' and '''||in_overdate||'''   group by substring(onday,1,4)||''-''||EXTRACT(QUARTER from onday)||''-01'') tab  ';  execute out_strsql into aggstr;  end if;  raise notice 'aggstr : %',aggstr;

阅读全文

1 0