トピック
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 は省略不可