こんにちは!
タダケン(@tadaken3)です。
データ分析をしていると手元のちょっとしたファイルをSQLで集計したいことってよくあります。
例えば数万行〜数十万行程度のちょっとしたCSVファイルなど、エクセルでやるにはちょっと重たいけど、MySQLやPostgreSQLのような本格的なデータベースを使うほどではないときです。
そんなとき、SQLite3を使えば、ちょっとしたデータを手元でSQLを使って集計できて便利です。
またSQLの学習にもちょうどいいですし、集計ロジックとデータを切り離すことができるのでで、集計ロジックを他のデータにも使いまわすことができます。
今回はSQLite3を使って、CSVファイルを集計する方法をお伝えします。
SQLite3とは
SQLite3はデータベースサーバを使用せずデータベース毎に1つのファイルを使って管理する極めてシンプルな構造をしています。
開発の現場ではアプリケーションに組み込みんで使われる場合が多いです。PythonやPHP、RubyではSQLiteを扱うライブラリが標準で組み込まれています。
また、Macには最初からSQLite3がインストールされています。
Windowsの方は以下の記事を参考にインストールしてみてください。
インストールできたら、SQlite3をコマンドラインから実行してみましょう。
$ sqlite3 --version 3.16.0 2016-11-04 19:09:39 0e5ffd9123d6d2d2b8f3701e8a73cc98a3a7ff5f
データベースの作成と接続
データベースを作成するにはコマンドラインで以下のように実行します。
以下の例ではsample.dbを作成をします。
$ sqlite3 sample.db
「sample.db」が存在していなかった場合は新たに作成され、SQLite3の対話モードに入ります。すでに「sample.db」が存在している場合は、そのまま「sample.db」にアクセスします。1
SQLite3を終了するには「.exit」コマンドを実行します。
sqlite> .exit
カレントディレクトリにsample.dbというファイルが作られていると思います。
もう一度、「sample.db」に接続するには
$ sqlite3 sample.db
実行すると、既存の「sample.db」に接続されます。
CSVファイルをインポートする
このままだとsample.dbはからっぽです。なのでCSVファイルからデータを取り込んでいきましょう。
データをインポートする前に、ちょっと説明です。
SQLite3にはモードという概念があります。
今回はCSVファイルからデータをインポートしたいので、CSVモードに変更します。
モードを変更すると、集計結果を出力する際の区切り文字が変わります。モードを変更するには、「.mode」コマンドを実行します。
モードを変更するには以下のように実行します。
sqlite> .mode csv
モードが切り替わったら、次にデータをインポートします。
データをインポートするには「.import」コマンドを実行します。
例えば、以下のようなsample.csvというファイルがあるとします。
sample.csvを取り込んで、sampleというテーブルを作成するには以下のようにコマンドを実行します。
sqlite> .import sample.csv sample
テーブルができたどうか、「SELECT * FROM sample;」というSQLを実行して確認してみましょう。
sqlite> SELECT * FROM sample;
無事にテーブルが作られていますね。
データを集計する
あとは集計です。SELECT文を実行すれば、結果が出力されます。
sqlite> SELECT count(name) as cnt_name FROM sample;
と実行するとnameの数が表示されます。
出力した結果を外部のファイルに保存したい場合は、「.output」コマンドを使います。
.outputコマンドの後に、SQLを実行すると結果がファイルに保存されます。
sqlite> .output cnt_name.csv sqlite> SELECT count(name) as cnt_name FROM sample;
このように結果がCSVファイルが保存されました。
まとめ
今回はSQLite3を使って
- データベースを作成・接続する方法
- CSVファイルからデータをインポートする方法
- SELECT文でデータを集計する方法
をお伝えしました。今回ご紹介したモノは基本的な内容にとどまっていますので、以下のサイトでSQLite3についてより学習をすすめるのもおすすめです。
読者登録、Twiiterアカウントのフォローをお願いします！
本ブログではプログラミングや仕事効率化に関する情報を公開しています。よろしければ、ブログの読者登録もしくはTwitterアカウントをフォローしていただけると更新の励みになります。ぜひ一緒にプログラミングを学びましょう。
-
拡張子はなんでもオッケーです。通例では「.sqlite3」にしますがコマンド名と同じで、ややこしいので今回の記事では「.db」にしています。↩
