select ddp.sale_planid ,unnest(case when(ddp.plan_receiver_id is null or ddp.plan_receiver_id[1] is null) then ARRAY[-1]::integer[] else ddp.plan_receiver_id end) cabinet_rule_id_r from ( select dp.plan_receiver_id --unnest(case when dp.cabinet_rule_id is null or dp.cabinet_rule_id[1] is null then ARRAY[-1]::integer[] else dp.cabinet_rule_id end ) cabinet_rule_id_r ,unnest(case when (dp.next_plan_id is null or dp.next_plan_id[1] is null) then ARRAY[-1]::integer[] else dp.next_plan_id end) as sale_planid --销地计划 from ods.ods_durian_delivery_plan AS dp where dp.type='ORIGIN') ddpSQL纠错

这段 SQL 语句存在语法错误。应该将 `CASE` 表达式的 `THEN` 和 `ELSE` 子句中的 `ARRAY[-1]::integer[]` 改为 `ARRAY[-1]`，同时在 `FROM` 子句中给子查询指定一个别名。修改后的 SQL 语句如下：

SELECT ddp.sale_planid, unnest(CASE WHEN (ddp.plan_receiver_id IS NULL OR ddp.plan_receiver_id[1] IS NULL) THEN ARRAY[-1] ELSE ddp.plan_receiver_id END) AS cabinet_rule_id_r
FROM (
  SELECT dp.plan_receiver_id, unnest(CASE WHEN (dp.next_plan_id IS NULL OR dp.next_plan_id[1] IS NULL) THEN ARRAY[-1] ELSE dp.next_plan_id END) AS sale_planid
  FROM ods.ods_durian_delivery_plan AS dp
  WHERE dp.type = 'ORIGIN'
) ddp;

相关问题

优化SQL select round( ohbmc.after_actual_amount/zz,0) cost_moneyi ,count(distinct case when ddp.orig_plan_rid = -1 then null else ddp.orig_plan_rid end) AS orig_num ,array_agg (dlt.state) AS loading_state ,count(DISTINCT CASE WHEN ddp.sale_planid = -1 THEN NULL ELSE ddp.sale_planid END) AS saleid_num--销地已计划数量 ,array_agg(dto.state) AS saletransport_state from ( SELECT id AS origin_planid , unnest(cabinet_rule_id) cabinet_rule_id -- 判断 next_plan_id 本身是空和 next_plan_id 为 {} ,unnest(case when (next_plan_id is null or next_plan_id[1] is null) then ARRAY[-1]::integer[] else next_plan_id end) as sale_planid --销地计划 , case when dp.plan_receiver_id is null then -1 else dp.plan_receiver_id end orig_plan_rid --产地计划 FROM ods.ods_durian_delivery_plan as dp left join ods.ods_hl_commodity_category as hcc on hcc.category_id = dp.category_id WHERE dp.type = 'ORIGIN' AND dp.deleted = 99 AND dp.tenant_id = 1 and cabinet_rule_id='{8}'or cabinet_rule_id='{9}'or cabinet_rule_id='{10000005}'---取白心火龙果 AND hcc.category_name = '火龙果') as ddp LEFT JOIN ods.ods_durian_loading_task AS dlt ON dlt.plan_id = ddp.origin_planid and dlt.plan_type='ORIGIN' AND dlt.deleted = 99 LEFT JOIN ods.ods_durian_transport_order AS dto ON dto.plan_id = ddp.sale_planid AND dto.deleted = 99 LEFT JOIN ods.ods_durian_receipt_task AS drt ON drt.plan_id = ddp.sale_planid AND drt.deleted = 99 LEFT JOIN ods.ods_durian_transport_order AS dto1 ON dto1.plan_id = ddp.sale_planid AND dto1.sort_no = 1 AND dto1.deleted = 99 left join (select odlsi.plan_id,sum(odlsi.quantity) zz from ods.ods_durian_loading_sku_item odlsi group by 1) odlsi on odlsi.plan_id=dlt.plan_id left join (select *, unnest(case when ( odbr.bill_main_id is null or odbr.bill_main_id is null) then ARRAY[-1]::integer[] else odbr.bill_main_id end) bill_main_id_r from ods.ods_durian_bill_rel odbr) odbr on odbr.data_id= dlt.plan_id and odbr.data_type='ORIGIN_FEE' left join ods.ods_hl_bill_main_currency ohbmc on ohbmc.bill_main_id=odbr.bill_main_id_r and ohbmc.deleted=99 group by 1;

这个SQL查询语句比较复杂，可以考虑以下几种优化方式：

1. 使用JOIN语句代替子查询：可以把子查询转换成JOIN语句，这样可以提高查询的效率。

2. 合理使用索引：根据实际情况，选择合适的索引可以大大提升查询效率。可以对WHERE子句中的条件表达式所涉及的列添加索引。

3. 减少DISTINCT关键字的使用：DISTINCT关键字会增加查询的开销，如果不必要，可以考虑去掉。

4. 减少聚合函数的使用：聚合函数是一种开销较大的操作，如果可以不用聚合函数，可以考虑去掉。

5. 优化WHERE子句中的条件表达式：可以优化WHERE子句中的条件表达式，尽量使用简单的条件表达式，减少复杂的逻辑判断。

请将下列shell语句转换为python语句：for ((i = 0; i < $num_gpus; ++i)); do { gpu_id=$(echo $CUDA_VISIBLE_DEVICES | cut -d',' -f$[$i+1]) python wenet/bin/train.py --gpu $gpu_id \ --config $train_config \ --data_type $data_type \ --symbol_table $dict \ ${bpemodel:+--bpe_model ${bpemodel}.model} \ --train_data data/$train_set/data.list \ --cv_data data/$train_dev/data.list \ ${checkpoint:+--checkpoint $checkpoint} \ ${enc_init:+--enc_init $enc_init} \ --enc_init_mods $enc_init_mods \ --model_dir $dir \ --ddp.init_method $init_method \ --ddp.world_size $num_gpus \ --ddp.rank $i \ --ddp.dist_backend $dist_backend \ --num_workers 6 \ $cmvn_opts } & done wait

以下是将该shell语句转换为python语句的代码：

import os

for i in range(num_gpus):
    gpu_id = os.environ["CUDA_VISIBLE_DEVICES"].split(',')[i]
    os.system(f"python wenet/bin/train.py --gpu {gpu_id} --config {train_config} --data_type {data_type} --symbol_table {dict} {'--bpe_model ' + bpemodel + '.model' if bpemodel else ''} --train_data data/{train_set}/data.list --cv_data data/{train_dev}/data.list {'--checkpoint ' + checkpoint if checkpoint else ''} {'--enc_init ' + enc_init if enc_init else ''} --enc_init_mods {enc_init_mods} --model_dir {dir} --ddp.init_method {init_method} --ddp.world_size {num_gpus} --ddp.rank {i} --ddp.dist_backend {dist_backend} --num_workers 6 {cmvn_opts} &")
    
os.system("wait")

需要注意的是，以上代码中的变量`num_gpus`、`train_config`、`data_type`、`dict`、`bpemodel`、`train_set`、`train_dev`、`checkpoint`、`enc_init`、`enc_init_mods`、`dir`、`init_method`、`dist_backend`、`cmvn_opts`需要在程序中进行定义和初始化。