Início / dba / Perguntas / 55553
Accepted
D3XT3R
Asked: 2013-12-25 04:16:06 +0800 CST

Selecione a linha no enésimo percentil

  • 772

Eu tenho duas tabelas table1e table2. Deixe as duas tabelas conterem a coluna de data, id e latência.

Tenho uma consulta simples que realiza um join nas duas tabelas e retorna um conjunto de linhas:

Select table1.date,(table2.latency - table1.latency) as ans from table1, table2
where table1.id = table2.id order by ans;

Preciso encontrar a linha do percentil n do conjunto de linhas retornado, digamos que preciso encontrar a linha do percentil 90%, 99% e 99,9% dos dados.

Eu preciso exibir os dados em um formulário como este:

    date       |   percentile  | ans
    01-12-1995 |    90         | 0.001563
    02-12-1999 |    99         | 0.0015
    05-12-2000 |    99.9       | 0.012

Este é meu primeiro encontro com o PostgreSQL. Estou confuso sobre como devo proceder.

Eu estava dando uma olhada na PERCENT_RANK()função. Por favor, me guie na direção correta.

postgresql window-functions

1 respostas

  1. Best Answer

    Use a função window ntile()em uma subconsulta (requer Postgres 8.4 ou posterior).
    Em seguida, selecione os segmentos de seu interesse (correspondentes aos percentis) e escolha a linha com o menor valor:

    SELECT DISTINCT ON (segment)
       the_date, to_char((segment - 1)/ 10.0, '99.9') AS percentile, ans
FROM  (
    SELECT t1.the_date 
          ,ntile(1000) OVER (ORDER BY (t2.latency - t1.latency)) AS segment
          ,(t2.latency - t1.latency) AS ans
    FROM   table1 t1
    JOIN   table2 t2 ON t1.id = t2.id
   ) sub
WHERE  segment IN (601, 901, 991, 1000)
ORDER  BY segment, ans;

    O específico do Postgres DISTINCT ONé útil para a última etapa. Explicação detalhada nesta resposta relacionada em SO:
    Selecione a primeira linha em cada grupo GROUP BY?

    Para obter o 90, 99e o 99.9percentil, escolhi a granularidade correspondente com ntile(1000). E adicionou um 60percentil de acordo com o comentário.

    Este algoritmo seleciona a linha no valor exato ou acima dele. Além disso, você pode adicionar uma linha à subconsulta percent_rank()para obter a classificação relativa exata da linha selecionada:

     percent_rank() OVER (ORDER BY (t2.latency - t1.latency)) AS pct_rank

    À parte: substituí o nome da coluna datepor the_datejá que tenho o hábito de evitar palavras-chave SQL reservadas como identificadores, mesmo que o Postgres as permita.

    • 5

relate perguntas