はじめに
データ分析とデータ品質改善に従事してきた筆者が、SQLを用いた分析の基本である「ウィンドウ関数」の使い方とデータ品質の調査改善を行う手法をまとめてみようと思います。
こちらの記事は前回投稿した「そろそろSQLのウィンドウ関数を理解したい - 連載1/3話」の続きとなっております。連載1話ではウィンドウ関数の基礎と動作イメージが書いてありますので、もし未読でしたらそちらも見ていただけると幸いです。
環境
本記事の内容は、下記RDBMSで動作確認しています。記事中の実行結果画面などの図はPostgreSQLの結果を使います。
- PostgreSQL 11.5
- Oracle xe 18.4
- SQL Server 2017 Express
- MySQL 8.0.17
※MySQLは2018年4月リリースの8.0からウィンドウ関数が搭載されました。つい最近の出来事なので、8.0より前のバージョンを使用しているシステムがまだ多いので使えるか要確認。
前回の記事の簡単な振り返り
- ウィンドウ関数とは「他のレコードの情報」を使って値を求めることができる関数
- ウィンドウ関数は大きく以下の二種類ある
- sum や count といった集計関数をウィンドウ関数として利用するもの
- 分析専用のウィンドウ関数を利用するもの
- ウィンドウ関数は sum(売上金額) over() という構文で記述することができる
ウィンドウとは集計する範囲のこと
それではさっそく、ウィンドウ関数の名前の由来となっているウィンドウについて解説していきます。ウィンドウとは集計・集約をする範囲・まとまりのことです。これまでの説明では、ウィンドウの範囲を指定していなかったので、全体(全レコード)を対象にしてウィンドウ関数は動いていました。次の図のようなイメージです。
そして、ここまでの説明で想像できたかもしれませんが、このウィンドウの範囲を自由に設定することができます。例えば、部門の値が同じもの同士をウィンドウとした場合の動きは次の通りです。
ウインドウの範囲を指定するには
over()の中に partition by <列名> の形式で記載します。
(partitionとは：パーティション / 仕切り。「<列名>による仕切り」と解りやすいですね)
早速SQLの構文と結果イメージを見てみましょう。
select 部門,
売上金額,
sum(売上金額) over(partition by 部門) as 部門ごとの売上金額合計
from 売上テーブル;
部門ごとにウィンドウを分けて、その中で sum が実行されています。ここまでは、通常の group by で列を指定した時と同じ考え方なので理解しやすいと思います。
レコードの並び順が関係してくるウィンドウ関数とは
それでは続いて、レコードの並び順が関係してくるウィンドウ関数の仕組みについて説明します。
例えば日付が古いものから連番を振りたい、売上金額の多いものから連番を振りたい、といった時にウィンドウ関数の row_number を使うと簡単に実現できます。
ウインドウ関数の中で並び順を指定するには
over()の中に order by <列名> の形式で記載します。
それではSQLと結果イメージを確認してみましょう。ここでは売上金額の昇順に連番を振ってみます。
説明しやすくするため、並び順や金額値を一部変更しています。
update 売上テーブル set 売上金額 = 30000 where 売上金額 = 3000;
update 売上テーブル set 売上金額 = 60000 where 売上金額 = 6000;
update 売上テーブル set 売上金額 = 90000 where 売上金額 = 9000;
select 部門,
売上金額,
row_number() over(order by 売上金額) as 売上金額昇順の行番号
from 売上テーブル;
結果イメージにあるように、売上金額が少ない順番で連番が付与できていますね。この例では partition by を指定していないので、ウィンドウは全体を対象として売上金額の昇順で連番を付与しています。また、降順で扱いたい場合はdescを指定すれば降順で処理されます。(並び順の指定を省略した場合はascと見なされ昇順となります。この記事では説明の簡略化のためascは省略しております)
partition by でウィンドウを指定した状態で並び順を指定することもできます。部門ごとに連番を1から振る場合は次のように記述します。
select 部門,
売上金額,
row_number() over(partition by 部門 order by 売上金額) as 部門ごとの売上金額昇順の行番号
from 売上テーブル;
上記結果の通り、部門ごとにウィンドウを分けて、その中で売上金額の昇順で連番を付与できました。
集計関数のウィンドウ関数をorder byすると推移が見れる
先ほどは row_number という分析専用のウィンドウ関数を使ってみましたが、集計関数である sum や avg に対して order by を指定すると、累積和、累積平均を求めることができます。累積和とは、それまでの和(合計)に現在の行の値を足し合わせた合計値のことです。
それではSQLと結果イメージを確認してみましょう。ここでは売上日の昇順に売上金額の累積和を求めてみます。
select 部門,
売上金額,
売上日,
sum(売上金額) over(order by 売上日) as 売上金額の累積和
from 売上テーブル;
続いて partition by を指定して部門ごとに累積和を求めてみましょう。
select 部門,
売上金額,
売上日,
sum(売上金額) over(partition by 部門 order by 売上日) as 部門ごとの売上金額の累積和
from 売上テーブル;
ウィンドウ関数の over() の中に order by が登場するまでは、「通常の集計関数の結果をレコードに付与できる」という動きだったので、ウィンドウ範囲 = 処理のまとまり として理解しやすかったと思います。しかし、上記のように order by で並び順を指定した時、ウィンドウの範囲が集計するごとに一つずつ増えて、最初のレコードから現在処理中のレコードまでがウィンドウ範囲となり動的に範囲が変わります。次の説明図でイメージをつかんでください。(このウィンドウの範囲の事をフレームという用語で説明する事もありますが、本記事ではウィンドウ範囲と明記します。)
ウィンドウ範囲の指定方法
これまでに出てきたウィンドウの範囲は
- レコード全体
- partition by で指定した範囲の全体
- order by で並び替えた時の最初の行から現在行
というものでしたが、ウィンドウの範囲を
- 自分のレコード前後の任意の範囲
と設定する事ができます。少しだけ複雑な考え(+長いスペルの英単語)が登場しますが、ウィンドウ関数の仕組みで抑えておくポイントはこれで最後です。頑張りましょう！
ウインドウの範囲を指定するには
over() の中の order by <列名> の後に rows between <開始行> and <終了行> 形式で記載します。
<開始行>には次の指定ができます。
| 指定値 | 説明 |
|---|---|
| current row | 現在行から |
| n preceding | n行前から |
| unbounded preceding | 最初の行から |
<終了行>には次の指定ができます。
| 指定値 | 説明 |
|---|---|
| current row | 現在行まで |
| n following | n行後まで |
| unbounded following | 最後の行まで |
そのため、先ほどの累積和を求めるSQLでは rows between を省略していましたが、
rows between unbounded preceding and current row
が暗黙的に指定されて、最初の行から現在の行をウィンドウの範囲として集計処理が動いていたことになります。念のため確認してみましょう。
select 部門,
売上金額,
売上日,
sum(売上金額) over(order by 売上日 rows between unbounded preceding and current row) as 売上金額の累積和
from 売上テーブル;
同じ結果になりました。 rows between を使うとウィンドウ範囲を細かく指定できますが、ウィンドウ関数の種類によって、ウィンドウ範囲の設定は、ある意味決まり文句というか、この関数の場合はこれ、この場合はこれとパターン化されることも多いです。また複雑な使い方をしない場合は rows between の記載自体を省略して利用できるものがほとんどです。
そのため、ここでは「ウィンドウ関数の over() の中で order by を指定すると、 rows between でウインドウの範囲を指定することができる。省略した場合は最初のレコードから現在処理中のレコードまでがウィンドウ範囲となる」と覚えておいてください。
まとめ
今回は over() の中で partition by, order by, rows between といった構文の指定方法と動きを説明してみました。新しい事が多く出てきてきましたが、ここまで読んでいただけたならばウィンドウ関数がどういう仕組みなのか、どういうことができるのかイメージできたと思います。
次回の「そろそろSQLのウィンドウ関数を理解したい - 連載3/3話」では、
- ウィンドウ関数一覧に記載した関数の説明
を中心に、関数ごとにSQL実行結果と解説をまとめたいと思います。
それではまた次回にて！お楽しみに。