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;