UNIX/Linux環境でcsv/tsvにクエリを投げることができるqが便利すぎて生きるのが辛い

% mkdir -p ~/local/bin
% git clone git@github.com:harelba/q.git
Cloning into q…
remote: Reusing existing pack: 145, done.
remote: Counting objects: 16, done.
remote: Compressing objects: 100% (16/16), done.
remote: Total 161 (delta 7), reused 8 (delta 0)
Receiving objects: 100% (161/161), 52.47 KiB, done.
Resolving deltas: 100% (60/60), done.

% /usr/bin/python -V
Python 2.4.3

% /usr/local/bin/python -V
Python 2.7.1

% head /home/serima/local/bin/q/q
#!/usr/local/bin/python

# Name      : q (With respect to The Q Continuum)
# Author    : Harel Ben Attia - harelba@gmail.com, harelba @ github, @harelba on twitter
# Requires  : python with sqlite3
#
#
# q allows performing SQL-like statements on tabular text data.
#
# Its purpose is to bring SQL expressive power to manipulating text data using the Linux command line.

% ~/local/bin/q/q -v
q version 1.1.2

% source ~/.zshrc
% q
Usage:
        q allows performing SQL-like statements on tabular text data.

        Its purpose is to bring SQL expressive power to manipulating text data using the Linux command line.

        Basic usage is q "<sql like query>" where table names are just regular file names (Use - to read from standard input)
        Columns are named c1..cN and delimiter can be set using the -d (or -t) option.

        All sqlite3 SQL constructs are supported.

        Examples:

          Example 1: ls -ltrd * | q "select c1,count(1) from - group by c1"
            This example would print a count of each unique permission string in the current folder.

          Example 2: seq 1 1000 | q "select avg(c1),sum(c1) from -"
            This example would provide the average and the sum of the numbers in the range 1 to 1000

        See the help or https://github.com/harelba/q for more details.

Options:
  -h, --help            show this help message and exit
  -b, --beautify        Beautify output according to actual values. Might be
                        slow...
  -z, --gzipped         Data is gzipped. Useful for reading from stdin. For
                        files, .gz means automatic gunzipping
  -d DELIMITER, --delimiter=DELIMITER
                        Field delimiter. If none specified, then standard
                        whitespace is used as a delimiter
  -t, --tab-delimited-with-header
                        Same as -d <tab> -H 1. Just a shorthand for handling
                        standard tab delimited file with one header line at
                        the beginning of the file
  -H HEADER_SKIP, --header-skip=HEADER_SKIP
                        Skip n lines at the beginning of the data (still takes
                        those lines into account in terms of structure)
  -f FORMATTING, --formatting=FORMATTING
                        Output-level formatting, in the format X=fmt,Y=fmt
                        etc, where X,Y are output column numbers (e.g. 1 for
                        first SELECT column etc.
  -e ENCODING, --encoding=ENCODING
                        Input file encoding. Defaults to UTF-8. set to none
                        for not setting any encoding - faster, but at your own
                        risk...
  -v, --version         Print version

% seq 1 1000 | q "select avg(c1),sum(c1) from -"
500.5 500500

% cat test.txt
Mike    10
John    1
Bob     8
Mary    19
Bob     15
Bob     20
Mike    2

% q "select c1, count(*), sum(c2) from test.txt group by c1"
Bob 3 43
John 1 1
Mary 1 19
Mike 2 12

% awk ’{a[$1]++;b[$1]=b[$1]+$2}END{for (i in a) printf("%s %d %dn", i, a[i], b[i])}’ test.txt
Mike 2 12
Bob 3 43
Mary 1 19
John 1 1

% cat test1.txt
a1234   aa1
b1234   aa2
c1234   aa3
d1234   aa3
% cat test2.txt
1       a1234   bb1
2       a1234   bb2
3       b1234   cc1
4       c1234   dd3
5       e1234   NULL
% q "select * from test1.txt as a left join test2.txt as b on a.c1 = b.c2"
a1234 aa1        1 a1234 bb1
a1234 aa1        2 a1234 bb2
b1234 aa2        3 b1234 cc1
c1234 aa3        4 c1234 dd3
d1234 aa3