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

朱雀 2021-03-25 14:30 640阅读 0赞

根据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;