SQL ウィンドウ関数の構造

Mar 31 2023

基本に戻る | 初心者のための SQL の基礎

企業データを理解するため。多くのクエリを実行する必要があります。私が「たくさん」と言うとき、私はそれを意味します。

canvaで作成された著者による画像

企業データを理解するため。多くのクエリを実行する必要があります。私が「たくさん」と言うとき、私はそれを意味します。なじみのないデータの山を操作するのは気が遠くなることが多いため、データ自体を調査して理解するために時間をかけることを常にお勧めします。基本的なデータ検索スキルを持っていることは良いことですが、データから有用な洞察を導き出すための分析関数を知ることは、ケーキの上にチェリーであり、楽しいことでもあります!

私はデータビジュアライゼーションのバックグラウンドを持っており、データを理解するだけでなく、注目に値する発見を見つけ出し、より広いチームに強調することが重要です。また、複雑なダッシュボードの構築は、データを集計するためにデータソースに戻ったり行ったり来たりのプロセスであることが非常に多く、SQL ウィンドウ関数は常に私のデータ分析の旅に同行してきました。

それらはデータ分析には非常に便利ですが、ある種の混乱があり、人々はしばしばそれらを使用することを恐れています. SQL ウィンドウ関数の詳細なガイドを書いているときに、それがあまりにも説明的になりすぎていることに気付きましたが、特に構文と一緒に使用される句に関する詳細を省略したくありませんでした。構成要素を理解することは重要ですよね？そのため、この記事ではウィンドウ関数の構成要素を分解して、処理と実装に圧倒されないようにします。

いつものように、自動車販売店のビジネスデータを保持するクラシックモデルのMySQL サンプルデータベースをデモンストレーションに使用します。以下は参照用のER図です。

著者による画像

まず最初に、ウィンドウ関数とは何ですか?

ウィンドウ関数の教科書的な定義は、

ウィンドウ関数は、現在の行に何らかの形で関連している一連のテーブル行にわたって計算を実行します。

画像ソース: マスターファイル

この小さなやつは窓から何を見ていると思いますか? この部屋または建物の窓の外のシーンからの部分的なビュー。右？それはまさにウィンドウ関数が行うことです。現在の行を集計せずに、データのサブセットに対して計算を実行できます。

何が必要ですか？なぜウィンドウ関数? 集計関数はどこで遅れていますか?

これはテーブルPRODUCTSのサンプルデータです。デモ目的で、 PRODUCTLINEs (飛行機、船、列車)に限定しています。

--sample data from table PRODUCTS.
SELECT
    *
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                
                    
                    
       Image by author

--total quantity in stock for each productline
SELECT 
    PRODUCTLINE,
    SUM(QUANTITYINSTOCK) AS TOTAL_QUNATITY
FROM 
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
GROUP BY PRODUCTLINE;

      
                
                    
                    
       Image by author

著者による画像

今すぐ要件を修正しましょう。

PRODUCTLINE内の各製品の数量と、その特定のPRODUCTLINEの在庫の合計数量を表示します。
PRODUCTLINEごとにグループ化された結果セットを整理します。

--sum() as a window function
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                
                    
                    
       Image by author

著者による画像

ウィンドウ関数の種類

正直なところ、ウィンドウ関数の公式な分類はありませんが、使用方法に基づいて、簡単に 3 つの方法で分類できます。

著者による画像

集計関数- 通常の集計関数をウィンドウ関数として使用して、合計売上高、パーティション内の最小値または最大値など、ウィンドウパーティション内の数値列の集計を計算できます。
ランキング関数- これらの関数は、パーティション内の各行のランキング値を返します。
値関数- これらの関数は、単純な統計や時系列分析を生成するのに役立ちます。

ウィンドウ関数の一般的な構文は次のとおりです。

著者による画像

深く掘り下げる前に。まず、その中の各節の意味を理解しましょう。

OVER() 句

OVER() 句は関数をウィンドウ関数として指定するため、常にステートメントに含める必要があります。ウィンドウ関数が適用される行のユーザー指定のサブセット (ウィンドウ) を定義します。OVER()内に何も指定しない場合、ウィンドウ関数は結果セット全体に適用されます。

上記の例に引き続き、

--empty OVER() clause
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER () AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                
                    
                    
       Image by author

PARTITION BY句

PARTITION BY はOVER句と共に使用されます。ユーザー指定の式に基づいてクエリ結果セットをパーティションまたはバケットに分割し、ウィンドウ関数を各パーティションまたはバケットに適用します。

