TSVファイルを入力としてSQLで集計した結果を新たなTSVファイルへ出力。
成果物
問題
以下のデータからクラスごとの平均点を求めたい。
id | name | class | point |
---|---|---|---|
0 | Yamada | 0 | 92 |
1 | Suzuki | 0 | 21 |
2 | Tanaka | 1 | 55 |
3 | Abe | 1 | 19 |
points.tsv
id name class point 0 Yamada 0 92 1 Suzuki 0 21 2 Tanaka 1 55 3 Abe 1 19
期待値は以下。
class | ave |
---|---|
0 | 56.5 |
1 | 37 |
class_averages.tsv
class ave 0 56.5 1 37
これをSQLite3で行う。そのシェルスクリプトを書く。
解決
run.sh
# カレントディレクトリを設定 SCRIPT_DIR=$(cd $(dirname $0); pwd) cd "$SCRIPT_DIR" # 入力ファイル作成 echo -e "id\tname\tclass\tpoint 0\tYamada\t0\t92 1\tSuzuki\t0\t21 2\tTanaka\t1\t55 3\tAbe\t1\t19" > ./points.tsv # 集計&ファイル出力 sqlite3 :memory: \ ".headers on" \ ".mode tabs" \ ".output ./class_averages.tsv" \ ".import ./points.tsv points" \ "select class, \ avg(cast(point as int)) as ave \ from points \ group by class \ order by ave desc;"
これを実行すればOK。
やってみる
points.tsv
ファイル作成
作成するコマンドは以下。
echo -e "id\tname\tclass\tpoint 0\tYamada\t0\t92 1\tSuzuki\t0\t21 2\tTanaka\t1\t55 3\tAbe\t1\t19" > ./points.tsv
ちなみに、ターミナルへリアルタイムでTab
を入力するときは、Ctrl
+V
+Tab
。
テスト表示
ターミナルで以下を実行する。
sqlite3 :memory: ".headers on" ".separator \"\t\"" ".import ./points.tsv points" "select * from points;" "select * from sqlite_master;"
id name class point 0 Yamada 0 92 1 Suzuki 0 21 2 Tanaka 1 55 3 Abe 1 19 type name tbl_name rootpage sql table points points 2 CREATE TABLE points( "id" TEXT, "name" TEXT, "class" TEXT, "point" TEXT )
型はすべてTEXTになってしまう
型
文字列型にだけ""
のようにダブルクォートすれば、それ以外は数値型になってくれないか。と期待したがダメだった。無条件ですべてtext
型になるらしい。
echo -e "id\tname\tclass\tpoint 0\t"Yamada"\t0\t92 1\t"Suzuki"\t0\t21 2\t"Tanaka"\t1\t55 3\t"Abe"\t1\t19" > ./points.tsv
sqlite3 :memory: ".mode tabs" ".import ./points.tsv points" "select sql from sqlite_master;"
CREATE TABLE points( "id" TEXT, "name" TEXT, "class" TEXT, "point" TEXT )
クラスの平均点を求める
select class, avg(cast(point as int)) as ave from points group by class order by ave desc;
select class, avg(cast(point as int)) as ave from points group by class order by ave desc;
- SQL関数
avg
で平均値を取得する cast(列名 as 型名)
で指定した列の型を、指定した型にキャストする.import
したら全列がtext
型になってしまう- 算術計算するためには
int
型への変換が必要
- 算術計算するためには
CLI
sqlite3 :memory: ".headers on" ".separator \"\t\"" ".import ./points.tsv points" "select class, avg(cast(point as int)) as ave from points group by class order by ave desc;"
class ave 0 56.5 1 37.0
ファイル出力
sqlite3 :memory: ".headers on" ".separator \"\t\"" ".output ./class_averages.tsv" ".import ./points.tsv points" "select class, avg(cast(point as int)) as ave from points group by class order by ave desc;"
class_averages.tsv
class ave 0 56.5 1 37.0
.output ファイルパス
- 出力先を
stdout
から指定ファイルへ変更した
- 出力先を
まとめ
スクリプト化すると以下。
run.sh
# カレントディレクトリを設定 SCRIPT_DIR=$(cd $(dirname $0); pwd) cd "$SCRIPT_DIR" # 入力ファイル作成 echo -e "id\tname\tclass\tpoint 0\tYamada\t0\t92 1\tSuzuki\t0\t21 2\tTanaka\t1\t55 3\tAbe\t1\t19" > ./points.tsv # 集計&ファイル出力 sqlite3 :memory: ".headers on" ".separator \"\t\"" ".output ./class_averages.tsv" ".import ./points.tsv points" "select class, avg(cast(point as int)) as ave from points group by class order by ave desc;"
最後のは以下のほうがわかりやすい。
# 集計&ファイル出力 sqlite3 :memory: \ ".headers on" \ ".mode tabs" \ ".output ./class_averages.tsv" \ ".import ./points.tsv points" \ "select class, \ avg(cast(point as int)) as ave \ from points \ group by class \ order by ave desc;"
おまけ
TSV→SQL
TSV
からcreate
文とinsert
文のSQL
ファイルを作成する。
# TSVファイル読込&create書込&insert書込 sqlite3 :memory: \ ".mode tabs" \ ".import ./points.tsv points" \ ".output ./points_create.sql" \ "select sql from sqlite_master;" \ ".mode insert" \ ".output ./points_insert.sql" \ "select * from points;" \ ".output ./points_insert_type.sql" \ "select cast(id as int), name, cast(class as int), cast(point as int) from points;"
以下のファイルが作成される。
points_create.sql
CREATE TABLE points( "id" TEXT, "name" TEXT, "class" TEXT, "point" TEXT )
points_insert.sql
INSERT INTO "table" VALUES('0','Yamada','0','92'); INSERT INTO "table" VALUES('1','Suzuki','0','21'); INSERT INTO "table" VALUES('2','Tanaka','1','55'); INSERT INTO "table" VALUES('3','Abe','1','19');
points_insert_type.sql
INSERT INTO "table" VALUES(0,'Yamada',0,92); INSERT INTO "table" VALUES(1,'Suzuki',0,21); INSERT INTO "table" VALUES(2,'Tanaka',1,55); INSERT INTO "table" VALUES(3,'Abe',1,19);
- TSVインポート
".mode tabs" \
".import ./points.tsv points" \
create
文ファイル作成".output ./points_create.sql" \
"select sql from sqlite_master;" \
insert
文ファイル作成".mode insert" \
".output ./points_insert.sql" \
"select * from points;" \
insert
文ファイル作成(クォート排除版)".output ./points_insert_type.sql" \
"select cast(id as int), name, cast(class as int), cast(point as int) from points;"
でもcreate
文で型や制約を修正できない……。ならinsert
の型を修正しても無駄……。
所感
これでSQLファイル内だけでなく、以下のような外部ファイル入出力にも応用する方法がわかってきた。
- SQLite3ファイル→集計→SQLite3ファイル
- SQLite3ファイル→集計→DSVファイル
- DSVファイル→集計→SQLite3ファイル
- DSVファイル→集計→DSVファイル
できればJSONファイルimport/exportや、Web上にあるファイルやURLの入出力もあったらいいな。
DSV
はCSV
やTSV
などのこと。Delimiter Separatord Values
。
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
bash 4.4.12(1)-release
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習をはじめよう
- SQLite3学習 SQLiteについて
- SQLite3学習 SQLiteの適切な用途
- SQLite3学習 SQLiteの特徴
- SQLite3学習 SQLiteのクセ
- SQLite3学習 データ型とアフィニティ
- SQLite3学習 演算子の一覧
- SQLite3学習 よくある質問
- SQLite3学習 SQLiteダウンロード&コンパイル
- SQLite3学習 Tclで操作する
- SQLite3学習 ビルドオプション動作確認(SQLITE_ALLOW_URI_AUTHORITY)
- SQLite3学習 面白そうなコンパイルオプション
- SQLite3学習 SQLiteの拡張について
- SQLite3学習 JSON拡張
- SQLite3学習 JSON拡張(json_extract)
- SQLite3学習 JSON拡張(json_each)
- SQLite3学習 JSON拡張(json_tree オブジェクト→行)
- SQLite3学習 JSON拡張(json_tree オブジェクトツリー→行)
- SQLite3学習 JSON拡張(json_tree オブジェクト配列→行)
- SQLite3学習 JSON拡張(json_group_array 行→配列)
- SQLite3学習 JSON拡張(json_group_object 行→オブジェクト)
- SQLite3学習 JSON拡張(json_array_length)
- SQLite3学習 JSON拡張(json_type)
- SQLite3学習 JSON拡張(json_valid)
- SQLite3学習 JSON拡張(json_quote)
- SQLite3学習 JSON拡張(json_array)
- SQLite3学習 JSON拡張(json_object)
- SQLite3学習 JSON拡張(json_patch)
- SQLite3学習 JSON拡張(json_insert)
- SQLite3学習 JSON拡張(json_replace)
- SQLite3学習 JSON拡張(json_set)
- SQLite3学習 JSON拡張(json_remove)
- SQLite3学習 全文検索(FTS5)
- SQLite3学習 全文検索FTSを日本語で使う方法を調べてみた
- 形態素解析MeCabをインストールする
- SQLite3学習 全文検索FTS5のMeCab用トークナイザを実装する
- SQLite3学習 FTS5+MeCabでクエリ構文
- SQLite3学習 FTS5のテーブル作成と初期化
- SQLite3学習 FTS5の補助関数
- SQLite3学習 FTS5のfts5vocab仮想テーブル
- SQLite3学習 再帰クエリ(WITH RECURSIVE)
- SQLite3学習 R-Treeモジュール