SQL content:
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 -- Agreement Party
WHEN b.source<>8 AND b.drug_type=1 THEN 10 -- Western Medicine
WHEN b.source<>8 AND b.drug_type=2 THEN 9 -- Chinese Medicine
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`);
Execution plan:
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
|
username: 海石花47
|
username: 
|
username: 
|
username: 
