データサイエンスのインタビューで知っておくべき上位XNUMXつのSQLウィンドウ関数

SQLはデータの世界で普遍的な言語であり、データの専門家としての地位を確立するための最も重要なスキルです。

SQLが非常に重要である理由は、SQLがデータラングリングフェーズで必要とされる主要なスキルであるためです。 多くのデータ探索、データ操作、パイプライン開発、およびダッシュボードの作成は、SQLを介して行われます。

優れたデータサイエンティストと優れたデータサイエンティストを区別するのは、優れたデータサイエンティストがSQLの機能が許す限りデータをまとめることができるということです。 SQLが提供するすべてを完全に使用できることの大きな部分は、ウィンドウ関数の使用方法を知ることです。

そうは言っても、それに飛び込みましょう！

1. LEAD（）およびLAG（）を使用したデルタ

 
LEAD（）およびLAG（）は主に、特定のメトリックについてXNUMXつの期間を前の期間と比較するときに使用されます。 いくつか例を挙げると…

• 毎年の売り上げと前年の売り上げの差を知ることができます
• 月ごとのサインアップ/コンバージョン/ウェブサイト訪問数のデルタを取得できます
• 月次ベースでユーザーチャーンを比較できます

例：

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

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（）で始まるウィンドウ関数を使用して簡単に実行できます。 これは、特定のメトリックの経時的な成長を表示する場合の強力なツールです。 具体的には、次のような状況で役立ちます。

• 時間の経過に伴う収益とコストの現在の合計を取得します
• ユーザーごとのアプリに費やされた時間の現在の合計を取得します
• 時間の経過に伴うコンバージョンの現在の合計を取得します

例：

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

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

3. AVG（）による移動平均

 
AVG（）は、計算を可能にするため、Windows関数で非常に強力です。 移動平均 時間をかけて。

移動平均は、短期的に値を予測するためのシンプルでありながら効果的な方法です。 また、グラフの揮発性曲線を平滑化するのにも非常に役立ちます。 一般に、移動平均は、物事が移動している場所の一般的な方向を測定するために使用されます。

すなわち…

• これらは、週ごとの売上の一般的な傾向を取得するために使用できます（平均は時間の経過とともに上昇していますか？）。 これは企業としての成長を示しています。
• 同様に、毎週のコンバージョンやWebサイトへのアクセスの一般的な傾向を把握するために使用できます。

例：

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

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

4. ROW_NUMBER（）

 
ROW_NUMBER（）は、最初または最後のレコードを取得する場合に特に便利です。 たとえば、ジムのメンバーがジムに来た日時のテーブルがあり、ジムに来た最初の日の日付を取得したい場合は、顧客（名前/ ID）でパーティションを作成し、購入日で注文することができます。 次に、最初の行を取得するために、rowNumberがXNUMXに等しい行を単純にフィルタリングできます。

例：

この例は、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ウィンドウ関数のほとんどの問題に関してはうまくいくと確信しています。

いつものように、私はあなたがあなたの学習努力で最高であることを望みます！

 
 
テレンスシン は、SQLで3年以上、Pythonで2年以上の経験を持つデータ愛好家であり、Towards DataScienceとKDnuggetsのブロガーです。

元の。 許可を得て転載。

ソース：https：//www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html