SQL ウィンドウ関数とその機能を理解するための初心者ガイド

採用プロセスを通じて、私はチームの多くの才能ある候補者に出会うことができてうれしかったです。私たちの仕事には複雑なデータセットの処理が含まれるため、各候補者の賢明なソリューションを見つける能力を評価することが私にとって重要でした。彼らの熟練度を評価するために、SQLのウィンドウ関数の経験について尋ねました。ほとんどの人がこれらの機能について知っていましたが、効果的に使用できる人はほとんどいませんでした。

ウィンドウ関数は 20 年近く前から存在していますが、多くの SQL 開発者は依然としてウィンドウ関数を理解するのが難しいと感じています。経験豊富な開発者であっても、 StackOverflowが何をするのかをよく理解せずに、StackOverflow からコードをコピーして貼り付けるだけであることは珍しくありません。この記事は役に立ちます!ウィンドウ関数をわかりやすく説明し、実際の世界でどのように機能するかを示す例を示します。

ウィンドウ関数について聞いたことがありますか?これらは多くの問題を解決できる素晴らしい分析ツールです。たとえば、クライアント ID などの共通の属性を共有する行のセットを計算する必要があるとします。ここでウィンドウ関数が役に立ちます。これらは集計関数のように機能しますが、行をグループ化するのではなく、各行の一意性を維持できます。さらに、ウィンドウ関数の結果は、出力選択の追加フィールドとして表示されます。これは、分析レポートを作成したり、移動平均や累計を計算したり、さまざまなアトリビューション モデルを計算したりするときに非常に役立ちます。

SQL とウィンドウ関数の世界へようこそ!始めたばかりなら、ここが正しい場所です。この記事は初心者向けにわかりやすく説明されており、複雑な用語や高度な概念はありません。トピックにまったく慣れていない場合でも、簡単に理解できるでしょう。

コンテンツの概要

• ウィンドウ関数で使用される関数の種類
  • 集計関数
  • ランキング機能
  • 価値関数
• 集計ウィンドウ関数
• 重要なポイント

ウィンドウ関数で使用される関数の種類

一連の行 (いわゆるウィンドウ) に対してウィンドウ関数を適用できる関数には、主に 3 つのタイプがあります。集計関数、ランキング関数、および値関数です。下の画像では、各カテゴリに分類されるさまざまな関数の名前が表示されます。

集計関数

これらはデータ グループに対して数学的演算を実行し、結果として 1 つの累積値が得られます。これらは、平均、行の合計数、最大値または最小値、各ウィンドウまたはパーティション内の合計など、さまざまな集計を計算するために使用されます。

• SUM: 列内のすべての値を合計します。

• COUNT: NULL 値を除いた列内の値の数を計算します。

• AVG: 列内の平均値を検索します。

• MAX: 列内の最大値を識別します

• MIN: 列内の最小値を識別します。

  
  

ランキング機能

これらは、パーティション内の各行にランクや順序を与えるために使用されます。これは、連続番号の割り当てや特定の値に基づくランキングなど、特定の基準を評価することによって行われます。

• ROW_NUMBER: パーティション内の各新しいレコードに連続したランク番号を割り当てます。
• RANK: 結果セット内の各行のランクを指定します。この場合、システムが同一の値を検出すると、それらに同じランクを割り当て、次の値をスキップします。
• DENSE_RANK: 結果セットのパーティション内の各行にランクを割り当てます。 RANK 関数とは異なり、この関数は後続の値をスキップすることなく、同一の値のランクを返します。
• NTILE: 現在の行がどのグループに属しているかを判断できます。グループの数は括弧内に示されています。
  

価値関数

これらにより、グループ内の異なる行間の値を簡単に比較できるようになり、値をそのグループ内の最初または最後の値と比較することもできます。つまり、ウィンドウ内のさまざまな行を簡単に移動して、ウィンドウの先頭または末尾の値をチェックアウトできます。

• LAG または LEAD: 自己結合操作を実行せずに、前または後続の行のデータにアクセスします。これらの関数は、経時的な差異の計算など、同じ結果セットまたはパーティション内の行と別の行を比較する必要がある問題を解決する場合に特に役立ちます。
• FIRST_VALUE または LAST_VALUE: 定義されたウィンドウまたはパーティションから最初または最後の値を取得します。これらの関数は、特定の期間内の差異を計算する場合に特に便利です。

ウィンドウ関数の使用を開始するには、仮想の「給与」テーブルを作成し、データを入力しましょう。

テーブルの作成:

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

テーブルに記入する:

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

