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

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

?

1 2 3 4 5 6 7 8 9

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;

　　>> 查询结果

 

> 处理函数(返回单条)

?

1 2 3 4 5 6 7 8 9

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、结算结果为单条插入
　　

?

1 2	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、计算结果为多条插入

?

1 2 3 4 5 6

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