読者です 読者をやめる 読者になる 読者になる

notebook

都内でWEB系エンジニアやってます。

CSVや標準入力からのデータをSQLライクに集計

q

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

q

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で使えるコマンドなら使えるという事になりますね

SQLiteMySQL

普段は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
  • 日付フォーマット

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

# 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

まとめ

普段SQLite触ってないのでそこらへんでつまづく事はありそうですが特に難しいところもなくすぐ使えそうです

調査でCSV出力してそこからさくっと集計したり...とかそういう作業のときに活躍してくれそうですね。