sql内容：
INSERT INTO
test.test_dws_performance_order15(
order_no,
item_id,
item_ids,
item_names,
sku_names,
cate_id,
cate_name,
biz_type_id,
biz_type_name,
position_type_id,
institution_id,
weimaihao,
user_name,
mobile,
doctor_id,
doctor_name,
order_amount,
pay_amount,
preference_amount,
drug_amount,
gradient_preference_amount,
confirmed_amount,
base_amount
)
SELECT
a.order_no,
CASE
WHEN biz.id = 8 THEN corecipe_id
ELSE NULL
END item_id,
CASE
WHEN biz.id = 8 THEN corecipe_id
ELSE NULL
END item_ids,
CASE
WHEN biz.id = 8 THEN corecipe_name
ELSE NULL
END item_names,
CASE
WHEN biz.id = 8 THEN corecipe_sku_sku_name
ELSE NULL
END sku_names,
biz.id cate_id,
biz_show_name cate_name,
biz.id biz_type_id,
biz_name biz_type_name,
2 position_type_id,
institution_id,
weimaihao,
patient_name user_name,
patient_mobile mobile,
CASE
WHEN biz.id = 8 THEN corecipe_goods_provider_id
ELSE doctor_id
END doctor_id,
CASE
WHEN biz.id = 8 THEN corecipe_goods_provider_name
ELSE doctor_name
END doctor_name,
order_amt order_amount,
pay_amt pay_amount,
discount_amt preference_amount,
recipe_drug_amount drug_amount,
0 gradient_preference_amount,
IF(biz.id = 9, recipe_drug_amount, order_amt) confirmed_amount,
IF(biz.id = 9, recipe_drug_amount, order_amt) - discount_amt base_amount
FROM
dws.dws_doc_recipe_order_detail_dm a
JOIN dws.dws_doc_recipe_order_dm b ON a.recipe_id = b.recipe_id
JOIN dim.dim_performance_business biz ON biz.id = CASE
WHEN b.source = 8 THEN 8 – 协定方 WHEN b.source<>8 AND b.drug_type=1 THEN 10 – 西药 WHEN b.source<>8 AND b.drug_type=2 THEN 9 – 中药 WHEN b.source<>8 AND b.drug_type=3 THEN 11 END ON DUPLICATE KEY UPDATE order_no=values(order_no), item_id=values(item_id), item_ids=values(item_ids), item_names=values(item_names), sku_names=values(sku_names), cate_id=values(cate_id), cate_name=values(cate_name), biz_type_id=values(biz_type_id), biz_type_name=values(biz_type_name), position_type_id=values(position_type_id), institution_id=values(institution_id), weimaihao=values(weimaihao), user_name=values(user_name), mobile=values(mobile), doctor_id=values(doctor_id), doctor_name=values(doctor_name), order_amount=values(order_amount), pay_amount=values(pay_amount), preference_amount=values(preference_amount), drug_amount=values(drug_amount), gradient_preference_amount=values(gradient_preference_amount), confirmed_amount=values(confirmed_amount), base_amount=values(base_amount);
执行计划：
id task estRows operator info actRows execution info memory disk
Insert_1 root 0 N/A 0 time:4m35.2s, loops:1, prepare: {total: 4m30.9s, auto_id_allocator: {alloc_cnt: 1, Get:{num_rpc:4, total_time:1.57ms}, scan_detail: {total_process_keys: 2, total_process_keys_size: 17187, total_keys: 4, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 25, read_count: 0, read_byte: 0 Bytes}}}, commit_txn: {prewrite:1.1ms, get_commit_ts:203.6µs, commit:1.03ms, region_num:1, write_keys:1, write_byte:33}}}, check_insert: {total_time: 4.29s, mem_insert_time: 2.92s, prefetch: 1.37s, rpc:{BatchGet:{num_rpc:181, total_time:1.1s}, total_process_time: 792ms, total_wait_time: 131ms, scan_detail: {total_process_keys: 0, total_process_keys_size: 0, total_keys: 183647, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 183827, read_count: 0, read_byte: 0 Bytes}}}}}, commit_txn: {prewrite:708.6ms, get_commit_ts:322.1µs, commit:25.2ms, region_num:2, write_keys:183647, write_byte:48431736} 49.3 MB N/A
└─Projection_15 root 229646.66 dws.dws_doc_recipe_order_detail_dm.order_no, case(eq(dim.dim_performance_business.id, 8), dws.dws_doc_recipe_order_dm.corecipe_id, <nil>)->Column#175, case(eq(dim.dim_performance_business.id, 8), dws.dws_doc_recipe_order_dm.corecipe_id, <nil>)->Column#176, case(eq(dim.dim_performance_business.id, 8), dws.dws_doc_recipe_order_dm.corecipe_name, <nil>)->Column#177, case(eq(dim.dim_performance_business.id, 8), dws.dws_doc_recipe_order_dm.corecipe_sku_sku_name, <nil>)->Column#178, dim.dim_performance_business.id, dim.dim_performance_business.biz_show_name, dim.dim_performance_business.id, dim.dim_performance_business.biz_name, 2->Column#179, dws.dws_doc_recipe_order_dm.institution_id, dws.dws_doc_recipe_order_dm.weimaihao, dws.dws_doc_recipe_order_dm.patient_name, dws.dws_doc_recipe_order_dm.patient_mobile, case(eq(dim.dim_performance_business.id, 8), cast(dws.dws_doc_recipe_order_dm.corecipe_goods_provider_id, decimal(20,0) UNSIGNED BINARY), cast(dws.dws_doc_recipe_order_dm.doctor_id, decimal(20,0) BINARY))->Column#180, case(eq(dim.dim_performance_business.id, 8), dws.dws_doc_recipe_order_dm.corecipe_goods_provider_name, dws.dws_doc_recipe_order_dm.doctor_name)->Column#181, dws.dws_doc_recipe_order_detail_dm.order_amt, dws.dws_doc_recipe_order_detail_dm.pay_amt, dws.dws_doc_recipe_order_detail_dm.discount_amt, dws.dws_doc_recipe_order_dm.recipe_drug_amount, 0->Column#182, if(eq(dim.dim_performance_business.id, 9), dws.dws_doc_recipe_order_dm.recipe_drug_amount, dws.dws_doc_recipe_order_detail_dm.order_amt)->Column#183, minus(if(eq(dim.dim_performance_business.id, 9), dws.dws_doc_recipe_order_dm.recipe_drug_amount, dws.dws_doc_recipe_order_detail_dm.order_amt), dws.dws_doc_recipe_order_detail_dm.discount_amt)->Column#184 183647 time:252.8ms, loops:181, Concurrency:OFF 1.63 MB N/A
└─Projection_16 root 229646.66 dws.dws_doc_recipe_order_detail_dm.order_no, dws.dws_doc_recipe_order_detail_dm.order_amt, dws.dws_doc_recipe_order_detail_dm.discount_amt, dws.dws_doc_recipe_order_detail_dm.pay_amt, dws.dws_doc_recipe_order_dm.patient_name, dws.dws_doc_recipe_order_dm.patient_mobile, dws.dws_doc_recipe_order_dm.doctor_id, dws.dws_doc_recipe_order_dm.doctor_name, dws.dws_doc_recipe_order_dm.institution_id, dws.dws_doc_recipe_order_dm.weimaihao, dws.dws_doc_recipe_order_dm.recipe_drug_amount, dws.dws_doc_recipe_order_dm.corecipe_id, dws.dws_doc_recipe_order_dm.corecipe_name, dws.dws_doc_recipe_order_dm.corecipe_sku_sku_name, dws.dws_doc_recipe_order_dm.corecipe_goods_provider_id, dws.dws_doc_recipe_order_dm.corecipe_goods_provider_name, dim.dim_performance_business.id, dim.dim_performance_business.biz_show_name, dim.dim_performance_business.biz_name 183647 time:163.6ms, loops:181, Concurrency:OFF 2.00 MB N/A
└─HashJoin_27 root 229646.66 inner join, equal:[eq(dim.dim_performance_business.id, Column#185)] 183647 time:159.9ms, loops:181, build_hash_table:{total:10.2ms, fetch:10.2ms, build:14.1µs}, probe:{concurrency:5, total:21m48.1s, max:4m26.7s, probe:21m46.2s, fetch:1.87s} 41.9 KB 0 Bytes
├─TableReader_75 root 21 data:TableFullScan_74 21 time:10.1ms, loops:2, cop_task: {num: 1, max: 2.59ms, proc_keys: 21, tot_proc: 1ms, tot_wait: 1ms, rpc_num: 2, rpc_time: 3.15ms, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 6ms} 1.35 KB N/A
│ └─TableFullScan_74 cop[tikv] 21 table:biz, keep order:false, stats:pseudo 21 tikv_task:{time:1ms, loops:1}, scan_detail: {total_process_keys: 21, total_process_keys_size: 3257, total_keys: 39, rocksdb: {delete_skipped_count: 0, key_skipped_count: 37, block: {cache_hit_count: 21, read_count: 5, read_byte: 871.6 KB}}} N/A N/A
└─Projection_28 root 183717.33 dws.dws_doc_recipe_order_detail_dm.order_no, dws.dws_doc_recipe_order_detail_dm.order_amt, dws.dws_doc_recipe_order_detail_dm.discount_amt, dws.dws_doc_recipe_order_detail_dm.pay_amt, dws.dws_doc_recipe_order_dm.patient_name, dws.dws_doc_recipe_order_dm.patient_mobile, dws.dws_doc_recipe_order_dm.doctor_id, dws.dws_doc_recipe_order_dm.doctor_name, dws.dws_doc_recipe_order_dm.institution_id, dws.dws_doc_recipe_order_dm.weimaihao, dws.dws_doc_recipe_order_dm.recipe_drug_amount, dws.dws_doc_recipe_order_dm.corecipe_id, dws.dws_doc_recipe_order_dm.corecipe_name, dws.dws_doc_recipe_order_dm.corecipe_sku_sku_name, dws.dws_doc_recipe_order_dm.corecipe_goods_provider_id, dws.dws_doc_recipe_order_dm.corecipe_goods_provider_name, case(eq(dws.dws_doc_recipe_order_dm.source, 8), 8, and(ne(dws.dws_doc_recipe_order_dm.source, 8), eq(dws.dws_doc_recipe_order_dm.drug_type, 1)), 10, and(ne(dws.dws_doc_recipe_order_dm.source, 8), eq(dws.dws_doc_recipe_order_dm.drug_type, 2)), 9, and(ne(dws.dws_doc_recipe_order_dm.source, 8), eq(dws.dws_doc_recipe_order_dm.drug_type, 3)), 11)->Column#185 183657 time:900.7ms, loops:183, Concurrency:OFF 1.91 MB N/A
└─IndexHashJoin_35 root 183717.33 inner join, inner:IndexLookUp_32, outer key:dws.dws_doc_recipe_order_detail_dm.recipe_id, inner key:dws.dws_doc_recipe_order_dm.recipe_id, equal cond:eq(dws.dws_doc_recipe_order_detail_dm.recipe_id, dws.dws_doc_recipe_order_dm.recipe_id) 183657 time:391.3ms, loops:183, inner:{total:15m10.5s, concurrency:5, task:15, construct:122.7ms, fetch:4.13s, build:29.1ms, join:15m6.3s} 73.4 MB N/A
├─TableReader_68 root 183657 data:Selection_67 183657 time:229.4ms, loops:187, cop_task: {num: 3, max: 231.6ms, min: 8.04ms, avg: 114.4ms, p95: 231.6ms, max_proc_keys: 124424, p95_proc_keys: 124424, tot_proc: 280ms, tot_wait: 2ms, rpc_num: 4, rpc_time: 343.8ms, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 6ms} 23.6 MB N/A
│ └─Selection_67 cop[tikv] 183657 not(isnull(dws.dws_doc_recipe_order_detail_dm.recipe_id)) 183657 tikv_task:{proc max:131ms, min:4ms, p80:131ms, p95:131ms, iters:193, tasks:3}, scan_detail: {total_process_keys: 183657, total_process_keys_size: 28478145, total_keys: 183661, rocksdb: {delete_skipped_count: 0, key_skipped_count: 183657, block: {cache_hit_count: 35, read_count: 492, read_byte: 9.50 MB}}} N/A N/A
│ └─TableFullScan_66 cop[tikv] 183657 table:a, keep order:false 183657 tikv_task:{proc max:127ms, min:4ms, p80:127ms, p95:127ms, iters:193, tasks:3} N/A N/A
└─IndexLookUp_32 root 1.00 181066 time:4s, loops:199, index_task: {total_time: 3.05s, fetch_handle: 3.05s, build: 48.3µs, wait: 226.3µs}, table_task: {total_time: 2.25s, num: 47, concurrency: 75} 30.4 KB N/A
├─IndexRangeScan_30 cop[tikv] 1.00 table:b, index:uk_recipe_id(recipe_id, data_from), range: decided by [eq(dws.dws_doc_recipe_order_dm.recipe_id, dws.dws_doc_recipe_order_detail_dm.recipe_id)], keep order:false 181066 time:3.04s, loops:219, cop_task: {num: 21, max: 552.9ms, min: 2.44ms, avg: 172.1ms, p95: 393.4ms, max_proc_keys: 25000, p95_proc_keys: 25000, tot_proc: 3.38s, tot_wait: 3ms, rpc_num: 21, rpc_time: 3.61s, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 4ms}, tikv_task:{proc max:497ms, min:2ms, p80:309ms, p95:370ms, iters:263, tasks:21}, scan_detail: {total_process_keys: 181066, total_process_keys_size: 9596498, total_keys: 362132, rocksdb: {delete_skipped_count: 0, key_skipped_count: 181066, block: {cache_hit_count: 1267202, read_count: 286, read_byte: 9.82 MB}}} N/A N/A
└─TableRowIDScan_31 cop[tikv] 1.00 table:b, keep order:false 181066 time:2.18s, loops:259, cop_task: {num: 98, max: 142.3ms, min: 531µs, avg: 30.2ms, p95: 86.1ms, max_proc_keys: 8586, p95_proc_keys: 6401, tot_proc: 2.7s, tot_wait: 9ms, rpc_num: 101, rpc_time: 2.95s, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 16ms}, tikv_task:{proc max:131ms, min:0s, p80:47ms, p95:73ms, iters:573, tasks:98}, scan_detail: {total_process_keys: 181066, total_process_keys_size: 150121776, total_keys: 211407, rocksdb: {delete_skipped_count: 1594, key_skipped_count: 305695, block: {cache_hit_count: 580817, read_count: 3581, read_byte: 87.4 MB}}} N/A N/A