はじめに
MySQL8.0がリリースされたことにより、Window関数の利用が可能になりました。
Oracle、PostgreSQL、SQL Server等でも利用できる関数ですが、「難しそう」、「どういう場合に使えば良いか分からない」というイメージを持たれている方もいると思いますので、この記事ではシンプルにどのようなことができるのかを紹介していきます。
過去の記事でもWindow関数について分かりやすく記載しておりますので、確認されていない方は是非こちらの記事もご参照ください。
・MySQL8.0のWindow関数を分かりやすく紹介する（１.「集計関数×Window関数」のコラボ）
・MySQL8.0のWindow関数を分かりやすく紹介する（２．順位・連番の付け方（基本編））
・MySQL8.0のWindow関数を分かりやすく紹介する（３．順位・連番の付け方（発展編））
・MySQL8.0のWindow関数を分かりやすく紹介する（４．そのデータの取り方は・・・）
今回は第五弾として「謎多きフレーム句を使うと良いことはあるのか？」というお題で記載します。
Window関数のフレーム句は、「どの行からどの行まで」という集計・分析の範囲を調整することができます。
フレーム句の仕様については、以下のリファレンスに記載されておりますので、参照頂ければと思います。
https://dev.mysql.com/doc/refman/8.0/ja/window-functions-frames.html
データ準備
検証用に１つのテーブルを作成し、テストデータを投入します。
テーブル構成としては、「年」、「国」、「商品」別の利益データを管理しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
mysql> #Create Table mysql> CREATE TABLE sales -> ( -> year INT, -> country VARCHAR(20), -> product VARCHAR(32), -> profit INT -> ); Query OK, 0 rows affected (0.08 sec) mysql> #Data Insert mysql> INSERT INTO sales(year, country, product, profit) VALUES -> ( 2000, 'Finland', 'Computer', 1500), -> ( 2000, 'Finland', 'Phone', 100), -> ( 2001, 'Finland', 'Phone', 10), -> ( 2000, 'India', 'Calculator', 75), -> ( 2000, 'India', 'Calculator', 75), -> ( 2000, 'India', 'Computer', 1200), -> ( 2000, 'USA', 'Calculator', 75), -> ( 2000, 'USA', 'Computer', 1500), -> ( 2001, 'USA', 'Calculator', 50), -> ( 2001, 'USA', 'Computer', 1500), -> ( 2002, 'USA', 'Computer', 1200), -> ( 2003, 'USA', 'Computer', 150), -> ( 2004, 'USA', 'Computer', 100); Query OK, 13 rows affected (0.01 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> #confirmation mysql> SELECT * FROM sales; +------+---------+------------+--------+ | year | country | product | profit | +------+---------+------------+--------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2001 | Finland | Phone | 10 | | 2000 | India | Calculator | 75 | | 2000 | India | Calculator | 75 | | 2000 | India | Computer | 1200 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 1500 | | 2002 | USA | Computer | 1200 | | 2003 | USA | Computer | 150 | | 2004 | USA | Computer | 100 | +------+---------+------------+--------+ 13 rows in set (0.01 sec) |
見えないけれども、実はしっかり働いている「フレーム」句
「集計関数×Window関数」のコラボで、
country毎のprofitの合計を出力するSQLを紹介している内容になりますが、
まずは以下のSQLと出力結果をご確認ください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> SELECT -> * -> , SUM(profit) OVER(PARTITION BY country) AS sum_country_profit -> FROM sales; +------+---------+------------+--------+--------------------+ | year | country | product | profit | sum_country_profit | +------+---------+------------+--------+--------------------+ | 2000 | Finland | Computer | 1500 | 1610 | | 2000 | Finland | Phone | 100 | 1610 | | 2001 | Finland | Phone | 10 | 1610 | | 2000 | India | Calculator | 75 | 1350 | | 2000 | India | Calculator | 75 | 1350 | | 2000 | India | Computer | 1200 | 1350 | | 2000 | USA | Calculator | 75 | 4575 | | 2000 | USA | Computer | 1500 | 4575 | | 2001 | USA | Calculator | 50 | 4575 | | 2001 | USA | Computer | 1500 | 4575 | | 2002 | USA | Computer | 1200 | 4575 | | 2003 | USA | Computer | 150 | 4575 | | 2004 | USA | Computer | 100 | 4575 | +------+---------+------------+--------+--------------------+ 13 rows in set (0.00 sec) |
SQL内のWindow関数の部分を見るとOVER句の後ろにPARTITION BY句があります。
このPARTITION BY句はレコード分割の単位を指定しています。さらにPARTITION BY countryと指定することでcountry毎に区切って、profitの合計が出力できていますね。
では、PARTITION BY countryの後に、ORDER BY profit descを追加するとどうなるでしょう？
「ORDER BYでデータを並び替えたところで、profitをサマリーしていることには変わりないので、合計値は変わらない！」・・・と思われる方もいると思います。
以下は実行結果になりますが、果たして皆さんが想像していた結果になっているでしょうか？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT -> * -> , SUM(profit) OVER(PARTITION BY country) AS sum_country_profit -> , SUM(profit) OVER(PARTITION BY country ORDER BY profit desc) AS sum_country_profit_desc -> FROM sales; +------+---------+------------+--------+--------------------+-------------------------+ | year | country | product | profit | sum_country_profit | sum_country_profit_desc | +------+---------+------------+--------+--------------------+-------------------------+ | 2000 | Finland | Computer | 1500 | 1610 | 1500 | | 2000 | Finland | Phone | 100 | 1610 | 1600 | | 2001 | Finland | Phone | 10 | 1610 | 1610 | | 2000 | India | Computer | 1200 | 1350 | 1200 | | 2000 | India | Calculator | 75 | 1350 | 1350 | | 2000 | India | Calculator | 75 | 1350 | 1350 | | 2000 | USA | Computer | 1500 | 4575 | 3000 | | 2001 | USA | Computer | 1500 | 4575 | 3000 | | 2002 | USA | Computer | 1200 | 4575 | 4200 | | 2003 | USA | Computer | 150 | 4575 | 4350 | | 2004 | USA | Computer | 100 | 4575 | 4450 | | 2000 | USA | Calculator | 75 | 4575 | 4525 | | 2001 | USA | Calculator | 50 | 4575 | 4575 | +------+---------+------------+--------+--------------------+-------------------------+ 13 rows in set (0.00 sec) |
では、結果です。
sum_country_profitはcountry毎のprofitの合計が出力できています。
sum_country_profit_descはcountry毎のprofitの累積が出力されているように見えますね・・・。
差分があった理由について、簡単な検証で確認をしてみたいと思います。
検証
過去に弊社ブログ(MySQLでJSON型を使う(JSON関数編))でも紹介したJSON_ARRAYAGG関数を使います。
JSON_ARRAYAGG関数を利用することの狙いは、「SUM関数内で具体的に何の値をサマリーしているかが見えます」ので、これを念頭に置いて以下の結果をご確認ください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> SELECT -> * -> , SUM(profit) OVER c AS sum_country_profit -> , SUM(profit) OVER o AS sum_country_profit_desc -> , JSON_ARRAYAGG(profit) OVER c AS json_country_profit -> , JSON_ARRAYAGG(profit) OVER o AS json_country_profit_desc -> FROM sales -> WINDOW c AS (PARTITION BY country) -> ,o AS (PARTITION BY country ORDER BY profit desc) ; +------+---------+------------+--------+--------------------+-------------------------+--------------------------------------+--------------------------------------+ | year | country | product | profit | sum_country_profit | sum_country_profit_desc | json_country_profit | json_country_profit_desc | +------+---------+------------+--------+--------------------+-------------------------+--------------------------------------+--------------------------------------+ | 2000 | Finland | Computer | 1500 | 1610 | 1500 | [1500, 100, 10] | [1500] | | 2000 | Finland | Phone | 100 | 1610 | 1600 | [1500, 100, 10] | [1500, 100] | | 2001 | Finland | Phone | 10 | 1610 | 1610 | [1500, 100, 10] | [1500, 100, 10] | | 2000 | India | Computer | 1200 | 1350 | 1200 | [75, 75, 1200] | [1200] | | 2000 | India | Calculator | 75 | 1350 | 1350 | [75, 75, 1200] | [1200, 75, 75] | | 2000 | India | Calculator | 75 | 1350 | 1350 | [75, 75, 1200] | [1200, 75, 75] | | 2000 | USA | Computer | 1500 | 4575 | 3000 | [75, 1500, 50, 1500, 1200, 150, 100] | [1500, 1500] | | 2001 | USA | Computer | 1500 | 4575 | 3000 | [75, 1500, 50, 1500, 1200, 150, 100] | [1500, 1500] | | 2002 | USA | Computer | 1200 | 4575 | 4200 | [75, 1500, 50, 1500, 1200, 150, 100] | [1500, 1500, 1200] | | 2003 | USA | Computer | 150 | 4575 | 4350 | [75, 1500, 50, 1500, 1200, 150, 100] | [1500, 1500, 1200, 150] | | 2004 | USA | Computer | 100 | 4575 | 4450 | [75, 1500, 50, 1500, 1200, 150, 100] | [1500, 1500, 1200, 150, 100] | | 2000 | USA | Calculator | 75 | 4575 | 4525 | [75, 1500, 50, 1500, 1200, 150, 100] | [1500, 1500, 1200, 150, 100, 75] | | 2001 | USA | Calculator | 50 | 4575 | 4575 | [75, 1500, 50, 1500, 1200, 150, 100] | [1500, 1500, 1200, 150, 100, 75, 50] | +------+---------+------------+--------+--------------------+-------------------------+--------------------------------------+--------------------------------------+ 13 rows in set (0.01 sec) |
ケース1：sum_country_profitは合計値が表示される理由は？
json_country_profitの値を見ると、country毎のprofitの値が「全て」表示されていますので、sum_country_profitの値もcountry毎のprofitの合計が出力されていますね。
ここでカギとなるのが、見えないところで働いている謎のフレーム句です！
Window関数を利用する上でフレーム句まで記述すると結構長くなります。
なのでWindow関数を考案した方々はきっとフレーム句の記述を省略可能とし、さらに省略してもいい感じに働かせて、かつ使い勝手の良いものにしようと考えたのだと思います。
ではフレーム句の省略・未省略した場合の記述を見たいと思います。
以下を比較したときに、未省略時では「長い」、「(絶対に)記述したくない」、「英語アレルギーが・・・」という所感になるのではと思います。
・フレーム句省略時：SUM(profit) OVER(PARTITION BY country)
・フレーム句未省略時：SUM(profit) OVER(PARTITION BY country RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
・・・とは言え、カギを握っているのはフレーム句になります。
ここでは、なぜ「country毎のprofitの値が全て対象になるか」を理解して頂きたいので、
注目すべき2点だけを記載します。
①BETWEEN構文
WHERE句を記述する上で「BETWEEN 下限値 AND 上限値」のように範囲を指定する方法があります。
Window関数でも同じ発想で、下限値～上限値までの範囲を指定することができます。
②UNBOUNDED PRECEDING と UNBOUNDED FOLLOWING
UNBOUNDED PRECEDINGは「PARTITION句の最初の行」、
UNBOUNDED FOLLOWINGは「PARTITION句の最後の行」を示しています。
つまり①、②からPARTITION句(country)の最初～最後の行までを範囲としてSUM関数で計算していることがわかります。範囲のイメージは以下のようになります。
ケース2：sum_country_profit_descは累計値が表示される理由は？
json_country_profit_descの値は、country毎の「最初～現在の行までのprofitの値」が示されていますので、sum_country_profit_descの値もcountry毎のprofitの累計が出力されていますね。
ケース1と同様にフレーム句が省略されています。
しかし、Window関数内でORDER BYを指定することによって省略されているフレーム句の内容が異なります。
・フレーム句省略時：SUM(profit) OVER(PARTITION BY country ORDER BY profit desc)
・フレーム句未省略時：SUM(profit) OVER(PARTITION BY country ORDER BY profit desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
ケース1と比較すると省略されているフレーム句の違いは「CURRENT ROW」の箇所になり、
CURRENT ROWは「現在の行」を示しています。
つまりPARTITION句(country)の最初～現在の行までを範囲としているため、
SUM関数で計算していることがわかります。
左側に「現在の行」と右側に「集計する範囲」のイメージを以下に記載しました。現在の行によって集計範囲が異なり、結果として累計値が表示されます。
フレーム句において、RANGE と ROWS の違いは？
さて、Window関数内でORDER BYを指定することによって、SUM関数においては累計値が表示できます！
・・・と認識頂いたと思いますが、以下のSQLと結果をみると少し違和感があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT -> * -> , SUM(profit) OVER rng AS sum_country_profit_desc -> , JSON_ARRAYAGG(profit) OVER rng AS json_country_profit_desc -> FROM sales -> WHERE -> country = 'India' -> AND product = 'Calculator' -> WINDOW rng AS (PARTITION BY country ORDER BY profit desc) -> ; +------+---------+------------+--------+-------------------------+--------------------------+ | year | country | product | profit | sum_country_profit_desc | json_country_profit_desc | +------+---------+------------+--------+-------------------------+--------------------------+ | 2000 | India | Calculator | 75 | 150 | [75, 75] | | 2000 | India | Calculator | 75 | 150 | [75, 75] | +------+---------+------------+--------+-------------------------+--------------------------+ 2 rows in set (0.00 sec) |
なんと、累計値ではなく合計値が表示されています・・・。
この原因も見えていないフレーム句にあり、具体的な原因はRANGEを使用していることにありますので、
フレーム句を省略せず記載しつつ、解決方法について説明していきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> SELECT -> * -> , SUM(profit) OVER rng AS sum_country_profit_rng -> , JSON_ARRAYAGG(profit) OVER rng AS json_country_profit_rng -> , SUM(profit) OVER rw AS sum_country_profit_rw -> , JSON_ARRAYAGG(profit) OVER rw AS json_country_profit_rw -> FROM sales -> WHERE -> country = 'India' -> AND product = 'Calculator' -> WINDOW rng AS (PARTITION BY country ORDER BY profit desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -> , rw AS (PARTITION BY country ORDER BY profit desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -> ; +------+---------+------------+--------+------------------------+-------------------------+-----------------------+------------------------+ | year | country | product | profit | sum_country_profit_rng | json_country_profit_rng | sum_country_profit_rw | json_country_profit_rw | +------+---------+------------+--------+------------------------+-------------------------+-----------------------+------------------------+ | 2000 | India | Calculator | 75 | 150 | [75, 75] | 75 | [75] | | 2000 | India | Calculator | 75 | 150 | [75, 75] | 150 | [75, 75] | +------+---------+------------+--------+------------------------+-------------------------+-----------------------+------------------------+ 2 rows in set (0.00 sec) |
フレーム句内の違いは「RANGE」か「ROWS」のどちらを使っているかという点です。
「ROWS」は、文字通り「行単位で計算」しますが、
「RANGE」は、「同じ値を持つ行をまとめて計算」します。
今回の例では、デフォルトの「RANGE」が利用され、
さらにprofitが同じ値の場合はサマリーされる仕組みになっています。
１行ずつ累計値を表示したい場合は「ROWS」を利用することが解決方法になります。
省略できる反面、見えないので何が起きているかもわからないので、知らないと「謎」ですよね・・・。
フレーム句で範囲を指定する。その活用方法は？
フレーム句で範囲を指定することで、移動平均を算出することができます。
移動平均とは、ある一定間隔ごとの平均値を指します。
例えばダイエットする上で、必ず体重と向き合わなければなりませんが、
いざ実施してみると１日毎の体重だけ注目して、「今日は増えた、減った」で一喜一憂し、
嫌になるという人もいると思います。
もし長期的に目標を決めて体重を減らしたいのであれば、
１週間単位の体重平均を算出して、平均の傾き具合から目標の体重に近くなっているかを観察して必要であればさらに対策を考え実行していくのが良いとされていて、こういった一定期間の平均値を観察するような場合は移動平均を利用します。
以下は移動平均を算出するSQLの例になります。
1 PRECEDINGは現在の行より１行前の行から、
1 FOLLOWINGは現在の行より１行後の行までを見る定義になっています。
つまり1行前～1行後の範囲で平均を算出しています。
カラムのデータ型が日付型であればX日前、X日後というように記載することもできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> SELECT -> * -> , AVG(profit) OVER o AS avg_country_profit_desc -> , JSON_ARRAYAGG(profit) OVER o AS json_country_profit_desc -> FROM sales -> WHERE -> country = 'USA' -> AND product = 'Computer' -> WINDOW o AS (ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) -> ; +------+---------+----------+--------+-------------------------+--------------------------+ | year | country | product | profit | avg_country_profit_desc | json_country_profit_desc | +------+---------+----------+--------+-------------------------+--------------------------+ | 2000 | USA | Computer | 1500 | 1500.0000 | [1500, 1500] | | 2001 | USA | Computer | 1500 | 1400.0000 | [1500, 1500, 1200] | | 2002 | USA | Computer | 1200 | 950.0000 | [1500, 1200, 150] | | 2003 | USA | Computer | 150 | 483.3333 | [1200, 150, 100] | | 2004 | USA | Computer | 100 | 125.0000 | [150, 100] | +------+---------+----------+--------+-------------------------+--------------------------+ 5 rows in set (0.00 sec) |
ちなみに以下のようにBETWEENを使用せず範囲を指定することもできます。
ROWSを利用すると現在の行しか集計されないのでSUM関数の意味がなくなります。
RANGEを利用すると同一値がある場合、SUM関数で集計できますが、
どちらも利用できるシチュエーションが思い浮かばず「謎」です・・・。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT -> * -> , SUM(profit) OVER rng AS sum_country_profit_rng -> , JSON_ARRAYAGG(profit) OVER rng AS json_country_profit_rng -> , SUM(profit) OVER rw AS sum_country_profit_rw -> , JSON_ARRAYAGG(profit) OVER rw AS json_country_profit_rw -> FROM sales -> WHERE -> country = 'USA' -> AND product = 'Computer' -> WINDOW rng AS (ORDER BY profit RANGE CURRENT ROW) -> , rw AS (ORDER BY profit ROWS CURRENT ROW) -> ; +------+---------+----------+--------+------------------------+-------------------------+-----------------------+------------------------+ | year | country | product | profit | sum_country_profit_rng | json_country_profit_rng | sum_country_profit_rw | json_country_profit_rw | +------+---------+----------+--------+------------------------+-------------------------+-----------------------+------------------------+ | 2004 | USA | Computer | 100 | 100 | [100] | 100 | [100] | | 2003 | USA | Computer | 150 | 150 | [150] | 150 | [150] | | 2002 | USA | Computer | 1200 | 1200 | [1200] | 1200 | [1200] | | 2000 | USA | Computer | 1500 | 3000 | [1500, 1500] | 1500 | [1500] | | 2001 | USA | Computer | 1500 | 3000 | [1500, 1500] | 1500 | [1500] | +------+---------+----------+--------+------------------------+-------------------------+-----------------------+------------------------+ 5 rows in set (0.01 sec) |
但し、以下のようにUNBOUNDED PRECEDINGを指定すると
「最初～現在の行までの範囲」を扱ってくれるので、これは使い道がありますね！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT -> * -> , SUM(profit) OVER rng AS sum_country_profit_rng -> , JSON_ARRAYAGG(profit) OVER rng AS json_country_profit_rng -> , SUM(profit) OVER rw AS sum_country_profit_rw -> , JSON_ARRAYAGG(profit) OVER rw AS json_country_profit_rw -> FROM sales -> WHERE -> country = 'USA' -> AND product = 'Computer' -> WINDOW rng AS (ORDER BY profit RANGE UNBOUNDED PRECEDING) -> , rw AS (ORDER BY profit ROWS UNBOUNDED PRECEDING) -> ; +------+---------+----------+--------+------------------------+------------------------------+-----------------------+------------------------------+ | year | country | product | profit | sum_country_profit_rng | json_country_profit_rng | sum_country_profit_rw | json_country_profit_rw | +------+---------+----------+--------+------------------------+------------------------------+-----------------------+------------------------------+ | 2004 | USA | Computer | 100 | 100 | [100] | 100 | [100] | | 2003 | USA | Computer | 150 | 250 | [100, 150] | 250 | [100, 150] | | 2002 | USA | Computer | 1200 | 1450 | [100, 150, 1200] | 1450 | [100, 150, 1200] | | 2000 | USA | Computer | 1500 | 4450 | [100, 150, 1200, 1500, 1500] | 2950 | [100, 150, 1200, 1500] | | 2001 | USA | Computer | 1500 |  4450 | [100, 150, 1200, 1500, 1500] | 4450 | [100, 150, 1200, 1500, 1500] | +------+---------+----------+--------+------------------------+------------------------------+-----------------------+------------------------------+ 5 rows in set (0.01 sec) |
まとめ
某ディスカウントストアのお弁当・総菜の開発・販売を行う上で・・・
「みんなの75点より、誰かの120点。」を掲げています。
例えば「鳥の皮だけ弁当」「あんだく溺れ天津飯」「焼肉のタレドバドバおにぎり」など・・・
「好きな人は絶対に好き！」、「誰かにとっての120点」をいただけるようなメニューを日々開発し、
時には、愛すべきおかずの脇役にスポットライトを当て、「脇役を主役」にしたお弁当も提供しています！！
私は「アメリカンドッグのカリカリを詰めたお弁当かお惣菜」を是非食べてみたい（あるのかな？）
あとは、子供のころに喫茶店で出してくれた揚げたての「スピン」や
お蕎麦を油で揚げて塩をふったものも商品化を是非！
「(主役ではないけど)これが良いんだよね！」というものは他にもたくさんあると思いますが、
そういうものを考えてみるのも楽しそうですし、会話のネタになりそうですね！
決して主役とは言えない、フレーム句にスポットを当てましたが、
理解することで少しでも「面白いな！」、「今度使ってみよう！！」と思って頂けると幸いです。
謎解きが好きな方は、フレーム句の謎を考えながら使うことでより詳しくなりフレーム句が好きになるかもしれませんね！
