1. 将同组数据聚合为一个对象
postgreSQL
select geo,seller,product_name
, json_agg(json_build_object('date',bucket,'value',pdp_final))
from ludp_pdp
WHERE version = '20221114' and geo in ( 'PRC','AP','EMEA','NA','LA' ) and country='AE' and seller= 'AE_Others' and product ='PAU20000AE' and bucket >= '2022-10-01' and bucket <= '2023-03-31' group by geo,seller,product_name order by geo,seller,product_name
mysql
select geo,seller,product_name
, concat("[", GROUP_CONCAT(concat( "{\"date\":\"", bucket, "\",\"value\":", pdp_final, "}" )), "]") as aggComplex
from catalog_pdp
WHERE version = '20221114' and geo in ( 'PRC','AP','EMEA','NA','LA' ) and country='AE' and seller= 'AE_Others' and product ='PAU20000AE' and bucket >= '2022-10-01' and bucket <= '2023-03-31' group by geo,seller,product_name order by geo,seller,product_name
解析json数组中的 json元素
with a as(
select *
from catalog_activation_projection_calculation
where "version" ='2023-01-30'
and geo = 'LA'
and product_name ='ARUBA'
)
, b as(
select jsonb_array_elements(complex)->>'date' as date
,jsonb_array_elements(complex)->>'value' as value
from a
)
-- select sum(value :: decimal(12,2)) from b where date>='2023-04-03' and date<='2023-12-25'
select c.start_date, sum(value :: decimal(12,2))
from b
left join sc_mbg_calendar_cml c
on b.date=c.start_date
where date>='2023-04-03' and date<='2023-12-25'
group by c.start_date
order by c.start_date