常用统计分析 SQL 在 AWK 中的实现（转）

最新推荐文章于 2024-07-31 17:06:19 发布

weixin_34015336

最新推荐文章于 2024-07-31 17:06:19 发布

阅读量107

点赞数

文章标签： awk shell 数据库

原文链接：http://www.cnblogs.com/payton/p/4223560.html

版权

转自：http://my.oschina.net/leejun2005/blog/100710

最近有需求需要本地处理一些临时的数据，用做统计分析。如果单纯的 MYSQL 也能实现，
不过一堆临时数据这样从 mysql 导来导去还是挺麻烦的，比较理想的选择是本机装个 cygwin
环境，然后可以用 awk 等 shell 工具做即时处理。

本文主要讲述如何在 awk 中实现 SQL 的常用操作，当做个简单的 awk 入门分享。
虽然文中部分 awk 会有其它更简洁高效的 shell 命令去完成，亦或是其它语言去完成，
但这都不在本文的讨论范畴。

注：本文所用到的两个测试文件 user、consumer，分别模拟两张 SQL 表：

user 表，字段：
id name addr

1 zhangsan hubei
3 lisi tianjin
4 wangmazi guangzhou
2 wangwu beijing

consumer 表，字段：
id cost date

1 15 20121213
2 20 20121213
3 100 20121213
4 99 20121213
1 25 20121114
2 108 20121114
3 100 20121114
4 66 20121114
1 15 20121213
1 115 20121114

测试环境：
OS 版本：
uname -a
CYGWIN_NT-6.1 june-PC 1.7.9(0.237/5/3) 2011-03-29 10:10 i686 Cygwin
awk 版本：
awk --version
GNU Awk 3.1.8

1、查询整张表记录，where 条件过滤，关键词：where

select * from user; awk 1 user;
select * from consumer where cost > 100;
awk '$2>100' consumer

2、对某个字段去重，或者按记录去重，关键词：distinct

select distinct(date) from consumer;
awk '!a[$3]++{print $3}' consumer
select distinct(*) from consumer;
awk '!a[$0]++' consumer

3、记录按序输出，关键词：order by

select id from user order by id;
awk '{a[$1]}END{asorti(a);for(i=1;i<=length(a);i++){print a[i]}}' user

4、取前多少条记录，关键词：limit

select * from consumer limit 2;
awk 'NR<=2' consumer
awk 'NR>2{exit}1' consumer # performance is better

5、分组求和统计，关键词：group by、having、sum、count

select id, count(1), sum(cost) from consumer group by id having count(1) > 2;
awk '{a[$1]=a[$1]==""?$2:a[$1]","$2}END{for(i in a){c=split(a[i],b,",");if(c>2){sum=0;for(j in b){sum+=b[j]};print i"\t"c"\t"sum}}}' consume
或：awk '{a[$1]+=$2;b[$1]++;}END{for(i in a){if(b[i]>2){print i"\t"b[i]"\t"a[i];}}}' consumer.log

6、模糊查询，关键词：like（like属于通配，也可正则 REGEXP）

select name from user where name like 'wang%';
awk '$2 ~/^wang/{print $2}' user
select addr from user where addr like '%bei';
awk '/.*bei$/{print $3}' user
select addr from user where addr like '%bei%';
awk '$3 ~/bei/{print $3}' user

7、多表 join 关联查询，关键词：join

select a.* , b.* from user a inner join consumer b  on a.id = b.id and b.id = 2;
awk 'ARGIND==1{a[$1]=$0;next}{if(($1 in a)&&$1==2){print a[$1]"\t"$2"\t"$3}}' user consumer

8、多表水平联接，关键词：union all

select a.* from user a union all select b.* from user b;
awk 1 user user
select a.* from user a union select b.* from user b;
awk '!a[$0]++' user user

9、随机抽样统计，关键词：order by rand()

SELECT * FROM consumer ORDER BY RAND() LIMIT 2;
awk 'BEGIN{srand();while(i<2){k=int(rand()*10)+1;if(!(k in a)){a[k];i++}}}(NR in a)' consumer

