学ぶウィンドウ関数の種類 | トリガー、ウィンドウ関数、DCL

メニューを表示するにはスワイプしてください

SQLで使用される主なウィンドウ関数の種類について簡単に説明。

集約関数

ウィンドウコンテキストで使用される標準的な集約関数（AVG、SUM、MAX、MIN、COUNT）。このタイプのウィンドウ関数は前の章ですでに使用。

順位付け関数

SQLの順位付け関数は、結果セットのパーティション内で各行に順位を割り当てるウィンドウ関数の一種。これらの関数は、順序付き計算や分析を行う際に非常に有用。

RANK(): ORDER BY句に基づき、パーティション内の各異なる行に一意の順位を割り当てる。同じ値の行は同じ順位となり、順位に飛びが生じる；
DENSE_RANK(): RANK()と似ているが、順位に飛びが生じない；
NTILE(n): 順序付けされたパーティション内の行をn個のグループに分割し、各行にグループ番号を割り当てる。

例

Amount関数を使用し、各ProductIDごとにDENSE_RANK()を昇順で並べて売上に順位を付与:


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

結果テーブルには、メインテーブルのすべての情報と、特定の製品ごとの各売上の順位を示す追加列が含まれる。

値比較関数

SQLの値比較ウィンドウ関数は、現在の行の値と同じパーティション内の他の行の値を比較するために使用。
これらの関数は、トレンド分析、隣接行を基にした計算、定義されたウィンドウ内の特定行の値へのアクセスなどに特に有用。 SQLにはいくつかの値比較関数が存在：

LAG() : セルフジョインを使わずに、結果セット内の前の行の値を取得；
LEAD(): セルフジョインを使わずに、結果セット内の次の行の値を取得；
FIRST_VALUE(): ウィンドウフレーム内の最初の行の値を返す；
LAST_VALUE(): ウィンドウフレーム内の最後の行の値を返す。

例

LAG()値比較ウィンドウ関数を使い、各製品ごとに前回の売上からの売上金額の変化を計算:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

クエリの説明

クエリの主な構成要素：

SELECT句：テーブルから取得する列を指定。
- 列名： SalesID、ProductID、SalesDate、Amountなど、各販売データの関連情報を表示。
- ウィンドウ関数： LAG()関数を使用し、パーティション内で特定列の前行の値を取得。さらに、現在値と前回値の差分を示す計算列も追加。

ウィンドウ関数の構文：

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: 前の値を取得する対象列。
- offset: 現在行から何行前の値を取得するか（デフォルトは1）。
- default_value: オフセットがパーティションの範囲外の場合に返す値（省略可能）。
- PARTITION BY partition_column: 結果セットをパーティションに分割し、各パーティションごとにウィンドウ関数を適用。
- ORDER BY order_column: 各パーティション内の行の順序を指定し、ウィンドウ関数が意味のある順序で処理されるようにする。

その結果、サブクエリやストアドプロシージャを使用せずに、各製品ごとの売上差分情報を簡単に抽出可能。また、次のクエリを使うことで、パーティションを使用せず全売上の差分も計算可能。


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

PARTITION BY ブロック内に OVER 句を含めていないことがわかります。これは、特定の製品ごとではなく、テーブル内のすべての売上に対して前の値を取得したいことを意味します。

すべて明確でしたか？

フィードバックありがとうございます！

セクション 3. 章 3

AIに質問する

何でも質問するか、提案された質問の1つを試してチャットを始めてください

セクション 3. 章 3