awk进行统计分析SQL

发表于 2021-05-31 更新于 2023-01-25

原文链接：https://blog.csdn.net/Learn_ZhangK/article/details/80392512

1.简介

1.1国际惯例

按照国际惯例先来理论的介绍。

awk是一个强大的文本分析工具，相对于grep的查找，sed的编辑，awk在其对数据分析并生成报告时，显得尤为强大。简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。

awk有3个不同版本: awk、nawk和gawk，未作特别说明，一般指gawk，gawk 是 AWK 的 GNU 版本。

awk其名称得自于它的创始人 Alfred Aho 、Peter Weinberger 和 Brian Kernighan 姓氏的首个字母。实际上 AWK 的确拥有自己的语言： AWK 程序设计语言，三位创建者已将它正式定义为“样式扫描和处理语言”。它允许您创建简短的程序，这些程序读取输入文件、为数据排序、处理数据、对输入执行计算以及生成报表，还有无数其他的功能。

2 实践

2.1 素材

user表(以空格分隔的txt)

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

consumer表 (以空格分隔的txt)

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

2.2 需求

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

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

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

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

2.2.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

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

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

2.2.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}}}' consumer

2.2.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

2.2.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

2.2.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

2.2.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

2.2.10 复杂的awk

awk -F'\t' 'BEGIN{OFS="\t"}{if(NR==FNR){install[$1]=$2}else{print $1,install[$1],$2}}' install.txt alive.txt >test.txt

实现需求：将install.txt中的数据读出，放入变量install[]中，类似java的map，第一列为key，第二列为value；读完install.txt后，再读入alive.txt，将alive.txt的第一列，install.txt中的对应的第二列，还有alive.txt的第二列，一起输出出来。

补充

关于简单的联表查询

$ cat  file1
23  中西
98  红
34  西瓜
53  巴巴

$ cat  file2
巴巴    c
红      b
西瓜    d
中西    f

# 希望得到如下结果
23  f
98  b
34  d
53  c

实现如下：

注意，数组a，自始自终都没有$前缀。

awk 'NR==FNR{a[$1]=$2}NR>FNR{print $1,a[$2]}' file2 file1
awk 'ARGIND==1{a[$1]=$2}ARGIND==2{print $1,a[$2]}' file2 file1 # 或这种

原理解释：

明白是awk是顺序处理file1、file2、file3…

1、NR=已处理的记录数;FNR= 当前文件处理的记录数,明确了这个，那么处理第一个文件时，NR是等于FNR的，处理第二个文件时，NR>FNR
2、处理第一个文件file2时，只是数组赋值，因为此时NR==FNR,即为：
```
a[巴巴]=c
a[红]=b
a[西瓜]=d
a[中西]=f
```
3、继续处理第二个文件file1，这时满足NR>FNR的判断条件，所以打印print $1,a[$2],即为：
```
print 23,a[中西],a[中西]=d，所以输出是23,f
print 98,a[红],a[红]=b，所以输出是98,
```

通用方法：

所以就要用到更通用的方法了：
1、ARGIND 当前被处理参数标志:

awk 'ARGIND==1{...}ARGIND==2{...}ARGIND==3{...}... ' file1 file2 file3 ...

2、ARGV 命令行参数数组:

awk 'FILENAME==ARGV[1]{...}FILENAME==ARGV[2]{...}FILENAME==ARGV[3]{...}...' file1 file2 file3 ...

3、把文件名直接加入判断:

awk 'FILENAME=="file1"{...}FILENAME=="file2"{...}FILENAME=="file3"{...}...' file1 file2 file3 ...       #没有前两种通用