Chinaunix

标题: unix内可以做到像excel般的统计吗? [打印本页]
作者: xmi    时间: 2011-02-10 12:45
标题: unix内可以做到像excel般的统计吗?
本帖最后由 xmi 于 2011-10-28 13:16 编辑

每个location都有一个log, 如usa-log, chn-log, kor-log ...... (有60个).
unix内可以做到像excel般的统计吗?
即想做:
1: 以usa-log的内容做一summary, 显示到每一个location的equipment (USAE01)有多少个event 12, event 440 .....
2: 以usa-log的内容做一个report, 显示如下:
lequipmentlocation: USA
event code: 12
09Feb11-13:45:00:108 USAE01
09Feb11-14:00:00:016 USAP05

event code: 440
09Feb11-14:00:00:035 USAP06
09Feb11-14:00:00:050 USAE06
09Feb11-14:00:00:051 USAP03
09Feb11-14:00:00:072 USAP03
09Feb11-14:00:00:075 USAP02
09Feb11-14:00:00:081 USAP05
.....

会不会天马行空呢?
-------------------------------------------------------
终于行了 ....
依cjaizss兄和yinyuemi (SS)的把 awk改为nawk就可以出现我想要的结果.

  2. cjaizss:
  3. nawk '$0!~/event/{next}{gsub(/'\''/,"",$(NF-3));a[$NF]=a[$NF]"\n" $1 " "$(NF-3)}END{print "lequipmentlocation: USA";for(i in a){print "event code:",i;sub(/\n/,"",a[i]);print a[i] "\n"}}'  log

  5. yinyuemi (SS):
  6. nawk -F "'| " 'BEGIN{print "lequipmentlocation: USA"} /event/{a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}END{for (i in a) print i, a[i]"\n"}'  log

  8. result:
  9. equipmentlocation: USA
  10. event code: 2
  11. 09Feb11-13:59:59:998 USAP01

  13. event code: 22
  14. 09Feb11-13:56:56:133 USAP07

  16. event code: 23
  17. 09Feb11-14:00:01:245 USAP02
  18. 09Feb11-14:00:01:253 USAP03
  19. 09Feb11-14:00:01:254 USAE01
  20. 09Feb11-14:00:01:255 USAP02
  21. 09Feb11-14:00:01:258 USAP03
  22. 09Feb11-14:00:01:260 USAP05

  24. event code: 9
  25. 09Feb11-13:59:59:982 USAP07

  27. event code: 77
  28. 09Feb11-13:55:54:283 USAP07

  30. event code: 44
  31. 09Feb11-13:45:00:151 USAP02
复制代码

但我怎也做不到依event code的大group做排列 (依event code 由细到大)? 请问要如何改呢?


另yinyuemi (SS)的出现统计错误, 可以如何更正呢?
  1. awk -F"'" '/event/{a[$2"\t"substr($3,5)]=++b[$3]}END{for(i in a) print i"\t"a[i]|"sort -n "}' FileName

  3. result:
  4. USAA01   event 33       1
  5. USAA05   event 55       1
  6. USAE01   event 12       1
  7. USAE01   event 23       3
  8. USAE06   event 440      2
  9. USAP01   event 2        1
  10. USAP02   event 156      1
  11. USAP02   event 23       4
  12. USAP02   event 44       1
  13. USAP02   event 440      5
  14. USAP03   event 23       5
  15. USAP03   event 440      4
  16. USAP05   event 1        1
  17. USAP05   event 23       6
  18. USAP05   event 440      6
  19. USAP06   event 440      1
  20. USAP07   event 22       1
  21. USAP07   event 66       1
  22. USAP07   event 77       1
  23. USAP07   event 9        1
复制代码

USA-log.zip

446 Bytes, 下载次数: 83

log
作者: cjaizss    时间: 2011-02-10 13:03
当然可以,awk吧
awk '/event code/{current=$NF;}/[0-9][0-9]:[0-9][0-9]:[0-9][0-9]/{a[current]++}END{for(i in a)print i,a[i]}'
作者: xmi    时间: 2011-02-10 13:14
本帖最后由 xmi 于 2011-02-10 13:16 编辑
当然可以,awk吧
awk '/event code/{current=$NF;}/[0-9][0-9]:[0-9][0-9]:[0-9][0-9]/{a[current]++}END{f ...
cjaizss 发表于 2011-02-10 13:03



    好像没有指定档名?  我按下enter良久也没有output?
