Shell编程,以后也许有用..-优快云博客

本文链接：https://blog.youkuaiyun.com/kimsung/article/details/5264680

#!/bin/sh ## shell

. /home/irteam/dalian/iat/batch-sh/spam_data_bin/bin/conf/test_iat_dev.conf ## 导入

PRINT "==============================="
PRINT ": [stc_image_task_cnt.sh] Inesrt stc_task_cnt : IMAGE DATA "
PRINT "; [stc_image_task_cnt.sh] DATE : $_DATE                "
PRINT "-------------------------------"

#MYSQL_EXEC="$_MYSQL_EXEC -h$_DB_SVR_DATA -u$_MYSQL_USER -p$_MYSQL_PASS -P$_MYSQL_PORT --sock=/home1/irteam/db/mysql_10175/tmp/mysql.sock $_MYSQL_DB --skip-column-name" ## 执行MYSQL的命令.

RESOURCE_MYSQL_EXEC="$_RESOURCE_MYSQL_EXEC -h$_RESOURCE_DB_SVR_DATA -u$_RESOURCE_MYSQL_USER -p$_RESOURCE_MYSQL_PASS -P$_RESOURCE_MYSQL_PORT --sock=/home1/irteam/db/mysql_10175/tmp/mysql.sock $_RESOURCE_MYSQL_DB --skip-column-name"    ## MYSQL执行命令.

TARGET_MYSQL_EXEC="$_TARGET_MYSQL_EXEC -h$_TARGET_DB_SVR_DATA -u$_TARGET_MYSQL_USER -p$_TARGET_MYSQL_PASS -P$_TARGET_MYSQL_PORT --sock=/home1/irteam/db/mysql_10175/tmp/mysql.sock $_TARGET_MYSQL_DB --skip-column-name" ## 执行MYSQL命令

IMG_VER="manage_ver_total"

TABLE_FIELD=("(task, path, ver, job_dt, idone_cnt, iadult_cnt, ilq_cnt, ifbid_cnt, inoimg_cnt, tot_cnt, cand_cnt)" );

for mid in aqimg nqimg img    ## 以 aqimg,nqimg,img来循环. 也就是循环3次. 每次循环负给mid变量.
do

        echo ${mid}

        if [ ${mid} = "aqimg" ]   ## 如果等于aqimg
        then
                task_cd='1'
        elif [ ${mid} = "nqimg" ]
        then
                task_cd='2'
        else
                task_cd='3'
        fi

        versions=(`echo "SELECT ver FROM $IMG_VER WHERE task=${task_cd} AND path=2;" | $RESOURCE_MYSQL_EXEC`) ## 执行SQL

        i=0
        unset qryset ## 清楚qryset变量. 我建议这个不要写在循环里面, 下面有一句是变量="", 这个更有效, 当循环多了 unset好像不会释放内存..
        for SUFFIX in "${versions[@]}"; do ## 用执行结果来循环, @注意这里结果集是一个数组, 不是以一行一个数组,是按空格来分数组的. 这个问题让我郁闷好长时间!!!!!!谢特!!

            IMG_TAB="pred_${mid}_image_${SUFFIX}"
            PRINT "; [stc_image_task_cnt.sh] - tbl = $IMG_TAB"
            DB_QRY=" SELECT ${task_cd} AS task, 2 AS path, T2.ver, T2.date, T2.idone_cnt, T2.iadult_cnt, T2.ilq_cnt, T2.ifbid_cnt, T2.inoimg_cnt, T2.tot_cnt , ( SELECT count(1) FROM ${IMG_TAB} ) AS cand_cnt FROM stc_task_cnt T1 RIGHT JOIN ( SELECT '${SUFFIX}' as ver , date_format(date_3, '%Y-%m-%d') as date , SUM(CASE WHEN ctgr_3 = 1 THEN 1 ELSE 0 END) AS idone_cnt , SUM(CASE WHEN ctgr_3 = 2 THEN 1 ELSE 0 END) AS iadult_cnt , SUM(CASE WHEN ctgr_3 = 11 THEN 1 ELSE 0 END) AS ilq_cnt , SUM(CASE WHEN ctgr_3 = 31 THEN 1 ELSE 0 END) AS ifbid_cnt , SUM(CASE WHEN ctgr_3 = 99 THEN 1 ELSE 0 END) AS inoimg_cnt , SUM(CASE WHEN ctgr_3 > 0 THEN 1 ELSE 0 END) AS tot_cnt FROM ${IMG_TAB} WHERE ctgr_3 > 0 GROUP BY 2) T2 ON T1.task=${task_cd} AND T1.path=2 AND T1.ver = T2.ver AND T1.job_dt = T2.date"
            INSERTQUERY=(`echo "SELECT concat('INSERT INTO stc_task_cnt ${TABLE_FIELD} VALUES(', task, ',', path, ',', ver, ',', date, ',', idone_cnt, ',', iadult_cnt, ',', ilq_cnt, ',', ifbid_cnt, ',', inoimg_cnt, ',', tot_cnt, ',', cand_cnt,')ON DUPLICATE KEY UPDATE idone_cnt = ', idone_cnt, ' , iadult_cnt = ', iadult_cnt, ' , ilq_cnt = ', ilq_cnt, ', ifbid_cnt = ', ifbid_cnt, ' , inoimg_cnt = ', inoimg_cnt, ' , tot_cnt = ', tot_cnt, ';') as insertquery FROM ( ${DB_QRY} ) as rs_tb" | $RESOURCE_MYSQL_EXEC`) ##意思就是用SELECT 制作INSERT语句, 然后下面循环插入.下面这个循环实际上没什么意义.

            for QUERYSET in "${INSERTQUERY[*]}"; do
               $TARGET_MYSQL_EXEC -e "${QUERYSET}"
                if [ $? -ne 0 ]
                then
                       PRINT "; [stc_image_task_cnt.sh] [ERROR] SELECT ERROR ${IMG_TAB}";
                       exit -1;
                else
                       PRINT "; [stc_image_task_cnt.sh] [DONE]"
                fi
            done

        done

