やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3学習 ファイル入出力(SQL集計)

 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);
  1. TSVインポート
    1. ".mode tabs" \
    2. ".import ./points.tsv points" \
  2. create文ファイル作成
    1. ".output ./points_create.sql" \
    2. "select sql from sqlite_master;" \
  3. insert文ファイル作成
    1. ".mode insert" \
    2. ".output ./points_insert.sql" \
    3. "select * from points;" \
  4. insert文ファイル作成(クォート排除版)
    1. ".output ./points_insert_type.sql" \
    2. "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の入出力もあったらいいな。

 DSVCSVTSVなどのこと。Delimiter Separatord Values

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで