SQL ウィンドウ関数: それらの使用について知っておくべきこと

2023/01/31

DBMS クエリ言語としての SQL の汎用性は、年々向上しています。その広範なユーティリティと汎用性により、すべてのデータアナリストにとって常にお気に入りのツールとなっています。

SQL の通常の関数以外にも、かなりの数の高度な関数があります。これらの関数は、一般にウィンドウ関数として知られています。複雑なデータを扱っていて、高度な計算を実行したい場合は、それらを使用してデータを最大限に活用できます。

ウィンドウ関数の重要性

SQL ではいくつかのウィンドウ関数を使用でき、それぞれが一連の計算を実行するのに役立ちます。パーティションの作成から、行のランク付けや行番号の割り当てまで、これらのウィンドウ関数はあらゆることを少しずつ行います。

ウィンドウ関数は、特定のデータセットまたは行のコレクションに対して集計関数を適用する場合に役立ちます。これらの関数は、GROUP BY が提供する集計関数をはるかに超えています。ただし、主な違いは、グループ化機能とは異なり、データが 1 つの行に結合されないことです。

WHERE、FROM 、およびGROUP BYステートメント内でウィンドウ関数を使用することはできません。

ウィンドウ関数の構文

ウィンドウ関数を参照するときは、正しく機能するように、既定の構文構造に従う必要があります。コマンドの構造が間違っていると、エラーが発生し、コードの実行に失敗します。

デフォルトの構文は次のとおりです。

SELECT columnname1, {window_function}(columnname2) OVER([PARTITION BY columnname1] [ORDER BY columnname3]) AS new_column FROM table_name;

どこ：

coulmnname1は、選択する最初の列名です。
{window_function}は、sum、avg、count、row_number、rank、dense_rank などの集計関数の名前です。
columnname2は、ウィンドウ関数を適用する列の名前です。
columnname3は、パーティションのベースを形成する 3 番目の列名です。
new_columnは、 ASキーワードを使用して適用できる新しい列のラベルです。
table_nameは、ソーステーブルの名前です。

ウィンドウ関数は、いくつかの最も基本的な SQL コマンドとは異なります。SQL の集計関数とは異なり、これらのウィンドウ関数を使用して高度な関数を実行できます。

データセットの準備

CREATE TABLEステートメントを使用して、SQL で新しいテーブルを作成できます。このガイドでいくつかのウィンドウ関数を定義するために使用するサンプルデータセットを次に示します。

Sum 関数の説明

カテゴリ列内の各値の合計売上を計算するとします。方法は次のとおりです。

SELECT category, color, sum(sale_price) OVER (order by category) AS total_sales FROM sahil.sample;

上記のコードでは、SQL ステートメントによって元のデータセットからカテゴリと色が取得されます。sum 関数は、sale_price 列を合計します。OVER 句はカテゴリ列による順序付けを指定しているため、これはカテゴリ別に行われます。最終結果は次のとおりです。

Avg() ウィンドウ関数の使用方法

sum 関数と同様に、 avg関数を使用して、データの行ごとの平均を計算できます。合計の代わりに、平均売上の列が表示されます。

SELECT category, color, avg(sale_price) OVER (order by category) AS avg_sales FROM sahil.sample;

Count() ウィンドウ関数の使い方を学ぶ

sum および avg 関数と同様に、SQL のカウントウィンドウ関数は非常に単純で、他の 2 つの関数と同じように機能します。count 関数を渡すと、新しい列内の各値の合計数が取得されます。

カウントを計算する方法は次のとおりです。

SELECT category, color, count(category) OVER (order by category) AS item_count FROM sahil.sample;

Row_Number() ウィンドウ関数

上記の他のウィンドウ関数とは異なり、row_number() の動作は少し異なります。row_number() 関数は、order by 句に応じて、各行に行番号を割り当てます。開始行番号は 1 です。row_number は、最後まで各行に対応する値を割り当てます。

row_number() 関数の基本構造は次のとおりです。

SELECT category, color, row_number() OVER (order by category) AS item_number FROM sahil.sample;

しかし、各カテゴリ項目に個別の行番号を割り当てたい場合はどうなるでしょうか? 上記の構文は、カテゴリ内に格納されているアイテムに関係なく、ローリングシリアル番号を設定します。たとえば、電化製品のカテゴリには独自の番号が付けられ、その後にバインダーが続きます。

partition関数を使用して、この単純でありながら実用的なタスクを実行できます。partition キーワードは、各カテゴリ項目に基づいて指定された行番号を割り当てます。

SELECT category, color, row_number() OVER (partition by category order by category) AS item_number FROM sahil.sample;

Rank() および Dense_Rank() 関数

rank()関数は、 row_number()関数とは異なる働きをします。ランク値を定義するベースとして使用するには、order by 関数内で列名を指定する必要があります。たとえば、次のコード例では、order by 関数内で color 列を使用できます。次に、クエリはその順序を使用して、ランク値を各行に割り当てます。

次のコード構文を使用して、SQL でランク関数を渡すことができます。

SELECT category, color, rank() OVER (order by color) AS item_rank FROM sahil.sample;

出力を見て、この関数がどのように機能するかを理解してください。

order by 関数は色カテゴリをソートし、rank 関数は各色にランクを割り当てます。ただし、同じ色の値はすべて同じランクであり、異なる色には別のランクがあります。黒はデータセット内で 3 回発生します。ランク値 1、2、および 3 を割り当てる代わりに、黒色のアイテムにはランク 1 が割り当てられます。

ただし、次の色のブラウンはランク 2 ではなくランク 4 を取得します。ランク関数は値をスキップし、次の時系列の値を異なるエントリに割り当てます。より意味のあるランク値を割り当てたい場合は、dense_rank()関数を使用できます。

dense_rank 関数は、order by 関数の実行中にランク値をスキップしません。たとえば、最初の 3 色のアイテム (黒) はランク 1 になります。ただし、次の色 (ブラウン) はランク 4 ではなく、ランク 2 になります。これは、番号付けリストの次の年代順の番号です。Dens_rank 関数は、アイテムのリストに意味のある値を割り当てるため、より実用的なウィンドウ関数です。

SQLでdense_rank関数を使用する方法は次のとおりです。

SELECT category, color, dense_rank() OVER (order by color) AS item_rank FROM sahil.sample;

この関数からの出力の例を次に示します。

救助のためのSQL関数

SQL のウィンドウ関数は、高度な分析操作を実行するのに理想的です。ただし、他の多くの SQL コマンドを使用して、計算スキルを一流にすることができます。複数の結果を組み合わせて一度に計算する場合、SQL のサブクエリを使用することに勝るものはありません。

サブクエリは、高度な機能を実行して結果の品質を向上させる優れたツールです。時間の必要に応じて、クエリをカスタマイズし、要件に合わせてより効果的にすることができます。