AWK 之 RS、ORS与FS、OFS

字符解释：

RS　　记录分隔符　　　　　　　　　　　　　　　　FS　　字段分隔符

ORS　　输出当前记录分隔符　　　　　　　　　　　OFS　　输出字段分隔符

 

示例：

（1）awk 'BEGIN {RS="|";}{print $0}' filename

输出结果文件：　　a|b|c

 

（2）awk 'BEGIN {ORS="---"}{print $0}' filename

输出结果文件：　　a\nb\nc

 

（3）FS默认"空格"

       awk 'BEGIN {FS="---"}{print $1,$2,$3}' filename

输出结果文件：　　a---b---c---

 

（4）awk 'BEGIN {FS="---"; OFS="***"}{print $1,$2,$3}' filename

 

其他：echo "abc" | awk '{OFS="."}{NF=NF;print NF,$0}'

 

SQL 与 awk 功能互转

表 consumer user

1.某字段去重
select distinct(date) from consumer;
awk '!a[$3]++{print $3}' consumer

select distinct(*) from consumer;
awk '!a[$0]++' consumer

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

 

3.取前多少条记录
select * from consumer limit 2;
awk 'NR<=2' consumer
awk 'NR>2{exit}1' consumer #performance is better

 

4.分组求和：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=spit(a[i],b,",");if(c>2){sum=0;for(j in b){sum+=b[j]};print i"\t"c"\t"sum}}' consumer

 

5.模糊查询；关键词：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

 

6.多表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

 

7.多表水平联接，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

 

8.随机抽样统计，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