Skip to content

Instantly share code, notes, and snippets.

@astrsk-hori
Created March 12, 2015 06:01
Show Gist options
  • Save astrsk-hori/a94100800d4ee661a824 to your computer and use it in GitHub Desktop.
Save astrsk-hori/a94100800d4ee661a824 to your computer and use it in GitHub Desktop.
csvや標準入力をsqlで取得できるqコマンド ref: http://qiita.com/astrsk_hori/items/fbe61e988f526d22efc9
a_id b_id a_name a_memo
1 1 aa1 a_memo1
2 2 aa2 a_memo2
3 3 aa3 a_memo3
4 4 aa4 a_memo4
5 5 aa5 a_memo5
6 6 aa6 a_memo6
7 7 aa7 a_memo7
8 8 aa8 a_memo8
9 9 aa9 a_memo9
10 10 aa10 a_memo10
b_id b_name b_memo
1 bb1 b_memo1
2 bb2 b_memo2
3 bb3 b_memo3
4 bb4 b_memo4
5 bb5 b_memo5
6 bb6 b_memo6
7 bb7 b_memo7
8 bb8 b_memo8
9 bb9 b_memo9
10 bb10 b_memo10
brew install q
~/tmp ᐅ q -H -O -d ',' "select * from aaa.csv where a_id in (2,4,6,8,10);"
a_id,b_id,a_name,a_memo
2,2,aa2,a_memo2
4,4,aa4,a_memo4
6,6,aa6,a_memo6
8,8,aa8,a_memo8
~/tmp ᐅ q -H -O -d ',' "select sum(b_id) from aaa.csv ;"
sum(b_id)
55
~/tmp ᐅ q -H -O -d ',' "select a.*,b.b_name,b.b_memo from aaa.csv as a left join bbb.csv as b on a.b_id=b.b_id"
a_id,b_id,a_name,a_memo,b_name,b_memo
1,1,aa1,a_memo1,bb1,b_memo1
2,2,aa2,a_memo2,bb2,b_memo2
3,3,aa3,a_memo3,bb3,b_memo3
4,4,aa4,a_memo4,bb4,b_memo4
5,5,aa5,a_memo5,bb5,b_memo5
6,6,aa6,a_memo6,bb6,b_memo6
7,7,aa7,a_memo7,bb7,b_memo7
8,8,aa8,a_memo8,bb8,b_memo8
9,9,aa9,a_memo9,bb9,b_memo9
10,10,aa10,a_memo10,bb10,b_memo10
~/tmp ᐅ cat aaa.csv|q -H -O -d ',' "select * from - where a_id=1"
a_id,b_id,a_name,a_memo
1,1,aa1,a_memo1
q -d ',' -e SJIS -E UTF-8 "select c3 from sjis_file.csv limit 10"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment