ウィンドウ関数の構文の概要ウィンドウ関数用データの一意の並び順サポートされている関数ウィンドウ関数例のサンプルテーブル

ウィンドウ関数

ウィンドウ関数を使用すると、分析的なビジネスクエリをより効率的に作成できます。ウィンドウ関数はパーティションまたは結果セットの「ウィンドウ」で演算し、ウィンドウのすべての行に値を返します。それに対して、ウィンドウ以外の関数は、結果セットの行ごとに計算を実行します。結果の行を集計するグループ関数とは異なり、ウィンドウ関数はテーブル式のすべての行を保持します。

戻り値はこのウィンドウの行セットの値を使用して計算されます。ウィンドウはテーブルの各行に、追加の属性を計算するために使用する行のセットを定義します。ウィンドウはウィンドウ仕様 (OVER 句) を使用して定義され、次の 3 つの主要な概念に基づいています。

ウィンドウのパーティション、列のグループを形成 (PARTITION 句)
ウィンドウの並び順、各パーティション内の行の順序またはシーケンスの定義 (ORDER BY 句)
ウィンドウのフレーム、各行に関連して定義され、行のセットをさらに制限 (ROWS 仕様)

ウィンドウ関数は、最後の ORDER BY 句を除いて、クエリで実行される最後の演算のセットです。すべての結合およびすべての WHERE、GROUP BY、および HAVING 句は、ウィンドウ関数が処理される前に完了されます。そのため、ウィンドウ関数は選択リストまたは ORDER BY 句のみに表示できます。複数のウィンドウ関数は、別のフレーム句を持つ 1 つのクエリ内で使用できます。ウィンドウ関数は、CASE などの他のスカラー式でも使用できます。

ウィンドウ関数はネストできません。例えば、集計関数 SUM はウィンドウ関数 SUM 内に表示される可能性がありますが、ウィンドウ関数 SUM は別のウィンドウ関数 SUM 内に表示できません。ウィンドウ関数が別のウィンドウ関数にネストされているため、以下はサポートされません。


SELECT SUM(SUM(selectcol) OVER (PARTITION BY ordercol)) OVER (Partition by ordercol) FROM t;

ウィンドウ関数の構文の概要

ウィンドウ関数は、次のような標準構文に従います。


function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )

ここで、function は、このセクションで説明している関数の 1 つです。

expr_list は次のとおりです。


expression | column_name [, expr_list ]

order_list は次のとおりです。


expression | column_name [ ASC | DESC ] 
[ NULLS FIRST | NULLS LAST ]
[, order_list ]

frame_clause は次のとおりです。


ROWS
{ UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } |

{ BETWEEN
{ UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW}
AND
{ UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }}

引数

function

ウィンドウ関数。詳細については、個々の関数の説明を参照してください。

OVER

ウィンドウの仕様を定義する句。OVER 句はウィンドウ関数に必須であり、ウィンドウ関数を他の SQL 関数と区別します。

PARTITION BY expr_list

(オプション) PARTITION BY 句は結果セットをパーティションに再分割します。これは GROUP BY 句と似ています。パーティション句が存在する場合、関数は各パーティションの行に対して計算されます。パーティション句が指定されていない場合、1 つのパーティションにテーブル全体が含まれ、関数は完全なテーブルに対して計算されます。

ランク付け関数 DENSE_RANK、NTILE、RANK、および ROW_NUMBER では、結果セットのすべての行でグローバルな比較が必要です。PARTITION BY clauseを使用すると、クエリオプティマイザーは、パーティションに応じて複数のスライスにワークロードを分散させることにより、個々の集計を並列で実行できます。PARTITION BY 句がない場合、集計ステップを 1 つのスライスで順次実行する必要があり、特に大規模なクラスターではパフォーマンスに大きな悪影響を与えることがあります。

Amazon Redshift は、PARTITION BY 句で文字列リテラルをサポートしていません。

ORDER BY order_list

(オプション) ウィンドウ関数は、ORDER BY で順序仕様に従ってソートされた各パーティション内の行に適用されます。この ORDER BY 句は、frame_clause の ORDER BY 句とは異なり、両者はまったく無関係です。ORDER BY 句は、PARTITION BY 句なしで使用できます。

