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).

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

構文

PERCENTILE_CONT(percentile) WITHIN GROUP(ORDER BY expr)

引数

percentile

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

expr

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

戻り値

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

Input type 戻り型
INT2, INT4, INT8, NUMERIC, DECIMAL DECIMAL
FLOAT, DOUBLE DOUBLE
DATE DATE
TIMESTAMP TIMESTAMP
TIMESTAMPTZ TIMESTAMPTZ

使用に関する注意事項

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

ステートメントにソートベースの集計関数 (LISTAGG、PERCENTILE_CONT、または MEDIAN) に対する複数の呼び出しが含まれる場合、すべて同じ ORDER BY 値を使用する必要があります。MEDIAN では、式の値による暗黙的な順序が適用されることに注意してください。

例えば、次のステートメントはエラーを返します。

SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(pricepaid) FROM sales GROUP BY salesid, pricepaid; An error occurred when executing the SQL command: SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(pricepaid) FROM sales GROUP BY salesid, pricepaid; ERROR: within group ORDER BY clauses for aggregate functions must be the same

次のステートメントは正常に実行されます。

SELECT TOP 10 salesid, SUM(pricepaid), PERCENTILE_CONT(0.6) WITHIN GROUP(ORDER BY salesid), MEDIAN(salesid) FROM sales GROUP BY salesid, pricepaid;

次の例では、TICKIT サンプルデータを使用します。詳細については、「サンプルデータベース」を参照してください。

以下は、PERCENTILE_CONT(0.5) が MEDIAN と同じ結果を生成する例です。

SELECT TOP 10 DISTINCT sellerid, qtysold, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY qtysold), MEDIAN(qtysold) FROM sales GROUP BY sellerid, qtysold; +----------+---------+-----------------+--------+ | sellerid | qtysold | percentile_cont | median | +----------+---------+-----------------+--------+ | 2 | 2 | 2 | 2 | | 26 | 1 | 1 | 1 | | 33 | 1 | 1 | 1 | | 38 | 1 | 1 | 1 | | 43 | 1 | 1 | 1 | | 48 | 2 | 2 | 2 | | 48 | 3 | 3 | 3 | | 77 | 4 | 4 | 4 | | 85 | 4 | 4 | 4 | | 95 | 2 | 2 | 2 | +----------+---------+-----------------+--------+

次の例では、SALES テーブルの各 selleridD の販売数量の PERCENTILE_CONT(0.5) と PERCENTILE_CONT(0.75) を求めます。

SELECT sellerid, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY qtysold) as pct_05, PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY qtysold) as pct_075 FROM sales GROUP BY sellerid ORDER BY sellerid LIMIT 10; +----------+--------+---------+ | sellerid | pct_05 | pct_075 | +----------+--------+---------+ | 1 | 1.5 | 1.75 | | 2 | 2 | 2.25 | | 3 | 2 | 3 | | 4 | 2 | 2 | | 5 | 1 | 1.5 | | 6 | 1 | 1 | | 7 | 1.5 | 1.75 | | 8 | 1 | 1 | | 9 | 4 | 4 | | 12 | 2 | 3.25 | +----------+--------+---------+

最初の sellerid に対する前回のクエリの結果を検証するには、次の例を使用します。

SELECT qtysold FROM sales WHERE sellerid=1; +---------+ | qtysold | +---------+ | 2 | | 1 | +---------+