MySQL8 _ window関数まとめ

MySQL8,mariaDB10 : window関数のまとめ

PostreSQL や Oracle に加え mysql8.1 , mariaDB 10.2 で使えるようになったwindow関数について、用途や記述方法をまとめてみた。
オンラインマニュアルでは、mysql8の日本語マニュアル:window-functionsで読める。 自動翻訳らしいので、所々日本語として意味がとおらないところがあるが、英文と切り替えて読めばなんとか解る。語順チェックには十分だろう。
mariaDBは、window-functions-overviewなど各コマンドごとにURLがあるが、一覧状態のものはないようなので、ここで解説したものについて、最後に載せることとする。 使用時には、ログイン後のhelpコマンドでマニュアルが読める。いずれも英文のみ。mysqlと基本文法自体は変わらないので、mysqlの日本語マニュアルも参考になる。

• over句について

  -- 書式
  function_name() OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )
  

  
  partition_expression : グループ化のためのカラム名リスト。複数ある時はカンマ「,」区切り、関数の適用結果も使える
  order_list : 順位付けのためのカラム名リスト。複数ある時はカンマ「,」区切り。 通常のorder by句と同様にasc 昇順、desc 降順　の指定が可能
  ※　partition by と order by との間は空白のみ。カンマ入れるとエラー。
  ※　両方省略可能だが、そうすると全体について全て同順となるので、集約関数での全体集約値を得る時以外は、どちらかは必要。
  ※　over は句なので () との間にブランク有ってもよい。
  ※　関数名と ( との間にブランクがあるとエラー。後方直後に ( がないものは関数と認識されない。
  ※　mariaDB10ではover句だけのhelpはない。mysql8では、セクション12.21.2「Window 関数の概念と構文」
  
  

• frame句について

  -- 書式
  function_name(expr) OVER ( [ PARTITION BY expression_list ] [ ORDER BY order_list [ frame_clause ] ] ) 
  
  frame_clause:
   {ROWS | RANGE} {frame_border | BETWEEN frame_border AND frame_border}
  
  frame_border:
   | UNBOUNDED PRECEDING
   | UNBOUNDED FOLLOWING
   | CURRENT ROW
   | expr PRECEDING
   | expr FOLLOWING
  
  --  ex.1: 前方から現在行までの合計　＝　累積値。以下は同じ。
  sum(datacolumn) OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list ROWS  UNBOUNDED PRECEDING )
  sum(datacolumn) OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list RANGE UNBOUNDED PRECEDING )
  sum(datacolumn) OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
  sum(datacolumn) OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
  
  --  ex.2: 前方と後方の３者による移動平均
  avg(datacolumn) over ( order by order_list  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING )
  

  
  計算範囲を移動させることで、前後の平均をとったり先頭から現在行までの合計を取ったりできる。
  order by句の後方に付ける。つまりorder by 句は必須
  ※　mysql8：12.21.3 ウィンドウ機能フレーム仕様の方が詳しい
  
  

• RANK, DENSE_RANK, ROW_NUMBER

  「ランク付け:by MySQL」と 「row_number関数を利用して当番表作成:by MySQL8,mariaDB10」にも利用法を記述しています。

  -- 書式
   RANK()      OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  DENSE_RANK() OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  ROW_NUMBER() OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  

  rank関数は順位付けで、partition by句のグループ内(以降パーティション内と表記)で order by句のカラム値順に番号を振ります。パーティション内で同値が有る場合は、同順とし、次の順位番号が抜けることになります。
  dense_rankは、同順のものがあった以降も数値の抜けなく抜けなく詰めて順位を表示します。
  row_number関数は、連番生成で、パーティション内で order by句の整列順に番号を振ります。パーティション内での同順や数値が飛んだりは有りません。
  ※　partition by句が無ければ全体を通しての、ランクや連番となります。
  ※　order by句を省略すると、全て同順一位となるので、rank付けの意味がなくなります。 row_numberの連番でランダム順が欲しいときは、order by句に rand()関数を指定します。
  ※　rank() やrow_number() の括弧には引数は不要です。
  
  

• CUME_DIST と PERCENT_RANK

  -- 書式
  CUME_DIST()    OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  PERCENT_RANK() OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  

  CUME_DISTは、累積分布値(cumulative distribution)で、現在ランク値までの累積度数（同値の行すべて含む）をパーティション内全件数で割ったもの、以下の式。0より大きく1.0以下の値を取る。
  
  (number of rows <= current row rank) / (total rows)
  
  PERCENT_RANKは、percentage of partitionと英文説明にあるが、現在ランク手前までの累積度数を（パーティション内全件-1）で割ったもの、以下の式。0以上1.0以下の値を取る。
  
  (rank - 1) / (number of rows in the window or partition - 1)
  
  
  ※　partition by句が無ければ全体を通しての、ランクや連番となります。
  ※　order by句を省略すると、全て同順一位となるので、どちらの場合も100%、つまり全部1.0になります。
  
  

• FIRST_VALUE、 NTH_VALUE、 LAST_VALUE

  -- 書式
  FIRST_VALUE(expr)   OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  LAST_VALUE(expr)    OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  NTH_VALUE(expr, N)  OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  

  FIRST_VALUEは、各パーティション内でのorder順位の最初の行におけるexpr値。
  ※　exprは、カラム名やカラム値を利用した演算を含む。
  LAST_VALUEは、各パーティション内でのorder順位の現在行におけるexpr値。
  NTH_VALUEは、各パーティション内でのorder順位のN番目の行におけるexpr値、N番に到達していなければnull値を返す。 N は 正の整数(1以上)。N=1のときはFIRST_VALUEと同じ。
  ※　partition by句が無ければ全体を通しての、順位判定となります。
  ※　order by句がなければ、見つけた順の最初、最後、N番目となる。from句のあとにorder by句をつけても、over句でorder by句がなければ、並べ替えしないときの見つけた順のデータとなる。
  
  