「給与」テーブルが正常に入力されたかどうかを確認してみましょう。

 select * from salary 

次のクエリは、テーブルの従業員の名前と給与を表示します。

 select employee_name, salary from salary 

給与の合計、平均給与、最大値、最小値、行数の計算は、集計関数の一般的な使用例です。

集計関数を適用すると、給与が集計されて 1 行に表示されます。

しかし、「給与」テーブルの従業員の名前と給与を表示し、3 番目の列にすべての給与の合計を表示したい場合はどうすればよいでしょうか。この値はすべての行で同じである必要があります。

ウィンドウ関数を使う絶好の機会です。

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

sum(salary) over()の各行の給与の合計を計算するウィンドウ関数を詳しく見てみましょう。

over()式は、関数が操作するウィンドウまたは行のセットを定義します。この例では、ウィンドウはテーブル全体です。つまり、関数はすべての行に適用されます。

over()式は、 over()の前にリクエストされた関数と組み合わせた場合にのみ機能します。

たとえば、 sum(salary) over() 、ここでsum()は集計関数です。そして、式sum(salary) over()全体が集計ウィンドウ関数です。

前に述べたように、ウィンドウ関数が適用されるすべての関数は、集計関数、ランキング関数、および値関数の 3 つのグループに分類できます。

集計関数sum() 、 count() 、 avg() 、 min() 、 max()とover()式は、集計ウィンドウ関数のグループを作成します。

この記事では、この特定の種類のウィンドウ関数に焦点を当てます。

集計ウィンドウ関数

例に戻りましょう。

従業員の名前を尋ねてみましょう。彼らの給料。すべての給与の合計。平均給与、最高給与、最低給与。従業員の数。

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

ウィンドウ関数が何であるかがより明確になったので、それが仕事で役立ついくつかのケースを検討してみましょう。

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

4 番目の列では、各給与の総給与予算の割合を計算しました。ジェッサの給与は給与予算全体の 15% 近くに相当します。

order by後の並べ替えにsalary/sum(salary)over()割合を計算する式も配置したことに注意してください。ウィンドウ関数は、出力selectだけでなく、 order byにもあります。

別の例: 給与を会社の平均給与と比較してみましょう。

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

ご覧のとおり、Andrew の給与は平均より 2110 低く、Jessa の給与は平均より 1690 高いことがわかります。

従業員名、部門、給与の 3 つの列をリクエストしてみましょう。また、部門ごとに分類してみます。

 select employee_name, department, salary from salary order by department 

次に、同じ 3 つの列に加えて、全従業員の給与の合計を含む列をリクエストします。ウィンドウ関数を使用して実行できることはすでにご存知でしょう。

 select employee_name, department, salary, sum(salary)over() from salary order by department 

しかし、最後の列に示すように、すべての給与の合計ではなく、各部門の給与の合計を要求したい場合はどうなるでしょうか。

これを行うには、パラメータpartition by over()式に追加します。

 select employee_name, department, salary, sum(salary)over(), sum(salary)over(partition by department) from salary order by department

Partition by使用すると、ウィンドウ関数をすべての行 (ウィンドウ全体) ではなく列セクションに適用できます。

単純なグループ分けのように見えませんか？各部門の給与の合計を計算するには、部門 (窓口関数の俗語でいうセクション) ごとにグループ化し、金額を計算します。

 select department, sum(salary) from salary group by department 

基本的に、グループ化とpartition byの違いは、 group by partition by関数の結果はgroup byを使用した集計関数の結果と同じですが、グループに基づいてすべての行を集計関数で提供することです。

ウィンドウ関数に戻りましょう。

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

ウィンドウ関数、特にパラメータpartition byを使用すると、部門の給与の合計から各従業員の給与の割合を計算できます。または、たとえば、給与を部門の平均給与と比較することもできます。

重要なポイント

要点をまとめると：

• ウィンドウ関数は、現在の行に何らかの形で関連する一連の行に対して計算を実行します。

• ウィンドウ関数が適用される主な関数の種類は、集計関数、ランキング関数、値関数です。

• ウィンドウ関数を使用するには、クエリ結果セット内のウィンドウ (行のセット) を定義するover()句を適用する必要があります。次に、ウィンドウ関数はウィンドウ内の各行の値を計算します。

• 集計を実行する列を指定するには、 over()句にpartition by句を追加する必要があります。 Partition byはグループ化に似ていますが、グループごとに 1 行ではなく、集計関数が適用されたすべての行を返します。

  
  

今のところこれです！次の 2 つの記事では、初心者に適した簡単な例を使用して、より高度な SQL の概念を探っていきますので、ご期待ください。