q

CSVファイルや入力に対してSQLを実行できるというとても便利なツールらしい

install

CentOS6.4にインストールしてみます

rpm -ivh https://github.com/harelba/packages-for-q/raw/master/rpms/q-text-as-data-1.5.0-1.noarch.rpm

導入

何はともあれ試してみます

cat list.csv
a,b,c
1,2,3
4,5,6

エラー

実行しようとしたらエラーが出た、サイトにも書いてあるがpython3系には対応していない模様

python --version
Python 3.4.0

q -H -d',' "select * from list.csv"
  File "/usr/bin/q", line 105
    print sql, " params: " + str(params)
            ^
SyntaxError: invalid syntax

python --version
Python 2.7.8

q -H -d',' "select * from list.csv"
1,2,3
4,5,6

オプション

とりあえず下記覚えておけば使えるはず

-H

ヘッダ(1行目)を読み込まない

-d

デリミタの指定

-O

カラム名を表示する

-b

出力を綺麗に表示する

cat beautify.csv
name,count
aaaaa,1
bb,2

q -d',' -bHO 'select * from beautify.csv'
name ,count
aaaaa,1
bb   ,2

from -

標準入力から集計する場合は「-」

cat list.csv | q -H -d',' "select * from -"
1,2,3
4,5,6

実践

もう少し実践っぽく以下のようなCSVを集計してみます

cat list.csv
id,name,date,c
1,testa,2016-01-11,1
1,testa,2016-01-12,2
1,testa,2016-01-13,3
2,testb,2016-01-11,6
2,testb,2016-01-11,5
2,testb,2016-01-11,4

# IDごとの合計
q -H -d',' "select name,sum(c) from list.csv group by id"
testa,6
testb,15

# IDごとの平均
q -H -d',' "select name,sum(c)/count(*) from list.csv group by id"
testa,2
testb,5

# 日付ごとの合計
q -H -d',' "select date,sum(c) from list.csv group by date"
2016-01-11,16
2016-01-12,2
2016-01-13,3

# 日付ごとの合計、合計値で降順
q -H -d',' 'select date,sum(c) count from list.csv group by date order by count desc'
2016-01-11,16
2016-01-13,3
2016-01-12,2

join

joinもできる模様、list.csvにcategory_idを追加してみます

cat list.csv
id,name,category_id,date,c
1,testa,1,2016-01-11,1
1,testa,1,2016-01-12,2
1,testa,1,2016-01-13,3
2,testb,2,2016-01-11,6
2,testb,2,2016-01-11,5
2,testb,2,2016-01-11,4

cat category.csv
id,name
1,category1
2,category2

q -H -d',' "select l.name,c.name,sum(c) from list.csv l join category.csv c on(l.category_id = c.id)  group by l.id"
testa,category1,6
testb,category2,15

内部的にはsqliteを使ってるみたいです、ファイルもしくは入力されたものをSQLiteに突っ込んでから集計してるのかな

ということはSQLiteで使えるコマンドなら使えるという事になりますね

SQLiteとMySQL

普段はMySQLの方が使うことが多いので軽く違いあるやつを調べてみました

現在時刻

# MySQL
select NOW();
2016-01-14 16:46:48
# SQLite
select datetime();
2016-01-14 16:46:48

qコマンドはファイルや入力がなくてもSQL実行は出来る模様

SQLiteの仕様でタイムゾーンはUTCで扱われるようです

q 'select datetime()'
"2016-01-14 16:46:48"

文字列結合

MySQLではおなじみconcatコマンドは使えなかった

# MySQL
select concat("a","b");
ab
# SQLite
select "a"||"b"
ab

日付フォーマット

分のフォーマット指定がMySQLとSQLiteで若干違うので迷いそう

# MySQL
select DATE_FORMAT(NOW(),'%Y-%m-%dT%H:%i:%S');
2016-01-15T01:56:48
# SQLite
select strftime("%Y-%m-%dT%H:%M:%S",datetime())
2016-01-15T01:56:48

他

下記を使って集計してみます

cat report.csv
id,name,category_id,date,c,time
1,testa,1,2016-01-11,1,2015-01-11 14:30:23
2,testa,1,2016-01-12,2,2016-01-12 23:11:20
3,testa,1,2016-01-13,3,2016-01-13 22:00:02
4,testb,2,2016-01-14,6,2016-01-14 14:03:56
5,testb,2,2016-01-12,5,2016-01-12 22:00:11
6,testb,2,2016-01-11,4,2016-01-11 09:21:28

having

q -d',' -HO 'select  date, count(c) count from report.csv group by date having count>1'
date,count
2016-01-11,2
2016-01-12,2

group_concat

q -d',' -HO 'select  date, count(c) count, group_concat(name) names from report.csv group by date'
date,count,names
2016-01-11,2,"testa,testb"
2016-01-12,2,"testa,testb"
2016-01-13,1,testa
2016-01-14,1,testb