作者: cjaizss    时间: 2011-02-10 13:25
后面跟文件名
作者: xmi    时间: 2011-02-10 17:50
回复 4# cjaizss


    我试验了, 把档名加在最后, 但结果它只display了'24'..........为什么呢?
作者: cjaizss    时间: 2011-02-10 22:30
回复  cjaizss


    我试验了, 把档名加在最后, 但结果它只display了'24'..........为什么呢?
xmi 发表于 2011-02-10 17:50



    不会吧
$ echo 'lequipmentlocation: USA
event code: 12
09Feb11-13:45:00:108 USAE01
09Feb11-14:00:00:016 USAP05

event code: 440
09Feb11-14:00:00:035 USAP06
09Feb11-14:00:00:050 USAE06
09Feb11-14:00:00:051 USAP03
09Feb11-14:00:00:072 USAP03
09Feb11-14:00:00:075 USAP02
09Feb11-14:00:00:081 USAP05 ' | awk '/event code/{current=$NF;}/[0-9][0-9]:[0-9][0-9]:[0-9][0-9]/{a[current]++}END{for(i in a)print i,a}'
12 2
440 6
作者: xmi    时间: 2011-02-10 22:43
回复 6# cjaizss


真的是呀! 在公司试验时, 我输入:
awk '/event code/{current=$NF;}/[0-9][0-9]:[0-9][0-9]:[0-9][0-9]/{a[current]++}END{for(i in a)print i,a}' USA-log

它的结果只有24 ...
为什么你要echo???
作者: cjaizss    时间: 2011-02-10 23:24
$ cat USA-log
lequipmentlocation: USA
event code: 12
09Feb11-13:45:00:108 USAE01
09Feb11-14:00:00:016 USAP05

event code: 440
09Feb11-14:00:00:035 USAP06
09Feb11-14:00:00:050 USAE06
09Feb11-14:00:00:051 USAP03
09Feb11-14:00:00:072 USAP03
09Feb11-14:00:00:075 USAP02
09Feb11-14:00:00:081 USAP05
$ awk '/event code/{current=$NF;}/[0-9][0-9]:[0-9][0-9]:[0-9][0-9]/{a[current]++}END{for(i in a)print i,a[i]}' USA-log
12 2
440 6
作者: cjaizss    时间: 2011-02-10 23:28
理会错了你的意思
作者: cjaizss    时间: 2011-02-10 23:38
本帖最后由 cjaizss 于 2011-02-10 23:51 编辑

1.
awk '!/event/{next}{a[$NF]++}END{for( i in a)print "event",i,a;}'
作者: cjaizss    时间: 2011-02-10 23:54
2.
awk '!/event/{next}{gsub(/'\''/,"",$(NF-3));a[$NF]=a[$NF]"\n" $1 " "$(NF-3)}END{print "lequipmentlocation: USA";for(i in a){print "event code:",i;sub(/\n/,"",a[i]);print a[i];print "";};}' USA-log
作者: yinyuemi    时间: 2011-02-11 03:07
  1. 1. awk -F"'" '/event/{a[$2"\t"substr($3,5)]=++b[$3]}END{for(i in a) print i"\t"a[i]|"sort -n "}' file

  3. 2. awk -F "'| " 'BEGIN{print "lequipmentlocation: USA"}
  4. /event/ {a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}
  5. END{for (i in a) print i, a[i]"\n"}' file
复制代码
作者: xmi    时间: 2011-02-11 23:12
谢谢两位, 今天在公司没空呢, 待我明天一试.
作者: xmi    时间: 2011-02-14 13:07
1.
awk '!/event/{next}{a[$NF]++}END{for( i in a)print "event",i,a;}'
cjaizss 发表于 2011-02-10 23:38



    % awk '!/event/{next}{a[$NF]++}END{for( i in a)print "event",i,a;}' USA-log
    /event/{next: Event not found   <======= 不成功哩
作者: xmi    时间: 2011-02-14 13:08
2.
awk '!/event/{next}{gsub(/'\''/,"",$(NF-3));a[$NF]=a[$NF]"\n" $1 " "$(NF-3)}END{print "lequipmen ...
cjaizss 发表于 2011-02-10 23:54



    % awk '!/event/{next}{gsub(/'\''/,"",$(NF-3));a[$NF]=a[$NF]"\n" $1 " "$(NF-3)}END{print "lequipmentlocation: USA";for(i in a){print "event code:",i;sub(/\n/,"",a);print a;print "";};}' usa-log
