ウィンドウ関数の利用方法

  • 投稿日:
  • by denden
  • カテゴリ:

初めてMySQLを使用したのは15年ほど前でしょうか。仕事で、あるシステムの自動機オプションサーバーで使用しました。MySQLにはこの頃から今に至るまで波乱万丈と言える歴史が有ります。記憶に残っているのは、Michael Widenius氏がMariaDBをリリースした時です。MySQLの歴史については、下記が良くまとまっています。

MySQLの歴史が面白い - Qiita

現在はwebkoza.comのMT5.2を動かすためにLinux上でMySQL 8.0.13を使用しています。最近の仕事で、社内業務システムをMySQL8.0.23とStrawberry Perlを使用して開発したので、ここではこの時初めて使用した「ウィンドウ関数」について記事にしておきます。DBを使っていてまだウィンドウ関数を使ったことの無い人向けの記事です。MySQLがウィンドウ関数に対応したのは8.0からだそうです。まあまあ最近の技術なんです。

１．ウィンドウ関数（分析関数）って何？

色々言い方は有ると思いますが、私なりに定義してみれば、

抽出したレコード間の集計を行いたい時に抽出したレコード集合に対してウィンドウを開いた上で集計を行う関数で、通常の集計関数は1つの結果行にグループ化しますが、ウィンドウ関数は抽出したレコード毎に結果を生成します。

となります。こう書くと使ったことの無い人は何が何だかわからないかもしれません。実例を挙げながら説明することにします。

２．基本

下記の担当者マスターテーブルが有るとします。

select * from testdb.person_mas as pm;

fig1_20210918.PNG

このテーブルには10人が登録されており、連番、名前、所属コード、役職コード、登録日時が入力されています。

下記のSQL文で所属部署毎の登録人数をcount関数を使って抽出した結果が次の図です。group by 句を使ってグループ化した人数を表示しています。

select pm.divison_cd,count(*) from testdb.person_mas as pm group by pm.divison_cd;

fig2_20210918.PNG

ここでいきなりウィンドウ関数としてのcount(*)を導入します。下記のSQL文は所属コードをグループ化する事なくレコード数をcount関数で数えた結果ですが、over句を使用しています。over句を使うとこの関数はウィンドウ関数となるのです。over句の役割は、windowを開くことと抽出結果表の範囲指定を行うことです。

select pm.divison_cd,count(*) over() from testdb.person_mas as pm;

fig3_20210918.PNG

結果表は全員分の10行となっていますが、数えた値は全て10となっています。これは各行毎に全員の人数分10人を数えたということです。これが「ウィンドウ関数は抽出したレコード毎に結果を生成」という意味です。但しこの抽出結果自体あまり使い道はなさそうです。

では、ウィンドウ関数を使って且つ最初のSQL文と同様に所属コードでグループ化したらどうなるでしょうか。結果は下記です。

select pm.divison_cd,count(*) over() from testdb.person_mas as pm group by pm.divison_cd;

fig4_20210918.PNG

グループ化はされているのですが、ウィンドウ関数の働きによって各行毎に全行数4が全ての行に出力されています。これも使い道は無さそうです。ではどういう時にウィンドウ関数は使うのか？その話に入る前に、over句について少し説明します。いままでover句に引数を指定していませんでした。引数には集計範囲を指定できます。指定が無ければ全レコードが対象となります。引数の種類としては、

  • Partition By指定
  • Order By指定
  • Window (Frame)指定

の3種類です。ここではorder by 指定のみ説明します。そのほかに付いては下記あたりを参考にしてください。

分析関数（ウインドウ関数）をわかりやすく説明してみた

上記のsql文でover句にorder by指定をしてみると下記の通りになります。

select pm.divison_cd,count(*) over(order by pm.divison_cd) from testdb.person_mas as pm group by pm.divison_cd;

fig5_20210918.PNG

こんどは各行が4ではありませんね。これは、「over句のoder by 指定は、行を順番に並べた上で、最初の行から現在行までのみを集計の対象にする」という仕様だからです。これにより1行目は1行のみが集計対象、2行目は2行が集計対象・・4行目は4行が集計対象となるためにこのような結果となっているわけです。このことは累積集計を行うような用途では大変便利な機能となります。

３．実例

あまり高度な例は紹介できませんが、私が最近の仕事で使用したlag,lead関数を使用した例を説明用に単純化して紹介します。

まず、本稿で使用しているテーブルから下記の＜命題＞を考えます。

＜命題＞
JさんはAさんが登録された日時から何日後に登録されたか

これをウィンドウ関数を使って計算するためにlagおよびlead関数を使います。lag関数は「指定行数前のレコードを対象として集計」、lead関数は「指定行数後のレコードを対象として集計」という仕様です。

下記はlag関数を使用して、1人前の人の登録日時を併記させたものです。

select pm.person_no,pm.person_name,pm.reg_date,lag(pm.reg_date,1) over(order by pm.person_no) as after_reg_date from testdb.person_mas as pm;

fig6_20210918.PNG

下記はlead関数を使用して、1人後の人の登録日時を併記させたものです。

select pm.person_no,pm.person_name,pm.reg_date,lead(pm.reg_date,1) over(order by pm.person_no) as after_reg_date from testdb.person_mas as pm;

fig7_20210918.PNG

1人前や後のレコードが存在しない場合はNULLとなっていることがわかります。ここで慣れている人は気づいたかもしれませんが、これら結果表を見ると、同じテーブルを1行ずらして外部結合したような結果になっていることがわかります。後で説明しますが、一般的にlag,leadを使って抽出する結果は、ウィンドウ関数を使わずに外部結合を使って実現する事も可能となる場合が有ります。

それでは命題を実現しするにはどうすれば良いでしょうか。上記のsql文でJさんとAさんのみを指定して、lagかlead関数を使ってAさんとJさんの登録日を同一レコードに生成しその差を取れば良いのです。下記はlag関数を使って実現したものです。unix_timeatampは'1970-01-01 00:00:00'から引数までの秒値を返します。2行抽出されますが2行目のdiff_from_AはNULL値となる意味のない行なのでlimit 1で最初の1行のみにしています。

＜命題＞をウィンドウ関数で実現したSQL文

select pm.person_name,(unix_timestamp(pm.reg_date) - unix_timestamp(lag(pm.reg_date,1) over(order by pm.person_no)))/3600/24 as diff_from_A
 from testdb.person_mas as pm
 where pm.person_no = 1 or pm.person_no = 10 order by pm.person_no desc limit 1;

前述で示唆したようにこれは、下記のような外部結合を使っても実現可能です。person_noを9だけずらすという結合条件です。

＜命題＞を外部結合で実現したSQL文 

select pm1.person_name,(unix_timestamp(pm1.reg_date) - unix_timestamp(pm0.reg_date))/3600/24 as diff_from_A
 from (select pm.person_no,pm.person_name,pm.reg_date from testdb.person_mas as pm where pm.person_no = 10) as pm1
 right join (select pm.person_no,pm.person_name,pm.reg_date from testdb.person_mas as pm where pm.person_no = 1) as pm0
 on pm1.person_no = pm0.person_no + 9;

どちらも同じ下記の結果を得ることができます。つまりJさんはAさんの９日後に登録されたということです。ウィンドウ関数を使用した方がすっきりしていますね。

fig8_20210918.PNG