Funzione finestra PERCENTILE_CONT

PERCENTILE_CONT è una funzione di distribuzione inversa che presuppone un modello di distribuzione continua. Prende un valore percentile e una specifica di ordinamento e restituisce un valore interpolato che rientrerebbe nel valore percentile dato rispetto alla specifica di ordinamento.

PERCENTILE_CONT calcola un'interpolazione lineare tra i valori dopo averli ordinati. Usando il valore percentile (P) e il numero di righe non null (N) nel gruppo di aggregazione, la funzione calcola il numero di righe dopo aver ordinato le righe secondo la specifica di ordinamento. Questo numero di riga (RN) è calcolato secondo la formula RN = (1+ (P*(N-1)). Il risultato finale della funzione di aggregazione è calcolato mediante interpolazione lineare tra i valori delle righe ai numeri di riga CRN = CEILING(RN) e FRN = FLOOR(RN).

Il risultato finale sarà il seguente.

Se (CRN = FRN = RN) allora il risultato è (value of expression from row at RN)

Altrimenti il risultato è il seguente:

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

È possibile specificare solo la clausola PARTITION nella clausola OVER. Se viene specificata la PARTITION, per ogni riga, PERCENTILE_CONT restituisce il valore che rientrerebbe nel percentile specificato tra un insieme di valori all'interno di una determinata partizione.

PERCENTILE_CONT è una funzione solo del nodo di calcolo. La funzione restituisce un errore se la query non fa riferimento a una tabella definita dall'utente o a una tabella di sistema di Amazon Redshift.

Sintassi

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

Argomenti

percentile

Costante numerica compresa tra 0 e 1. I valori null vengono ignorati nel calcolo.

WITHIN GROUP ( ORDER BY expr)

Specifica i valori numerici o di data/ora per ordinare e calcolare il percentile.

OVER

Specifica il partizionamento della finestra. La clausola OVER non può contenere una specifica del frame della finestra o dell'ordinamento della finestra.

PARTITION BY expr

Argomento facoltativo che imposta l'intervallo di registrazioni per ciascun gruppo nella clausola OVER.

Valori restituiti

Il tipo di restituzione è determinato dal tipo di dati dell'espressione ORDER BY nella clausola WITHIN GROUP. La seguente tabella mostra il tipo di restituzione per ciascun tipo di dati dell'espressione ORDER BY.

Tipo di input	Tipo restituito
INT2, INT4, INT8, NUMERIC, DECIMAL	DECIMAL
FLOAT, DOUBLE	DOUBLE
DATE	DATE
TIMESTAMP	TIMESTAMP

Note per l'utilizzo

Se l'espressione ORDER BY è un tipo di dati DECIMAL definito con la precisione massima di 38 cifre, è possibile che PERCENTILE_CONT restituirà un risultato inaccurato o un errore. Se il valore di ritorno della funzione PERCENTILE_CONT supera le 38 cifre, il risultato viene troncato per adattarsi, il che causa una perdita della precisione. Se, durante l'interpolazione, un risultato intermedio supera la precisione massima, si verifica un'eccedenza numerica e la funzione restituisce un errore. Per evitare queste condizioni, consigliamo di utilizzare un tipo di dati con una precisione inferiore o di assegnare l'espressione ORDER BY a una precisione inferiore.

Ad esempio, una funzione SUM con un argomento DECIMAL restituisce una precisione predefinita di 38 cifre. Il ridimensionamento del risultato coincide con il ridimensionamento dell'argomento. Quindi, ad esempio, un SUM di una colonna DECIMAL(5,2) restituisce un tipo di dati DECIMAL(38,2).

L'esempio seguente utilizza una funzione SUM nella clausola ORDER BY di una funzione PERCENTILE_CONT. Il tipo di dati della colonna PRICEPAID è DECIMAL (8,2), quindi la funzione SUM restituisce 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;

Per evitare una perdita potenziale di precisione o un errore di sovraccarico, assegnare il risultato a un tipo di dati DECIMAL con una precisione inferiore, come mostra il seguente esempio.

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;

Esempi

Gli esempi seguenti usano la tabella WINSALES. Per una descrizione della tabella WINSALES, consultare Tabella di esempio per gli esempi della funzione finestra.

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)

L'esempio seguente calcola le PERCENTILE_CONT e PERCENTILE_DISC delle vendite dei biglietti per i venditori nello stato di Washington.

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)