awk处理ip数据库

iziang

现在有一个ip数据库文件，大小是220M，每一行的格式如下
"起始ip","终止ip","国家","省","城市"

下面是几行例子

"1.4.4.0","1.4.4.255","CN","Beijing","Beijing"
"1.5.0.0","1.5.255.255","JP","Tokyo","Chiyoda"
"1.6.0.0","1.7.255.255","IN","Tamil Nadu","Chennai"
"1.8.0.0","1.8.255.255","CN","Beijing","Beijing"
"1.9.0.0","1.9.1.255","MY","Federal Territory of Kuala Lumpur","Kuala Lumpur"
"1.9.2.0","1.9.2.255","MY","Selangor","Cyberjaya"
"1.11.0.0","1.11.255.255","KR","Gyeonggi-do","Bucheon-si"
"1.21.0.0","1.21.255.255","JP","Tokyo","Chiyoda"

每个城市在文件中可能有多个记录

现在要按照拥有ip地址的数量对城市排序，输出的格式如下

城市名：ip总数

大家有没有什么好的办法？

关阴月飞

开会中，等会贴代码, 来晚了，楼下都写好了，方法是一样的：

1. [root@ awktest]# awk -F\" 'function ip(s){split(s,a,".");return a[1]*256^3+a[2]*256^2+a[3]*256+a[4]}{b[$(NF-1)]+=(ip($4)-ip($2)+1)}END{for(i in b)print i":"b[i]|"sort -t: -k2nr"}' city
   
2. Chennai:131072
   
3. Chiyoda:131072
   
4. Beijing:65792
   
5. Bucheon-si:65536
   
6. Kuala Lumpur:512
   
7. Cyberjaya:256
   
8. [root@ awktest]# cat city
   
9. "1.4.4.0","1.4.4.255","CN","Beijing","Beijing"
   
10. "1.5.0.0","1.5.255.255","JP","Tokyo","Chiyoda"
    
11. "1.6.0.0","1.7.255.255","IN","Tamil Nadu","Chennai"
    
12. "1.8.0.0","1.8.255.255","CN","Beijing","Beijing"
    
13. "1.9.0.0","1.9.1.255","MY","Federal Territory of Kuala Lumpur","Kuala Lumpur"
    
14. "1.9.2.0","1.9.2.255","MY","Selangor","Cyberjaya"
    
15. "1.11.0.0","1.11.255.255","KR","Gyeonggi-do","Bucheon-si"
    
16. "1.21.0.0","1.21.255.255","JP","Tokyo","Chiyoda"
    
17. [root@ awktest]#

复制代码

liaosnet

试一下这样。
先做个临时文件，去除双引号。

1. 
   
2. sed 's#"##g' ufile > tmpfile
   
3. 
   
4. awk -F',' 'function ip2num(s){split(s,a,".");return a[1]*16777216+a[2]*65536+a[3]*256+a[4]+1;}{print $5,ip2num($2)-ip2num($1);}' tmpfile | sort -k2nr
   
5. 
   

复制代码

reb00t

1. awk -F'"' 'function abs(m){c=256;split(m,ip,".");return ip[4]+ip[3]*c+ip[2]*c^2+ip[1]*c^3}{a[$(NF-1)]=a[$(NF-1)]?a[$(NF-1)]+abs($4)-abs($2):abs($4)-abs($2)}END{for(i in a)print i":"a[i]}' 2.txt   
   
2. Bucheon-si:65535
   
3. Kuala Lumpur:511
   
4. Cyberjaya:255
   
5. Chiyoda:131070
   
6. Chennai:131071
   
7. Beijing:65790

复制代码

liaosnet

回复 4# reb00t


      方法一致。。。

haokoo

awk 'BEGIN{FS=",";}
function a2n( x)
{
l=split(x,a,".");
return a[1]*256*256*256+a[2]*256*256+a[3]*256+a[4];
}{ gsub(/\"/,"",$5);print $5 ":" a2n($2)-a2n($1) }' yourfile | sort -t ":" -nrk 2

我来凑热闹

iziang

谢谢大家了，写的都很好，学到了很多。有几个朋友可能没看清题目，城市可能有多条记录的，要计算每个城市总的ip数量

liaosnet

回复 7# iziang


    额 。那要再改一下。 话说，LZ就不能举一反三，只是需要结果？

1. 
   
2. sed 's#"##g' ufile > tmpfile
   
3. 
   
4. awk -F',' 'function ip2num(s){split(s,a,".");return a[1]*16777216+a[2]*65536+a[3]*256+a[4]+1;}{sum[$5]+=(ip2num($2)-ip2num($1));}END{for(i in sum)print i,sum[i];}' tmpfile | sort -k2nr
   
5. 
   

复制代码