SQL と Pandas の対応表

## トピック

SQL のクエリと、Pandas のメソッドの対応表を作成する。

SQL 勉強中のため、備忘録代わりに箇条書き（殴り書き）で書いていく。

**Udemy の[こちら](https://www.udemy.com/standard-sql-for-beginners/)のコースで勉強していました。**

DBやテーブル自体の更新・操作に関するものは[こちら](https://qiita.com/takaiyuk/items/38c303c50fdc132bb378)にまとめている。（SQL のクエリだけを書き散らかしているだけ）

## 順序

### 記述順序

1. select
2. from
3. join系(+on)
4. where
5. group by
6. having
7. order by
8. limit

### 実行順序(※)

1. from
2. join系(+on)
3. where
4. group by
5. select
6. having
7. order by
8. limit

**(※)追記**

@nora1962jp さんからご指摘をいただきましたので、コメント内容を追記します。

>> 実行順序
from
join系(+on)
where

> SQLについてなら実行順序はonとwhereの順序はonが先とは一概に言えないです。
外部結合などが絡まない

> from a join b
on a.id = b.id
where a.col1 = 1

> のような場合、a.col1にインデックスが存在し選択性が有効ならwhereの判定結果をもとに結合処理を行うrdbmsは少なくありません。

## 基本操作

### 列選択

```{sql: sql}
select COL1, COL2 
from TABLE;
```

```{python: py}
df.loc[:, ["COL1", "COL2"]]
```

### 列名変更

```{sql: sql}
select 
    COL1 as COL1_renamed,
    COL2 as COL2_renamed 
from TABLE;
```

```{python: py}
df.rename(columns={"COL1": "COL1_renamed", "COL2": "COL2_renamed"})
```

### 列追加

```{sql: sql}
select 
    COL1 as COL1_renamed, 
    COL2 as COL2_renamed,
    COL2 * 2 as NEW_COL
from TABLE;
```

```{python: py}
df["NEW_COL"] = df["COL2"] * 2
```

### 条件付き行抽出

```{sql: sql}
select COL1, COL2 
from TABLE 
where COL1 = 'hoge'
    and COL2 > 100;
```

```{python: py}
df[(df["COL1"]=="hoge") & (df["COL2"] > 100)]
```

### 代表的な演算子

- **一致**

```{sql: sql}
select * 
from TABLE 
where COL1 = 'hoge';
```

```{python: py}
df[df["COL1"] == "hoge"]
```

- **不一致**

```{sql: sql}
select * 
from TABLE 
where COL1 != 'hoge';
-- select * from TABLE where COL1 <> 'hoge'; という書き方もできる
```

```{python: py}
df[df["COL1"] != "hoge"]
```

- **含まれない**

```{sql: sql}
select * 
from TABLE 
where COL2 not in (1,2,3);
```

```{python: py}
df[~(df["COL1"].isin(1,2,3))]
```

- **NULL**

```{sql: sql}
select * 
from TABLE 
where COL2 is null;
```

```{python: py}
df[df["COL2"].isnull()]
```

- **範囲**

```{sql: sql}
select * 
from TABLE 
where COL2 between 1000 and 2000;
-- select * from TABLE where COL2 >= 1000 and COL2 <= 2000; という書き方もできる
```

```{python: py}
df[(df["COL2"] >= 1000) & (df["COL2"] <= 2000)]
```

### パターンマッチング

ワイルドカード文字を利用

> **追記**
@satorimon さんのご指摘により、Python のコードを埋めることができました。

- **0文字以上の任意の文字列: '%'**

```{sql: sql}
select * 
from TABLE 
where NAME_COL like '中%';
-- 「中林」とか「中田」とかが抽出
```

```{python: py}
df[df["NAME_COL"].str.startswith('中')]  # 指定した文字で始まる
```

```{sql: sql}
select * 
from TABLE 
where NAME_COL like '%中%';
-- 「中林」とか「竹中」とかが抽出
```

```{python: py}
df[df["NAME_COL"].str.contains('[ぁ-んァ-ン一-龥]中[ぁ-んァ-ン一-龥]', regex=True)]
# [ぁ-んァ-ン一-龥]: 任意のひらがな・カタカナ・漢字
# cf.) http://fujiringo.sakura.ne.jp/hayabusa/blog/code/2011/07/post-1.html
```

```{sql: sql}
select * 
from TABLE 
where NAME_COL like '%子';
-- 「翔子」とか「美智子」とか
```

```{python: py}
df[df["NAME_COL"].str.endswith('子')]  # 指定した文字で終わる
```

- **任意の1文字: '_'**

```{sql: sql}
select * 
from TABLE 
where NAME_COL like '__子';
-- アンダースコア(_)が2つ
-- 「あや子」とか「美智子」とか
```

```{python: py}
df[df["NAME_COL"].str.contains('..子', regex=True)]  # ○○子: 正規表現で"."は任意の一文字を表す
```

### 取得件数を制限

```{sql: sql}
select * 
from TABLE 
limit 10;
-- 最初の10行を取得
```

```{python: py}
df.head(10)
```

```{sql: sql}
select * 
from TABLE 
limit 0, 10;
-- 0行目の次から、10行を取得
```

```{python: py}
df.iloc[0:10, :]
```

```{sql: sql}
select * 
from TABLE 
limit 10, 10;
-- 10行目の次から、10行を取得
```

```{python: py}
df.iloc[10:20, :]
```

## データの集約

- expr: expression(式)...引数

### 合計値

```{sql: sql}
select sum(COL1) 
from TABLE;
```

```{python: py}
data["COL1"].sum()
```

### 平均値

```{sql: sql}
select avg(COL1) 
from TABLE;
```

```{python: py}
data["COL1"].mean()
```

- **最小値**

省略

- **最大値**

省略

### 集約関数における null の扱い

- 集約関数では、null は基本的に無視される 
- 値に null が含まれないように DB 設計を構造したほうがトラブルが減る
    - null を許可する場合、0 と null の違いや、null と空文字の違いを意識する必要がある
    - 例えば、null の代わりに下記は使えないのか？検討する
        - 数値: 0
        - 文字列: ''(空文字)

### カウント

```{sql: sql}
select count(COL1) 
from TABLE
```

```{python: py}
len(df)
```

### ユニークなカウント

```{sql: sql}
select count(distinct COL1) 
from TABLE
```

```{python: py}
len(df["COL1"].unique())
```

### グループ化

```{sql: sql}
select COL1, count(*) 
from TABLE 
group by COL1;
```

```{python: py}
df.groupby("COL1").count()
```

### 集約結果をさらに絞り込む

- having: テーブルのデータを集約した結果に対して、条件式を適用する場合に利用する。

```{sql: sql}
select COL1, count(*) 
from TABLE 
where ... 
group by COL1 
having count(*) > 10;
```

```{python: py}
df_grouped = df.groupby("COL1").count()
df_grouped[df_grouped["COL2"] > 10]
```

## 並び替え

### 降順

```{sql: sql}
select * 
from TABLE 
order by COL desc;
```

```{python: py}
df["COL"].sort_values(ascending=False)
```

### 昇順

```{sql: sql}
select * 
from TABLE 
order by COL asc;
-- order by はデフォルトでは昇順なので、昇順の場合 asc は省略可
-- ただし、取得するデータの並び順が重要な場合は明示的に示しておいたほうが無難
```

```{python: py}
df.sort_values("COL")
```

### 複数条件

```{sql: sql}
select * 
from TABLE 
order by COL1 desc, COL2 asc;
```

```{python: py}
df.sort_values("COL1", ascending=False).sort_values("COL2")
# 両方とも同じ順ならば、df.sort_values(["COL1", "COL2"]) で可
```

## 関数と演算子

- **四則演算, 絶対値, 四捨五入**

省略

```{sql: sql}
select COL1, round(COL1 * 1.08, 0) 
from products;
-- round(COL1 * 1.08, 0) を返り値のデータ型は？？？
```

- **null を含んだ計算結果は null を返す**  
  
省略

### 文字列の演算

```{sql: sql}
select concat(COL1, '_', COL2) 
from TABLE;
```

```{python: py}
df["NEW_COL"] = [f"{COL1}_{COL2}" for COL1, COL2 in zip(df["COL1"], df["COL2"])]
```

### 日付と時刻の演算

- 現在の日付: current_date()
- 現在の時刻: current_time()
- 現在の日付時刻: current_timestamp()
- N日後の日付: d + interval N day
- N日前の日付: d - interval N day
- X時間後の時刻: t - interval X hour
- X時間前の時刻: t - interval X hour
- extract: 日付や時刻の特定の部分（年や月）までを取り出す
- date_format(TIMESTAMP, '%Y%M'): タイムスタンプから年月を取り出す

```{sql: sql}
select * 
from TABLE 
where extract(year_month from COL) = 201811;
-- 単体の場合は、select * from TABLE where extract(year from COL) = 2018; などと書く
```

```{python: py}
df[COL] = pd.to_datetime(df[COL])
df["COL_year"] = [ymd.year for ymd in df[COL]]
df[df["COL_year"] == 2018]
```

## 結合

- テーブルの正規化: テーブルを分けて情報の重複をなくす作業
    - メリット: 管理と容量の面で嬉しい
- キーの種類
    - 主キー(Primary Key, PK): 1つの行を特定できる列のこと
    - 外部キー(Foreign Key, FK): 他のテーブルとの関連づけに使う列のこと
- リレーションシップの種類
    - 一対多: ex.) ユーザー - 注文
    - 多対多: ex.) 商品 - 商品カテゴリ
    - 一対一: ex.) ユーザー - 電話番号