done

# Direct addition img added by ST13206@091027

IMG_TAB="pred_direct_image"
task_cd='4'
PRINT "; [stc_image_task_cnt.sh] - tbl = $IMG_TAB"

DB_QRY="SELECT ${task_cd} AS task, 2 AS path, T2.ver, T2.date, T2.idone_cnt, T2.iadult_cnt, T2.ilq_cnt, T2.ifbid_cnt, T2.inoimg_cnt, T2.tot_cnt , ( SELECT count(1) FROM ${IMG_TAB} where ctgr=0 ) AS cand_cnt FROM stc_task_cnt T1 RIGHT JOIN ( SELECT '000001' as ver , date_format(editdate, '%Y-%m-%d') as date , SUM(CASE WHEN ctgr = 1 THEN 1 ELSE 0 END) AS idone_cnt , SUM(CASE WHEN ctgr = 2 THEN 1 ELSE 0 END) AS iadult_cnt , SUM(CASE WHEN ctgr = 11 THEN 1 ELSE 0 END) AS ilq_cnt , SUM(CASE WHEN ctgr = 31 THEN 1 ELSE 0 END) AS ifbid_cnt , SUM(CASE WHEN ctgr = 99 THEN 1 ELSE 0 END) AS inoimg_cnt , SUM(CASE WHEN ctgr > 0 THEN 1 ELSE 0 END) AS tot_cnt FROM ${IMG_TAB} WHERE ctgr > 0 GROUP BY 2) T2 ON T1.task=${task_cd} AND T1.path=2 AND T1.ver = '000001' AND T1.job_dt = T2.date"

INSERTQUERY=(`echo "SELECT concat('INSERT INTO stc_task_cnt ${TABLE_FIELD} VALUES (',task, ',', path, ',', ver, ',', date, ',', idone_cnt, ',', iadult_cnt, ',', ilq_cnt, ',', ifbid_cnt, ',', inoimg_cnt, ',', tot_cnt, ',', cand_cnt, ') ON DUPLICATE KEY UPDATE idone_cnt = ', idone_cnt, ' , iadult_cnt = ', iadult_cnt, ' , ilq_cnt = ', ilq_cnt, ' , ifbid_cnt = ', ifbid_cnt, ' , inoimg_cnt = ', inoimg_cnt, ' , tot_cnt = ', tot_cnt, ';') as insertquery FROM(${DB_QRY}) as rs_tb" | $RESOURCE_MYSQL_EXEC`)

`echo "START_SECOND_QUERY" >> 1.txt`

for INSERT_QUERY in "${INSERTQUERY[*]}"; do
    $TARGET_MYSQL_EXEC -e "$INSERT_QUERY"

    if [ $? -ne 0 ]
    then
        PRINT "; [stc_image_task_cnt.sh] [ERROR] SELECT ERROR ${IMG_TAB}";
        exit -1;
    else
        PRINT "; [stc_image_task_cnt.sh] [DONE]"
    fi
done

#added end

PRINT "=================== FINISHED ====================="

------------------------------------------------------我是分割线-------------------------------------------------------

#!/bin/sh

. /home1/irteam/spam_data/bin/common/iat.conf

PRINT "================================"
PRINT ": Inesrt stc_host_cnt DATA      "
PRINT "; DATE : $_DATE                 "
PRINT "--------------------------------"