/event/{next: Event not found  <===== 这个也不行呀?
作者: xmi    时间: 2011-02-14 13:19
本帖最后由 xmi 于 2011-02-14 13:26 编辑
yinyuemi 发表于 2011-02-11 03:07



    1. 那个成功, 但可以加入header吗?

   2. 那个就不行了 , 我是直接copy到unix内...
% awk -F "'| " 'BEGIN{print "lequipmentlocation: USA"}
/event/ {a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}
END{for (i in a) print i, a"\n"}'Unmatched '
% Variable syntax

   如把它写成一行的话, 也不成功:
% awk -F "'| " 'BEGIN{print "lequipmentlocation: USA"}/event/ {a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(N
F-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}END{for (i in a) print i, a"\n"}' log
awk: syntax error near line 1
awk: bailing out near line 1
作者: yinyuemi    时间: 2011-02-14 13:37
回复 16# xmi

第二个结果是这个么？
  1. 1. awk -F"'" 'BEGIN{"header1","header2","header3","header4"}/event/{a[$2"\t"substr($3,5)]=++b[$3]}END{for(i in a) print i"\t"a[i]|"sort -n "}'

  3. 2. awk -F "'| " 'BEGIN{print "lequipmentlocation: USA"} /event/ {a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}END{for (i in a) print i, a[i]"\n"}' file

  5. event code: 77
  6. 09Feb11-13:55:54:283    USAP07

  8. event code: 9
  9. 09Feb11-13:59:59:982    USAP07

  11. event code: 12
  12. 09Feb11-13:45:00:108    USAE01

  14. event code: 22
  15. 09Feb11-13:56:56:133    USAP07

  17. event code: 23
  18. 09Feb11-14:00:01:245    USAP02
  19. 09Feb11-14:00:01:253    USAP03
  20. 09Feb11-14:00:01:254    USAE01
  21. 09Feb11-14:00:01:255    USAP02
  22. 09Feb11-14:00:01:258    USAP03
  23. 09Feb11-14:00:01:260    USAP05

  25. event code: 33
  26. 09Feb11-13:45:00:716    USAA01

  28. event code: 440
  29. 09Feb11-14:00:00:035    USAP06
  30. 09Feb11-14:00:00:050    USAE06
  31. 09Feb11-14:00:00:051    USAP03
  32. 09Feb11-14:00:00:072    USAP03
  33. 09Feb11-14:00:00:075    USAP02
  34. 09Feb11-14:00:00:081    USAP05

  36. event code: 1
  37. 09Feb11-14:00:00:016    USAP05

  39. event code: 44
  40. 09Feb11-13:45:00:151    USAP02

  42. event code: 2
  43. 09Feb11-13:59:59:998    USAP01

  45. event code: 55
  46. 09Feb11-13:50:40:303    USAA05

  48. event code: 156
  49. 09Feb11-13:55:48:228    USAP02

  51. event code: 66
  52. 09Feb11-13:56:52:380    USAP07

复制代码
作者: xmi    时间: 2011-02-14 13:43
本帖最后由 xmi 于 2011-02-14 13:55 编辑
回复  xmi

第二个结果是这个么？
yinyuemi 发表于 2011-02-14 13:37
1.awk -F"'" 'BEGIN{"header1","header2","header3","header4"}/event/{a[$2"\t"substr($3,5)]=++b[$3]}END{for(i in a) print i
"\t"a|"sort -n "}' log
2. awk -F "'| " 'BEGIN{print "lequipmentlocation: USA"} /event/ {a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}END{for (i in a) print i, a"\n"}' file



1.
% awk -F"'" 'BEGIN{"header1","header2","header3","header4"}/event/{a[$2"\t"substr($3,5)]=++b[$3]}END{for(i in a) print i
"\t"a|"sort -n "}' log
awk: syntax error near line 1
awk: illegal statement near line 1

