ExcelでCSVの集計頑張るのが辛いならSQLiteでSQL書くのもアリ

SQLiteを今まで使ったことが無かったのですが、先日ExcelでSQL的なことをしようとして思うように書けず辛かったのでSQLiteへデータを流し込んでSQLのクエリで集計しました。

SQLiteはPostgreSQL, MySQL等と比較すると気軽にデータを格納・集計できるので、普段ExcelよりSQLを書く機会が多い方は使ってみるのもアリだと思います。

以下でCUIからSQLiteを使う際の基本的な使い方をまとめてみました。

SQLiteとは

RDBMSの1つで、以下のような特徴を持ちます。

• 1つのデータベースを1ファイルで取り扱う
• サーバを立てる必要がない
• メモリ消費量が少ない

SQLiteの準備

今回はWindows(Windows 11 Home (22H2))での利用を想定します。macOSだと嬉しいことにデフォルトでインストールされているようです。

WindowsのCUIからインストールする場合、パッケージマネージャーであるChocolateyが便利です。 使い方は以下の公式ページからどうぞ。

chocolatey.org

# ChocolateyでSQLiteのインストール
$ choco install sqlite

インストールされているかどうか、以下のコマンド等で確認しましょう。

$ sqlite3 --version
3.40.0 2022-11-16 12:10:08 89c459e766ea7e9165d0beeb124708b955a4950d0f4792f457465d71b158d318

Chocolateyでインストールする際「管理者として実行する」必要になりますので、コマンドプロンプトやGit Bashは予め「管理者として実行する」ようにしてください。（よくハマりました）

データを流し込む

まずは予めSQLiteに読み込むCSVを用意してください。 今回は例として以下のようなCSVを用意しました。

users.csv

id,name,address
1,yamashita,kyoto
2,tanaka,osaka
3,okamoto,hokkaido

用意ができたら以下のコマンドでSQLite側にCSVデータを読み込みます。

# DBを作成
$ winpty sqlite3 test.sqlite3

# sqlite3に接続する
$ winpty sqlite3

# 区切り文字をカンマに指定する
sqlite> .separator ,
# usersテーブルにCSVをインポートする（usersテーブルは未定義なのでこの時にCREATE TABLEされる）
sqlite> .import users.csv users

# usersテーブルを照会する
sqlite> SELECT * FROM users;
1,yamashita,kyoto
2,tanaka,osaka
3,okamoto,hokkaido

# テーブル定義を確認する
sqlite> .schema users

# sqliteを終了する
sqlite> .exit

# DBがファイル(*.sqlite3)として作成されている
$ ls
test.sqlite3  users.csv

SQLiteではDBが1つのファイルとして扱われます。お手軽ですね。 *.sqlite3が作成されたら、以後はwinpty sqlite3 *.sqlite3で既存のDBに接続して利用できます。

DBに接続してしまえば、あとはSQLでデータを集計できますので、GROUP BYなりWHEREなりを書いて良い塩梅になるようデータを加工しちゃえばOKです。

※Git Bashを使う場合、頭にwinptyを付けないと正しく動作しません。winptyについては下記の記事で少し書きました。 tm-progapp.hatenablog.com

SQLの実行結果を出力する

DBでデータを加工したらそれを出力したい！という場面もあるかもしれません。 その場合は以下のように.outputを使います。

# usersテーブルから条件指定したレコードだけをCSV出力する
sqlite> .separator ,
.headers on
.output output.csv
SELECT * FROM users WHERE name IN ('tanaka', 'okamoto');

# output.csvが出力されている
$ cat output.csv
id,name,address
2,tanaka,osaka
3,okamoto,hokkaido

.output {出力ファイル名}.csv {SQL文}でクエリの実行結果が出力されます。 .headersはヘッダ行が必要な時だけ記述すればOKです。

あとがき

SQLiteはSQLで集計したいけどサーバを立てるほどのことでもないな…という時はおすすめです。 自分はエクセルの関数を駆使した集計が得意ではないので、SQLiteの方が書きやすいなーという感想です🤤