Função de janela PERCENTILE_CONT

PERCENTILE_CONT é uma função de distribuição inversa que assume um modelo de distribuição contínua. Ela pega um valor percentil e uma especificação de classificação e retorna um valor intercalar que cairia dentro do valor percentil fornecido em relação à especificação de classificação.

PERCENTILE_CONT computa uma interpolação linear entre valores após ordená-los. Usando o valor percentil (P) e o número de linhas não nulas (N) no grupo de agregação, a função computa o número da linha após ordenar as linhas de acordo com a especificação de classificação. Esse número de linha (RN) é computado de acordo com a fórmula RN = (1+ (P*(N-1)). O resultado final da função agregada é computado por interpolação linear entre os valores das linhas nos números de linha CRN = CEILING(RN) e FRN = FLOOR(RN).

O resultado final será o seguinte.

Se (CRN = FRN = RN), o resultado é (value of expression from row at RN)

Caso contrário, o resultado é o seguinte:

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

Você pode especificar somente a cláusula PARTITION na cláusula OVER. Se PARTITION é especificada, para cada linha, PERCENTILE_CONT retorna o valor que cairia no percentil especificado entre um conjunto de valores dentro de dada partição.

Sintaxe

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

Argumentos

percentil

Constante numérica entre 0 e 1. Nulls são ignorados no cálculo.

WITHIN GROUP ( ORDER BY expr)

Especifica valores numéricos ou de data/hora para classificação e computação do percentil.

OVER

Especifica o particionamento da janela. A cláusula OVER não pode conter uma especificação de ordenação de janela ou de quadro de janela.

PARTITION BY expr

Argumento opcional que define o intervalo de registros para cada grupo na cláusula OVER.

Retornos

O tipo de retorno é determinado pelo tipo de dados da expressão ORDER BY na cláusula WITHIN GROUP. A tabela a seguir mostra o tipo de retorno para cada tipo de dados da expressão ORDER BY.

Tipo de entrada	Tipo de retorno
INT2, INT4, INT8, NUMERIC, DECIMAL	DECIMAL
FLOAT, DOUBLE	DOUBLE
DATA	DATA
TIMESTAMP	TIMESTAMP

Observações de uso

Se a expressão ORDER BY é um tipo de dados DECIMAL com a precisão máxima de 38 dígitos, é possível que PERCENTILE_CONT retorne um resultado impreciso ou um erro. Se o valor de retorno da função PERCENTILE_CONT excede 38 dígitos, o resultado é truncado, o que causa a perda de precisão. Se, durante a interpolação, um resultado intermediário excede a precisão máxima, um excedente numérico ocorre e função retorna um erro. Para evitar essas condições, recomendamos o uso de um tipo de dados com menor precisão ou a conversão da expressão ORDER BY para uma precisão mais baixa.

Por exemplo, uma função SUM com um argumento DECIMAL retorna uma precisão padrão de 38 dígitos. A escala do resultado é a mesma que a escala do argumento. Portanto, por exemplo, uma SUM de uma coluna DECIMAL(5,2) retorna um tipo de dados DECIMAL(38,2).

O seguinte exemplo usa uma função SUM na cláusula ORDER BY de uma função PERCENTILE_CONT. O tipo de dados de coluna PRICEPAID é DECIMAL (8,2), portanto a função SUM retorna 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;

Para evitar a perda potencial de precisão ou um erro de sobrecarga, converta o resultado para um tipo de dados DECIMAL com menor precisão, conforme exibido no exemplo a seguir.

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;

Exemplos

Os seguintes exemplos usam a tabela WINSALES. Para uma descrição da tabela WINSALES, consulte Amostra de tabela para exemplos de funções de janela.

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)

O seguinte exemplo calcula o PERCENTILE_CONT e PERCENTILE_DISC das vendas de ingressos para vendedores no estado de 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)