2.
你个是我想要做到的, 但我这儿一行就出了以下error:
%awk -F "'| " 'BEGIN{print "lequipmentlocation: USA"} /event/ {a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(
NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}END{for (i in a) print i, a"\n"}' log
awk: syntax error near line 1
awk: bailing out near line 1
%
作者: cjaizss    时间: 2011-02-14 13:43
你的awk版本?奇怪.我似乎并没有用版本的特有语法啊
把/event/换成$0~/event/
把!/event/换成$0!~/event/
试下呢
作者: xmi    时间: 2011-02-14 13:50
你的awk版本?奇怪.我似乎并没有用版本的特有语法啊
把/event/换成$0~/event/
把!/event/换成$0!~/event/
...
cjaizss 发表于 2011-02-14 13:43



试过了, 两个方法都说"illegal reference to array a" record number 25

% awk '$0!~/event/{next}{a[$NF]++}END{for( i in a)print "event",i,a;}' log
awk: illegal reference to array a
record number 25
%

% awk '$0~/event/{next}{a[$NF]++}END{for( i in a)print "event",i,a;}' log
awk: illegal reference to array a
record number 25
%
作者: cjaizss    时间: 2011-02-14 13:55
试过了, 两个方法都说"illegal reference to array a" record number 25

% awk '$0!~/event/{next ...
xmi 发表于 2011-02-14 13:50



    ...不好意思,我应该用code包起来的,后面的括号掉了
  1. print "event",i,a[i]
复制代码
作者: yinyuemi    时间: 2011-02-14 13:57
回复 18# xmi


   开始改下呢,加个转义：
awk -F "\'| "
作者: xmi    时间: 2011-02-15 10:38
本帖最后由 xmi 于 2011-02-15 10:54 编辑
...不好意思,我应该用code包起来的,后面的括号掉了
cjaizss 发表于 2011-02-14 13:55


这个没有error出现, 但只出现下面两行结果....
  1. %awk '$0~/event/{next}{a[$NF]++}END{for( i in a)print "event",i,a[i];}' log
  2. event  1
  3. event 09Feb11-14:00:01:052 1
  4. %
复制代码
那么第二那个要怎改呢?
  1. awk '$0!~/event/{next}{gsub(/'\''/,"",$(NF-3));a[$NF]=a[$NF]"\n" $1 " "$(NF-3)}END{print "lequipmentlocation: USA";for(i in a){print "event code:",i;sub(/\n/,"",a[i]);print a[i];print "";};}' log
  2. awk: illegal statement near line 1
  3. awk: syntax error near line 1
  4. awk: illegal statement near line 1
  5. awk: syntax error near line 1
  6. %
复制代码
谢谢您!
作者: xmi    时间: 2011-02-15 10:56
本帖最后由 xmi 于 2011-02-15 11:15 编辑
回复  xmi


   开始改下呢,加个转义：
awk -F "\'| "
yinyuemi 发表于 2011-02-14 13:57



也不行呢....
  1. % awk -F "\'| " 'BEGIN{"header1","header2","header3","header4"}/event/{a[$2"\t"substr($3,5)]=++b[$3]}END{for(i in a) print i"\t"a[i]|"sort -n "}' log
  2. awk: syntax error near line 1
  3. awk: bailing out near line 1
  4. %
  5. % awk -F "\'| " 'BEGIN{print "lequipmentlocation: USA"} /event/ {a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}END{for (i in a) print i, a[i]"\n"}'log
  6. awk: syntax error near line 1
  7. awk: bailing out near line 1
  8. %
复制代码
作者: yinyuemi    时间: 2011-02-15 12:17
回复 24# xmi


    要不你把代码存成文件script.awk
然后执行 awk -f script.awk urfile
希望这样可以
作者: cjaizss    时间: 2011-02-15 12:26
这个没有error出现, 但只出现下面两行结果....那么第二那个要怎改呢?谢谢您!
xmi 发表于 2011-02-15 10:38



    你这个版本偶不知道了,没有测试环境.不过你可以试着把我的那个字符串连接改用sprintf试试?
作者: xmi    时间: 2011-02-15 14:40
回复  xmi


    要不你把代码存成文件script.awk
然后执行 awk -f script.awk urfile
希望这样可以
yinyuemi 发表于 2011-02-15 12:17



