Pythonによるデータ解析を一通りやっている人向けのSQLチートシート

　こんにちは、WINUです。名前の読み方は「ウィヌ」となっております。今回はpythonでのデータ分析を既に行ったよーという方（numpyやpandas）に向けたデータベース言語の一つであるSQLのチートシートです。

　最近のデータサイエンス界隈はpythonによるデータ分析が主流となってきています。ただ、実際にデータサイエンティストの方と話をしているとまだまだSQLも現役との事。実際データベースに直接作用できるという点が点数高いですからね。それに、interviewでも出てくるという事で避けては通れません。

　そこで、つい先日SQLの基礎だけでも身に着けておこうと思い勉強した所、めちゃくちゃ簡単な事にびっくりしました。少しクセがあるものの、pythonによるデータ分析の知識を適用するとスラスラできる感じでした。

　そのため、この記事ではnumpyやpandasの知識を多く活用します。もし純粋にSQLを学びたい方は他のソースでの勉強をおススメします。それでは、早速チートシートに入りたいと思います。

基礎知識と用語

用語

・リレーショナルデータベース：一つ、又は複数のデータをテーブル形式で保存するデータベース。DataFrame型のデータを一つから複数まで保存するデータベース。

・テーブル：データ形式の一つ。それぞれのデータを示す行とカテゴリーを示す列によって構成されている。DataFrame型のデータの事。

・クエリ（query）：データベースに対する質問。SQL（Structured Query Language）によって書かれたクエリの「答え」に当たるデータをデータベースから引っ張ってきてもらう事がゴール。

例）「このテーブル内には合計でどれぐらいのデータがあるの？」というクエリを書くとテーブル内の行数を返す。

・クローズ（Clause）：データベースにおける操作を担うキーワード群。ケースはどちらでもいいが、基本的には大文字という事になっている。functionやmethodに近い。

基本的なクローズとクエリの基礎知識

・FROM - テーブルの指定。どのテーブルを操作するかを宣言する。

・WHERE - 条件の指定。演算子で条件を定義するときに使われる。

・演算子（AND, OR）- 通常の演算子。pythonのAND / ORと同じ効力を持つ。

・= - 基本的に代入も同値か否かの比較も=に統一されている。その代わり、それらを差別化するためのクローズが存在する。

・; - クエリの最後はセミコロン（;）によって締めくくられる。これは質問文の結びである、はてなマークと同じ役割がある。

・ダブルダッシュ（--）はコメント。pythonでは#。

Manipulation / 基本操作

・CREATE TABLE - テーブルを作る。pandasでのpd.DataFrameによるテーブルデータの作成と同じもの。内容は「column_name dtype (optional_args),」という形で列の定義を行う。

CREATE TABLE テーブル名 (
    カラム名 データ型 オプション, 
    ...
); 

・INSERT INTO - データの追加。pandasではappendやconcatにあたるもの。どちらの方法でも書けるが、カラム全てにデータを入れるのであれば、最初の方法を推奨。

INSERT INTO テーブル名
VALUES (データ1, データ2, ...); 

-- with specifying columns 
INSERT INTO テーブル名 (カラム1, カラム2)
VALUES (データ1, データ2);

・SELECT - データの選択。カラムを選択してデータを得る。*は全てのデータを取ってくるという意味。DataFrameで格納してある変数を呼び出す事か、リストや辞書のようにカラム名を指定する事と同値。

-- select all data from specified table 
SELECT * FROM テーブル名;

-- select specific column from table 
SELECT カラム, ... FROM テーブル名;

・ALTER TABLE - テーブルに変化を加える。カラムを増やす、データ型を変えるなどの操作を宣言後に定義する事で可能とする（下記の例はカラムの追加）。そのため、ALTER TABLE自体はテーブルの指定に使われる。

ALTER TABLE テーブル名

--option comes after (adding column this time)
ADD カラム名 データ型; 

・UPDATE - テーブル内のデータの変更。UPDATEによりテーブル名を宣言、SETでどのカラムに何の値を入れるのかを定義し、WHEREで条件の宣言。df[df[col]==value] = ...と非常に似た事をやっている。

UPDATE テーブル名
SET カラム名1 = データ1, カラム名2 = データ2
WHERE カラム名 = データ; 

・DELETE FROM - テーブル内のデータ削除を行う。WHEREで削除する対象となるデータの条件を宣言する。pandasのdropと同じ。

DELETE FROM テーブル名

-- specify condition 
WHERE カラム名 = データ; 

Queries / 細かいクエリ操作

・AS - カラム名の更新。pandasのrenameメソッドがこれに値する。

SELECT カラム名 AS 新カラム名
FROM テーブル名; 

・%, _ Wildcard - LIKEと併用する事でキーワード検索をかける。%はキーワードを前後（又は中間）に持つデータを検索し、_は逆にキーワードの中で欠けている部分を作り、その条件に合うデータを検索する。pandasではstartwithやendwith等で再現可能。

SELECT カラム名
FROM テーブル名
WHERE カラム名 LIKE '%ワード%';

