タダケンのEnjoy Tech

楽しみながらラクに成果を上げる仕組みを考える

トップ > プログラミング > SQLite3を使ってちょっとしたCSVファイルをSQLで集計する

SQLite3を使ってちょっとしたCSVファイルをSQLで集計する

プログラミング シェルスクリプト SQL

f:id:tadaken3:20171020194951p:plain こんにちは!
タダケン(@tadaken3)です。

データ分析をしていると手元のちょっとしたファイルをSQLで集計したいことってよくあります。

例えば数万行〜数十万行程度のちょっとしたCSVファイルなど、エクセルでやるにはちょっと重たいけど、MySQLやPostgreSQLのような本格的なデータベースを使うほどではないときです。

そんなとき、SQLite3を使えば、ちょっとしたデータを手元でSQLを使って集計できて便利です。

またSQLの学習にもちょうどいいですし、集計ロジックとデータを切り離すことができるのでで、集計ロジックを他のデータにも使いまわすことができます。

今回はSQLite3を使って、CSVファイルを集計する方法をお伝えします。

SQLite3とは

SQLite3はデータベースサーバを使用せずデータベース毎に1つのファイルを使って管理する極めてシンプルな構造をしています。

開発の現場ではアプリケーションに組み込みんで使われる場合が多いです。PythonやPHP、RubyではSQLiteを扱うライブラリが標準で組み込まれています。

また、Macには最初からSQLite3がインストールされています。

Windowsの方は以下の記事を参考にインストールしてみてください。

SQLiteインストール(Window版)

インストールできたら、SQlite3をコマンドラインから実行してみましょう。

$ sqlite3 --version
3.16.0 2016-11-04 19:09:39 0e5ffd9123d6d2d2b8f3701e8a73cc98a3a7ff5f

データベースの作成と接続

データベースを作成するにはコマンドラインで以下のように実行します。

書き方 sqlite3 データベース名

以下の例ではsample.dbを作成をします。

$ sqlite3 sample.db

「sample.db」が存在していなかった場合は新たに作成され、SQLite3の対話モードに入ります。すでに「sample.db」が存在している場合は、そのまま「sample.db」にアクセスします。1

SQLite3を終了するには「.exit」コマンドを実行します。

sqlite> .exit


カレントディレクトリにsample.dbというファイルが作られていると思います。

f:id:tadaken3:20171020192900p:plain

もう一度、「sample.db」に接続するには

$ sqlite3 sample.db

実行すると、既存の「sample.db」に接続されます。

CSVファイルをインポートする

このままだとsample.dbはからっぽです。なのでCSVファイルからデータを取り込んでいきましょう。

データをインポートする前に、ちょっと説明です。

SQLite3にはモードという概念があります。

今回はCSVファイルからデータをインポートしたいので、CSVモードに変更します。

モードを変更すると、集計結果を出力する際の区切り文字が変わります。モードを変更するには、「.mode」コマンドを実行します。

モードを変更するには以下のように実行します。

sqlite> .mode csv


モードが切り替わったら、次にデータをインポートします。 データをインポートするには「.import」コマンドを実行します。

書き方 .import 元ファイル名 テーブル名

例えば、以下のようなsample.csvというファイルがあるとします。

f:id:tadaken3:20171020192931p:plain

sample.csvを取り込んで、sampleというテーブルを作成するには以下のようにコマンドを実行します。

sqlite> .import sample.csv sample

テーブルができたどうか、「SELECT * FROM sample;」というSQLを実行して確認してみましょう。

sqlite> SELECT * FROM sample;

無事にテーブルが作られていますね。

f:id:tadaken3:20171020192455p:plain

データを集計する

あとは集計です。SELECT文を実行すれば、結果が出力されます。

sqlite> SELECT count(name) as cnt_name FROM sample;

と実行するとnameの数が表示されます。

f:id:tadaken3:20171020193834p:plain


出力した結果を外部のファイルに保存したい場合は、「.output」コマンドを使います。

書き方 .output 保存したいファイル名

.outputコマンドの後に、SQLを実行すると結果がファイルに保存されます。

sqlite> .output cnt_name.csv
sqlite> SELECT count(name) as cnt_name FROM sample;

このように結果がCSVファイルが保存されました。

f:id:tadaken3:20171020192314p:plain

まとめ

今回はSQLite3を使って

  • データベースを作成・接続する方法
  • CSVファイルからデータをインポートする方法
  • SELECT文でデータを集計する方法

をお伝えしました。今回ご紹介したモノは基本的な内容にとどまっていますので、以下のサイトでSQLite3についてより学習をすすめるのもおすすめです。

SQLite入門

読者登録、Twiiterアカウントのフォローをお願いします！

本ブログではプログラミングや仕事効率化に関する情報を公開しています。よろしければ、ブログの読者登録もしくはTwitterアカウントをフォローしていただけると更新の励みになります。ぜひ一緒にプログラミングを学びましょう。

  1. 拡張子はなんでもオッケーです。通例では「.sqlite3」にしますがコマンド名と同じで、ややこしいので今回の記事では「.db」にしています。↩