sql - percentile_cont 和 percentile_disc 都没有在 PostgreSQL 9.6.3 中计算所需的第 75 个百分位数

Question

使用百分位数函数，但我没有得到所需的输出。我会说“不正确”，但功能可能按预期工作，我只是没有正确理解它们。

这些是我正在处理的数字:

n = 32

160000
202800
240000
250000
265000
280000
285000
300000
300000
300000
300000
300000
309000
325000
350000
358625
364999.92
393750
400000
420000
425000
450000
450000
463500
475000
475000
505808
525000
550000
567300
665000
900000

我对 percentile_cont 的理解是，如果计数是偶数，它将聚合两个数字，因为它将添加它们然后除以二。我对 percentile_disc 的理解是，如果计数是偶数，它只会选择最小的数字。

这是我对计算百分位数的理解，以第 50 个(中位数)为例:

如果数字(n)的个数是奇数，就挑中间的数字；如果数字是偶数，则将中间的两个数字平均。所以在这种情况下，有 32 个数，所以中位数 = (358625 + 364999.92)/2 = 361812.46。 percentile_cont 返回正确的值，因为它对两个值求平均； percentile_disc 返回不正确的值，因为它选择了两者中的最低值。

关于其他百分位数，例如第 10 个，我的理解是您将百分位数乘以数字的数量 (n) 以获得索引:在本例中为 .10 * 32 = 3.2 index。然后你应该四舍五入到最接近的整数，这就是你的百分位值。如果索引是整数，则将索引中的数字与其后的数字进行平均。

在这种情况下，percentile_cont 是错误的，因为它返回 251500，这甚至不是我可以得出的数字。我能得到的最接近的平均值是 24000、250000、265000，即 251666.67。 percentile_disc 返回 250000 的正确结果。

但真正的关键是这个:第 75 个。根据我的计算，它应该返回 469250。 index = (32*.75) = 24，该索引应导致 (463500 + 475000) = 469250。 percentile_disc 返回 463500； percentile_cont 返回 466375，我这辈子也无法得出这个数字。

这是我的查询:

SELECT 
    itemcode, 
    COUNT(itemcode) AS n, 
    PERCENTILE_DIST(0.10) WITHIN GROUP (ORDER BY price) AS 10th,
    PERCENTILE_DIST(0.25) WITHIN GROUP (ORDER BY price) AS 25th,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY price) AS median,
    AVG(price) AS mean,
    PERCENTILE_DIST(0.65) WITHIN GROUP (ORDER BY price) AS 65th,
    PERCENTILE_DIST(0.75) WITHIN GROUP (ORDER BY price) AS 75th,
    PERCENTILE_DIST(0.90) WITHIN GROUP (ORDER BY price) AS 90th
FROM items
WHERE itemcode = 26 AND removed IS NULL
GROUP BY itemcode;

注意:没有removed不是NULL的情况。

我需要做什么才能使它正确且一致地工作？我是否需要编写一个函数来先检查 n，然后根据它是偶数还是奇数来决定哪个 percentile_disc 或 percentile_cont？

SQL fiddle :http://sqlfiddle.com/#!17/aa09c/9

Answer 1

将这个问题发布到 Reddit 并获得了一些帮助。

显然，percentile_cont 函数，除了 Excel 中的 percentile 和 percentile.inc 函数外，还使用 ​​C=1 变体进行计算维基百科中解释的线性插值:

https://en.wikipedia.org/wiki/Percentile#Second_variant.2C_.7F.27.22.60UNIQ--postMath-00000043-QINU.60.22.27.7F

显然，我一直在使用的是平均经验分布。

因此 PostgreSQL 的 native 函数将无法很好地工作，需要制作一个自定义函数，我将在完成后发布。 (我怀疑它会使用 9.4 之前的旧 ntile 方法，但仍在研究中)。

但无论如何，这就是它关闭的原因。