postgreSQL 实现group_concat

不念不忘少年蓝@ 2022-11-11 05:16 394阅读 0赞

### 1.返回json ###

with tem as(
     SELECT 
      extract(year from bucket::date)as year,
      extract(quarter from bucket::date) as quarter ,
      extract(month from bucket::date) as month,
      extract(week from bucket::date) as week,
      extract(day from bucket::date) as day ,
      * 
     FROM ludp_pdp 
     WHERE product_name='DVX' and version='20160118' 
    ) 
    select 
     geo,subgeo,country,region,productName,product,siteId,version ,seller,color,mram,mrom,xcvr,
     json_agg(json_build_object('quarters',quarters,'value',pdpFinal)) as res
    from (
     SELECT 
      year||'-'||quarter as quarters,
      geo,subgeo,country,region,product_name as productName,product,site_id as siteId ,
      version,seller,color,mram,mrom,xcvr ,
      sum(tem.pdp_final) as pdpFinal 
     FROM tem 
     GROUP BY 
     geo,subgeo,country,region,product_name,product,site_id,version ,seller,color,mram,mrom,xcvr,year,quarter
      order by year,quarter
    ) t group by 
    geo,subgeo,country,region,productName,product,siteId,version ,seller,color,mram,mrom,xcvr

### 2. 返回字符串拼接 ###

with tem as(
     SELECT 
      extract(year from bucket::date)as year,
      extract(quarter from bucket::date) as quarter ,
      extract(month from bucket::date) as month,
      extract(week from bucket::date) as week,
      extract(day from bucket::date) as day ,
      * 
     FROM ludp_pdp 
     WHERE product_name='DVX' and version='20160118' 
    ) 
    select 
     geo,subgeo,country,region,productName,product,siteId,version ,seller,color,mram,mrom,xcvr,
     string_agg(res, ',') res
    from(
     SELECT 
      year||'-'||quarter||':'||sum(tem.pdp_final) as res,
      geo,subgeo,country,region,product_name as productName,product,site_id as siteId ,
      version,seller,color,mram,mrom,xcvr
     FROM tem 
     GROUP BY 
     geo,subgeo,country,region,product_name,product,site_id,version ,seller,color,mram,mrom,xcvr,year,quarter
     order by year,quarter
    ) t GROUP BY geo,subgeo,country,region,productName,product,siteId,version ,seller,color,mram,mrom,xcvr