MySQLウィンドウ関数その1 (2022/08/31)

8月 30, 2022

MySQLウィンドウ関数その1 (2022/08/31)

https://blogs.oracle.com/mysql/post/mysql-window-functions-part-1

投稿者： Scott Stroz | MySQL Developer Advocate

SQ:2003 Standard の一部として導入され、MySQL 8.0 で利用できるようになった MySQL のウィンドウ関数は魅力的ですが、初めて使用するときは構文に少し戸惑うかもしれません。この投稿は、ウィンドウ関数について説明するシリーズの最初のもので、構文の分解と異なるウィンドウ関数の例を使用します。

定義

ウィンドウ関数を分解する前に、ウィンドウ関数が何であり、何をするものであるかを定義しておきます。ウィンドウ関数は MySQL の組み込み関数で、クエリ内の定義された行の範囲に対して集約のような機能を提供します。SUM() のような他の集約関数が結果を一行またはグループ化された行にまとめるのに対し、ウィンドウ関数はクエリ結果の各行に対して値を返します。ウィンドウ関数には、SUM() のような集約関数と、RANK() のような非集約関数があります。集計関数をウィンドウ関数として使用する方法については、ウィンドウ関数の構文について説明するときに説明します。この記事では、いくつかの非集計ウィンドウ関数の例を紹介します。

データのセットアップ

先に進む前に、デモのデータをセットアップしておきましょう。以下は、この記事のテーブルを定義し、入力するために使用するスクリプトです。このデータは、プレイヤーが各マッチでポイントを獲得する架空の競技会を表しています。使用する情報は、プレーヤーが蓄積したポイントの合計数です。

-- Create the schema
CREATE SCHEMA IF NOT EXISTS `window-function-demo`;
-- Switch to use the schema
USE `window-function-demo`;
-- Drop table
DROP TABLE IF EXISTS `player`;
-- Create the table
CREATE TABLE IF NOT EXISTS `player` (
`id` INT NOT NULL AUTO_INCREMENT,
`full_name` VARCHAR(45) NOT NULL,
`points` DECIMAL(5,2) NOT NULL,
`group_name` VARCHAR(10),
PRIMARY KEY (`id`)
);

ご覧のとおり、このテーブルには4つのカラムしかありません。主キーであるid、プレイヤーの名前、プレイヤーの得点数、所属するグループです。

-- Insert data
insert into player (full_name, points, group_name) values ('Noe Mann', 155.85, 'Group A');
insert into player (full_name, points, group_name) values ('Precious Cummings', 188.58, 'Group A');
insert into player (full_name, points, group_name) values ('Maryetta Wehner', 81.09, 'Group A');
insert into player (full_name, points, group_name) values ('Todd Sharp', 188.59, 'Group A');
insert into player (full_name, points, group_name) values ('Macie Bartoletti', 142.72, 'Group A');
insert into player (full_name, points, group_name) values ('Emmitt Metz', 155.85, 'Group A');
insert into player (full_name, points, group_name) values ('Ardella Langosh', 188.58, 'Group A');
insert into player (full_name, points, group_name) values ('MARK Reilly', 73.3, 'Group A');
insert into player (full_name, points, group_name) values ('Ardath Greenfelder', 71.4, 'Group A');
insert into player (full_name, points, group_name) values ('Coleman Ferry', 124.2, 'Group A');
insert into player (full_name, points, group_name) values ('Ray Camden', 176.34, 'Group A');
insert into player (full_name, points, group_name) values ('Carolyne Abshire', 176.34, 'Group A');
insert into player (full_name, points, group_name) values ('Jimmie Neighbors', 71.27, 'Group A');
insert into player (full_name, points, group_name) values ('Kevin Hardy', 71.27, 'Group A');
insert into player (full_name, points, group_name) values ('Loralee Fahey', 176.34, 'Group A');
insert into player (full_name, points, group_name) values ('Corrinne Raynor', 86.74, 'Group B');
insert into player (full_name, points, group_name) values ('Parthenia Gutmann', 100.01, 'Group B');
insert into player (full_name, points, group_name) values ('Porfirio Medhurst', 161.45, 'Group B');
insert into player (full_name, points, group_name) values ('Alex Cremin', 173.98, 'Group B');
insert into player (full_name, points, group_name) values ('Sibyl Schaefer', 60.82, 'Group B');
insert into player (full_name, points, group_name) values ('Marsha Robel', 191.62, 'Group B');
insert into player (full_name, points, group_name) values ('Shayne Donnelly', 138.91, 'Group B');
insert into player (full_name, points, group_name) values ('Tyler Stroz', 190.66, 'Group B');
insert into player (full_name, points, group_name) values ('Douglass Grimes', 107.61, 'Group B');
insert into player (full_name, points, group_name) values ('Jesse Rosenbaum', 105.52, 'Group B');
insert into player (full_name, points, group_name) values ('Jeri Schmidt', 50.83, 'Group B');
insert into player (full_name, points, group_name) values ('Roy McHaffa', 183.45, 'Group B');
insert into player (full_name, points, group_name) values ('Scott Stroz', 183.45, 'Group B');
insert into player (full_name, points, group_name) values ('Pamala Mann', 159.33, 'Group B');
insert into player (full_name, points, group_name) values ('Bernita Yundt', 187.6, 'Group B');