10、行列转换，关键词：SUM(IF())、WITH ROLLUP
mysql 写法：http://my.oschina.net/leejun2005/blog/77796
awk 写法：http://hi.baidu.com/leejun_2005/item/2bac30c2b97e5e56ad00ef86

11、awk 小应用之 RTX 订餐统计：

1、功能：
统计 rtx 聊天记录中的订餐信息，包括且限于：菜名、人员姓名、人数
2、支持的功能：
订餐、取消、修改
3、格式：
订餐：“+1 空格菜名”，如： “+1 鸡腿” // 不含双引号
取消：“-1” 即可，如： “-1” // 不含双引号
修改：格式同订餐一样，会自动根据姓名覆盖
4、使用限制与注意事项：
（1）必须严格遵守格式，否则会统计错误，例如：菜名和+1-1之间要空格分隔，且必须 -1+1 开头
（2）如果一人代订多人，需要复制格式，修改姓名，然后发布多条信息，
格式：
//代订 // 这一行一定要带上，不能以 +-( 字符开头
(userName) //要以 ( 打头，如果你自己点多份，请在名字后面带上数字序号，如 userName1
+1 菜名
暂不支持直接 “+2 菜名” 这种形式，因为最后需要按姓名汇总
（3）此 awk 脚本需要在 4.0 版本以上运行，因为 4.0 以下的 HashMap 不支持中文 key。
测试用例：

echo "
user(统计测试) 18:30:52
对吧
user(统计测试) 18:30:55
下单了，嗯
user(张三) 18:31:11
+1 西瓜泡方便面
user(统计测试) 18:30:52
对吧 -1 测试
user(统计测试) 18:30:52
// 这是帮人代订的测试，这行一定要，随便写点啥都行 -------------------- 测试代订功能
(代订测试人)
+1 豆腐脑-甜的
。。。。。。。。。
(代订测试人2)
+1 豆腐脑-酸的
user(统计测试) 18:30:55
下单了，嗯
user(李四) 18:31:11
+1 大排
user(李四) 18:31:11
-1
user(统计测试) 18:30:52
对吧
user(统计测试) 18:30:55
下单了，嗯
user(张三) 18:31:11
+1 带鱼
user(王麻子) 18:31:11
+1 大蒜
user(统计测试) 18:30:55
下单了，嗯
user(测试程序) 18:31:11
+1 唐僧肉
user(测试程序1) 18:31:11
+1 带鱼
user(赵六) 18:31:11
+1 大蒜
"|\
awk '/\(/{gsub(/.*\(|\).*/,"");name=$0;getline;if(!($0~/^(\+|-)/))next;a[name]=$0}END{for(i in a){split(a[i],b," ");if(b[2]=="")continue;c[b[2]]=c[b[2]]==""?i:c[b[2]]","i};for(i in c){split(c[i],d,",");print i":\t"c[i]"\t"length(d)}}'|column -t

结果：
带鱼: 测试程序1,张三 2
唐僧肉: 测试程序 1
大蒜: 赵六,王麻子 2
豆腐脑-酸的: 代订测试人2 1
豆腐脑-甜的: 代订测试人 1

12、查找父ID

 
         echo 
         "1 0 
        
         11 1 
        
         111 11 
        
         1111 111 
         "|awk '{a[$1]=$2;if($2==0){b[$1]=$12}}END{for(i in a){j=i;c=0;while(a[j]!=0){j=a[j];c++};print i" 
         \t 
         "j" 
         \t"c}}'

结果：

 
         id      
         rootId  level 
        
         1111    1       3 
        
         111     1       2 
        
         11      1       1 
        
         1       1       0

关于 id 间父子关系的建立与查找，还可以参考这个例子中的 python 写法：

python 数据结构转换,将线性元祖转换成字典树：

