はじめに
データ分析とデータ品質改善に従事してきた筆者が、SQLを用いた分析の基本である「ウィンドウ関数」の使い方とデータ品質の調査改善を行う手法をまとめてみようと思います。
こちらの記事は、SQLの知識向上と振り返りを主題としているので、ABC分析、バスケット分析、RFM分析などの「データ分析の手法」について説明している記事ではありません。(反響やコメントによって別投稿するかもしれません)
背景
SQLはエンジニアの大多数が利用しており、多くの方はWebサービス開発などでデータの登録画面や検索画面を作る際にSQLを利用したり、またはシステムの運用保守で障害の原因調査のためにSQLを利用して原因を特定すると思います。そのため、テーブル結合・サブクエリ・集計関数といったSQL構文は理解されている人が多いと思いますが、分析関数を理解して使っている人となると、ぐっと減ると思います。
私は以前、社内の管理職やエンジニアを対象にデータマネジメントのセミナー講師を行なっていました。その経験の中で、次のようなことがありました。
私「SQLの利用経験がある人、手を挙げてください」 ⇒ 9割方、手が挙がった
私「外部結合やサブクエリを理解して利用できる人」 ⇒ 7割方、手が挙がったまま
私「分析関数を使ってレコードに行番号を付けれる人」 ⇒ 全員の手が下がる。。
ここで強く言いたいことは、
「分析関数を使える人は極端に少ないため、知っているだけで他の人と差別化を図れ、あなたの強みになる」
ということです。
対象者
この記事は、以下のような方を対象としています。
- SQLの基本的な構文やテーブル結合といった基礎的な内容は理解しているが、「OLAP関数」や「ウィンドウ関数」という分析関数の用語を聞いてもピンとこない方
- 分析関数をどういう場面で利用するかイメージできず、詳しく触ってこなかったという方
分析業務に関わる人も、そうでない人も、SQLの理解を深め自分の強みとしたい！と考えている方にオススメの内容となっております。
ウィンドウ関数を使うと、例えばこんなことができる
早速ですが、次のような情報を知りたい時にSQLでささっと書けますか？
知りたいこと： 部門ごとの売上比率(割合)を知りたい
上記の3レコードから、以下の画像のように「全体の売り上げ」と「売上比率」を求めるというSQLを考えてみましょう。
シンプルですが、いかがですか？（シンキングタイム 1分）
こちらの記事を読み終わった時には、上記内容を求められるSQLが簡単に書けるようになります。
繰り返しになりますが、分析関数はデータ分析業務に携わる方だけでなく、システム開発や保守を行うエンジニアにも有用な知識です。是非とも理解を深め、皆様の業務に活かしてください。
環境
本記事の内容は、下記RDBMSで動作確認しています。記事中の実行結果画面などの図はPostgreSQLの結果を使います。
- PostgreSQL 11.5
- Oracle xe 18.4
- SQL Server 2017 Express
- MySQL 8.0.17
※MySQLは2018年4月リリースの8.0からウィンドウ関数が搭載されました。つい最近の出来事なので、8.0より前のバージョンを使用しているシステムがまだ多いので使えるか要確認。
他のレコードの情報を使って値を算出するウィンドウ関数
SQLで分析を行う関数は次のように色々な呼び方があります。
- ウィンドウ関数
- OLAP(OnLine Analytical Processing)関数
- 分析関数
RDBMSによって呼称が違っていたり、関数の種類によって使い分けているため呼び方が複数ありますが、まず最初のうちは全て同じものと考えて良いです。本記事では、分析を行う関数はSQL標準でも定義されている呼称でもある「ウィンドウ関数」と記載します。
ウィンドウ関数とは、通常のSQLでは苦手な「他のレコードの情報」を使って値を求めることができる関数です。大きく分けると以下の二種類存在します。
- sumやcountといった見慣れた集計関数をウィンドウ関数として利用するもの
- 分析専用のウィンドウ関数を利用するもの
今回の記事では前者について説明し、後者については次回の記事で説明を記載する予定です。
集計関数をウィンドウ関数として利用するとは？
通常のsumやcountなどの集計関数を利用すると結果が1行に集約されるため、select句に書ける列は、group byで指定した列と集計関数だけという決まりがありますよね。
ウィンドウ関数を使うと、こういった制限を受けることなく、sumやcountの結果を扱えるのです。つまり、次の実行結果イメージのように、全体の売上合計値を各レコードのカラムに追加できるのです！
select 部門,
売上金額
from 売上テーブル;
select sum(売上金額) as 全体の売上
from 売上テーブル;
select 部門,
売上金額,
sum(売上金額) over() as 全体の売上
from 売上テーブル;
それでは、このようなウィンドウ関数の構文はどのようなものになるのでしょうか？(というか上でしれっと書いておりますが)
「いつも見慣れているSQLの構文とは全然違くて、結局あまり使うことは無いんだろうな」と思っている皆様、ご安心ください！
書き方はselect句の中で、通常の集計関数の後ろに over()を追加するだけ！
sum(売上金額) over()
いかがでしょう、ウィンドウ関数がものすごく身近に感じられたのではないでしょうか？
もちろんこれはウィンドウ関数の構文の中で一番シンプルなものですが、over()を追加するだけでこういう動きになるということは結構大きな発見ですよね。
それでは、冒頭の売上比率を求める問題をウィンドウ関数で書いてみましょう。
(売上比率は 個別の売上/全体の売上 です)
select 部門
,売上金額
,sum(売上金額) over() as "全体の売上"
,売上金額 / sum(売上金額) over() as "売上比率(割合)"
from 売上テーブル
補足コメント
売上比率を求める際、列に別名として "全体の売上" を使いたいですがSQLの処理順の関係でまだ使えません。実行順の詳細や使うためのテクニックは次記事で執筆予定。
ウィンドウ関数を知らない状態だと、全体の売上を取得しそれと結合して、、というアプローチを考えたり、スカラーサブクエリを使っていけるかな、、などというアプローチになるかと思います。
もちろんこのようなアプローチでも結果を取得することは出来ますが、力技で書いたSQLは他の人が見たときに「なぜこういうことをしているのか」というのが理解しづらい状況になりがちです。いわば、関数が用意されているのにそれを使わずロジックを1から記述しているという状況です。
ウィンドウ関数の動きを知らなかったこれまでと、動きを知ったこれからとで、出来ることの幅が広がるのは良いことですよね。
よく使うウィンドウ関数の一覧
この一覧は今回検証のために利用した4つのRDBMSに共通して搭載されています。
| 関数名 | 説明 |
|---|---|
| AVG | 全体の平均、グループごとの平均、累積平均を求める |
| COUNT | 全体の件数、グループごとの件数、累積件数を求める |
| MAX | 全体の最大、グループごとの最大を求める |
| MIN | 全体の最小、グループごとの最小を求める |
| SUM | 全体の合計、グループごとの合計、累積合計を求める |
| ROW_NUMBER | ソートして順位付けする(同じ値の場合も重複なし)1,2,3,4,5 |
| RANK | ソートして順位付けする(同じ値の場合に重複あり、値は重複分飛ぶ)1,2,2,4,5 |
| DENSE_RANK | ソートして順位付けする(同じ値の場合に重複あり、値は飛ばない)1,2,2,3,4 |
| NTILE | レコードを等分割した値を付与する |
| FIRST_VALUE | 最初の行の値を利用できる |
| LAST_VALUE | 最後の行の値を利用できる |
| LAG | 前の行の値を利用できる |
| LEAD | 次の行の値を利用できる |
この中で最もイメージしやすい、集計関数のウィンドウ化のSQLと実行結果をまとめて書きます。
count over
select 部門,
売上金額,
count(売上金額) over() as "全体の件数"
from 売上テーブル;
max over
select 部門,
売上金額,
max(売上金額) over() as "全体の最大値"
from 売上テーブル;
min over
select 部門,
売上金額,
min(売上金額) over() as "全体の最小値"
from 売上テーブル;
sum over
select 部門,
売上金額,
sum(売上金額) over() as "全体の合計値"
from 売上テーブル;
avg over
select 部門,
売上金額,
avg(売上金額) over() as "全体の平均値"
from 売上テーブル;
最後に
まずはウィンドウ関数の中で最も基本的な構文の説明と動作をまとめてみました。
次回は、ウィンドウ関数のメインの概念である、ウィンドウ・パーティションといった用語の説明と分析用の関数として初めて登場してきたrank()やlag()といった関数について、それぞれどういったことが出来るか、どのように記述するかといったことをまとめてみたいと思います。
今回はウィンドウ関数の基本機能の紹介だったため、「これくらいのことならjoinとサブクエリでも簡単に出来るよ」と思われた方もいるかもしれません。ですが、ウィンドウ関数で出来ることはまだまだ沢山あります！
次回以降ご紹介したいと思いますが参考までにこういったことも出来るというものを載せておきます。(ウィンドウ関数を使わないでSQLをパズル的に考えるのも個人的には好きなので、こうすれば出来るといったことを考えてみるのも楽しいですよね)
参考１）部門ごとの売上合計を求める
select 部門,
売上金額,
sum(売上金額) over(partition by 部門) as 部門ごとの売上合計
from 売上テーブル;
参考２）部門ごとの売上合計の推移を求める
select 部門,
売上金額,
売上日,
sum(売上金額) over(partition by 部門 order by 売上日) as 部門ごとの売上合計の推移
from 売上テーブル;
参考３）売上ランキングを求める
select rank() over(order by 売上金額 desc) as 売上ランキング,
部門,
売上金額,
売上日
from 売上テーブル
order by 売上ランキング;
それではまた次回にて。