どうも、hosigakiです！
今日はSQLで、構成比率と累積比率の算出方法を説明します！
構成比率と累積比率について
以下は、1月における日別登録者数を示します。
3列目は構成比率で、その日の登録者数が5月の全登録者数の何%なのかを示しています。
4列目は累積比率で、1/1からその日までの合計の登録者数が、5月の全登録者数の何%なのかを示しています。
SQLでの算出方法
今回は以下のusersテーブルを使い、構成比率と累積比率を求めて見ましょう！
SQLで構成比率と累積比率を算出する場合は、ウィンドウ関数を用いて、以下のようなクエリを書きます。
SELECT
days,
users_count,
users_count / SUM(users_count) OVER(ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS users_rate,
SUM(users_count) OVER(ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(users_count) OVER (ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS users_com_rate
FROM
usersもしくは、ウィンドウ関数の省略形を用いて、以下のようなクエリも同じ結果となります。
SELECT
days,
users_count,
users_count / SUM(users_count) OVER() AS users_rate,
SUM(users_count) OVER(ORDER BY days ASC) / SUM(users_count) OVER() AS users_com_rate
FROM
usersそれぞれ、4行目が構成比率、5行目が累積比率を算出するクエリです。
それぞれのクエリについて、順に解説します！
構成比率のクエリの解説
/*ウィンドウ関数をちゃんと書いた場合の書き方*/
users_count / SUM(users_count) OVER(ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS users_rate
/*ウィンドウ関数の省略形を使った場合の書き方*/
users_count / SUM(users_count) OVER() AS users_rate
構成比率は、各日の登録者数を、その月全体の登録者数で割れば算出できます。
上記のクエリでは、各日の登録者数であるusers_countを、ウィンドウ関数を用いて合算した全体の登録者数で割っています。
省略形を使わない書き方
/*ウィンドウ関数をちゃんと書いた場合の書き方*/
users_count / SUM(users_count) OVER(ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS users_rate分母の部分に注目して欲しいのですが、集約関数SUM( )の後ろに、OVER( )をつけることで、集計範囲を指定しています。OVER( ) の中では、「daysカラムの値が古い順になるようにレコードを並べた時の、最初から最後までの全ての行を範囲とする」という指定を行っています。
ウィンドウ関数のポイント！
ウィンドウ関数では、OVER( )を用いて、
①レコードを並び替えた際、
②現在の行を起点としてどこからどこまでを集約関数の計算範囲とするか
を指定します。
①は単にORDER BY句を用いて指定します。
②はROWS BETWEEN 〇〇〇 AND △△△ という構文を用いて指定します。
ちなみに、今回②にて以下の表現を範囲指定に用いました。
UNBOUNDED PRECEDING：「現在の行から限りなく前の行」、つまり「最初の行」 。UNBOUNDED FOLLOWING：「現在の行から限りなく後ろの行」、つまり「最後の行」。
省略形を使った書き方
/*ウィンドウ関数の省略形を使った場合の書き方*/
users_count / SUM(users_count) OVER() AS users_rate上記は、省略形を使った書き方です。さっきよりもスッキリ簡単な印象ですね！OVER( )にて、括弧内に何も書かない場合は、「全ての行を範囲とする」という意味になりますので、覚えておくと便利です！
累積比率のクエリの解説
/*ウィンドウ関数をちゃんと書いた場合の書き方*/
SUM(users_count) OVER(ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(users_count) OVER (ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS users_com_rate
/*ウィンドウ関数の省略形を使った場合の書き方*/
SUM(users_count) OVER(ORDER BY days ASC) / SUM(users_count) OVER() AS users_com_rate
累積比率は、1月1日から各日までの合計登録者数を、その月全体の登録者数で割れば算出できます。
上記のクエリでは、分子も分母もどちらもウィンドウ関数を用いています。
省略形を使わない書き方
/*ウィンドウ関数をちゃんと書いた場合の書き方*/
SUM(users_count) OVER(ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(users_count) OVER (ORDER BY days ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS users_com_rate分母は先ほどの構成比率と同じなので、ここでは説明を割愛します。
分子に注目すると、集約関数の計算範囲として、「daysカラムの値が古い順になるようにレコードを並べた時の、最初から現在までの行を範囲とする」という指定を行っています。
これにより、各行にて1月1日からその行までの合算を算出することができます。
省略形を使った書き方
/*ウィンドウ関数の省略形を使った場合の書き方*/
SUM(users_count) OVER(ORDER BY days ASC) / SUM(users_count) OVER() AS users_com_rate上記は、省略形を使った書き方です。
OVER(ORDER BY 〇〇)という風に、括弧内で並び順のみを指定することで、「その並び順における、最初から現在の行までを範囲とする」という意味になります。こちらも覚えておくと便利です！
いかがだったでしょうか？
ウィンドウ関数については、「ウィンドウ関数で7日間移動平均を算出しよう！」という記事でも解説しておりますので、是非興味がある方はご覧いただけますと幸いです！
ここまで読んでいただきありがとうございました！
それではまた、次の記事でお会いしましょう(^^)/