ウィンドウ関数構文

RANK()、DENSE_RANK()、OVER()の使用法

ウィンドウ関数を構成する多くの異なる部分がありますが、それらはすべて OVER() 節を共通に持っています。非集約型ウィンドウ関数には OVER() 節が必要ですが、集約型関数は OVER() 節を追加するとウィンドウ関数のように動作するようになります。例えば、得点数に基づいて各選手の順位を表示したい場合、RANK()ウィンドウ関数を使用し、クエリは次のようになります。

SELECT `full_name`,
`points`,
RANK() OVER(
ORDER BY `points` desc
) player_overall_rank,
`group_name`
FROM `player`
ORDER BY player_overall_rank;

ご覧のように、OVER()句の一部にはORDER BY句があります。つまり、この例では、ポイントの降順に基づいてランク値を返すように RANK() に指示しています。

このクエリの結果、各選手のランクは次のようになります。

ランク値は、Ardella Langoshまで連続し、4つ繰り返されることに注目してください。この繰り返しは、ArdellaとPrecious Cummingsが同じ点数で、それぞれ総合4位にランクされているからです。また、次のプレイヤーであるBernita Yundtは6位にランクインしている。この動作は、RANK()のデフォルトの動作で、複数の値が他の値と同じであれば、番号をスキップします。このロジックは、競技のリーダーボードの仕組みと一致することが多いのです。

タイブレーカーとして使用するデータにアクセスできる限り、これを使用して1つのランクにつき1人のプレーヤーだけを表示することができます。私たちの場合、タイブレーカーとしてプレーヤーの名前を使用します。したがって、2つ以上のチームが同点の場合、アルファベット順で名前が最初に来るプレーヤーが上位にランクされることになります。

このタイブレーカーを適用するためのクエリは次のようになります。

SELECT `full_name`,
       `points`,
       RANK() OVER(
          ORDER BY `points` desc, full_name
          ) player_overall_rank,
       `group_name`
FROM `player`
ORDER BY player_overall_rank;

RANK() ウィンドウ関数の ORDER BY に full_name を追加していることに注意してください。

このクエリの結果は、次のように表示されます。

見ての通り、各プレイヤーは、同じスコアを持つプレイヤー間でランクが重複するのではなく、1～10にランク付けされるようになりました。

もし、2つの値が同じ場合に番号を飛ばさないと決めたのであれば、以下のクエリのように DENSE_RANK() を使用することになります。

SELECT `full_name`,
       `points`,
       DENSE_RANK() OVER(
          ORDER BY `points` desc
          ) player_overall_rank,
       `group_name`
FROM `player`
ORDER BY player_overall_rank;

下の画像は、DENSE_RANK()を使用した結果を示しています。Bernita Yundtの順位は、最初の例の6ではなく、5であることに注意してください。

PARTITION BYの使用

各プレイヤーが他のみんなと比較してどこにランク付けされているかを見るのは面白いですが、2 つのグループのそれぞれでプレイヤーがどのようにランク付けされているかを見るのは簡単ではありません。もし、選手がグループ内でどのような順位にいるかを示したい場合は、OVER()句のPARTITION BY句を使用する必要があります。パーティションは、データを異なるセットにグループ化する方法をウィンドウ関数に指示します。PARTITION BYはGROUP BY句と同じように機能します。

以下は、PARTITION BY を使用して、グループ内の各選手の順位を表示する方法です。

SELECT `full_name`,
       `points`,
       RANK() OVER(
          ORDER BY `points` desc
          ) player_overall_rank,
       `group_name`,
       RANK() OVER( PARTITION BY `group_name`
          ORDER BY `points` desc
          ) player_group_rank
FROM `player`
ORDER BY group_name, player_group_rank;

PARTITION BY句を使用する結果セットに、player_group_rankという別のカラムを追加し、group_nameをパーティションとして使用していることに注意してください。パーティションは、グループが変更されたときにランキングを再開するように MySQL に指示します。パーティションを指定しない場合、MySQLは結果セット全体を1つのパーティションとして扱います。このパーティション定義により、両グループにまたがる各プレイヤーのランキングを取得することができました。ORDER BY 句では、まずグループ名でソートし、次にそのグループ内のランキングでソートしていることに注意してください。

以下は、このクエリの結果です。

各選手がグループ内の順位で表示されているのがわかる。Todd Sharpは全体では3位ですが、グループAでは最高位です。また、Ray Camdenは全体では9位ですが、グループAでは4位タイとなっています。

FIRST_VALUE() の例

これで、グループ内の各選手のランキングを確認することができました。しかし、もしある選手がグループ内の1位から何ポイント離れているかを表示したいとしたらどうでしょうか？これは、FIRST_VALUE()というウィンドウ関数を使用することで実現できます。FIRST_VALUE()は、その名前から推測できるように、パーティション内の最初の行のデータ値を返します。これまで見てきたものとは少し異なり、返したい値のカラム名を渡します。

