awk的类sql数据处理

莫问

　　工作中经常处理文本数据，以前经常接触db2数据库，最近在学习awk的过程中发现awk处理数据的强大，可谓无所不能！下面讲的就算awk对sql语句统计数据的模拟。
　　一、先讲讲sql的单表操作，对应awk的单文件处理。
　　测试环境：sco unix + db2，数据文件名file，数据库表名mytable，为保证准确性，所有语句均做测试。
　　就以存折明细这样的数据来举例吧，为了实现一些复杂的sql语句，数据有点多。
　　字段说明：1日期date 2摘要zy  3借贷标志bz 交易金额je   4余额ye 5操作员czy
　　#cat file
　　20070106|存款|2|400.00|500.27|010
　　20070106|取款|1|100.00|400.27|030
　　20070305|工资|2|400.00|800.27|999
　　20070505|电话费|1|50.00|750.27|auto
　　20070930|电费|1|50.00|700.27|auto
　　20071205|工资|2|300.00|1000.27|999
　　20080127|电话费|1|50.00|950.27|auto
　　20080303|取款|1|80.00|870.27|102
　　20080411|存款|2|600.00|1470.27|020
　　20080622|取款|1|300.00|1170.27|010
　　20080920|工资|2|800|1970.27|999
　　20090106|取款|1|200.00|1770.27|020
　　db2 "select * from mytable"
　　DATE ZY BZ JE YE CZY
　　-------- -------- -- ------------------ ------------------ ------
　　20070106 存款 2 400.00 500.27 010
　　20070106 取款 1 100.00 400.27 030
　　20070305 工资 2 400.00 800.27 999
　　20070505 电话费 1 50.00 750.27 auto
　　20070930 电费 1 50.00 700.27 auto
　　20071205 工资 2 300.00 1000.27 999
　　20080127 电话费 1 50.00 950.27 auto
　　20080303 取款 1 80.00 870.27 102
　　20080411 存款 2 600.00 1470.27 020
　　20080622 取款 1 300.00 1170.27 010
　　20080920 工资 2 800.00 1970.27 999
　　20090106 取款 1 200.00 1770.27 020
　　12 record(s) selected.
--------------例子开始----------------　　1、 统计07、08年每月交易发生笔数，按月排序
　　sql语句:
　　select substr(date,1,6),count(*) from mytable where date between '20070101' and '20081231' group by substr(date,1,6) order by substr(date,1,6)
　　1 2
　　------ -----------
　　200701 2
　　200703 1
　　200705 1
　　200709 1
　　200712 1
　　200801 1
　　200803 1
　　200804 1
　　200806 1
　　200809 1
　　10 record(s) selected.
　　awk语句：
　　awk -F "|" '$1>=20070101&&$1=20070101&&$1750) print i,a}' file
　　2008 800
二、sql的表关联操作，对应awk的多文件处理。    还是以账户明细为例子。账户明细表mx，各字段依次为：acct帐号，date日期，zy摘要，je发生额，ye余额客户信息表khxx，各字段依次为：acct帐号，name客户姓名，tel电话，addr地址明细表中的所有帐号，在客户信息表中都会有对应。数据库中数据如下：　　p1:MySco:[/tmp]$db2 "select * from mx"
　　ACCT DATE ZY JE YE
　　-------- -------- -------- ------------ ------------
　　1010001 20070106 存款 400.00 500.00
　　1010001 20070116 取款 300.00 200.00
　　1010001 20070118 工资 400.00 600.00
　　1010001 20070126 电话费 50.00 550.00
　　1010001 20070128 电费 50.00 500.00
　　1010001 20070209 存款 800.00 1300.00
　　1010001 20070211 工资 700.00 2000.00
　　1010001 20070226 电话费 50.00 1950.00
　　1010001 20070228 电费 50.00 1900.00
　　1010001 20070308 取款 400.00 1500.00
　　1010001 20070218 工资 900.00 2400.00
　　1010001 20070226 电话费 100.00 2300.00
　　1010001 20070228 电费 100.00 2200.00
　　1010001 20070313 取款 300.00 1900.00
　　1010001 20070319 工资 900.00 2800.00
　　1010001 20070326 电话费 50.00 2750.00
　　1010001 20070328 电费 50.00 2700.00
　　1010002 20070107 存款 900.00 2900.00
　　1010002 20070117 取款 300.00 2600.00
　　1010002 20070119 工资 1400.00 4000.00
　　1010002 20070127 电话费 200.00 3800.00
　　1010002 20070129 电费 150.00 3650.00
　　1010002 20070210 存款 1800.00 5450.00
　　1010002 20070212 工资 1750.00 7200.00
　　1010002 20070227 电话费 200.00 7000.00
　　1010002 20070229 电费 100.00 6000.00
　　1010002 20070309 取款 400.00 5600.00
　　1010002 20070219 工资 1000.00 6600.00
　　1010002 20070227 电话费 100.00 6500.00
　　1010002 20070229 电费 200.00 6300.00
　　1010002 20070314 取款 800.00 5500.00
　　1010002 20070320 工资 1400.00 6900.00
　　1010002 20070327 电话费 150.00 6750.00
　　1010002 20070329 电费 250.00 6500.00
　　34 record(s) selected.
　　p1:MySco:[/tmp]$db2 "select * from khxx"
　　ACCT NAME TEL ADDR
　　-------- -------- -------- --------
　　1010001 张三 11111 民主路
　　1010002 李四 22222 建设路
　　1010003 王五 33333 解放路
　　3 record(s) selected.
　　对应文件mx.txt和khxx.txt
　　mx.txt内容如下：
　　1010001,20070106,存款,400.00,500.00
　　1010001,20070116,取款,300.00,200.00
　　1010001,20070118,工资,400.00,600.00
　　1010001,20070126,电话费,50.00,550.00
　　1010001,20070128,电费,50.00,500.00
　　1010001,20070209,存款,800.00,1300.00
　　1010001,20070211,工资,700.00,2000.00
　　1010001,20070226,电话费,50.00,1950.00
　　1010001,20070228,电费,50.00,1900.00
　　1010001,20070308,取款,400.00,1500.00
　　1010001,20070218,工资,900.00,2400.00
　　1010001,20070226,电话费,100.00,2300.00
　　1010001,20070228,电费,100.00,2200.00
　　1010001,20070313,取款,300.00,1900.00
　　1010001,20070319,工资,900.00,2800.00
　　1010001,20070326,电话费,50.00,2750.00
　　1010001,20070328,电费,50.00,2700.00
　　1010002,20070107,存款,900.00,2900.00
　　1010002,20070117,取款,300.00,2600.00
　　1010002,20070119,工资,1400.00,4000.00
　　1010002,20070127,电话费,200.00,3800.00
　　1010002,20070129,电费,150.00,3650.00
　　1010002,20070210,存款,1800.00,5450.00
　　1010002,20070212,工资,1750.00,7200.00
　　1010002,20070227,电话费,200.00,7000.00
　　1010002,20070229,电费,100.00,6000.00
　　1010002,20070309,取款,400.00,5600.00
　　1010002,20070219,工资,1000.00,6600.00
　　1010002,20070227,电话费,100.00,6500.00
　　1010002,20070229,电费,200.00,6300.00
　　1010002,20070314,取款,800.00,5500.00
　　1010002,20070320,工资,1400.00,6900.00
　　1010002,20070327,电话费,150.00,6750.00
　　1010002,20070329,电费,250.00,6500.00
　　khxx.txt内容如下：
　　1010001,张三,11111,民主路
　　1010002,李四,22222,建设路
　　1010003,王五,33333,解放路
　　-------------例子开始-------------
　　1、统计出2007年1月份发生额总和大于2000的客户，列出帐号、姓名、月份、发生额合计
　　sql的表关联操作
　　db2 "select a.acct,a.name,substr(b.date,1,6),sum(b.je) from khxx a,mx b where a.acct=b.acct and substr(b.date,1,6)='200701' group by a.acct,a.name,substr(b.date,1,6) having sum(b.je)>2000"
　　ACCT NAME 3 4
　　-------- -------- ------ ---------------------------------
　　1010002 李四 200701 2950.00
　　1 record(s) selected.
　　awk的处理
　　awk -F, 'NR==FNR&&substr($2,1,6)=="200701"{a[$1]+=$4}NR>FNR&&a[$1]>2000{print $1,$2,"200701",a[$1]}' mx.txt khxx.txt
　　1010002 李四 200701 2950
　　这条AWK语句开始让我很迷糊，看不懂。花了些时间，终于搞懂了。下面解析下，做个笔记：
　　1、NR：The total number of input records seen so far.（目标文件总共有多少行）
　　2、FNR：The input record number in the current input file.（当前读取文件的行数）
　　这里注意，awk的FNR是以花括号的前后顺序对应目标文件的。
　　拿上面的语句为例：
　　'NR==FNR&&substr($2,1,6)=="200701"{a[$1]+=$4}处理的是mx.txt文件
　　NR>FNR&&a[$1]>2000{print $1,$2,"200701",a[$1]}' 处理的是khxx.txt，这样看，大家就应该很容易明白了。
　　3、a[$1]这个数组很关键，也就是连接两个表的关键字段。第一段代码统计出每个$1的金额，在第二段
　　代码中，先过滤此数组的结果，然后使用第二个表的$1字段为索引，把相关信息列出来。
2、统计每个账户2007年1-3月取款业务的发生额总笔数、总金额，列出帐号、户名、摘要、发生额、笔数　　db2 "select a.acct,a.name,b.zy,sum(b.je),count(*) from khxx a,mx b where a.acct=b.acct and substr(b.date,1,6) between '200701' and '200703' and b.zy='取款' group by a.acct,a.name,b.zy"
　　ACCT NAME ZY 4 5
　　-------- -------- -------- --------------------------------- -----------
　　1010001 张三 取款 1000.00 3
　　1010002 李四 取款 1500.00 3
　　2 record(s) selected.
　　awk -F, 'NR==FNR& amp;&substr($2,1,6)~/200701|200702|200703/&&$3=="取款" {a[$1]+=$4;b[$1]++}NR>FNR&&$1 in a{print $1,$2,"取款",a[$1],b[$1]}' mx.txt khxx.txt
　　1010001 张三 取款 1000 3
　　1010002 李四 取款 1500 3
　　就写到这吧，编数据，想例子好累。