データ分析のためのウィンドウ関数の使い方例

じゃあ、いよいよ実践例の世界に飛び込んで、実際の課題でどう動くのか見てみよう！

例：地域ごとの売上ランキング計算

たとえば、salesテーブルがあって、いろんな地域の売上データが入ってるとする。各地域ごとに売上のランクを決めたいんだ。

課題：各地域ごとに売上のランク（RANK）を出す

SELECT
    region,
    sales_amount,
    RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM 
    sales;

結果：

PARTITION BY regionを使って、各地域ごとにランクを計算してるのがポイント。もしPARTITION BYを使わなかったら、テーブル全体でグローバルにランクが計算されちゃうよ。

例：収入の累積合計を計算する

今度はtransactionsテーブルを使って、各顧客ごとに収入の累積（キュムレイティブ）合計を出してみよう。

課題：各顧客ごとに累積合計を計算する

SELECT
    customer_id,
    purchase_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM 
    transactions;

結果：

ここで大事なのは、OVER()の中でORDER BY purchase_dateを使って、累積合計が時系列順に計算されるようにしてること！

例：データをクォンタイルに分割する

たとえばstudentsテーブルがあって、名前とテストの点数が入ってる。点数で生徒を4つのグループに分けたいとき。

課題：NTILE()を使って生徒を4グループに分ける

SELECT
    name,
    test_score,
    NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM 
    students;

クエリの結果はこんな感じ：

NTILE(4)はデータを4つのグループに分けてくれる。点数が高い生徒は最初のグループ、低い生徒は最後のグループになるよ。

例：時系列データの分析

site_visitsテーブルには、サイトごとの日別訪問数データが入ってる。各サイトごとに日ごとの訪問数の差分を計算したい。

課題：日ごとの訪問数の差分を計算する

SELECT
    site_id,
    visit_date,
    visits,
    visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM 
    site_visits;

結果：

LAG()関数は前の行の値を取ってこれる。前の行がなかったら、結果はNULLになるよ。詳しくは次のレクチャーで教えるね :P