postgresSql/pgsql 函数使用[json[数组]解析、每日累计，字符串拼接、去重、排序 等]

每日累计计算历史数据

每日累计：sum(num) over (order by dt_date)

select id,dt_date,
sum(num) over (order by dt_date) as num  
from (
	select id, dt_date, sum(num) as num
	from mating
	group by id,dt_date
) a 
group by id, dt_date ,num

分区内每日累计：sum(num) over (partition by id order by dt_date)

select id,dt_date,
sum(num) over (partition by id order by dt_date) as num
from (
	select id, dt_date, sum(num) as num
	from mating
	group by id,dt_date
) a 
group by id, dt_date ,num

string_agg

字符串拼接、去重、排序 ：string_agg(distinct rect_all.account_id ,‘,’ order by rect_all.account_id desc )

select
id,dt_date,
string_agg(distinct account_id ,',' order by account_id desc ) as admin_id
from rect_all
group by
id,dt_date

json数组解析

数据样例：
event_context:{“piglets”: [{“sex”: “0”, “earno”: “1-1”, “gbCode”: “LB101”, “weight”: 1.0, “reserved”: “1”, “srcBreedNo”: “AAAA1”, “breedingCode”: “L3”, “offspringCode”: “L-1”, “yearMonthFlag”: “ff”}], “locationId”: “22”}

1、解析字段 locationId：
event.event_content::jsonb->>‘locationId’ as location_id

2、解析字段breedingCode；使用解析函数json_array_elements_text： json_array_elements_text((event.event_content::json->>‘piglets’)::json)::json->>‘breedingCode’ as breeding_code

round 保留百分比小数后两位且四舍五入

concat(round((sum(case when numerator is null then 0 else numerator end)/sum(denominator))::numeric(10,5) * 100 , 2) ,‘%’)