概要
情報整理程度にしかSQL分を使わなくて、毎回joinについて、LEFTだーRIGHTだー、をすぐわすれるので、メモした。
Excelめんどくさいし、sqlite3にデータ入れて検索しよ、という程度のDBライトユーザの話。
- CSVファイル(data.sv)を、samba経由でsqlite3が使えるホストに置く
- 処理用データベースを開く
$ sqlite3 newDB.db
- インポートするデータに合わせた、テーブルを作る
sqlite> create table newtable (...);
- セパレータを変えてインポートする
sqlite> .separator ,sqlite> .import ./data.csv newtable
- データをいじる
- .mode columnや.headers on辺りで見やすくして、selectとか云々
- データ書き出し
sqlite> .output ./output.csvsqlite> select ....
- samba経由でデータ改修して終わり。
確認用のデータ
sqlite3で alphabet, phonetic というテーブルを用意しています。
sqlite> .mode column
sqlite> .headers on
sqlite> .schema
CREATE TABLE phonetic ( id integer, name text);
CREATE TABLE alphabet ( id integer, name text);
sqlite> select * from alphabet;
id name
---------- ----------
1 A
2 B
3 C
4 D
sqlite> select * from phonetic;
id name
---------- ----------
1 Alpha
2 Bravo
3 Charlie
sqlite>
イメージはこれですね
sqlite> select a.name, a.id, p.name from alphabet a left outer join phonetic p on a.id=p.id;
name id name
---------- ---------- ----------
A 1 Alpha
B 2 Bravo
C 3 Charlie
D 4
sqlite>
結合の種類
以下の結合から好きなのを選んで使おう
- CROSS JOIN
- INNER JOIN
- OUTER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN (sqlite3未実装)
- FULL OUTER JOIN (sqlite3未実装)
ONで結合の条件、WHEREで抽出の条件を書くよね。
CROSS JOIN
選択されたもののすべての組み合わせが出てくる。
テーブルの列同士の比較。
sqlite> select a.name, p.name from alphabet a CROSS JOIN phonetic p;
name name
---------- ----------
A Alpha
A Bravo
A Charlie
B Alpha
B Bravo
B Charlie
C Alpha
C Bravo
C Charlie
D Alpha
D Bravo
D Charlie
sqlite>
テーブルのレコード単位での結合なので、ここでは alphabet(id,name)とphonetic(id,name)の塊での突合せになる。
sqlite> select * from alphabet a CROSS JOIN phonetic p;
id name id name
---------- ---------- ---------- ----------
1 A 1 Alpha
1 A 2 Bravo
1 A 3 Charlie
2 B 1 Alpha
2 B 2 Bravo
2 B 3 Charlie
3 C 1 Alpha
3 C 2 Bravo
3 C 3 Charlie
4 D 1 Alpha
4 D 2 Bravo
4 D 3 Charlie
sqlite>
INNER JOIN
キーとなる特定の列で結合し、結合できたものだけを得る。
キーが両方に存在しないものは、除外される。
- alphabet(4,"Delta")は、phoneticにid:4が無いので結合時に除外されてる。
sqlite> select * from alphabet a INNER JOIN phonetic p on a.id=p.id;
id name id name
---------- ---------- ---------- ----------
1 A 1 Alpha
2 B 2 Bravo
3 C 3 Charlie
sqlite>
CROSS JOINでキーが同じものを抽出した、と同じ。
sqlite> select * from alphabet a CROSS JOIN phonetic p where a.id=p.id;
id name id name
---------- ---------- ---------- ----------
1 A 1 Alpha
2 B 2 Bravo
3 C 3 Charlie
sqlite>
OUTER JOIN
INNER JOINではキーが一致しない行は全て捨てられたが、OUTER JOINでは「存在しない場合はNULL」で検索できる。
- slqite3だとRIGHT/FULL JOINが使えないので、LEFT OUTER JOINのみ。
sqlite> select * from alphabet a LEFT OUTER JOIN phonetic p on a.id=p.id;
id name id name
---------- ---------- ---------- ----------
1 A 1 Alpha
2 B 2 Bravo
3 C 3 Charlie
4 D
sqlite>
必要なものと価格表との突合せ、みたいな。
sqlite> select w.wants, a.price from 欲しいもの w left outer join amazonの金額 a on w.wants=a.product limit 5;
wants price
---------- ----------
時間
お金
ドローン 30000
バイク 55000
希望 999999999
sqlite>
まとめ
普通は inner join か left outer join しか使わなくね？
まぁ、備忘録なんで。。