Fensterfunktion PERCENTILE_CONT

PERCENTILE_CONT ist eine Funktion für die inverse Verteilung, die ein kontinuierliches Verteilungsmodell annimmt. Sie empfängt einen Perzentilwert und eine Sortierspezifikation und gibt einen interpolierten Wert zurück, der in Bezug auf die Sortierspezifikation in den angegebenen Perzentilwert fällt.

PERCENTILE_CONT berechnet eine lineare Interpolierung zwischen Werten, nachdem diese der Reihenfolge entsprechend angeordnet wurden. Mithilfe des Perzentilwerts (P) und der Anzahl der Nicht-Null-Zeilen (N) in der Aggregationsgruppe berechnet die Funktion die Anzahl der Zeilen, nachdem die Zeilen entsprechend der Sortierspezifikation angeordnet wurden. Die Anzahl von Zeilen (RN) wird mit der Formel RN = (1+ (P*(N-1)) berechnet. Das Endergebnis der Aggregationsfunktion wird durch lineare Interpolierung zwischen den Werten aus Zeilen zwischen CRN = CEILING(RN) und FRN = FLOOR(RN) berechnet.

Das Ergebnis wird wie folgt aussehen.

Wenn (CRN = FRN = RN), ist das Ergebnis (value of expression from row at RN)

Andernfalls sieht das Ergebnis wie folgt aus:

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

Sie können in der OVER-Klausel nur die PARTITION-Klausel angeben. Wenn PARTITION angegeben ist, gibt PERCENTILE_CONT für jede Zeile den Wert zurück, der in einem Satz von Werten innerhalb einer bestimmten Partition in das angegebene Perzentil fallen würde.

PERCENTILE_CONT ist eine reine Datenverarbeitungsknoten-Funktion. Die Funktion gibt einen Fehler zurück, wenn die Abfrage auf keine benutzerdefinierte Tabelle oder Amazon-Redshift-Systemtabelle verweist.

Syntax

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

Argumente

percentile

Numerische Konstante zwischen 0 und 1. Null-Werte werden bei der Berechnung ignoriert.

WITHIN GROUP ( ORDER BY expr)

Gibt numerische oder Datum-/Zeitwerte an, nach denen das Perzentil sortiert und berechnet werden soll.

OVER

Gibt die Fensterpartitionierung an. Die OVER-Klausel darf keine Spezifikation für Fensteranordnungen oder Fensterrahmen enthalten.

PARTITION BY expr

Optionales Argument, das den Datensatzbereich für die einzelnen Gruppen in der OVER-Klausel festlegt.

Rückgabewert

Der Rückgabetyp wird durch den Datentyp des ORDER BY-Ausdrucks in der WITHIN GROUP-Klausel festgelegt. Die folgende Tabelle zeigt den Rückgabetyp für jeden ORDER BY-Datentyp an.

Typ der Eingabe	Typ der Rückgabe
INT2, INT4, INT8, NUMERIC, DECIMAL	DECIMAL
FLOAT, DOUBLE	DOUBLE
DATUM	DATUM
TIMESTAMP	TIMESTAMP

Nutzungshinweise

Wenn das Argument ORDER BY den Datentyp DECIMAL hat und mit der maximal zulässigen Präzision von 38 Stellen definiert ist, gibt PERCENTILE_CONT möglicherweise ein falsches Ergebnis oder einen Fehler zurück. Wenn der Rückgabewert der Funktion PERCENTILE_CONT 38 Stellen überschreitet, wird das Ergebnis entsprechend abgekürzt. Dies führt zu einem Genauigkeitsverlust. Wenn während der Interpolierung ein Zwischenergebnis die maximal zulässige Genauigkeit überschreitet, erfolgt ein numerischer Überlauf und die Funktion gibt einen Fehler zurück. Um diese Bedingungen zu vermeiden, werden die Verwendung eines Datentyps mit einer niedrigeren Genauigkeit oder die Umwandlung des Ausdrucks ORDER BY in einen Ausdruck mit niedrigerer Genauigkeit empfohlen.

Beispielsweise gibt eine SUM-Funktion mit einem DECIMAL-Argument standardmäßig eine Präzision von 38 Stellen zurück. Die Ergebnisskala ist die gleiche wie die Skala des Arguments. Eine SUM für eine DECIMAL(5,2)-Spalte gibt also einen DECIMAL(38,2)-Datentyp zurück.

Im folgenden Beispiel wird in der ORDER BY-Klausel einer PERCENTILE_CONT-Funktion eine SUM-Funktion verwendet. Der Datentyp der Spalte PRICEPAID ist DECIMAL(8,2). Daher gibt die SUM-Funktion DECIMAL(38,2) zurück.

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

Um einen möglichen Präzisionsverlust oder Overflow-Fehler zu vermeiden, wandeln Sie das Ergebnis in einen DECIMAL-Datentyp mit einer niedrigeren Präzision um, wie im folgenden Beispiel gezeigt.

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;

Beispiele

Im folgenden Beispiel wird die Tabelle WINSALES verwendet. Eine Beschreibung der Tabelle WINSALES finden Sie unter Beispieltabelle mit Beispielen von Fensterfunktionen.

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)

Im folgenden Beispiel wird der Wert für PERCENTILE_CONT und PERCENTILE_DISC für Ticketverkäufe von Verkäufern im Bundesstaat Washington berechnet.

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)