ここでは、あるプレイヤーが指定されたグループの1位から何ポイント離れているかを返すクエリを示します。

SELECT `full_name`,
`points`,
`group_name` group_name,
RANK() OVER(
PARTITION BY `group_name`
ORDER BY `points` desc
) player_group_rank,

        points - FIRST_VALUE( points ) OVER (
            PARTITION BY `group_name`
            ORDER BY points DESC
        ) points_back_of_first

FROM `player`
ORDER BY group_name, player_group_rank;

FIRST_VALUE() に引数として points を渡すと、パーティションの最初の行から points 列の値が取り出されます。1位から何ポイント離れているかを計算するには、これまでと違うことをする必要があります。パーティションの最初の行のポイントを、現在の行のプレーヤーのポイントから引く必要があります。この例では、FIRST_VALUE()を呼び出した結果を方程式の中で使っています。このように、関数呼び出しの結果を単に結果セットに追加するだけでなく、他の関数と同じように使うことができるのが、ウィンドウ関数の魅力です。WHERE句やCASE文の一部として値を使用するなど、他の値と同様に使用することができます（後者の例については、後ほど紹介します）。

このクエリの結果は次のようになります。

このランキングを見れば、ある選手が1位の選手からどれだけ離れているかを判断するのは非常に簡単だ。どのプログラミング言語でも、これをプログラムで処理するのは些細なことでしょう。結果セットの一部としてこのデータを返すことで、よりわかりやすい処理になります。

NTH_VALUE() の例

この架空の競技会では、レギュラーシーズンの終わりにプレーオフがあります。各グループの上位4チームがプレーオフに進出します。プレーヤーが1位から何ポイント離れているかを見るのは良いことですが、プレーヤーにとっては、プレーオフのスポットを獲得するまでに何ポイント離れているかを見る方がより有益かもしれません。そのために、NTH_VALUE()ウィンドウ関数を使用します。

SELECT `full_name`,
       `points`,
       RANK() OVER( PARTITION BY `group_name`
          ORDER BY `points` desc
          ) player_group_rank,
       points - FIRST_VALUE( points ) OVER (
          PARTITION BY `group_name`
          ORDER BY points DESC
          ) points_back_of_first,
       CASE
         WHEN NTH_VALUE( points, 4 ) OVER(
            PARTITION BY `group_name`
            ORDER BY points DESC
            ) IS NULL THEN 0
         ELSE points - NTH_VALUE( points, 4 ) OVER(
            PARTITION BY `group_name`
            ORDER BY points DESC
         )
      END AS points_from_playoffs
FROM `player`
ORDER BY group_name, player_group_rank;

お約束のCASE文での窓関数の使い方の例です。

まず、NTH_VALUE()の使い方を見てから、CASE文の中でどのように使うかをお話しします。見ての通り、NTH_VALUE()には2つの引数を使用しています。1つ目の引数は使用する列で、前の例ではポイント列の値を使用しています。2つ目の引数は、調べたい結果の行番号です。この例では、すべての行を 4 行目の値と比較したいので、4 という値を渡します。つまり、NTH_VALUE() の呼び出しは、パーティションの 4 行目にある points カラムの値を取得するよう MySQL に指示することになります。

なぜ、この値にCASE文を使うことにしたのか不思議に思うかもしれませんが、簡単な答えは一貫性を保つためです。この状況では、結果セットに常に数値を返すようにするためにCASEを使用しています。CASE文のWHENでわかるように、ウィンドウ関数はNULLを返すことがあります。この例では、4行目がまだ存在しないので、最初の3行はNULLを返します。CASE文は、NTH_VALUE()から返された値がNULLの場合、0という値を返すことを保証します（1位から3位の選手はプレーオフ出場までまだ0点だからです）。NTH_VALUE()から返された値がNULLでない場合は、現在の行のポイント値からその値を引いて、結果を返します。

以下は、上記のクエリの結果です。

各グループのプレーオフ出場権から勝ち点0のチームが4つ以上あるのは、不思議に思われるかもしれません。例えば、グループAでは、2位タイが2チーム、4位タイが3チームあります。グループBでは、4位タイが2チームあります。もしシーズン終了時に同率のチームがあった場合、プレーオフに進出するチームを決めるためにタイブレーカーを使用する必要があります。タイブレーカーのルールにもよりますが、タイブレーカーの情報に基づいて、ポイントが同点のチームが表示されるように、クエリにそのロジックを追加することができます。

まとめ

これまで見てきたように、ウィンドウ関数はデータセット内の他の行に関連するデータを返すさまざまな方法を提供します。ウィンドウ関数の基本的な構文について説明し、結果セットで非集計ウィンドウ関数を使用する例を示しました。

MySQL ウィンドウ関数の構文について、よりよく理解していただけたと思います。

次回の記事では、他のウィンドウ関数について調べ、OVER()句のさまざまな部分について理解を深めていきます。

MySQL のウィンドウ関数についてもっと知りたい場合は、ドキュメントにアクセスしてください。

このブログを検索

Oracle Cloud Infrastructure Blog(翻訳)