これはオプションであるため、 PARTITION BY句を指定しない場合、関数はすべての行を単一のパーティションとして扱います。上記の例とまったく同じように、PARTITION BY句を指定せずに空のOVER()句を指定しただけなので、すべてのPRODUCTLINEの合計数量が計算されました。

1つを指定するとどうなるか、

--OVER() with PARTITION BY
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                
                    
                    
       Image by author

さて、 PARTITION BY 句とGROUP BY句はどうなるでしょうか。それらは似ていますか、それとも異なりますか?

GROUP BY句、

単一または複数の列/式に基づいて、複数の行を要約行にグループ化します (グループごとに 1 行を返します)。簡単に言えば、結果セットの行数を減らします。
SUM()、AVG()、MAX() などの集計関数と一緒に使用されます。
WHERE句の後、 HAVING、ORDER BY句の前に配置されます。一般的な構文は、

著者による画像

ウィンドウ関数のOVER()句で使用されます。クエリ結果セットをパーティションに分割し、ウィンドウ関数を各パーティションに適用します。
PARTITION BY は、式に基づいて結果を集計するため、GROUP BYに似ています。ただし、主な違いは、結果セットの行が減らないことです。
これはオプションであるため、 PARTITION BY句を指定しない場合、関数はすべての行を単一のパーティションとして扱います。
一般的な構文は、

著者による画像

ORDER BY 句

結果セットの各パーティション内で昇順または降順で結果セットをソートするために使用されます。デフォルトでは昇順です。

ROWS/RANGE 句

ウィンドウ関数の重要な機能は、 PARTITION BY句を使用して結果セットのウィンドウまたはパーティションを作成し、各パーティションで計算を実行することです。これらのパーティション内にさらにサブセットを作成したい場合はどうすればよいでしょうか? うわー！パーティション内のパーティション? はい、それがFRAME句がある理由です。

FRAME句は、現在のパーティションのサブセットをさらに定義します。ROWまたはRANGEを使用して、このサブセットの開始点と終了点を定義します。ORDER BY句が必要です。

フレームは現在の行に対して決定されます。これは、現在の行の位置を基点として取得し、その参照を使用してパーティション内のフレームを定義することを意味します。

ROWS — 現在の行の前後の行数を指定することで、フレームの開始と終了を定義します。
RANGE — ROWSとは対照的に、RANGE は現在の行の値と比較した値の範囲を指定して、パーティション内のフレームを定義します。

{列 | RANGE} BETWEEN <frame_starting> AND <frame_ending>

著者による画像

先に進む前に、フレームを定義するいくつかの基本的な用語を理解しましょう。

画像ソース: mysqltutorials

UNBOUNDED PRECEDING - これは、パーティション内の現在の行より前のすべての行 (最初の行から開始) を指定します。
N PRECEDING - これは、パーティション内の現在の行の前に「N」行を指定します。
UNBOUNDED FOLLOWING - これは、パーティション内の現在の行の後のすべての行 (最後の行まで) を指定します。
M FOLLOWING - これは、パーティション内の現在の行の下にある「M」行を指定します。

SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS TOTAL
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                
                    
                    
       Image by author

著者による画像

FRAME句の例を次に示します。

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — これは、パーティションの最初の行からパーティションの最後の行までのフレームを考慮することを意味します。
ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING - これは、パーティションの最初の行から現在の行の 4 行後までのフレームを考慮することを意味します。
ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING - フレームは、現在の行の 1 行前までの前の 4 行になります。

{ROWS/RANGE}無制限の先行行と現在の行の間

これは、フレームを、パーティション内の行番号 1 から現在の行までのすべての行と見なすことを意味します。

ORDER BY句がない場合、デフォルトのフレームは、

{ROWS/RANGE}無制限の先行と無制限の後続の間

これは単にパーティション全体を意味します。

ウィンドウエイリアスの定義、

同じウィンドウを使用するクエリに複数のウィンドウ関数がある場合は、ウィンドウエイリアスを使用することをお勧めします。

--finding out minimum and maximum MSRP for each productline
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MIN_MSRP,
    MAX(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

--using window alias
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER MSRP_WINDOW AS MIN_MSRP,
    MAX(MSRP) OVER MSRP_WINDOW AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
WINDOW MSRP_WINDOW AS (PARTITION BY PRODUCTLINE);

クエリの実行中に、ウィンドウ関数が結果セットに対して実行されます。