awk模拟数据库做数据统计

工作中经常处理文本数据，以前经常接触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<=20081231{a[substr($1,1,6)]++}END{for (i in a) print i,a[i]}' file | sort -k1,1n

 

200701 2 200703 1 200705 1 200709 1 200712 1 200801 1 200803 1 200804 1 200806 1 200809 1

2、统计07、08年各类交易发生的笔数、金额

sql语句：

select zy,count(*),sum(je) from mx where date between '20070101' and '20081231' group by zy

 

ZY 2 3 -------- ----------- --------------------------------- 存款 2 1000.00 电费 1 50.00 电话费 2 100.00 工资 3 1500.00 取款 3 480.00   5 record(s) selected.

awk语句：

awk -F "|" '$1>=20070101&&$1<=20081231{a[$2]+=$4;b[$2]++}END{for (i in a) print i,b[i],a[i]}' file

 

工资 3 1500 电话费 2 100 存款 2 1000 取款 3 480 电费 1 50

3、嗯，在我的存折明细中，按月统计下07、08年每个操作员、每月的交易发生笔数吧，扣电费、电话费（czy为auto）的不统计，结果按月份、操作员号排序

sql语句：

select substr(date,1,6)\"月份\",czy,count(*)\"笔数\" from mytable where czy <>'auto' and date between '2007010' and '20081231' group by substr(date,1,6),czy  order by substr(date,1,6),czy

 

月份 CZY 笔数 ------ ------ ----------- 200701 010 1 200701 030 1 200703 999 1 200712 999 1 200803 102 1 200804 020 1 200806 010 1 200809 999 1   8 record(s) selected.

awk语句：

awk -F "|" '$6!="auto"&&substr($1,4,1)~/7|8/{a[substr($1,1,6)" "$6]++}END{for (i in a) print i,a[i]} ' file | sort -k1,1n -k2,2

 

200701 010 1 200701 030 1 200703 999 1 200712 999 1 200803 102 1 200804 020 1 200806 010 1 200809 999 1

对以上3个例子做个小总结：

    awk如何实现sql语句的group分组功能呢？

    关键是定义好数组，如：第1例中sql对月份(substr(date,1,6))分组，那awk中就定义数组a[substr($1,1,6)]。至于 要给该数组赋怎样的值，看统计需求。如例1统计分组后的次数，就a[substr($1,1,6)]++，表示 a[substr($1,1,6)]=a[substr($1,1,6)]+1；若要合计金额，如例2，则a[$2)]+=$4，等价于 a[$2]=a[$2]+$4，$4表示第4字段，是金额字段；至于例3，又稍微复杂了点，要根据两个条件分组（月份substr($1,1,6)、操作 员$6)，那定义的数组就是a[substr($1,1,6)" "$6]，注意下标中的" "，是为了输出时显示效果，你也可以改成别的，如改成"#"，最后显示效果就是这样：

200701#010 1 200701#030 1 200703#999 1 200712#999 1 200803#102 1 200804#020 1 200806#010 1 200809#999 1

4、又想到稍微复杂点的，用到了sql语句的having筛选。

   统计每年发工资的总额，显示超过750元的年份。

sql语句：

select substr(date,1,4),sum(je) from mytable where zy='工资' group by substr(date,1,4) having sum(je)>750

1 2 ---- --------------------------------- 2008 800.00   1 record(s) selected.

awk语句：

awk -F "|" '$2=="工资"{a[substr($1,1,4)]+=$4}END{for (i in a) if (a[i]>750) print i,a[i]}' 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

就写到这吧，编数据，想例子好累。

 

   补充一个awk调用printf的例子

 

 
 
[root@station11 shell]#awk  -F ":" '{a[$2]++} END {for (i in a) printf ("%-30s %5d/%-5d = percent:%.2f%%\n",i,a[i],FNR,a[i]/FNR*100)}' 2.txt  | sort -k 4 -r 
 updateh.360safe.com^M         percent:0.641026  
 www.360safe.com^M             percent:0.179487  
 se.360.cn^M                   percent:0.102564  
 bo.duba.net                   percent:0.051282  
 w6.safe.lfc.qihoo.net^M       percent:0.025641  
[root@station11 shell]# cat 2.fi 
cat: 2.fi: No such file or directory 
[root@station11 shell]# cat 2.file 
cat: 2.file: No such file or directory 
[root@station11 shell]# cat 2.txt 
Host: bo.duba.net:8080^M 
Host: bo.duba.net:8080^M 
Host: updateh.360safe.com^M 
Host: www.360safe.com^M 
Host: updateh.360safe.com^M 
Host: se.360.cn^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: www.360safe.com^M 
Host: updateh.360safe.com^M 
Host: www.360safe.com^M 
Host: www.360safe.com^M 
Host: se.360.cn^M 
Host: www.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: w6.safe.lfc.qihoo.net^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: www.360safe.com^M 
Host: updateh.360safe.com^M 
Host: se.360.cn^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: www.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: se.360.cn^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 
Host: updateh.360safe.com^M 

转自：

http://linuxguest.blog.51cto.com/195664/424496