ROWS & RANGEの基準
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ここでは、現在の行、その前の行、その後の行を含むウィンドウを作成します。
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWINGここでは、各レベルについて(昇順に並べ替えて)、現在のレベル、その1つ前のレベル、およびその1つ後のレベルを含むウィンドウを作成します。現在のレベルが5の場合、レベル4、5、6が含まれます。
注: RANGE BETWEENを使用すると、範囲内の値と同じ値を持つすべての行が含まれるため、ウィンドウに含まれる行数が増えることがあります。一方、ROWS BETWEENでは、(データセット内に存在する限り)常に同じ数の行が含まれます。
また、テキストとして保存された日付('2024-01-05'など)では、RANGEオフセットによる日付計算は行われません。1 PRECEDINGでは、ウィンドウには同じ日付の行だけが含まれます。日数を数えるには、julianday(date)などの数値で並べ替えます。
ROWS と RANGE の違いを示す簡単な例です。
ROWS を使う場合:
SELECT employee_name, salary,
AVG(salary) OVER (
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as avg_salary_rowsRANGEを使う:
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と呼びます。
自分で試してみよう
このレッスンには短いクイズがあります。レッスンを始めて解答し、進捗を記録しましょう。
基礎のすべてのレッスン
自分で練習してみよう: SQLプレイグラウンド