PERCENTILE_CONT ウィンドウ関数 - Amazon Redshift

PERCENTILE_CONT ウィンドウ関数

PERCENTILE_CONT は、連続型分散モデルを前提とする逆分散関数です。これは、パーセンタイル値とソート仕様を取得し、ソート仕様を基準として、そのパーセンタイル値に該当する補間値を返します。

PERCENTILE_CONT は、値の順序付けを行った後に値の間の線形補間を計算します。この関数は、パーセンタイル値 (P) と集計グループの Null ではない行の数 (N) を使用して、ソート使用に従って行の順序付けを行った後に行番号を計算します。この行番号 (RN) は、計算式 RN = (1+ (P*(N-1)) に従って計算されます。集計関数の最終結果は、行番号 CRN = CEILING(RN) および FRN = FLOOR(RN) にある行の値の間の線形補間に基づいて計算されます。

最終結果は次のとおりです。

(CRN = FRN = RN) である場合、結果は (value of expression from row at RN)

そうでない場合、結果は

(CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN).

OVER 句では PARTITION 句のみを指定できます。各行に対して PARTITION を指定すると、PERCENTILE_CONT は、特定のパーティション内にある一連の値から、指定したパーセンタイルに該当する値を返します。

PERCENTILE_CONT はコンピューティングノード専用の関数です。クエリがユーザー定義のテーブルまたは Amazon Redshift システムテーブルを参照しない場合、関数がエラーを返します。

構文

PERCENTILE_CONT ( percentile ) WITHIN GROUP (ORDER BY expr) OVER ( [ PARTITION BY expr_list ] )

引数

percentile

0 と 1 の間の数値定数。Null は計算では無視されます。

WITHIN GROUP ( ORDER BY expr)

パーセンタイルをソートして計算するための数値または日付/時間値を指定します。

OVER

ウィンドウのパーティションを指定します。OVER 句にウィンドウの並び順またはウィンドウフレーム仕様を含めることはできません。

PARTITION BY expr

OVER 句の各グループのレコードの範囲を設定するオプションの引数。

戻り値

戻り型は、WITHIN GROUP 句の ORDER BY 式のデータ型に基づいて決定されます。次の表は、各 ORDER BY 式のデータ型に対応する戻り型を示しています。

入力の型 戻り型
INT2、INT4、INT8、NUMERIC、DECIMAL DECIMAL
FLOAT、DOUBLE DOUBLE
DATE DATE
TIMESTAMP TIMESTAMP

使用に関する注意事項

ORDER BY 式が DECIMAL データ型であり、その最大精度が 38 桁である場合、PERCENTILE_CONT が不正確な結果またはエラーを返す可能性があります。PERCENTILE_CONT 関数の戻り値が 38 桁を超える場合、結果は 38 桁までとなり、39 桁以降は切り捨てられるため、精度が失われます。補間中に中間結果が最大精度を超えた場合には、数値オーバーフローが発生し、この関数はエラーを返します。このような状態を回避するため、精度が低いデータ型を使用するか、ORDER BY 式を低い精度にキャストすることをお勧めします。

例えば、DECIMAL 引数の SUM 関数のデフォルトの 38 桁の精度を返します。結果のスケールは、引数のスケールと同じです。したがって、例えば、DECIMAL(5,2) 列の SUM は DECIMAL(38,2) データ型を返します。

次の例は、PERCENTILE_CONT 関数の ORDER BY 句で SUM 関数を使用します。PRICEPAID 列のデータ型は DECIMAL(8,2) であるため、SUM 関数は DECIMAL(38,2) を返します。

select salesid, sum(pricepaid), percentile_cont(0.6) within group (order by sum(pricepaid) desc) over() from sales where salesid < 10 group by salesid;

精度の損失またはオーバーフローエラーを回避するには、次の例が示すように、精度が低い DECIMAL データ型に結果をキャストします。

select salesid, sum(pricepaid), percentile_cont(0.6) within group (order by sum(pricepaid)::decimal(30,2) desc) over() from sales where salesid < 10 group by salesid;

次の例は、WINSALES テーブルを使用します。WINSALES テーブルの説明については、「ウィンドウ関数例のサンプルテーブル」を参照してください。

select sellerid, qty, percentile_cont(0.5) within group (order by qty) over() as median from winsales; sellerid | qty | median ----------+-----+-------- 1 | 10 | 20.0 1 | 10 | 20.0 3 | 10 | 20.0 4 | 10 | 20.0 3 | 15 | 20.0 2 | 20 | 20.0 3 | 20 | 20.0 2 | 20 | 20.0 3 | 30 | 20.0 1 | 30 | 20.0 4 | 40 | 20.0 (11 rows)
select sellerid, qty, percentile_cont(0.5) within group (order by qty) over(partition by sellerid) as median from winsales; sellerid | qty | median ----------+-----+-------- 2 | 20 | 20.0 2 | 20 | 20.0 4 | 10 | 25.0 4 | 40 | 25.0 1 | 10 | 10.0 1 | 10 | 10.0 1 | 30 | 10.0 3 | 10 | 17.5 3 | 15 | 17.5 3 | 20 | 17.5 3 | 30 | 17.5 (11 rows)

次の例は、ワシントン州の販売者のチケット販売の PERCENTILE_CONT と PERCENTILE_DISC を計算します。

SELECT sellerid, state, sum(qtysold*pricepaid) sales, percentile_cont(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over(), percentile_disc(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over() from sales s, users u where s.sellerid = u.userid and state = 'WA' and sellerid < 1000 group by sellerid, state; sellerid | state | sales | percentile_cont | percentile_disc ----------+-------+---------+-----------------+----------------- 127 | WA | 6076.00 | 2044.20 | 1531.00 787 | WA | 6035.00 | 2044.20 | 1531.00 381 | WA | 5881.00 | 2044.20 | 1531.00 777 | WA | 2814.00 | 2044.20 | 1531.00 33 | WA | 1531.00 | 2044.20 | 1531.00 800 | WA | 1476.00 | 2044.20 | 1531.00 1 | WA | 1177.00 | 2044.20 | 1531.00 (7 rows)