RESOURCE_MYSQL_EXEC="$_RESOURCE_MYSQL_EXEC -h$_RESOURCE_DB_SVR_DATA -u$_RESOURCE_MYSQL_USER -p$_RESOURCE_MYSQL_PASS -P$_RESOURCE_MYSQL_PORT --sock=/home1/irteam/db/mysql_10175/tmp/mysql.sock $_RESOURCE_MYSQL_DB --skip-column-name --default-character-set=utf8"

TARGET_MYSQL_EXEC="$_TARGET_MYSQL_EXEC -h$_TARGET_DB_SVR_DATA -u$_TARGET_MYSQL_USER -p$_TARGET_MYSQL_PASS -P$_TARGET_MYSQL_PORT --sock=/home1/irteam/db/mysql_10175/tmp/mysql.sock $_TARGET_MYSQL_DB --skip-column-name --default-character-set=utf8"

TASK_VER="manage_ver_total"

TABLE_FIELD=("(task_cd, path_cd, ver, job_dt, degree, tester_no, tester_nm, work_cnt, error_cnt, cand_cnt)" );

QRY_COUNT=300

for mid in aqimg img
do
    echo ${mid}

    if [ ${mid} = "aqimg" ]
    then
        task_cd='1'
    else
        task_cd='3'
    fi

    # VERSION
    versions=(`echo "SELECT ver FROM $TASK_VER WHERE task=${task_cd} AND path=1;" | $RESOURCE_MYSQL_EXEC`)

    i=0
    for SUFFIX in "${versions[@]}"; do

        HOST_TAB="pred_${mid}_host_${SUFFIX}"
        PRINT "- tbl = $HOST_TAB"

        SELECT_FIELD=("${task_cd} as task_cd, 1, '${SUFFIX}' as ver, date, degree, tester, testernm, work_cnt, error_cnt" );

        DB_QRY="SELECT ${SELECT_FIELD}, CASE WHEN degree < 3 THEN ( select count(1) from ${HOST_TAB}) ELSE ( select sum( case when (hctgr_1 <> hctgr_2 or ctgr_1 <> ctgr_2) then 1 else 0 end ) from ${HOST_TAB}) END as cand_cnt FROM ( SELECT tester_1 AS tester, testernm_1 AS testernm, date_format(date_1, '%Y-%m-%d') as date, 1 AS degree, SUM(CASE WHEN ctgr_1 > 0 THEN 1 ELSE 0 END)AS work_cnt , SUM(CASE WHEN ctgr_1 <> ctgr_3 THEN 1 ELSE 0 END)AS error_cnt FROM ${HOST_TAB} WHERE status > 0 GROUP BY 1,3 UNION ALL SELECT tester_2 AS tester, testernm_2 AS testernm, date_format(date_2, '%Y-%m-%d') as date, 2 AS degree , SUM(CASE WHEN ctgr_2 > 0 THEN 1 ELSE 0 END)AS work_cnt , SUM(CASE WHEN ctgr_2 <> ctgr_3 THEN 1 ELSE 0 END)AS error_cnt FROM ${HOST_TAB} WHERE status > 0 GROUP BY 1,3 UNION ALL SELECT tester_3 AS tester, testernm_3 AS testernm, date_format(date_3, '%Y-%m-%d') as date, 3 AS degree , SUM(CASE WHEN ctgr_3 > 0 THEN 1 ELSE 0 END)AS work_cnt , 0 AS error_cnt FROM ${HOST_TAB} WHERE status > 0 AND ctgr_1 <> ctgr_2 GROUP BY 1,3) ins WHERE work_cnt > 0 "

        (echo " SELECT concat('INSERT INTO stc_ver_person_cnt ${TABLE_FIELD} VALUES (', task_cd , ',', 1, ',''', ver, ''',''', date, ''',', degree , ',''', tester , ''',''', testernm, ''',', work_cnt, ',', error_cnt, ',', cand_cnt, ')ON DUPLICATE KEY UPDATE work_cnt = ', work_cnt, ', error_cnt = ', error_cnt, ', cand_cnt = ', cand_cnt, ' ; ') as insertquery FROM( $DB_QRY ) rs_tb UNION ALL select 'LAST_LINE'" | $RESOURCE_MYSQL_EXEC) | while read LINE
        do
            i=$(($i+1))

            if [ "$LINE" == "LAST_LINE" ]
            then
                $TARGET_MYSQL_EXEC -e "${INSERT_QUERIES}"
                INSERT_QUERIES=""
                break
            fi

            INSERT_QUERIES="${INSERT_QUERIES}""${LINE}"

            if [ `expr $i % $QRY_COUNT` -eq 0 ]
            then
                $TARGET_MYSQL_EXEC -e "${INSERT_QUERIES}"
                INSERT_QUERIES=""
            fi

        done

        if [ $? -ne 0 ]
        then
            PRINT "[ERROR] SELECT ERROR ${HOST_TAB}";
            exit -1;
        else
            PRINT "[DONE]"
        fi

    done

done

PRINT "=================== FINISHED ====================="