天天看點
傳回

awk的類sql資料處理

轉自ywlscpl.cublog.cn

工作中經常處理文本資料，以前經常接觸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 -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）的不統計，結果按月份、操作員号排序

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 -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元的年份。

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 -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
就寫到這吧，編資料，想例子好累。 
     
  1.  [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       
    2.       
  2.   updateh.360safe.com^M         percent:0.641026         
    3.       
  3.   www.360safe.com^M             percent:0.179487         
    4.       
  4.   se.360.cn^M                   percent:0.102564         
    5.       
  5.   bo.duba.net                   percent:0.051282         
    6.       
  6.   w6.safe.lfc.qihoo.net^M       percent:0.025641         
    7.       
  7.  [root@station11 shell]# cat 2.fi        
    8.       
  8.  cat: 2.fi: No such file or directory        
    9.       
  9.  [root@station11 shell]# cat 2.file        
    10.       
  10.  cat: 2.file: No such file or directory        
    11.       
  11.  [root@station11 shell]# cat 2.txt        
    12.       
  12.  Host: bo.duba.net:8080^M        
    13.       
  13.  Host: bo.duba.net:8080^M        
    14.       
  14.  Host: updateh.360safe.com^M        
    15.       
  15.  Host: www.360safe.com^M        
    16.       
  16.  Host: updateh.360safe.com^M        
    17.       
  17.  Host: se.360.cn^M        
    18.       
  18.  Host: updateh.360safe.com^M        
    19.       
  19.  Host: updateh.360safe.com^M        
    20.       
  20.  Host: www.360safe.com^M        
    21.       
  21.  Host: updateh.360safe.com^M        
    22.       
  22.  Host: www.360safe.com^M        
    23.       
  23.  Host: www.360safe.com^M        
    24.       
  24.  Host: se.360.cn^M        
    25.       
  25.  Host: www.360safe.com^M        
    26.       
  26.  Host: updateh.360safe.com^M        
    27.       
  27.  Host: updateh.360safe.com^M        
    28.       
  28.  Host: w6.safe.lfc.qihoo.net^M        
    29.       
  29.  Host: updateh.360safe.com^M        
    30.       
  30.  Host: updateh.360safe.com^M        
    31.       
  31.  Host: www.360safe.com^M        
    32.       
  32.  Host: updateh.360safe.com^M        
    33.       
  33.  Host: se.360.cn^M        
    34.       
  34.  Host: updateh.360safe.com^M        
    35.       
  35.  Host: updateh.360safe.com^M        
    36.       
  36.  Host: updateh.360safe.com^M        
    37.       
  37.  Host: updateh.360safe.com^M        
    38.       
  38.  Host: updateh.360safe.com^M        
    39.       
  39.  Host: updateh.360safe.com^M        
    40.       
  40.  Host: www.360safe.com^M        
    41.       
  41.  Host: updateh.360safe.com^M        
    42.       
  42.  Host: updateh.360safe.com^M        
    43.       
  43.  Host: updateh.360safe.com^M        
    44.       
  44.  Host: se.360.cn^M        
    45.       
  45.  Host: updateh.360safe.com^M        
    46.       
  46.  Host: updateh.360safe.com^M        
    47.       
  47.  Host: updateh.360safe.com^M        
    48.       
  48.  Host: updateh.360safe.com^M        
    49.       
  49.  Host: updateh.360safe.com^M        
    50.
職場 資料庫 休閑 用awk實作sql語句
上一篇: 使用shc保護shell腳本源碼
下一篇: 淺談mysql慢查詢和msl更新檔（microslow patch）

繼續閱讀