ランク付け関数の場合、ORDER BY 句はランク付けの値に使用する基準を特定します。集計関数の場合、パーティションで分割された行は、集計関数がフレームごとに計算される前に順序付けされる必要があります。ウィンドウ関数の種類の詳細については、「ウィンドウ関数」を参照してください。

列識別子または列識別を検証する式は、順序リストで必要とされます。定数も定数式も、列名の代用として使用することはできません。

NULL 値は独自のグループとして扱われ、NULLS FIRST または NULLS LAST オプションに従ってソートおよびランク付けされます。デフォルトでは、NULL 値は昇順ではソートされて最後にランク付けされ、降順ではソートされて最初にランク付けされます。

Amazon Redshift は、ORDER BY 句で文字列リテラルをサポートしていません。

ORDER BY 句を省略した場合、行の順序は不確定になります。

注記

Amazon Redshift などの並列システムでは、ORDER BY 句がデータの一意および全体の並び順を生成しない場合、行の順序は不確定になります。つまり、ORDER BY 式が重複した値を生成する場合 (部分的ソート)、これらの行の戻り値の順序は Amazon Redshift の実行によって異なることがあります。そのため、ウィンドウ関数は予期しない結果または矛盾した結果を返す場合があります。詳細については、「ウィンドウ関数用データの一意の並び順」を参照してください。

column_name

パーティション化または順序付けされる列の名前。

ASC | DESC

次のように、式のソート順を定義するオプション:

ASC: 昇順 (数値の場合は低から高、文字列の場合は「A」から「Z」など) オプションを指定しない場合、データはデフォルトでは昇順にソートされます。
DESC: 降順 (数値の場合は高から低、文字列の場合は「Z」から「A」)。

NULLS FIRST | NULLS LAST

NULL を NULL 以外の値より先に順序付けするか、NULL 以外の値の後に順序付けするかを指定するオプション。デフォルトでは、NULL は昇順ではソートされて最後にランク付けされ、降順ではソートされて最初にランク付けされます。

frame_clause

集計関数では、ORDER BY を使用する場合、フレーム句は関数のウィンドウで行のセットをさらに絞り込みます。これは、順序付けされた結果内の行のセットを含めるか、または除外できるようにします。フレーム句は ROWS キーワードおよび関連する指定子で構成されます。

frame 句は、ランク付け関数には適用されません。また、集計関数の OVER 句の中に ORDER BY 句がない場合は、フレーム句は必要ありません。ORDER BY 句が集計関数に使用される場合、明示的なフレーム句が必要です。

ORDER BY 句が指定されていない場合、暗黙的なフレームはバインドされません (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING と同じ)。

ROWS

この句は、現在の行からの物理オフセットを指定してウィンドウフレームを定義します。

この句は、現在のウィンドウの行、または現在の行の値を組み合わせるパーティションを指定します。また、現在の行の前後に配置される行の位置を指定する引数を使用します。すべてのウィンドウフレームの参照点は現在の行です。ウィンドウフレームがパーティションで次にスライドすると、各行は順に現在の行になります。

フレームは、次のように現在の行までと現在の行を含む行の簡易セットである場合があります。


{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}

また、次の 2 つの境界の間の行のセットである場合があります。


BETWEEN
{ UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }
AND
{ UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }

UNBOUNDED PRECEDING はウィンドウがパーティションの最初の行で開始することを示し、offset PRECEDING はウィンドウが現在の行の前のオフセット値と等しい行数で開始することを示します。デフォルトは UNBOUNDED PRECEDING です。

CURRENT ROW は、ウィンドウが現在の行で開始または終了することを示します。

UNBOUNDED FOLLOWING はウィンドウがパーティションの最後の行で終了することを示し、offset FOLLOWING はウィンドウが現在の行の後のオフセット値と等しい行数で終了することを示します。

offset は、現在の行の前後にある物理的な行数を識別します。この場合、offset は、正の数値に評価される定数である必要があります。例えば、5 FOLLOWING は現在の行より 5 行後のフレームを終了します。

BETWEEN が指定されていない場合、フレームは現在の行により暗黙的に区切られます。例えば、ROWS 5 PRECEDING は ROWS BETWEEN 5 PRECEDING AND CURRENT ROW と同じです。また、ROWS UNBOUNDED FOLLOWINGは ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING と同じです。

注記

開始境界が終了境界よりも大きいフレームを指定することはできません。例えば、以下のフレームはいずれも指定することができません。