### 内部結合

- お互いに一致している行が結合テーブルの対象となる。

```{sql: sql}
select T1.COL1, T1.COL2, T2.COL3
from TABLE1 as T1
inner join TABLE2 as T2
on T1.COL4 = T2.COL5;
```

```{python: py}
df1.merge(df2, how="inner", left_on="lkey1", right_on="rkey1")
```

- 内部結合 + 絞り込み

```{sql: sql}
select T1.COL1, T1.COL2, T2.COL3
from TABLE1 as T1
inner join TABLE2 as T2 on T1.COL4 = T2.COL5
where T1.COL2 = 'hoge';
```

### 外部結合

- 片方のテーブルの情報がすべて出力される、テーブルの結合
- 片方のテーブルをベースに、お互いに一致している行は結合、一致していない行は null として結合テーブルの対象となる

```{sql: sql}
select T1.COL1, T1.COL2, T2.COL3
from TABLE1 as T1
left outer join TABLE2 as T2  -- left join とも書かれる
on T1.COL4 = T2.COL5;
```

```{python: py}
df1.merge(df2, how='left', left_on="lkey1", right_on="rkey1")
```

### 集合演算子

集合演算子 union は、テーブルの足し算を行う

- テーブル1とテーブル2で列数を合わせる必要がある。
- 同じ位置にあるカラムのデータ型は一致している必要がある。
- order by は全体の最後に一度しか用いることができない。

