トピック

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

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

Udemy のこちらのコースで勉強していました。

DBやテーブル自体の更新・操作に関するものはこちらにまとめている。（SQL のクエリだけを書き散らかしているだけ）

順序

記述順序

select
from
join系(+on)
where
group by
having
order by
limit

実行順序(※)

from
join系(+on)
where
group by
select
having
order by
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}

select COL1, COL2 
from TABLE;

py}

df.loc[:, ["COL1", "COL2"]]

列名変更

sql}

select 
    COL1 as COL1_renamed,
    COL2 as COL2_renamed 
from TABLE;

py}

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

列追加

sql}

select 
    COL1 as COL1_renamed, 
    COL2 as COL2_renamed,
    COL2 * 2 as NEW_COL
from TABLE;

py}

df["NEW_COL"] = df["COL2"] * 2

条件付き行抽出

sql}

select COL1, COL2 
from TABLE 
where COL1 = 'hoge'
    and COL2 > 100;

py}

df[(df["COL1"]=="hoge") & (df["COL2"] > 100)]

代表的な演算子

一致

sql}

select * 
from TABLE 
where COL1 = 'hoge';

py}

df[df["COL1"] == "hoge"]

不一致

sql}

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

py}

df[df["COL1"] != "hoge"]

含まれない

sql}

select * 
from TABLE 
where COL2 not in (1,2,3);

py}

df[~(df["COL1"].isin(1,2,3))]

NULL

sql}

select * 
from TABLE 
where COL2 is null;

py}

df[df["COL2"].isnull()]

範囲

sql}

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

py}

df[(df["COL2"] >= 1000) & (df["COL2"] <= 2000)]

パターンマッチング

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

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

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

sql}

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

py}

df[df["NAME_COL"].str.startswith('中')]  # 指定した文字で始まる

sql}

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

py}

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

sql}

select * 
from TABLE 
where NAME_COL like '%子';
-- 「翔子」とか「美智子」とか

py}

df[df["NAME_COL"].str.endswith('子')]  # 指定した文字で終わる

任意の1文字: '_'

sql}

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

py}

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

取得件数を制限

sql}

select * 
from TABLE 
limit 10;
-- 最初の10行を取得

py}

df.head(10)

sql}

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

py}

df.iloc[0:10, :]

sql}

select * 
from TABLE 
limit 10, 10;
-- 10行目の次から、10行を取得

py}

df.iloc[10:20, :]

データの集約

expr: expression(式)...引数

合計値

sql}

select sum(COL1) 
from TABLE;

py}

data["COL1"].sum()

平均値

sql}

select avg(COL1) 
from TABLE;

py}

data["COL1"].mean()

最小値

省略

最大値

省略

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

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

カウント

sql}

select count(COL1) 
from TABLE

py}

len(df)

ユニークなカウント

sql}

select count(distinct COL1) 
from TABLE

py}

len(df["COL1"].unique())

グループ化

sql}

select COL1, count(*) 
from TABLE 
group by COL1;

py}

df.groupby("COL1").count()

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

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

sql}

select COL1, count(*) 
from TABLE 
where ... 
group by COL1 
having count(*) > 10;

py}

df_grouped = df.groupby("COL1").count()
df_grouped[df_grouped["COL2"] > 10]

並び替え

降順

sql}

select * 
from TABLE 
order by COL desc;

py}

df["COL"].sort_values(ascending=False)

昇順

sql}

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

py}

df.sort_values("COL")

複数条件

sql}

select * 
from TABLE 
order by COL1 desc, COL2 asc;

py}

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

関数と演算子

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

省略

sql}

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

null を含んだ計算結果は null を返す

省略

文字列の演算

sql}

select concat(COL1, '_', COL2) 
from TABLE;

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}

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

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}

select T1.COL1, T1.COL2, T2.COL3
from TABLE1 as T1
inner join TABLE2 as T2
on T1.COL4 = T2.COL5;

py}

df1.merge(df2, how="inner", left_on="lkey1", right_on="rkey1")

内部結合 + 絞り込み

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}

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

py}

df1.merge(df2, how='left', left_on="lkey1", right_on="rkey1")

集合演算子

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

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

sql}

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

py}

pd.concat([ df1[["COL1", "COL2"]], df[["COL1", "COL2"]] ]).drop_duplicates()

ビュー

ビューとは
- データを取り出すSELECT文だけを保存する
- DBユーザーの利便性を高める道具（SQLの観点から見ると、テーブルと同じもの）
ビューのメリット
- 必要なデータが複数のテーブルにまたがる場合などの複雑な集約を行いやすくなる
- よく使う select 文はビューにして使い回すことができる
- データを保存しないので、記憶装置の容量を節約できる
ビューのデメリット
- パフォーマンスの低下を招く場合がある
ビューの作成

sql}

create view VIEW_NAME(COL1, COL2, ...)
as 
select * 
from TABLE ...;

ビューの呼び出し

sql}

select *
from VIEW_NAME;

ビューの削除

sql}

drop view VIEW_NAME;

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

サブクエリ

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

sql}

select *
from TABLE1
where COL1 演算子 (select COL2 from TABLE2 ...);

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

条件分岐

Python で言うところの if文

sql}

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