用Linux命令行实现SQL的groupby

用Linux命令行实现SQL的groupby

• 需求

给定一个sql脚本文件，里面全是insert操作，样例如下：
insert into `passport_daily` (uid,loginname,ip,operatetime,result) values ('9454268',' [e-mail]','222.168.129.186','1281801604225','register');
insert into `passport_daily` (uid,loginname,ip,operatetime,result) values ('9454269',' [e-mail]','60.181.1.247','1281801607618','register');
insert into `passport_daily` (uid,loginname,ip,operatetime,result) values ('9036732','[e-mail]','118.112.144.140','1281801609235','login');
insert into `passport_daily` (uid,loginname,ip,operatetime,result) values ('9454270',' [e-mail]','111.112.18.214','1281801612152','register');
insert into `passport_daily` (uid,loginname,ip,operatetime,result) values ('8363103','[e-mail]','113.106.101.3','1281801615463','login');

关系表(uid,loginname,ip,operatetime,result)的语义是：某某用户（账号ID是：uid，登录名是：loginname）在operatetime时间按从源IP执行了一个result操作。

现在需要统计：来自不同的IP分别有多少？  限用Linux命令统计。

• 实现结果

gawk -F "','"  '{ print $3 }' sql.txt | sort -T ./ | uniq -c | sort -k 1 -nrs -T ./ > sql-stat.txt

23  60.181.1.247
9  111.112.18.214
5  113.106.101.3
1  118.112.144.140
1    222.168.129.186

• 命令解释

• gawk

gawk -F "','"  '{ print $3 }' sql.txt   表示从sql.txt文件中读取第三列。其中列分割符是’,’ （包含三个字符），由于列分割符号超过三个字符可使用双引号或单引号包围。由于此处分割符’,’中已经有单引号，所以使用双引号包围。
命令选项：
-F fs                   --field-separator=fs   用来指定列分割符号

insert into `passport_daily` (uid,loginname,ip,operatetime,result) values ('9454268',' [e-mail]','222.168.129.186','1281801604225','register');

当然通过grep+正则表达式也可以提取IP地址：
grep -P '([0-9]{1,3}\.){3}[0-9]{1,3}' sql.txt –o

• uniq

-c, --count           prefix lines by the number of occurrences （统计功能）
-d, --repeated        only print duplicate lines  （只找出重复行）
-u, --unique          only print unique lines   （只找出单行）

-f, --skip-fields=N   avoid comparing the first N fields  （对于有文件头的文件可以忽略前N行）

但是uniq命令有个前提：重复，单行的判断依据是只跟前后几行对比，并不是全文对比。所以执行uniq前一般先要sort。
Discard all but one of successive identical lines from INPUT (or
standard input), writing to OUTPUT (or standard output).

实例1：假设t1.txt文件如下
1
2
1
# uniq -u t1.txt        （虽然有两个1，但是不是连续的，依然会作为单行）
1
2
1
实例2：假设t2.txt文件如下
1
1
2
# uniq -u t2.txt    （连续的两个1，被认为是冗余行，所以取单行时被删除了）
2

• Sort

-n,-r选项
-n, --numeric-sort  compare according to string numerical value  作为数字，而不是作为字母
-r, --reverse  reverse the result of comparisons  倒序
-s, --stable              stabilize sort by disabling last-resort comparison 如果希望稳定排序，则使用-s。所谓“稳定排序”举个例子，两个二元组原始顺序：(1,zhangsan), (1,lisi); 如果稳定排序，那么排序的结果始终会是(1,zhangsan)在(1,lisi)前面；非稳定的，那么(1,lisi)可能会排(1,zhangsan)的前面。
-k , -t 选项
-k, --key=POS1[,POS2]     start a key at POS1, end it at POS2 (origin 1)
-t, --field-separator=SEP  use SEP instead of non-blank to blank transition

这两个选项可以让我们指定排序关键字。
假如有三行(uid,name,ip,state)：
8345891, zhangsan, 201.22.135.64, 0
3845891, wangwu, 69.22.136.64, 1
3845891, wangwu2, 198.22.16.164, 1

那么，如果按uid升序排序：
# sort -k 1 -t ',' -n s.txt   （-t 是字段分割符，-k用来指定排序关键字（可以是多个））
3845891, wangwu2, 198.22.16.164, 1  （非稳定排序，尽管wangwu和wangwu2的uid都是：3845891，但是wangwu2排wangwu前面去了。）
3845891, wangwu, 69.22.136.64, 1
8345891, zhangsan, 201.22.135.64, 0

# sort -k 1 -t ',' -n s.txt –s （稳定排序，wangwu依然在wangwu2的前面）
3845891, wangwu, 69.22.136.64, 1
3845891, wangwu2, 198.22.16.164, 1
8345891, zhangsan, 201.22.135.64, 0

# sort -k 2 -t ','  s.txt -s  （以第二个减排序）
3845891, wangwu2, 198.22.16.164, 1
3845891, wangwu, 69.22.136.64, 1
8345891, zhangsan, 201.22.135.64, 0

-T 选项
-T, --temporary-directory=DIR  use DIR for temporaries, not $TMPDIR or /tmp;
                              multiple options specify multiple directories

-T是用来指定临时目录的。因为sort排序过程中间可能需要用到一些临时文件作为交换空间，默认情况下这些临时文件会保存在$TMPDIR或/tmp中，但是我们也可以通过-T参数来指定。这个参数一般用在如果待排序文件特别大，默认临时目录可能没有足够空间。有个帖子说明了这一点：
http://stackoverflow.com/questions/3451388/perl-sort-temporary-directory

I ran into space issues on my machine and therefore the sort command in unix failed because of lack of space in /tmp. In order to circumvent this, I decided to run sort with the -T option allowing it to use some other directory for creating temporary files. here is the perl script I have
   my $TMPDIR              = "/home/xyz/workspace/";
    my $sortCommand         = "awk 'NR == 1; NR > 1 { print \$0 | \"sort -T \$TMPDIR -k1,1\" }' test > test.sort";
    system_call($sortCommand, "Sort");
    sub system_call {
      .......
}
this works perfectly on my desktop.

• 总结

gawk -F "','"  '{ print $3 }' sql.txt | sort -T ./ | uniq -c | sort -k 1 -nrs -T ./ > sql-stat.txt

（1）    gawk -F "','"  '{ print $3 }' sql.txt 用来从sql.txt文件中取出IP列；
（2）    sort -T ./ | uniq –c  用来统计各个IP的数量。由于uniq –c统计的前提是相同的元素得相邻，所以之前得对输入进行排序。另外考虑到待排序输入流很大，默认临时空间可能不够，因此指定临时空间为当前目录。
（3）    sort -k 1 -nrs -T ./  按第一列，以数字，倒序排列，而且是稳定排序。（原本只有一个IP列，但是uniq –c后，在前面插入了统计量，因此变成两列了。）
（4）    > sql-stat.txt  将结果重定向输出到sql-stat.txt文件。