matrixorigin / matrixorigin/matrixone
[Feature Request]: PERCENTILE_CONT / PERCENTILE_DISC ordered-set aggregate functions
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### Is there an existing issue for the same feature request?
- [x] I have checked the existing issues.
### Is your feature request related to a problem?
When building NL-to-SQL analytics on MatrixOne for a real-world POC (toll-plaza monitoring, 1000-question stress test), arbitrary-percentile queries are very common (\"95th-percentile gate processing time yesterday\", \"P99 wait minutes per plaza\", etc.).
MatrixOne has a native `MEDIAN()` which is great for **P50 only**. For any other percentile the user / LLM has to hand-roll a `ROW_NUMBER() OVER (ORDER BY x) / COUNT(*) OVER ()` workaround, which is:
- error-prone (LLMs frequently write subtly wrong versions — observed in 100% of P95 attempts during our stress test),
- slow on large fact tables,
- and unfamiliar to users coming from PostgreSQL / Snowflake / Oracle / Spark / DuckDB, all of which support `PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col)` as a standard ordered-set aggregate.
### Describe the feature you'd like
Implement the two SQL-standard ordered-set aggregates:
- ``PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY expr)`` — continuous percentile (linear interpolation between adjacent values).
- ``PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY expr)`` — discrete percentile (the smallest value with cumulative distribution ≥ p).
Both should be usable as scalar aggregates and (ideally later) as window functions via ``WITHIN GROUP`` over partitions.
\`\`\`sql
-- desired
SELECT plaza_id,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY gate_processing_sec) AS p95_sec
FROM toll_transactions
WHERE transaction_datetime >= NOW() - INTERVAL 1 HOUR
GROUP BY plaza_id;
\`\`\`
### Describe implementation you've considered
PostgreSQL's implementation is a good reference — they treat these as ordered-set aggregates with the special ``WITHIN GROUP (ORDER BY …)`` clause. Internally a sorted run of the input column is materialized per group; for CONT the position is interpolated, for DISC the value at the ceiling position is returned.
A pragmatic shortcut for the MVP could be to support **only the non-window scalar form** first, since that's the form 99% of NL2SQL output uses. Window-partitioned variants can come later.
### Documentation, Adoption, Use Case, Migration Strategy
Any analytics tool that emits MySQL/PostgreSQL-flavored SQL (Looker, Metabase, Superset, Tableau live-query, LangChain SQL agents, custom NL2SQL pipelines) generates these aggregates routinely. Without native support, integrators have to special-case MatrixOne in their dialect rules — which is what we ended up doing in our LLM system prompt for this POC.
### Additional information
Reproduction on \`8.0.30-OmniFabric-v4.0.0-rc2\` (commit \`e0a8185\`):
\`\`\`
mysql> SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY gate_processing_sec) FROM toll_transactions LIMIT 1;
ERROR 1064 (HY000): SQL parser error: syntax error at line 1 column 43 near " (ORDER BY gate_processing_sec) ..."
mysql> SELECT PERCENTILE_CONT(0.95) FROM toll_transactions LIMIT 1;
ERROR 20105 (HY000): not supported: function or operator 'percentile_cont'
mysql> SELECT PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY gate_processing_sec) FROM toll_transactions LIMIT 1;
ERROR 1064 (HY000): SQL parser error: ...
\`\`\`
Observed in a 1000-question NL2SQL stress test as the **only LLM failure that was directly caused by a missing MatrixOne function** — every other failure was generic SQL hygiene or a model-side issue.
Contributor guide
Assessment
This issue has not been assessed yet.