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