postgreSQL 实现group_concat

最新推荐文章于 2024-09-09 14:53:10 发布

GoodStudyAndDayDayUp

最新推荐文章于 2024-09-09 14:53:10 发布

阅读量389

点赞数

分类专栏： postgresql 文章标签： postgreSQL 实现group_concat

本文链接：https://blog.csdn.net/leinminna/article/details/115003598

版权

postgresql 专栏收录该内容

26 篇文章 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