• LAG と LEAD

  -- 書式
  LAG(expr[, offset])  OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list )
  LEAD(expr[, offset]) OVER ( [ PARTITION BY partition_expression ]  ORDER BY order_list  )
  

  LAGは、各パーティション内でのrder順位のoffset行手前の行におけるexpr値、先頭では手前は存在しないのでnull。
  LEADは、各パーティション内でのorder順位のoffset行後方におけるexpr値、最終行では、次は存在しないのでnull。
  ※　exprは、カラム名やカラム値を利用した演算を含む。
  ※　第二引数 offset で何行手前または後方かを指定。この場合offset行手前または後方となる行が存在しない場合はnullとなる。 offsetは0以上 整数int型の範囲内。 offsetが0のときは現在行。null不可。省略時 1 ：１行手前または後方の意味。
  ※　partition by句が無ければ全体を通しての、order順位となります。
  ※　frame句はつけても意味がないようです。
  ※　order by句は必須です。省略すると以下のエラーが出ます。
  :No order list in window specification for 'lag'
  ※　mysql8 では、第三引数でdefault値の指定が可能。 　→　on line manual window関数の説明参照
  
  

• 集計関数とwindow関数

  count(expr) や sum(expr) などの集計関数に続けて、over句を記述することで、パーティション毎の集約値を表示します。
  order by句は、frame句が無ければパーティション内全体を集約するのであまり意味がありません。
  order by句とframe句によって、範囲移動しつつ集約結果を得ることができます。→frame句参照

  -- 書式
   aggregate_func(expr) OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list frame_clause ] )
  -- ex.
   count(*)     over() --  partition指定無いときテーブルの全行を１groupとして集約
   sum(Dcolumn) over (order by Acolumn ROWS UNBOUNDED PRECEDING)
      -- Aculumn順に先頭から現在行までDcolumnの値が合計される。よって累積値を得ることができる。
  
  --  over句を利用できる集約関数一覧 mariaDB10 も mysql8 も同じ
  AVG       BIT_AND   BIT_OR    BIT_XOR
  COUNT     MAX       MIN
  STD       STDDEV    STDDEV_POP          STDDEV_SAMP
  SUM
  VAR_POP   VAR_SAMP  VARIANCE
  

• window関数使用時のエラーとwith句

   Window function is allowed only in SELECT list and ORDER BY clause.
  

  window関数を使ってるクエリにwhere句やgroup by句があるとこのようなエラーがでる。
  window関数を使ったサブクエリテーブルにたいして条件抽出したり、逆に、where句やgroup by実行後のサブクエリテーブルを対象にwindow関数を使うことはできる。
  よって、with句を使ってサブクエリテーブルに別名を付けると、全体が見通しやすくなる。 with句は、temporary viewとにているが、一回のselect用SQL発行時にのみ利用できる点が異なる。

  -- with句とwindow関数使用例
  with t1 as (select * from tableA where id between 10 and 99)
  select *, row_number() over (order by id) number from t1 ;
  

• mariaDB online manual

  1. window-functions-overview

  2. with 句
  3. Aggregate Functions as Window Functions
  4. window-frams : 移動型範囲指定。over句内order by句の後方に付ける。 mysql8：12.21.3 ウィンドウ機能フレーム仕様の方が詳しい
  5. RANK関数
  6. DENCE_RANK関数
  7. ROW_NUMBER関数
  8. CUME_DIST()　：累積度数 同ランクまでの度数割合
  9. PERCENT_RANK()　：手前ランクまでの累積度数
  10. FIRST_VALUE(column)　：group内のorder順位の最初の値
  11. LAST_VALUE(column)　：group内 order順位の現在行の値
  12. NTH_VALUE(datacolumn, num)　：現在行のrow_numberがnum以降ならnum番のdatacolumn値を手前ならnullを返す
  13. LAG(datacolumn [,offset])　：パーティション内order順位で現在行のoffset行手前のdatacolumn値
  14. LEAD(datacolumn [,offset])　：パーティション内order順位で現在行のoffset行後方のdatacolumn値
      
      
  15. Median Window Function 　：median(datacolumn) over(partition by groupcolumn) ：パーティション内でdatacolumnにおける中央値の算出
      mariaDB 10.3.3以降利用可能。mysql8.0ではまだ未実装。
  16. NTILE(n) 　：パーティション内を N グループ (バケット) に分割し、パーティション内でorder順に１からn番までのバケット番号を割り当て、現在行のバケット番号を返します。
  17. PERCENTILE_CONT(d) WITHIN GROUP (ORDER BY sorter) OVER (PARTITION BY group)
      MariaDB 10.3.3以降利用可能。mysql8.0ではまだ未実装。
  18. PERCENTILE_DISC(d) WITHIN GROUP (ORDER BY sorter) OVER (PARTITION BY group)
      MariaDB 10.3.3以降利用可能。mysql8.0ではまだ未実装。
      
      
  19. window-name : mariaDB 10.4 ではまだ未実装