LoginSignup
315
334

More than 3 years have passed since last update.

@takaiyukin株式会社Gunosy

SQL と Pandas の対応表

Last updated at Posted at 2018-11-10

トピック

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.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 は省略不可
315
334
7

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
315
334