http://segmentfault.com/q/1010000000415526

 
         t  
         = 
         ( 
        
 
              
         ( 
         1 
         ,  
         - 
         1 
         ,  
         'python' 
         ), 
        
 
              
         ( 
         2 
         ,  
         - 
         1 
         ,  
         'ruby' 
         ), 
        
 
              
         ( 
         3 
         ,  
         - 
         1 
         ,  
         'php' 
         ), 
        
 
              
         ( 
         4 
         ,  
         - 
         1 
         ,  
         'lisp' 
         ), 
        
 
              
         ( 
         5 
         ,  
         1 
         ,  
         'flask' 
         ), 
        
 
              
         ( 
         6 
         ,  
         1 
         ,  
         'django' 
         ), 
        
 
              
         ( 
         7 
         ,  
         1 
         ,  
         'webpy' 
         ), 
        
 
              
         ( 
         8 
         ,  
         2 
         ,  
         'rails' 
         ), 
        
 
              
         ( 
         9 
         ,  
         3 
         ,  
         'zend' 
         ), 
        
 
              
         ( 
         10 
         ,  
         6 
         ,  
         'dblog' 
         ) 
        
 
         ) 
        
 
         # fid 无序版 
        
 
         from 
         itertools  
         import 
         groupby 
        
 
         from 
         operator  
         import 
         itemgetter as get 
        
 
         from 
         pprint  
         import 
         pprint 
        

            
        
 
         # group by fid 
        
 
         tmp  
         = 
         dict 
         ([(k,  
         list 
         (rows))  
         for 
         k, rows  
         in 
         groupby( 
         sorted 
         (t, key 
         = 
         get( 
         1 
         )), get( 
         1 
         ))]) 
        

            
        
 
         def 
         map_fun(row): 
        
 
            
         item  
         = 
         dict 
         ( 
         zip 
         (( 
         'id' 
         ,  
         'fid' 
         ,  
         'title' 
         ), row)) 
        
 
            
         if 
         row[ 
         0 
         ]  
         in 
         tmp: 
        
 
              
         item[ 
         'son' 
         ]  
         = 
         find_children(row[ 
         0 
         ]) 
        
 
            
         return 
         item; 
        

            
        
 
         def 
         find_children(parent): 
        
 
              
         return 
         map 
         (map_fun, tmp[parent]) 
        

            
        
 
         pprint(find_children( 
         - 
         1 
         )) 
        

 
         t  
         = 
         ( 
        
 
              
         ( 
         1 
         ,  
         - 
         1 
         ,  
         'python' 
         ), 
        
 
              
         ( 
         2 
         ,  
         - 
         1 
         ,  
         'ruby' 
         ), 
        
 
              
         ( 
         3 
         ,  
         - 
         1 
         ,  
         'php' 
         ), 
        
 
              
         ( 
         4 
         ,  
         - 
         1 
         ,  
         'lisp' 
         ), 
        
 
              
         ( 
         5 
         ,  
         1 
         ,  
         'flask' 
         ), 
        
 
              
         ( 
         6 
         ,  
         1 
         ,  
         'django' 
         ), 
        
 
              
         ( 
         7 
         ,  
         1 
         ,  
         'webpy' 
         ), 
        
 
              
         ( 
         8 
         ,  
         2 
         ,  
         'rails' 
         ), 
        
 
              
         ( 
         9 
         ,  
         3 
         ,  
         'zend' 
         ), 
        
 
              
         ( 
         10 
         ,  
         6 
         ,  
         'dblog' 
         ) 
        
 
         ) 
        
 
         # fid 有序版 
        
 
         from 
         pprint  
         import 
         pprint 
        

            
        
 
         l  
         = 
         [] 
        
 
         entries  
         = 
         {} 
        

            
        
 
         for 
         id 
         , fid, title  
         in 
         t: 
        
 
              
         entries[ 
         id 
         ]  
         = 
         entry  
         = 
         { 
         'id' 
         :  
         id 
         ,  
         'fid' 
         : fid,  
         'title' 
         : title} 
        
 
              
         if 
         fid  
         = 
         = 
         - 
         1 
         : 
        
 
                  
         l.append(entry) 
        
 
              
         else 
         : 
        
 
                  
         parent  
         = 
         entries[fid] 
        
 
                  
         parent.setdefault( 
         'son' 
         , []).append(entry) 
        

            
        
 
         pprint(l) 
        