matrixorigin / matrixorigin/matrixone

[Feature Request]: PERCENTILE_CONT / PERCENTILE_DISC ordered-set aggregate functions

Open
#25,144 1 comment 0 reactions 1 assignee Claimed by @slyunnn View on GitHub
ai-heavy
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.