Edited at

SQL と Pandas の対応表


トピック

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

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

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

DBやテーブル自体の更新・操作に関するものはこちらにまとめている。(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

select COL1, COL2 

from TABLE;


py

df[["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["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"] != 1) & (df["COL1"] != 2) & (df["COL1"] != 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)] # [ぁ-んァ-ン一-龥]: 任意のひらがな・カタカナ・漢字

# http://fujiringo.sakura.ne.jp/hayabusa/blog/code/2011/07/post-1.html
# df[df["NAME_COL"].str.contains('[^\x01-\x7E]中[^\x01-\x7E]',
# regex=True)] # [^\x01-\x7E]: 「1バイト文字ではないもの」


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"] = [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, 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 は省略不可