主页 / dba / 问题 / 55553
Accepted
D3XT3R
Asked: 2013-12-25 04:16:06 +0800 CST

选择第 n 个百分位的行

  • 772

我有两张桌子，table1和table2。让这两个表包含日期、id 和延迟列。

我有一个简单的查询，它对两个表执行连接并返回一组行：

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

我需要从返回的行集中找到第 n 个百分位行，假设我需要从数据中找到 90%、99% 和 99.9% 的百分位行。

我需要以如下形式显示数据：

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

这是我第一次接触 PostgreSQL。我很困惑我应该如何进行。

我在看PERCENT_RANK()功能。请指导我正确的方向。

postgresql window-functions

1 个回答

  1. Best Answer

    在子查询中使用窗口函数ntile()（需要 Postgres 8.4 或更高版本）。
    然后选择您感兴趣的段（对应于百分位数）并从中选择具有最低值的行：

    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;

    Postgres 特有DISTINCT ON的在最后一步就派上用场了。在这个相关答案中的详细说明：
    选择每个 GROUP BY 组中的第一行？

    为了获得和百分位数90，我选择了匹配的粒度。并根据评论添加了一个百分位数。9999.9ntile(1000)60

    该算法选择等于或高于确切值的行。您可以在子查询中添加一行以percent_rank()获得选择行的确切相对排名：

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

    另外：我将列名替换为date，the_date因为我习惯于避免保留 SQL 关键字作为标识符，即使 Postgres 允许它们。

    • 5

相关问题