ワンライナーで CSV に対して SQLite クエリを実行する

One-liner for running queries against CSV files with SQLite という記事で紹介されていた、ワンライナーを使って CSV ファイルに対して SQL クエリを実行する方法。

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

ソース元に記述されてる通り、taxi.csv を GitHub からダウンロードして試すことができる。7z で圧縮されていて解凍する必要がある。

SQLite は通常ストレージディスク（SSD とか）上に保存して利用するのが一般的だが、SQLite 側で用意されている特別なファイル名 :memory: を指定して開くことでデータをインメモリで扱うことができる。これについては SQLite の In-Memory Databases ¹ のページで紹介されている。

taxi.csv の中身はこんな感じの CSV ファイル。

VendorID,tpep_pickup_datetime,tpep_dropoff_datetime,passenger_count,trip_distance,RatecodeID,store_and_fwd_flag,PULocationID,DOLocationID,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,improvement_surcharge,total_amount,congestion_surcharge
1,2021-04-01 00:00:18,2021-04-01 00:21:54,1,8.40,1,N,79,116,1,25.5,3,0.5,5.85,0,0.3,35.15,2.5
1,2021-04-01 00:42:37,2021-04-01 00:46:23,1,.90,1,N,75,236,2,5,3,0.5,0,0,0.3,8.8,2.5
1,2021-04-01 00:57:56,2021-04-01 01:08:22,1,3.40,1,N,236,168,2,11.5,3,0.5,0,0,0.3,15.3,2.5
1,2021-04-01 00:01:58,2021-04-01 00:54:27,1,.00,1,N,47,61,1,44.2,0,0.5,0,0,0.3,45,0

始めの -cmd '.mode csv' -cmd '.import taxi.csv taxi' を指定することで taxi というテーブルを作成し、CSV 形式としてファイルの中身をテーブルにインポートするらしい。実際にテーブルスキーマを確認すると taxi テーブルが作成されていることがわかる。

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' '.schema taxi'

CREATE TABLE IF NOT EXISTS "taxi"(
  "VendorID" TEXT,
  "tpep_pickup_datetime" TEXT,
  "tpep_dropoff_datetime" TEXT,
  "passenger_count" TEXT,
  "trip_distance" TEXT,
  "RatecodeID" TEXT,
  "store_and_fwd_flag" TEXT,
  "PULocationID" TEXT,
  "DOLocationID" TEXT,
  "payment_type" TEXT,
  "fare_amount" TEXT,
  "extra" TEXT,
  "mta_tax" TEXT,
  "tip_amount" TEXT,
  "tolls_amount" TEXT,
  "improvement_surcharge" TEXT,
  "total_amount" TEXT,
  "congestion_surcharge" TEXT
);

ソース記事にも記述されているが、そのまま実行するとカラムの情報が表示されず CSV フォーマットとして結果のみ出力される。

"",128020,32.2371511482553
0,42228,17.0214016766151
1,1533197,17.6418833067999
2,286461,18.0975870711456
3,72852,17.9153958710923
4,25510,18.452774990196
5,50291,17.2709248175672
6,32623,17.6002964166367
7,2,87.17
8,2,95.705
9,1,113.6

上記コマンドに -cmd '.mode column' を指定するとカラム名と一緒に SQL の見慣れたフォーマットとして出力される。

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' -cmd '.mode column' \
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

passenger_count  COUNT(*)  AVG(total_amount)
---------------  --------  -----------------
                 128020    32.2371511482553
0                42228     17.0214016766151
1                1533197   17.6418833067999
2                286461    18.0975870711456
3                72852     17.9153958710923
4                25510     18.452774990196
5                50291     17.2709248175672
6                32623     17.6002964166367
7                2         87.17
8                2         95.705
9                1         113.6

また、-cmd '.mode json' とすることで JSON 形式で出力することもできる。

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' -cmd '.mode json' \
  'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

[{"passenger_count":"","COUNT(*)":128020,"AVG(total_amount)":32.23715114825532968},
{"passenger_count":"0","COUNT(*)":42228,"AVG(total_amount)":17.021401676615067088},
{"passenger_count":"1","COUNT(*)":1533197,"AVG(total_amount)":17.641883306799908126},
{"passenger_count":"2","COUNT(*)":286461,"AVG(total_amount)":18.097587071145646575},
{"passenger_count":"3","COUNT(*)":72852,"AVG(total_amount)":17.915395871092314905},
{"passenger_count":"4","COUNT(*)":25510,"AVG(total_amount)":18.452774990196012083},
{"passenger_count":"5","COUNT(*)":50291,"AVG(total_amount)":17.270924817567234299},
{"passenger_count":"6","COUNT(*)":32623,"AVG(total_amount)":17.600296416636713736},
{"passenger_count":"7","COUNT(*)":2,"AVG(total_amount)":87.170000000000005258},
{"passenger_count":"8","COUNT(*)":2,"AVG(total_amount)":95.704999999999991189},
{"passenger_count":"9","COUNT(*)":1,"AVG(total_amount)":113.59999999999998987}]

面白い。出力できる mode はこれだけある。markdown フォーマットとしても出力可能とのこと。

$ sqlite3 -cmd '.help mode'
.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.