ウィンドウ関数で順位と傾向を求める
目標
row_number、rank、dense_rank、累積合計、lag、割合、ntileを使って、順位と傾向を求め、ウィンドウ関数をいつ使うかを判断できるようになります。
なぜ重要なのか
集計は行を畳みます。畳んだ後は、個々の行の情報が消えてしまいます。ところが、実務の質問の相当な部分は、「各行が自分のグループの中で何番目か」、「直前の値と比べるとどうか」、「全体に対して何パーセントか」のように、個々の行を維持したまま周りを参照する必要があって初めて答えられます。
ウィンドウ関数は、まさにこの場所を埋めます。同じことを自己結合や相関サブクエリでもできますが、コードが長くなり、表を何度も読むことになります。ウィンドウ関数は、1回のスキャンで終わります。
制約を1つだけ覚えておけば十分です。ウィンドウ関数の結果は、同じSELECTのWHEREでは使えません。処理順序上、WHEREが先だからです。そのため、「上位3つだけ」のように順位で絞り込むには、サブクエリやCTEで1段包む必要があります。
ステップ
- 商品をカテゴリごとに、価格の降順(同点は
idの昇順)で並べた連番を含むビューv_ranked_productsを作ります。カラムはcategory、id、price、rnです。 - 全商品を価格の降順に、2種類の方式で順位付けしたビュー
v_rank_compareを作ります。カラムはid、price、rnk(同点の後の番号を飛ばす)、drnk(飛ばさない)です。 - 完了ステータスの注文の月別売上と累積売上を含むビュー
v_running_revenueを作ります。カラムはmonth、revenue、cum_revenueで、月の基準はAT TIME ZONE 'Asia/Seoul'です。 - 同じ月別集計に、前月の売上と増減を付けたビュー
v_momを作ります。カラムはmonth、revenue、prev_revenue、diffで、最初の月のprev_revenueは空である必要があります。 - チャネル別の売上と、全体に対する割合を含むビュー
v_channel_shareを作ります。カラムはchannel、revenue、share_pctで、割合は小数第2位で丸め、合計が100になる必要があります。 - カテゴリ別の価格上位3つの商品を含むビュー
v_top3_per_categoryを作ります。カラムはcategory、id、priceです。 - 商品を価格の昇順(同点は
idの昇順)で4分割したビューv_price_quartileを作ります。カラムはid、price、quartileです。 - 顧客ごとの最初の注文を含むビュー
v_first_orderを作ります。カラムはcustomer_id、order_id、ordered_atで、同じ時刻ならidが小さい注文を最初の注文と見なします。
参考
- 基本形:
함수() OVER (PARTITION BY 그룹 ORDER BY 정렬)(プレースホルダーは、関数、グループ、並べ替えです) OVER ()のように括弧を空にすると、全体が1つのウィンドウになります。- よくある間違い1: 順位の結果を同じSELECTのWHEREに使うと、エラーになります。
- よくある間違い2: 並べ替えの基準に同点があると、結果が実行のたびに変わることがあるので、tie-breakのカラムを入れてください。
カテゴリ別に価格の連番を付ける
商品をカテゴリごとに、価格の降順(同点はidの昇順)で並べた連番を含むビューv_ranked_productsを作ります。カラムはcategory、id、price、rnです。
ウィンドウを分ける句と、ウィンドウ内の順序を決める句を、一緒に使います。同点はidで決めます。
2つの順位関数を比較する
全商品を価格の降順に、2種類の方式で順位付けしたビューv_rank_compareを作ります。カラムはid、price、rnk(同点の後の番号を飛ばす)、drnk(飛ばさない)です。
同点の後の番号を飛ばす関数と、飛ばさない関数が、別々にあります。
累積売上を求める
完了ステータスの注文の月別売上と累積売上を含むビューv_running_revenueを作ります。カラムはmonth、revenue、cum_revenueで、月の基準はAT TIME ZONE 'Asia/Seoul'です。
月別集計を先に作って、その結果の上でウィンドウを開きます。順序を逆にすると、集計とウィンドウが混ざります。
前月比の増減を求める
同じ月別集計に、前月の売上と増減を付けたビューv_momを作ります。カラムはmonth、revenue、prev_revenue、diffで、最初の月のprev_revenueは空である必要があります。
直前の行の値を取得する関数があります。最初の月には値がないので、空である必要があります。
全体に対する割合を求める
チャネル別の売上と、全体に対する割合を含むビューv_channel_shareを作ります。カラムはchannel、revenue、share_pctで、割合は小数第2位で丸め、合計が100になる必要があります。
OVERの括弧を空にすると、全体が1つのウィンドウになります。クエリを2回動かす必要はありません。
グループ別の上位3つを取り出す
カテゴリ別の価格上位3つの商品を含むビューv_top3_per_categoryを作ります。カラムはcategory、id、priceです。
ウィンドウ関数の結果は、同じSELECTのWHEREでは使えません。1段包む必要があります。
価格の四分位に分ける
商品を価格の昇順(同点はidの昇順)で4分割したビューv_price_quartileを作ります。カラムはid、price、quartileです。
行をn分割して番号を付ける関数があります。並べ替えの基準を正確に合わせてください。
顧客ごとの最初の注文を探す
顧客ごとの最初の注文を含むビューv_first_orderを作ります。カラムはcustomer_id、order_id、ordered_atで、同じ時刻ならidが小さい注文を最初の注文と見なします。
顧客ごとに時間順の連番を付けて、1番目だけを残します。注文がない顧客は出てこない必要があります。