```{sql: sql}
select COL1, COL2
from TABLE
union  -- 重複削除がデフォルト。残したい場合は、union all とする。
select COL1, COL2
from TABLE
```

```{python: py}
pd.concat([ df1[["COL1", "COL2"]], df[["COL1", "COL2"]] ]).drop_duplicates()
```

## ビュー

- **ビューとは**
    - データを取り出すSELECT文だけを保存する
    - DBユーザーの利便性を高める道具（SQLの観点から見ると、テーブルと同じもの）

- **ビューのメリット**
    - 必要なデータが複数のテーブルにまたがる場合などの複雑な集約を行いやすくなる
    - よく使う select 文はビューにして使い回すことができる
    - データを保存しないので、記憶装置の容量を節約できる

- **ビューのデメリット**
    - パフォーマンスの低下を招く場合がある

- **ビューの作成**

```{sql: sql}
create view VIEW_NAME(COL1, COL2, ...)
as 
select * 
from TABLE ...;
```

- **ビューの呼び出し**

```{sql: sql}
select *
from VIEW_NAME;
```

- **ビューの削除**

```{sql: sql}
drop view VIEW_NAME;
```

- **ビューの制限事項**
    - order by 句が使えない
    - 更新系(insert, delete, update)に制約がある
        - ビューとテーブルの更新は連動して行われるため、集約されたビューは更新不可。

## サブクエリ

- あるクエリの結果に基づいて、異なるクエリを行う仕組み。
- where 句の中で使われることが多い（それ以外にも様々な場所で利用できる）

```{sql: sql}
select *
from TABLE1
where COL1 演算子 (select COL2 from TABLE2 ...);
```

- **スカラ・サブクエリ**
    - 1行1列だけの戻り値を返すサブクエリのこと

## 条件分岐

- Python で言うところの if文

```{sql: sql}
case
    when 条件式1 then 値1
    [when 条件式2 then 値2]
    [else 値3]
    -- []の部分は省略可能
end  -- end は省略不可
```