简化从bash脚本生成的where子句

核磁共振

我有以下变量 bash

source_FROM_batch_date='2020-06-06'
source_to_batch_date='2020-06-07'
min_batch_date_seq_num=2
max_batch_date_seq_num=3

我的数据加载在批次运行1,以44为最大批次。

我想where根据上面的变量动态生成一个子句

batch_date和batch_seq_num是我将过滤数据的列

conditions

1) read all the data where batch_date = '$source_FROM_batch_date' and batch_seq_num >= 'min_batch_date_seq_num'
2) read all the data where batch_date = '$source_to_batch_date' and batch_seq_num <= 'max_batch_date_seq_num'
3) read all the data that occurs between $source_FROM_batch_date and $source_to_batch_date

我已经做了如下。

#!/bin/bash

run_project_tmp_dir='/home/$USER'

source_FROM_batch_date='2020-06-06'
source_to_batch_date='2020-06-07'
min_batch_date_seq_num=2
max_batch_date_seq_num=3

export min_batch_date=${source_FROM_batch_date}
export max_batch_date=${source_to_batch_date}
export min_batch_date_seq_num=${min_batch_date_seq_num}
export max_batch_date_seq_num=${max_batch_date_seq_num}


####################Generate batch filter#######################
startdate=${min_batch_date}
enddate=${max_batch_date}
d=
n=0
loop_cnt=0
start_seq=${min_batch_date_seq_num}
end_seq=${max_batch_date_seq_num}
max_seq=4
max_seq_num=$((max_seq + 1))

batch_filter_file=${run_project_tmp_dir}/batch_filter_file.txt

if [ -f ${batch_filter_file} ]; then
    rm -f ${batch_filter_file}
fi

until [ "$d" = "$enddate" ]

do
    d=$(date -d "$startdate + $n days" +%Y-%m-%d)

    ## Case when only one batch to process
    if [[ $d = ${enddate} && ${start_seq} = "${end_seq}" && ${loop_cnt} = 0 ]];then
        echo "batch_date='$d' AND batch_seq_num='$start_seq'" >> ${batch_filter_file}
    fi

    ## Case when multiple batches on same batch date
    if [[ $d = ${enddate} && ${start_seq} -ne ${end_seq} && ${loop_cnt} = 0 ]];then
        until [ "$start_seq" = $((end_seq + 1)) ]
    do

    echo "(batch_date='$d' AND batch_seq_num='$start_seq') OR " >> ${batch_filter_file}

    ((start_seq++))

    done
    fi

    if [[ $d != "${enddate}" ]];then
        until [ "$start_seq" = "$max_seq_num" ]
    do

    echo "(batch_date='$d' AND batch_seq_num='$start_seq') OR " >> ${batch_filter_file}

    ((start_seq++))

    done
    fi

    if [[ $d = "${enddate}" && ${loop_cnt} != 0 ]];then
        until [ "$start_seq" = $((end_seq + 1)) ]
    do

    echo "(batch_date='$d' AND batch_seq_num='$start_seq') OR " >> ${batch_filter_file}

    ((start_seq++))

    done
    fi

    ((n++))
    ((loop_cnt++))
    start_seq=1

done

if [ -f ${batch_filter_file} ]; then
    sed -i '$s/OR $//' ${batch_filter_file}
    sed -i '1i (' ${batch_filter_file}
    echo ")" >> ${batch_filter_file}
fi

output

(
(batch_date='2020-06-06' AND batch_seq_num='2') OR 
(batch_date='2020-06-06' AND batch_seq_num='3') OR 
(batch_date='2020-06-06' AND batch_seq_num='4') OR 
(batch_date='2020-06-07' AND batch_seq_num='1') OR 
(batch_date='2020-06-07' AND batch_seq_num='2') OR 
(batch_date='2020-06-07' AND batch_seq_num='3') 
)

required output

(
(batch_date='2020-06-06' AND batch_seq_num in ('2', '3', '4') OR 
(batch_date='2020-06-07' AND batch_seq_num in ('1', '2', '3') 
)

我怎样才能实现自己想要的

markp主轴

对于多批次:

wclause="(batch_date=... and batch_seq_num in"

然后在每个序列的循环内:

wclause="${wclause}(${start_seq}"    # for first seq
wclause="${wclause},${start_seq}"    # rest of seq's

退出循环后:

wclause="${wclause}))"
echo "${wclause}"

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章