【図解】SQLのpartition byの使い方
partition byとは?
一言で表すとpartition by
は指定した値ごとに計算をしてくれる。
例えば、「商品ごとに販売個数のランキングをつけたい」や
「部署ごとに年齢のランクをつけたい」といった処理を実行する際に、
partition by
を使います。
○○ごとを指定するときに、partition by 商品
や
partition by 部署
と指定をしてあげます。
参考文献
SQLの入門書として非常にオススメです。
分かりやすい解説とドリルがあるので、
これからSQLを勉強される方は1冊持っていて損は無いと思います。
スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリシリーズ)
- 作者: 中山清喬,飯田理恵子,株式会社フレアリンク
- 出版社/メーカー: インプレス
- 発売日: 2018/11/30
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
処理を図解
ここに社員の部署と年齢が格納されたテーブルがあります。
部署ごとに年齢順でランクを付けてみましょう。
社員マスター
名前 | 部署 | 年齢 |
---|---|---|
田中 | 営業 | 30 |
清水 | 総務 | 40 |
山田 | 経理 | 25 |
水野 | 営業 | 33 |
川崎 | 経理 | 29 |
鈴木 | 総務 | 45 |
吉田 | 営業 | 23 |
このテーブルを使って、以下のような
部署ごとに年齢の若い順番でランクを付けていきましょう。
名前 | 部署 | 年齢 | ランク |
---|---|---|---|
吉田 | 営業 | 23 | 1 |
田中 | 営業 | 30 | 2 |
水野 | 営業 | 33 | 3 |
清水 | 総務 | 40 | 1 |
鈴木 | 総務 | 45 | 2 |
山田 | 経理 | 25 | 1 |
川崎 | 経理 | 29 | 2 |
クエリ
結果を導き出すクエリは、このように記載します。
SELECT 名前、 部署, 年齢, RANK() OVER(partition by 部署 order by 年齢) as ランク FROM 社員マスター ORDER BY 部署
重要なのは、RANK() OVER(partition by 部署 order by 年齢)
の箇所ですね。
今回やりたいことは、「部署ごと(partition by 部署
)に年齢の若い順(order by 年齢
)に
ランク(RANK()
)を付ける」でしたね。
RANK
は分析関数と言われ、ROW_NUMBER
やDENSE_RANK
などがあります。
処理したい内容によって、使い分ける必要があります。
OVER
句
RANK
のあとにOVER
句が出てきました。
このOVER
句の中に何を基準として処理をするのか明記してあげます。
今回は、部署と年齢だったので、OVER(partition by 部署 order by 年齢)
となりましたね。
処理イメージ
これまでのクエリを理解した上で、処理のイメージを図解します。
このように部署ごとに色をつけました。
色ごとに年齢をソートして、若い順番にランクを付けます。
記法
さて、一度記法に関しておさらいしましょう。
分析関数 over(partition by)
このように、partition by
は分析関数と一緒に利用します。
そして、OVER
句の中で何ごとに処理をするのか明記しましょう。
前処理について
今回window関数でpartiton byを利用しましたが、 データを前処理するためには、他にも様々な手法が沢山あります。
しかしながら、前処理の知識は共有されていることが少なく、 自分でいろんな壁に当たりながら覚えていくか、先輩から学ぶ他にはありませんでした。
そんななか、ある本が出版されたことにより、
データ分析者が長年悩んできた前処理の知識が1冊に集約されたのです。
前処理大全[データ分析のためのSQL/R/Python実践テクニック]
- 作者: 本橋智光
- 出版社/メーカー: 技術評論社
- 発売日: 2018/04/13
- メディア: 大型本
- この商品を含むブログ (1件) を見る
この書籍は、優秀なデータ分析者たちの前処理のナレッジが集約されているだけでなく、
SQL、Python、Rで処理する際のサンプルコードまで掲載されています。
この本を読めば、どうやって処理しようかな?といった悩みが
かなり解消されるでしょう!
まとめ
今回はpartition by
について、解説しました。
使いこなすことができると分析をするときに、表現の幅が広がるので、
ぜひ使いこなしてみましょう。
参考文献
SQLの入門書として、こちらの本が一番良いと思います。
分かりやすい解説と豊富な演習問題が用意されており、
実際に書きながらSQLを習得することができます。
スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリシリーズ)
- 作者: 中山清喬,飯田理恵子,株式会社フレアリンク
- 出版社/メーカー: インプレス
- 発売日: 2018/11/30
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
「初級者から中級者になりたい!」という方には、 こちらの本が参考になると思います。
達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ (CodeZine BOOKS)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2018/10/11
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
基本的な記法を抑えているが、
初級から一歩踏み込んだ内容も習得したいという方は、
ぜひ読んで見ると良いかも良いかもしれません。