Bug #93234 Support percentile_cont (SQL Standard) for median.

• Submitted: 16 Nov 2018 21:28
• Modified: 17 Nov 2018 6:46
• Reporter: Meiji Kimura
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[16 Nov 2018 21:28] Meiji Kimura

Description:
percentile_cont is a function defined in SQL Standard(SQL:2008).

# Please see Oracle 12c's manual in details.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/PERCENTILE_CONT.html...

As the document said, The MEDIAN function is a specific case of PERCENTILE_CONT where the percentile value defaults to 0.5.

FR for median, was requested in this bug. But if percentile_cont is supported, we can use percentile_cont for median.

Bug#14545

Please consider to support percentile_cont, and percentile_disc.

percentile_conf is supprted by major RDBMS as belows.

Oracle 9i
IBM Db2 11.1
MS SQL Server 2012
PostgreSQL 9.4
MariaDB 10.3.3

Firebird and MySQL are not supported that function.

How to repeat:
N/A

Suggested fix:
Support percentile_cont, percentile_disc in SQL:2008.

[16 Nov 2018 21:45] MySQL Verification Team

SQLite also doesn't support percentile_cont, but support median. See this link in details.

https://modern-sql.com/blog/2018-08/whats-new-in-mariadb-10.3#3.percentiles

[17 Nov 2018 6:46] MySQL Verification Team

Hello Meiji-San,

Thank you for the feature request!

regards,
Umesh

[22 Jun 2023 12:24] Vladimir Lasky

It's now mid 2023. Please implement MEDIAN() and PERCENTILE_CONT() in MySQL.

The median is an extremely useful metric, especially when analyzing response times, latencies or error rates, as the median is resistant to being skewed by outliers, unlike the average.

All the existing median calculation queries I found online are crude, relying on user-defined variables, string concatenation via GROUP_CONCAT() and/or multiple subqueries with self-joins.

I came up with a cleaner median calculation query that leverages the MySQL 8 window function ROW_NUMBER(), but it's still rather long-winded:

https://gist.github.com/vlasky/d98a7d162582c32c541157a5abbf59b8

I really want to be able to write the concise queries that I can write in MariaDB() due to its support for MEDIAN() and PERCENTILE_CONT().