SQL数据库基本操作对应Shell脚本文件处理

一、前言

日常工作中，一些数据统计的源数据是文件，因为文件数据统计不像数据库操作那样方便，如果导入数据库再进行操作，可能比较麻烦；因此，这里将SQL数据库基本操作与Shell对文件数据操作进行对应，方便用Shell的语法进行文件处理。这里分别用Oracle SQL与shell工具操作同一数据进行操作演示，演示数据较少,日常操作数据一般为千万级以上,文件数也较多。因此需要结合多进程并发处理,由于前期对shell，多进程并发已说明(详见帖子: http://eip.teamshub.com/t/3356518),此处不再解释。因shell操作涉及内存,因此在控制并发处理文件或其他操作时需要考虑内存的使用，如果超过限制,可split分割文件后进行处理。

二、对应关系简述

1.Where+字段比较条件(AND/OR)

ORACLE:

select 'yuanwm',to_char(id_no),acct_item,orig_amount,bill_amount,chrg_amount from TEST_PAYEDOWE_INFO a where substr(id_no,1,2) in ('80','75') and ORIG_AMOUNT>8000;

SHELL:

awk '{if($3>8000&&(substr($1,1,2)=="80" || substr($1,1,2)=="75")) print $0}' ${File_PayedInfo}

 

2.按字段orderby 升/降

ORACLE:

select 'yuanwm',to_char(id_no),orig_amount,bill_amount,chrg_amount from TEST_PAYEDOWE_INFO a order by ORIG_AMOUNT DESC,to_char(id_no);

SHELL:

sort -k3nr -k1,1 -t' ' ${File_PayedInfo}|awk '{$2="\b";print $0}'

 

3.Distinct的使用

ORACLE:

select distinct 'yuanwm',to_char(id_no),substr(acct_item,1,2) from TEST_PAYEDOWE_INFO a ;

SHELL:

awk '{print $1,substr($2,1,2)}' ${File_PayedInfo}|sort -u

 

4.Insert的使用

echo "直接建立文件,或者追加即可>,这个不用说了!"

 

5.Update的使用+where

ORACLE:

update TEST_PAYEDOWE_INFO set bill_amount=50 where id_no='80077008777970';

SHELL:

##解释:直接遍历,语法简单,如果数据量大可使用多进程并发(这里使用awk不使用sed,因sed考虑因素过多,控制不好,会造成原文件损坏.因此这里采用awk重输出,理解方便,输出后可删除原文件)

awk '{if($1=="80077008777970") {$4=50};print $0}' ${File_PayedInfo}

 

6.Delete的使用+where

ORACLE:

delete from TEST_PAYEDOWE_INFO where id_no='80077008777970';

SHELL:"

##解释:直接遍历,语法简单,如果数据量大可使用多进程并发(这里使用awk不使用sed,因sed考虑因素过多,控制不好,会造成原文件损坏.因此这里采用awk重输出,理解方便,输出后可删除原文件)

awk '{if($1=="80077008777970"){} else print $0}' ${File_PayedInfo}

 

7.Where+Like匹配的使用

ORACLE:

select 'yuanwm',to_char(id_no),acct_item,orig_amount,bill_amount,chrg_amount from TEST_PAYEDOWE_INFO where acct_item like '%8' and to_char(id_no) like '8%' and to_char(id_no) like '%898%';

SHELL:"

#解释 a.匹配,^开头$结尾不带.b.不匹配加!即可.

awk '{if($2~/8$/ && $1 ~/^8/ && $1~/898/) {print $0}}' ${File_PayedInfo}

 

8.where+rownum的使用

ORACLE:

select 'yuanwm',to_char(id_no),acct_item,orig_amount,bill_amount,chrg_amount from TEST_PAYEDOWE_INFO where rownum<=3 order by id_no;

SHELL:

#解释，文件也可以使用head命令,这里用awk内置作解释,可能与oracle取的不一样,这根排序有关,更改排序条件即可达到一样的效果.NR为内置变量,具体参考手册

#这样遍历可能较多次数

#sort -k1 ${File_PayedInfo}|awk '{ if(NR<=3) print$0}'

#这样就合适一些

awk '{if(NR>3) {exit 0} else print $0}' ${File_PayedInfo}

 

9.对于sum/count/gropby/having等关键词的使用

ORACLE:

select 'yuanwm',to_char(id_no),acct_item,sum(chrg_amount),sum(bill_amount),count(1) from TEST_PAYEDOWE_INFO group by id_no,acct_item having sum(orig_amount)>500 and count(1)>0 order by acct_item;

SHELL:

#解释:a.$1" "$2-这种作为下标匹配,同时可以用为数组去重

awk '{Arr[$1" "$2] += $3;Arr1[$1" "$2] += $4;CArr[$1" "$2]++}END{for(i in Arr) { if(Arr[i]>500 && CArr[i]>0){print i,Arr[i],Arr1[i],CArr[i]}}}' ${File_PayedInfo}|sort -k2,2

 

10.inner join/where+两表关联(Shell多文件操作)

ORACLE:

select 'yuanwm',to_char(a.ID_NO),a.ACCT_ITEM,a.ORIG_AMOUNT,a.BILL_AMOUNT,a.CHRG_AMOUNT,to_char(b.ID_NO),to_char(CONTRACT_NO),b.RUN_CODE,b.BELONG_CODE,b.OPEN_TIME from TEST_PAYEDOWE_INFO a inner join TEST_USER_INFO b on a.id_no=b.id_no and a.id_no!='80077008777970';

SHELL:

#解释:a.这里会对${File_UserInfo}文件中id_no去重,因此需要保证唯一主键.b:逻辑:保存用户信息id_no->遍历账单文件(按条件输出)c.条件文件放前,输出文件放后,这里使用next

#可以让文件依次遍历,而不执行后续其他文件的条件语句.

awk 'ARGIND==1{Arr[$1]=$0;next} {if($1 in Arr&&ARGIND==2&&$1!=80077008777970) {print $0,Arr[$1]}}' ${File_UserInfo} ${File_PayedInfo}