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

最近有需求需要本地处理一些临时的数据,用做统计分析。如果单纯的 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}}}' consumer

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)

【updating】 本文将会不定期更新。。。

推荐阅读:

[1] 更快的IP库查找方法以及AWK中的二分查找

http://blogread.cn/it/article/6369?f=wb

[2] q - Text as Data

http://harelba.github.io/q/index.html

q is a command line tool that allows direct execution of SQL-like queries on CSVs/TSVs (and any other tabular text files). q treats ordinary files as database tables, and supports all SQL constructs, such as WHERE, GROUP BY, JOINs etc. It supports automatic column name and column type detection, and provides full support for multiple encodings.

q "SELECT COUNT(*) FROM ./clicks_file.csv WHERE c3 > 32.3"

ps -ef | q -H "SELECT UID,COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 3"

[3] 数据工程师常用的 Shell 命令

http://blog.jobbole.com/99034/

[4] awk入门.md

http://bit.ly/291uE5V

[5] AWK程序设计语言

http://awk.readthedocs.io/en/latest/chapter-one.html

[6] awk实例一:简单入门

http://suo.iteye.com/blog/1319525

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏文渊之博

数据仓库中如何使用索引

数据仓库的索引是个棘手的问题。如果索引太多,数据插入很快但是查询响应就会很慢。如果太多索引,数据导入就很慢并且数据存储空间更大,但是查询响应更快。数据库中索引的...

23970
来自专栏技术博文

MyISAM InnoDB 区别

MyISAM 和 InnoDB 讲解   InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差...

40170
来自专栏数据分析

SQL Server 性能优化之——系统化方法提高性能

1. 概述 在比较大的范围内找出能够大幅提高性能的区域,并且专注于分析这个区域,这是最有效的优化SQL Server性能的方式。否则,大量的时间和精力可能被浪费...

50460
来自专栏数据和云

你真的会用索引吗?来看看COUNT(*)到底能有多快

作者简介 ? 案例说明 一个大表的COUNT,究竟能有多快?除类似物化视图的做法,我们所能做到的极限能有多快?这不是一个真实的案例,而是根据笔者在网上发的一篇帖...

38660
来自专栏杨建荣的学习笔记

持续近7个小时的索引扫描的查询优化分析 (r5笔记第44天)

昨天客户的DBA反映有一个数据抽取的任务持续了很长时间最后超时退出了,让我看看有什么地方可以调优一下。 找到了对应的日志,发现在一个大表抽取的时候,抽取持续了将...

41650
来自专栏别先生

增量数据,如果下次增量数据存在重复数据,如何解决。

1、如果增量数据,每次增量数据可能会存在增量数据,如何解决。思路,首先可以复制一个备份表,然后将主表中存在的数据,在备份表中进行删除,然后将备份表插入到主表,最...

9210
来自专栏数据和云

【云和恩墨大讲堂】Oracle线上嘉年华第二讲

编辑手记:Oracle线上嘉年华,正在持续分享中。本次的主题是系统割接中的SQL解析问题和结合业务的SQL优化改写技巧。 1 嘉宾介绍 小鱼(邓秋爽) 云和恩...

29960
来自专栏乐沙弥的世界

Oracle 重建索引的必要性

      索引重建是一个争论不休被不断热烈讨论的议题。当然Oracle官方也有自己的观点,我们很多DBA也是遵循这一准则来重建索引,那就是Oracle建议对于...

13210
来自专栏大白虾谈架构

数据库主外建适用场景

10350
来自专栏代码世界

数据库三范式详解

范式的概念   为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计...

42680

扫码关注云+社区

领取腾讯云代金券