postgresql - 计算 pen ultimate ntile postgres 的成本

Question

我有一个查询，我试图为每个 ID 获取与第 99%ile 数据关联的最大成本。我首先计算 ntile 桶，然后尝试过滤出特定 %ile 的最大值。这适用于在 where 子句中指定百分位数但不适用于 having。 (我不知道我的数据是否每个 ID 都有 100 个尼特)有人能提出解决方法吗？

select distinct t1.id,
       max(t1.cost) over (partition by t1.id) as n99_percentile
from( select id, 
      cost,
      ntile(100) over (partition by id, order by cost) as percentile_bucket
     ) t1
group by t1.id, t1.cost, t1.percentile_bucket
having percentile_bucket = max(percentile_bucket) -1;

谢谢!

Answer 1

如果我正确理解您的问题，您希望按“id”进行分区，然后获取“cost”的值，该值占按相同“cost”排序的分区中所有记录的 <=99%。如果这确实是您想要的，那么您不需要 ntile() 函数，但您可以使用 cume_dist() 函数:

SELECT DISTINCT id, cost, cume_dist
FROM (
  SELECT id, cost, cume_dist, rank() OVER (PARTITION BY id ORDER BY cume_dist DESC) AS rnk
  FROM (
    SELECT id, cost, cume_dist() OVER (PARTITION BY id ORDER BY cost)
    FROM cum) sub2
  WHERE cume_dist <= 0.99) sub
WHERE rnk = 1
ORDER BY id;