awk的类sql数据处理

    工作中经常处理文本，以前经常接触db2数据库，最近在学习的过程中发现awk处理数据的强大，可谓无所不能！下面讲的就算awk对语句统计数据的模拟。

一、先讲讲sql的单表操作，对应awk的单处理。

    测试环境：sco + 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

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&&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

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