awk模拟数据库做数据统计
来源:互联网 发布:火影主线集数知乎 编辑:程序博客网 时间:2024/05/16 07:21
工作中经常处理文本数据,以前经常接触db2数据库,最近在学习awk的过程中发现awk处理数据的强大,可谓无所不能!下面讲的就算awk对sql语句统计数据的模拟。
一、先讲讲sql的单表操作,对应awk的单文件处理。
测试环境:sco unix + db2,数据文件名file,数据库表名mytable,为保证准确性,所有语句均做测试。
就以存折明细这样的数据来举例吧,为了实现一些复杂的sql语句,数据有点多。
字段说明:1日期date 2摘要zy 3借贷标志bz 交易金额je 4余额ye 5操作员czy
#catfile
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 wheredate 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语句:
selectsubstr(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#0101
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语句:
selectsubstr(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
2008800
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字段为索引,把相关信息列出来。
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
- awk模拟数据库做数据统计
- 使用awk做数据统计
- 使用awk + sort做数据统计
- 使用awk对广告数据中bid、imp中的ip做运营商分类统计
- Centreon数据库数据规整做月度性能统计
- AWK统计
- awk 统计
- 分类统计数据库数据
- Shell 统计导出数据 awk 里使用shell变量 参数
- oracle数据库数据统计问题
- 利用httpHandlers来做数据统计
- 使用Bitmap做数据分析统计
- 3.3用变动数据做模拟
- awk统计业务流量
- awk命令统计访问量
- awk统计独立IP
- awk统计排序
- awk 统计编程
- Java虚拟机之垃圾回收
- 新浪微博API中返回的中文表情转成相应图片的PHP代码
- 砸向敌人的炮弹:已知初速度让抛物线过任意点
- 树莓派(raspberry pi)播放flash 远程登录 视频播放
- 集群服务器--LVS/tun
- awk模拟数据库做数据统计
- IOS 测心跳的实现原理
- 轻试Nginx的负载均衡
- HTML学习笔记
- curl
- 什么是内核线程和用户线程
- 有些爱意,在岁月中飘遥
- awk日期时间转换
- MVC4 WebAPI(一)