PG plpython存储过程计算结果直接入库

> 处理函数(返回多条结果数组)

drop FUNCTION cal_charge_sample(records dwd_pv_behavior_di[]);
CREATE FUNCTION cal_charge_sample(records dwd_pv_behavior_di[]) 
RETURNS json AS $$
import json
import pandas as pd
df = pd.DataFrame.from_records(records)
result_tag = [{"vin":'AHHDHD',"tag":'片段1'},{"vin":'AHHDHD',"tag":'片段2'}]
return json.dumps(result_tag)
$$ LANGUAGE plpython3u;

　　>> 查询结果

 

> 处理函数(返回单条)

drop FUNCTION cal_charge_sample(records dwd_pv_behavior_di[]);
CREATE FUNCTION cal_charge_sample(records dwd_pv_behavior_di[]) 
RETURNS json AS $$
import json
import pandas as pd
df = pd.DataFrame.from_records(records)
result_tag = {"vin":'AHHDHD',"tag":'片段1'}
return json.dumps(result_tag)
$$ LANGUAGE plpython3u;

　　>> 查询结果

 

1、结算结果为单条插入
　　

with result as (select cal_charge_cycles(array_agg(dwd_pv_behavior_di.*)) from dwd.dwd_pv_behavior_di GROUP BY vin) 
insert into dws.dws_pv_behavior_charge_diselect * from result;

2、计算结果为多条插入

with result as (
select unnest(cal_cycle_sample)::json as tag_res from 
select dws.cal_cycle_sample('charge',array_agg(dwd_pv_behavior_di.*),'[{"id":106}]') from dwd.dwd_pv_behavior_di group by vin
as t1)
insert into dws.dws_pv_behavior_charge_di
select res.* from result,json_populate_record(null::dws.dws_pv_behavior_charge_di,tag_res) as res