between 5 following and 5 preceding
between current row and 2 preceding
between 3 following and current row

ウィンドウ関数用データの一意の並び順

ウィンドウ関数の ORDER BY 句がデータの一意および全体の並び順を生成しない場合、行の順序は不確定になります。ORDER BY 式が重複した値 (部分的な順序付け) を生成する場合、これらの行の戻り値の順序は実行時によって異なる可能性があります。この場合、ウィンドウ関数は予期しない結果または矛盾した結果を返す場合があります。

例えば、次のクエリは、複数の実行にわたって異なる結果を返します。これらの異なる結果は、order by dateid が SUM Window 関数でデータの一意の順序を生成しないために発生します。


select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |   1730.00 |     1730.00
1827 |    708.00 |     2438.00
1827 |    234.00 |     2672.00
...

select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |    234.00 |      234.00
1827 |    472.00 |      706.00
1827 |    347.00 |     1053.00
...

この場合、2 番目の ORDER BY 列をウィンドウ関数に追加すると問題を解決できます。


select dateid, pricepaid,
sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid | sumpaid
--------+-----------+---------
1827 |    234.00 |  234.00
1827 |    337.00 |  571.00
1827 |    347.00 |  918.00
...

サポートされている関数

Amazon Redshift は、集計とランク付けという 2 つのタイプのウィンドウ関数をサポートします。

サポートされる集約関数は次のとおりです。

AVG ウィンドウ関数
COUNT ウィンドウ関数
CUME_DIST ウィンドウ関数
DENSE_RANK ウィンドウ関数
FIRST_VALUE ウィンドウ関数
LAG ウィンドウ関数
LAST_VALUE ウィンドウ関数
LEAD ウィンドウ関数
LISTAGG ウィンドウ関数
MAX ウィンドウ関数
MEDIAN ウィンドウ関数
MIN ウィンドウ関数
NTH_VALUE ウィンドウ関数
PERCENTILE_CONT ウィンドウ関数
PERCENTILE_DISC ウィンドウ関数
RATIO_TO_REPORT ウィンドウ関数
STDDEV_SAMP および STDDEV_POP ウィンドウ関数 (STDDEV_SAMP と STDDEV はシノニムです)
SUM ウィンドウ関数
VAR_SAMP および VAR_POP ウィンドウ関数 (VAR_SAMP と VARIANCE はシノニムです)

サポートされる集計関数は次のとおりです。

ウィンドウ関数例のサンプルテーブル

ウィンドウ関数別の例をそれぞれの説明と共に参照できます。一部の例で使用している WINSALES という名前のテーブルには、以下の 11 行が含まれています。

SALESID	DATEID	SELLERID	BUYERID	QTY	QTY_SHIPPED
30001	8/2/2003	3	B	10	10
10001	12/24/2003	1	C	10	10
10005	12/24/2003	1	A	30
40001	1/9/2004	4	A	40
10006	1/18/2004	1	C	10
20001	2/12/2004	2	B	20	20
40005	2/12/2004	4	A	10	10
20002	2/16/2004	2	C	20	20
30003	4/18/2004	3	B	15
30004	4/18/2004	3	B	20
30007	9/7/2004	3	C	30

次のスクリプトは、WINSALES テーブルの例を作成し、値を入力します。


CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid char(10),
  qty int,
  qty_shipped int);

INSERT INTO winsales VALUES
  (30001, '8/2/2003', 3, 'b', 10, 10),
  (10001, '12/24/2003', 1, 'c', 10, 10),
  (10005, '12/24/2003', 1, 'a', 30, null),	
  (40001, '1/9/2004', 4, 'a', 40, null),	
  (10006, '1/18/2004', 1, 'c', 10, null),	
  (20001, '2/12/2004', 2, 'b', 20, 20),
  (40005, '2/12/2004', 4, 'a', 10, 10),
  (20002, '2/16/2004', 2, 'c', 20, 20),
  (30003, '4/18/2004', 3, 'b', 15, null),
  (30004, '4/18/2004', 3, 'b', 20, null),	
  (30007, '9/7/2004', 3, 'c', 30, null);

ブラウザで JavaScript が無効になっているか、使用できません。

AWS ドキュメントを使用するには、JavaScript を有効にする必要があります。手順については、使用するブラウザのヘルプページを参照してください。

ドキュメントの表記規則

TO_VARBYTE

AVG