ROWS & RANGE Criterion

CoddyのSQLジャーニー「基礎」セクションの一部 — レッスン 69/72。

現在、考慮する前の行数や後の行数を柔軟に選択することはできませんでした。今では、ROWS & RANGE 基準で可能です。これらを使用するには、以下のように記述します：

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

以下のオプションを指定できます：

CURRENT ROW - 現在の行
n PRECEDING - 現在の行より前の行
n FOLLOWING - 現在の行より後の行

ROWS & RANGE の違いは、ROWS の基準は値には関心を持たず位置のみを考慮するのに対し、RANGE は行の位置ではなく値の範囲でウィンドウを定義します。

RANGE の場合、ORDER BY --column_name-- を指定する必要があります。そうしないと、ウィンドウを選択する方法がわからないためです。

例：

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

ここでは、現在の行、その前の行、およびその後の行を含むウィンドウが作成されます。

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

ここでは、各レベル（昇順にソートされた）に対して、現在のレベル、その前の1レベル、およびその後の1レベルを含むウィンドウを作成します。現在のレベルが5の場合、レベル4、5、および6を含みます。

注意: RANGE BETWEEN の使用は、範囲内の行と同じ値を持つすべての行を含むため、ウィンドウに含まれる行数が多くなる可能性があります。一方、ROWS BETWEEN はデータセットに利用可能な限り、常に同じ数の行を含みます。

また RANGE は日付列をサポートしていません。

ROWS と RANGE の違いを示す簡単な例です：

ROWS を使用：

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

RANGE を使用：

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range

チャレンジ

簡単

利用可能なテーブルと列：
newspapers: date, num_newspapers

このチャレンジでは、生産用の新聞があります。現在の行の2日前から1日後までの印刷された新聞数の平均を知りたいです。また、現在の日付およびその前の3日間すべての印刷された新聞数の最大値と最小値の差を知りたいです。これらの列をそれぞれavg_newspapersとdiff_newspapersと名付けてください。

チートシート

ROWS と RANGE を使用して、ウィンドウ関数で柔軟なウィンドウフレームを定義します：

ROWS - 行位置でウィンドウを定義します：

OVER (ROWS BETWEEN --START-- AND --END--)

RANGE - 値範囲でウィンドウを定義します（ORDER BY が必要です）：

OVER (ORDER BY column_name RANGE BETWEEN --START-- AND --END--)

ウィンドウ境界オプション：

CURRENT ROW - 現在の行
n PRECEDING - 現在の前の n 行/値
n FOLLOWING - 現在の後の n 行/値

例：

-- ROWS: includes current row + 1 before + 1 after (by position)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- RANGE: includes values within range (by value)
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

主な違い：