为何老是显示以下:
awk: syntax error near line 1
awk: bailing out near line 1  

看来我真是没用了.
作者: yinyuemi    时间: 2011-02-15 14:44
回复 27# xmi


    改下前面的部分：
  1. awk  'BEGIN{FS="|";print "lequipmentlocation: USA"}
复制代码
作者: xmi    时间: 2011-02-15 15:40
回复  xmi

    改下前面的部分：
yinyuemi 发表于 2011-02-15 14:44
  1. awk  'BEGIN{FS="|";print "lequipmentlocation: USA"} /event/ {a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}END{for (i in a) print i, a[i]"\n"}' file
复制代码
把您的存成script.awk 再运行 awk -f t.awk log:
awk: syntax error near line 1
awk: bailing out near line 1

再把那段code在command prompt下行一下:
awk: syntax error near line 1
awk: illegal statement near line 1


我心灰了.  
作者: xmi    时间: 2011-10-28 13:18
终于行了 ....
依cjaizss兄和yinyuemi (SS)的把 awk改为nawk就可以出现我想要的结果.


  3. cjaizss:
  4. nawk '$0!~/event/{next}{gsub(/'\''/,"",$(NF-3));a[$NF]=a[$NF]"\n" $1 " "$(NF-3)}END{print "lequipmentlocation: USA";for(i in a){print "event code:",i;sub(/\n/,"",a[i]);print a[i] "\n"}}'  log


  7. yinyuemi (SS):
  8. nawk -F "'| " 'BEGIN{print "lequipmentlocation: USA"} /event/{a["event code: "$NF]>0?a["event code: "$NF]="\n"$1"\t"$(NF-4):a["event code: "$NF]=a["event code: "$NF]"\n" $1"\t"$(NF-4)}END{for (i in a) print i, a[i]"\n"}'  log

  10. result:
  11. equipmentlocation: USA
  12. event code: 2
  13. 09Feb11-13:59:59:998 USAP01

  15. event code: 22
  16. 09Feb11-13:56:56:133 USAP07

  18. event code: 23
  19. 09Feb11-14:00:01:245 USAP02
  20. 09Feb11-14:00:01:253 USAP03
  21. 09Feb11-14:00:01:254 USAE01
  22. 09Feb11-14:00:01:255 USAP02
  23. 09Feb11-14:00:01:258 USAP03
  24. 09Feb11-14:00:01:260 USAP05

  26. event code: 9
  27. 09Feb11-13:59:59:982 USAP07

  29. event code: 77
  30. 09Feb11-13:55:54:283 USAP07

  32. event code: 44
  33. 09Feb11-13:45:00:151 USAP02
复制代码
但我怎也做不到依event code的大group做排列 (依event code 由细到大)? 请问要如何改呢?


另yinyuemi (SS)的出现统计错误, 可以如何更正呢?
  1. awk -F"'" '/event/{a[$2"\t"substr($3,5)]=++b[$3]}END{for(i in a) print i"\t"a[i]|"sort -n "}' FileName

  3. result:
  4. USAA01   event 33       1
  5. USAA05   event 55       1
  6. USAE01   event 12       1
  7. USAE01   event 23       3
  8. USAE06   event 440      2
  9. USAP01   event 2        1
  10. USAP02   event 156      1
  11. USAP02   event 23       4
  12. USAP02   event 44       1
  13. USAP02   event 440      5
  14. USAP03   event 23       5
  15. USAP03   event 440      4
  16. USAP05   event 1        1
  17. USAP05   event 23       6
  18. USAP05   event 440      6
  19. USAP06   event 440      1
  20. USAP07   event 22       1
  21. USAP07   event 66       1
  22. USAP07   event 77       1
  23. USAP07   event 9        1
复制代码
作者: waker    时间: 2011-10-28 13:30
既然楼主精于excel,大家干嘛还要强迫用shell解决,楼主可以考虑一下类excel的工具嘛,比如openoffice里的calc
作者: xmi    时间: 2011-10-28 16:53
回复 31# waker


先生, 我不是精于excel, 为什么这么说呢....

因这系统记录有十多万条, 所以才有所问 ... 如能做到真的帮助很大..
在这我也真的很多谢大家用心的帮忙......



欢迎光临 Chinaunix (http://bbs.chinaunix.net/) Powered by Discuz! X3.2