SELECT カラム名
FROM テーブル名
WHERE カラム名 LIKE 'ワ_ド';

・ORDER BY - テーブルのソート。ASCで昇順、DESCで降順。np.sort等と同じ機能。別途定義している。

-- sort by ascending order 
SELECT *
FROM テーブル名
ORDER BY カラム名 ASC;

-- sort by descending order 
SELECT *
FROM テーブル名
ORDER BY カラム名 DESC;

・DISTINCT - ユニークな（重複のない）値を返す。pd.Series.unique()と同じ働きをする。

SELECT DISTINCT カラム名
FROM テーブル名; 

・BETWEEN - 任意の値の範囲に存在するデータを返す。X以上Y以下等の条件を書くことが出来る。

SELECT *
FROM テーブル名
WHERE カラム名 BETWEEN 条件1 AND 条件2;

・LIMIT - 返り値であるテーブルの行数を制限する。続く数字が行数となる。pd.DataFrame.head()メソッドと同じ機能。

SELECT *
FROM テーブル名
LIMIT 行数; 

・NULL - データ値がない状態。pythonのNull、numpyのnp.nanと同じ。

SELECT *
FROM テーブル名
-- IS NOT / IS works in same way as python 'is' do
WHERE カラム名 IS NOT NULL;

・CASE - if/elseの宣言。CASEとENDで囲った部分にWHEREとELSEクローズで条件と返り値を羅列する。なお、基本的には既存のカラムのデータから新しいカラムを作り出す際の条件となる。

SELECT カラム名, 
	CASE 
		WHERE 条件1 THEN 返り値1
		WHERE 条件2 THEN 返り値2
		ELSE 返り値3
	END
FROM テーブル名; 

Aggregate / 集計

・SUM - 合計の計算。np.sum等と同じ。 

SELECT SUM(カラム名)
FROM テーブル名; 

・MAX / MIN - 最大・最小値を返す。np.maximum / np.minimum等と同じ。

-- maximum 
SELECT MAX(カラム名)
FROM テーブル名; 

-- minimum
SELECT MIN(カラム名)
FROM テーブル名; 

・COUNT - 行数の合計を計算。len()やpd.DataFrame.shape[0]等と同じ。

SELECT COUNT(*)
FROM テーブル名; 

・GROUP BY - データをカテゴリー / 範囲ごとにまとめて集計する。pd.DataFrame.groupby(col)と同じ。以下の例ではそれぞれの範囲内のデータ数を集計している。

SELECT カラム名, COUNT(*)
FROM テーブル名
GROUP BY カラム名; 

・AVG - 指定されたデータの平均値を計算。np.mean等と同じ。

SELECT カラム1, AVG(カラム2)
FROM テーブル名
GROUP BY カラム1;

・HAVING - WHEREと基本的には同じ働きをするが、既にGROUP BYで集計されたデータに対して使われる。以下の例ではカラム１でまとめられたデータのデータ数が数値よりも大きい物のみを残す。

SELECT カラム1, COUNT(*)
FROM テーブル名
GROUP BY カラム1
HAVING COUNT(*) > 数値;

・ROUND - 四捨五入。np.round等と同じ。二つの引数を取り、一つ目は対象のカラム、もう一つは小数点第何位までに納めるかを指定。

SELECT カラム1, ROUND(カラム2, 数値)
FROM テーブル名;

Multiple Tables / 複数テーブルの操作

・Inner JOIN - テーブルの内部結合。両テーブルに存在するデータのみで結合する。テーブル名.カラム名とする事で、どのテーブルのどのカラムで結合するかを選択する。pd.merge()でhow='inner'とするのと同じ。

FROM テーブル1
JOIN テーブル2
	ON テーブル1.カラム1 = テーブル2.カラム2; 

・Outer (LEFT) JOIN - テーブルの外部結合。シンタックスはLEFTとつける以外は通常のJOINと同じ。pd.merge()でhow='left'とするのと同じ。

SELECT *
FROM テーブル1
LEFT JOIN テーブル2
	ON テーブル1.カラム1 = テーブル2.カラム2; 

・WITH - エイリアスを使って仮のテーブルを作る。元のデータを変えたくない場合に使われる。pd.DataFrame.copy()と同義。

WITH 仮テーブル名 AS (
    SELECT *
    FROM テーブル名
)
SELECT *
FROM 仮テーブル名
WHERE 条件;

・UNION - 異なるテーブルの同じ種類のデータを重複なく一つのデータへと結合する。pandasでpd.concatした後、drop_duplicates()する事と同じ。

SELECT カラム名1
FROM テーブル名1
UNION
SELECT カラム名2
FROM テーブル名2; 

・CROSS JOIN - 指定したカラム1とカラム2の全ての組み合わせを返す。pd.DataFrame.merge()でhow='outer'と指定する事と同じ。

SELECT テーブル1.カラム1, テーブル2.カラム2
FROM テーブル1
CROSS JOIN テーブル2; 

　とりあえず今回は以上です。もし何か間違いなどがありましたら遠慮なくご指摘ください。