19 lines
8.6 KiB
HTTP
19 lines
8.6 KiB
HTTP
###
|
||
POST https://powertest.cnecloud.com/api/v4/trade-schedule/sql/flow/data/lineage
|
||
Authorization: bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiLotL7nqIvnmpMiLCJlbnRlcnByaXNlQ29kZSI6MTA0MywidXNlckVudGVycHJpc2VTdGF0dXMiOjMsImF1dG8iOiIwIiwidXNlclN0YXR1cyI6MSwidXNlcl9uYW1lIjoi6LS-56iL55qTIiwicm9sZUlkIjoiMTExMTcxIiwiY29tcGFueU5hbWUiOiIiLCJ1c2VyRnVsbE5hbWUiOiJSM1k3N3JJUU0xYTBKU25HdWRYQ0xnPT0iLCJwaG9uZU51bSI6ImFyYStCdzVCb1ZHMFFXU214b3JDblE9PSIsImxhYmVsIjpudWxsLCJ1c2VyTG9nbyI6Ii9pbWcvbm9waWMucG5nIiwidXNlcklkIjoiOTgwNTExNzAzNjE5NTg0IiwiY2xpZW50X2lkIjoiY25lZ3JvdXAiLCJtYW5hZ2VUeXBlIjpudWxsLCJzY29wZSI6WyJhbGwiLCJyZWFkIiwid3JpdGUiXSwicm9sZU5hbWUiOiLlhajph4_nlLXlnLoiLCJlbnRlcnByaXNlSWQiOiI4OTY3NDg2MDg5NzIyODkiLCJleHAiOjE3MjkzMzE4NDAsImVudGVycHJpc2VMb2dvIjoiaHR0cHM6Ly9zMy5jbmVjbG91ZC5jb20vY25lL2ltYWdlcy9jZDc1NzM0NjI2OGU0YmMwOTExNTMzYjhmYThiZTY3My5wbmciLCJlbnRlcnByaXNlTmFtZSI6IitnMm5uQ21zYThscFJOQTVaV0d3SVE9PSIsImp0aSI6ImN2X0pCY2ppS1VubzdOUWU0QUpGdWZEN01LNCIsInVzZXJuYW1lIjoiUjNZNzdySVFNMWEwSlNuR3VkWENMZz09In0.Gr1Iz6dHfEQtML0NNDFtil0bd50SXoT3dPBhDdg_HHI
|
||
Content-Type: application/json
|
||
|
||
{
|
||
"dbVendor": "dbvpostgresql",
|
||
"sqlText": "INSERT INTO fdw.fact_trade_unit_contract_decomposition_detail(\n\tmarket_id,\n\ttrade_unit_id,\n\tcontract_id,\n\t\"date\",\n\ttime_order,\n\ttag,\n\tcontract_price,\n\tcontract_quantity,\n\tcontract_income,\n\tupdate_time\n)\nSELECT\n\tspcci.market_id,\n\tdtu.trade_unit_id,\n\tspcci.contract_id,\n\tspcci.operation_date \"date\",\n\tspccd.time_order,\n\tcase\n\t\twhen dc.contract_name like '%日滚%' then 1\n\t\twhen dc.contract_name like '%旬%' then 2\n\t\twhen dc.contract_name like '%汇总%' then 3\n\t\telse\n\t\t\tcase\n\t\t\t\twhen dc.contract_name like '%省间%' then 4\n\t\t\t\telse 5\n\t\t\tend\n\tend as tag,\n\tcase \n\t\twhen spcci.market_id = 'PXBSX' then dc.contract_price \n\t\telse spccd.price\n\tend as contract_price,\n\tcase \n\t\twhen spcci.market_id = 'PXBXJ' then spccd.\"power\" / 4 /*新疆数据是24时序的，因此分解的96时点数据需要除以4*/\n\t\telse spccd.\"power\"\n\tend contract_quantity,\t/* 交易中心合约电量全是MW.h */\n\tcase \n\t\twhen spcci.market_id = 'PXBXJ' then spccd.price * spccd.\"power\" / 4\n\t\twhen spcci.market_id = 'PXBSX' then dc.contract_price * spccd.\"power\"\n\t\telse spccd.price * spccd.\"power\"\n\tend::decimal(20, 6) contract_income,\n\tcurrent_timestamp\nFROM\n\t-- (select * from (select *, row_number() over(partition by market_id, member_id, contract_id, operation_date, curve_type order by modification_time desc) group_idx from cne.sgcc_px_contract_curve_info where curve_type in ('5', '6') and market_id = 'PHBSX') as t where group_idx = 1) spcci\t/* 曲线类型 5-对方交易单元曲线 3-对方机组曲线 6-己方交易单元曲线 4-己方发电机组曲线 */\t/* 去重 */\n\t(\n\tselect \n\t\t* \n\tfrom \n\t\t(\n\t\tselect *, \n\t\t\trow_number() over(partition by market_id, member_id, contract_id, operation_date, curve_type order by modification_time desc) group_idx \n\t\tfrom \n\t\t\t(\n\t\t\tselect \n\t\t\t\tcoalesce(a.market_id, b.market_id) as market_id,\n\t\t\t\tcoalesce(a.member_id, b.member_id) as member_id,\n\t\t\t\tcoalesce(a.contract_id, b.contract_id) as contract_id,\n\t\t\t\tcoalesce(a.guid, b.guid) as guid,\n\t\t\t\tcoalesce(a.curve_type, b.curve_type) as curve_type,\n\t\t\t\tcoalesce(a.generator_name, b.generator_name) as generator_name,\n\t\t\t\tcoalesce(a.start_date, b.start_date) as start_date,\n\t\t\t\tcoalesce(a.end_date, b.end_date) as end_date,\n\t\t\t\tcoalesce(a.operation_date, b.operation_date) as operation_date,\n\t\t\t\tcoalesce(a.daily_quantity, b.daily_quantity) as daily_quantity,\n\t\t\t\tcoalesce(a.curve_point_num, b.curve_point_num) as curve_point_num,\n\t\t\t\tcoalesce(a.modification_time, b.modification_time) as modification_time\n\t\t\tfrom\n\t\t\t\tcne.sgcc_px_contract_curve_info a\n\t\t\t\tfull join\n\t\t\t\ttianrun_new.sgcc_px_contract_curve_info b\n\t\t\t\t\ton a.member_id = b.member_id and a.guid = b.guid\n\t\t\t) spcci\n\t\t\twhere curve_type in ('5', '6') and market_id in ('PHBSX', 'PXBSX')\n\t\t) as t where group_idx = 1\n\t) spcci /* 增量处理需注意数据的准确性（多表join，主表增量，其它表数据更新，关联的主表数据若不在取的增量里，会导致结果失真） */\n\tinner join (select * from fdw.dim_trade_unit) dtu on spcci.member_id = dtu.counterparty_code and spcci.generator_name = dtu.trade_unit_name_old\t/* 过滤 */\n\tinner join fdw.dim_contract dc on dtu.trade_unit_id = dc.trade_unit_code and spcci.contract_id = dc.contract_code and spcci.start_date between dc.contract_start_date and dc.contract_end_date\t/* 合约表数据先于分解表 */\n\tinner join \n\t\t(\n\t\tselect \n\t\t\tcoalesce(c.market_id, d.market_id) as market_id,\n\t\t\tcoalesce(c.member_id, d.member_id) as member_id,\n\t\t\tcoalesce(c.contract_id, d.contract_id) as contract_id,\n\t\t\tcoalesce(c.guid, d.guid) as guid,\n\t\t\tcoalesce(c.role, d.role) as role,\n\t\t\tcoalesce(c.time_order, d.time_order) as time_order,\n\t\t\tcoalesce(c.power, d.power) as power,\n\t\t\tcoalesce(c.price, d.price) as price,\n\t\t\tcoalesce(c.modification_time, d.modification_time) as modification_time\n\t\tfrom \n\t\t\tcne.sgcc_px_contract_curve_detail c\n\t\t\tfull join \n\t\t\t\ttianrun_new.sgcc_px_contract_curve_detail d\n\t\t\t\ton c.member_id = d.member_id and c.guid = d.guid and c.time_order = d.time_order\n\t\t) spccd on spcci.member_id = spccd.member_id and spcci.guid = spccd.guid\nwhere spcci.modification_time >= current_date - interval '3 day' or spccd.modification_time >= current_date - interval '3 day'\nunion all\nselect \n\t'PHBSD' as market_id,\n\tfsscdd.trade_unit_code as trade_unit_id,\n\tfsscdd.contract_code as contract_id,\n\tfsscdd.\"date\",\n\tfsscdd.time_order_96 as time_order,\n\tcase \n\t\twhen fsscdd.tag = 99 then 4 \n\t\telse fsscdd.tag \n\tend as tag,\n\tfsscdd.contract_decomposition_price as contract_price,\n\tfsscdd.contract_decomposition_quantity as contract_quantity,\n\tfsscdd.contract_decomposition_income as contract_income,\n\tcurrent_timestamp\nFROM \n\t-- fdw.fact_shandong_trade_unit_contract_decomposition_detail fsscdd\n\t(select * from fdw.fact_shandong_trade_unit_contract_point_decomposition_detail where update_time >= current_date) fsscdd\nunion all\nselect \n\tmarket_id,\n\ttrade_unit_id,\n\tcontract_id,\n\t\"date\",\n\ttime_order,\n\ttag,\n\tcontract_price,\n\tcontract_quantity,\n\tcontract_income,\n\tcurrent_timestamp\nfrom \n\tfdw.fact_mengxi_trade_unit_contract_decomposition_detail\nwhere update_time >= current_date - interval '3 day'\nunion all\nselect \n\tmarket_id,\n\ttrade_unit_id,\n\tcontract_id,\n\t\"date\",\n\ttime_order,\n\ttag,\n\tcontract_price,\n\tcontract_quantity,\n\tcontract_income,\n\tcurrent_timestamp\nfrom \n\tfdw.fact_gansu_trade_unit_contract_decomposition_detail\nunion all\nselect \n\tmarket_id,\n\ttrade_unit_id,\n\tcontract_id,\n\t\"date\",\n\ttime_order,\n\ttag,\n\tcontract_price,\n\tcontract_quantity,\n\tcontract_income,\n\tcurrent_timestamp\nfrom \n\tfdw.fact_hebei_trade_unit_contract_decomposition_detail\nunion all\nselect \n\tmarket_id,\n\ttrade_unit_id,\n\tcontract_id,\n\t\"date\",\n\ttime_order,\n\ttag,\n\tcontract_price,\n\tcontract_quantity,\n\tcontract_income,\n\tcurrent_timestamp\nfrom \n\tfdw.fact_xinjiang_trade_unit_contract_decomposition_detail\nwhere update_time >= current_date - interval '3 day'\nunion all\nselect \n\tmarket_id,\n\ttrade_unit_id,\n\tcontract_id,\n\t\"date\",\n\ttime_order,\n\ttag,\n\tcontract_price,\n\tcontract_quantity,\n\tcontract_income,\n\tcurrent_timestamp\nfrom \n\tfdw.fact_jilin_trade_unit_contract_decomposition_detail\nwhere update_time >= current_date - interval '3 day'\nON CONFLICT (trade_unit_id, contract_id, \"date\", time_order)\n DO UPDATE SET\n (\n\t\tmarket_id,\n\t\ttag,\n\t\tcontract_price,\n\t\tcontract_quantity,\n\t\tcontract_income,\n\t\tupdate_time\n ) = (\n\t\t\texcluded.market_id,\n\t\t\texcluded.tag,\n\t\t\texcluded.contract_price,\n\t\t\texcluded.contract_quantity,\n\t\t\texcluded.contract_income,\n\t\t\texcluded.update_time\n \t)\n;\n\nDELETE FROM fdw.fact_trade_unit_contract_decomposition_detail fsspcdd1 WHERE EXISTS(SELECT 1 FROM (SELECT DISTINCT fsspcdd.trade_unit_id,fsspcdd.contract_id,dc.contract_name FROM fdw.fact_trade_unit_contract_decomposition_detail fsspcdd left join fdw.dim_contract dc on fsspcdd.contract_id = dc.contract_code and fsspcdd.trade_unit_id = dc.trade_unit_code where dc.contract_name is null) tmp WHERE fsspcdd1.contract_id = tmp.contract_id and fsspcdd1.trade_unit_id = tmp.trade_unit_id );",
|
||
"ignoreRecordSet": false,
|
||
"showConstantTable": false,
|
||
"simpleShowFunction": false,
|
||
"indirect": false,
|
||
"tableLevel": false,
|
||
"showTransform": false
|
||
}
|
||
|
||
|
||
|