データ抽出時に良く使うSQLのウィンドウ関数トップ５

入門／解説
2022.06.20

１．データ抽出時に良く使うSQLのウィンドウ関数トップ５まとめ

・SQLのウィンドウ関数は分析用のSQLを書く時によく使いサブクエリより簡潔に書ける
・1つ前の行、１つ後ろの行、移動平均、累積などある程度パターンで覚えると良い
・特定の行を基準に並べて番号を付けるrow_numberやdense_rank()も応用範囲が広い

２．SQLのウィンドウ関数

以下、www.kdnuggets.comより「Top Five SQL Window Functions You Should Know For Data Science Interviews」の意訳です。元記事は2022年1月、Terence Shinさんによる投稿です。

SQLのウィンドウ関数は分析用のSQLを書く時によく使います。サブクエリをゴチャゴチャ書くよりスッキリ書けるのですが、そもそもウィンドウ関数の存在を知らないと使えないと思うので参考になるかなと思い意訳しました。

アイキャッチ画像の生成はlatent-diffusionで与えた入力文は「A watercolor illustration like image that a data scientist wearing glasses writes SQL on a blackboard and explains.」

SQLはデータの世界における世界共通言語であり、データのプロフェッショナルとして最も重要なスキルです。

なぜSQLが重要かというと、データを扱う段階で必要とされる主なスキルだからです。データ探索、データ操作、パイプライン開発、ダッシュボード作成などの多くは、SQLを通じて行われます。

優れたデータサイエンティストと優秀なデータサイエンティストの違いは、優れたデータサイエンティストは、SQLの能力が許す限りデータをかき集めることができるという点です。SQLの機能をフルに活用するためには、ウィンドウ関数の使い方を知っていることが大きなポイントになります。

というわけで、さっそく調べてみましょう。

1. LEAD()とLAG()を使った差分計算

LEAD()とLAG()は、ある指標について、ある期間とその前の期間を比較するときに主に使われます。いくつか例を挙げると…

各年の売上高と前年の売上高との差分を取得することができます。
サインアップ数、コンバージョン数、ウェブサイト訪問数の月ごとの差分を得ることができます。ユーザー離脱率を月単位で比較することができます。

例

次のクエリは、コストの月間変化率を照会する方法を示しています。

with monthly_costs as (
  SELECT
    date
    , monthlycosts
    , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts
  FROM
    costs
)
SELECT
  date
  , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange
FROM
  monthly_costs

2. SUM() あるいは COUNT() による累積和の計算

累計の計算は、SUM() あるいは COUNT() で始まる Windows関数で簡単に行うことができます。これは、特定の指標の伸びを時系列で表示したい場合に強力なツールとなります。具体的には、以下のような状況で有効です。

・収益と費用の時間経過による合計を取得する
・ユーザーごとのアプリ使用時間の累計を取得する。
・コンバージョンの経時的な合計を取得する

例

次の例は、毎月の費用の累積合計の列を含める方法を示しています。

SELECT
  date
  , monthlycosts
  , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts
FROM
  cost_table

3. AVG()による移動平均

AVG()は、時間の移動平均を計算することができるので、ウィンドウ関数では実に強力です。

移動平均は、短期的な値を予測するためのシンプルで効果的な方法です。また、グラフ上の変動するカーブを滑らかにするのにも非常に有効です。一般的に、移動平均は物事が動いている大まかな方向を測るために使われます。

具体的には..

週次売上高の一般的な傾向をつかむのに使えます(平均が時間とともに上がっているか)。

これは、企業としての成長を示すものです。同様に、週ごとのコンバージョンやウェブサイト訪問の一般的な傾向を把握するためにも使用できます。

例

次のクエリは、コンバージョンの10日間移動平均を取得する例です。

SELECT
  Date
  , dailyConversions
  , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage
FROM
  conversions

4. ROW_NUMBER()

ROW_NUMBER() は、特に最初か最後のレコードを取得したい場合に便利です。たとえば、ジムの会員がジムに来たときのテーブルがあり、 その会員が初めてジムに来た日の日付を取得したい場合は、 PARTITION BY customer (name/id) と ORDER BY purchase date を使用します。そして、最初の行を取得するために、rowNumber が 1 に等しい行をフィルタリングすればよいのです。

例

この例では、ROW_NUMBER() を使用して、各メンバー (ユーザー) が訪問した最初の日付を取得する方法を示しています。

with
numbered_visits as (
  SELECT
    memberId
    , visitDate
    , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber
  FROM
    gym_visits
)
SELECT
  *
FROM
  numbered_visits
WHERE
  rowNumber = 1

要約すると、最初または最後のレコードを取得する必要がある場合、ROW_NUMBER()はそれを達成するための素晴らしい方法です。

5. DENSE_RANK()によるレコードランキング

DENSE_RANK() は ROW_NUMBER() と似ていますが、同じ値に対して同じ順位を返すという点が異なります。密なランキングは、たとえば上位のレコードを取得するときにかなり便利です。

・今週もっとも視聴された Netflix の番組トップ 10 を取得したい場合
・消費ドル数で上位100人のユーザーを取得したい場合
・最もアクティブでないユーザー1000人の行動を見たい場合

例

売上高で上位の顧客をランク付けしたい場合、DENSE_RANK()が適切な関数となります。

SELECT
  customerId
  , totalSales
  , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank
FROM
  customers

お読みいただきありがとうございました。

以上です。この5つのコンセプトを知っていれば、ほとんどのSQL窓関数の問題でうまくいくことでしょう。この5つの概念を知っていれば、SQLのウィンドウ関数の問題でもうまくいくはずです。

３．データ抽出時に良く使うSQLのウィンドウ関数トップ５関連リンク

１）www.kdnuggets.com
Top Five SQL Window Functions You Should Know For Data Science Interviews

タイトルとURLをコピーしました