MySQL/MariaDB中PERCENTILE_CONT的替代方法

Question

我想在这个表上计算percentile_cont。 在Oracle中，查询语句如下：

SELECT PERCENTILE_CONT(0.05) FROM sometable;

它在MariaDB/MySQL中的替代方案是什么？

- sushma

---

请定义 percentile_cont 或根据您的示例表格展示您想要从该 SELECT 中获取的数据。 - Rick James

你需要使用 WITHIN GROUP 功能吗？ - Rick James

Answer 1

虽然MariaDB 10.3.3已经支持这些函数，以窗口函数的形式（请参见Lukasz Szozda的答案），但是在MySQL 8中也可以使用窗口函数来模拟它们：

SELECT DISTINCT first_value(matrix_value) OVER (
  ORDER BY CASE WHEN p <= 0.05 THEN p END DESC /* NULLS LAST */
) x,
FROM (
  SELECT
    matrix_value,
    percent_rank() OVER (ORDER BY matrix_value) p,
  FROM some_table
) t;

我在这里详细介绍了这个问题的解决方法。

- Lukas Eder

---

很棒的解决方案，Lukas。但是在您的博客中，您只模拟了PERCENTILE_DISC。关于PERCENTILE_CONT，您只是提到它类似但更棘手。您是否有时间为博客撰写模拟PERCENTILE_CONT的文章？我会非常感激。 - Cristian Scutaru

如果有帮助的话，我发现这个在我的MySQL 8上运行大约快了3倍：SELECT p FROM ( SELECT matrix_value, percent_rank() OVER (ORDER BY matrix_value) p, FROM some_table ) t WHERE p <= 0.5 ORDER BY p desc LIMIT 1; - RayCh

Answer 2

MariaDB 10.3.3 引入了窗口函数 PERCENTILE_CONT、PERCENTILE_DISC 和 MEDIAN，用于处理百分位数和中位数。

PERCENTILE_CONT

PERCENTILE_CONT() (standing for continuous percentile) is an ordered set aggregate function which can also be used as a window function. It returns a value which corresponds to the given fraction in the sort order. If required, it will interpolate between adjacent input items.

SELECT name, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY star_rating) 
        OVER (PARTITION BY name) AS pc 
FROM book_rating;

- Lukasz Szozda

Answer 3

在MariaDB和MySQL中都没有内置函数来解决这个问题，因此您需要在SQL级别上解决它（或通过添加用C编写的用户定义函数来解决...）

这可能有助于提供SQL解决方案：

http://rpbouman.blogspot.de/2008/07/calculating-nth-percentile-in-mysql.html

- Hartmut Holzgraefe

Answer 4

MariaDB 10.2具有窗口函数。

对于MySQL / 旧版MariaDB，假设您只想要单个值集的第N个百分位。

最好从应用程序代码中完成此操作，但也可以构建为存储例程。

1. 计算总行数：SELECT COUNT(*) FROM tbl。
2. 构造并执行一个带有LIMIT n,1的SELECT，其中n被计算为百分位乘以计数，然后填入查询中。

如果您需要在两个值之间插值，则会变得更加混乱。您也需要这样吗？

- Rick James

---

是的，但从10.3.3开始，它原生支持PERCENTILE_CONT。 - Lukasz Szozda

@lad2025 - 我勾勒了如何做到这一点。 - Rick James