Support 'mode' features
- Dominant language
- Jupyter Notebook
- Stars
- 201
- Forks
- 62
- PR merge metrics
- No merged PRs in 30d
Description
Collate appears to hardcode the pattern '{metric}('quantity')' for aggregate functions.
This is problematic for computing the mode. In Postgres 9.4 and above ( https://wiki.postgresql.org/wiki/Aggregate_Mode ) mode is included by the following:
`SELECT mode() WITHIN GROUP (ORDER BY some_value) AS modal_value FROM tbl;`
So there's no way with the current syntax to support this.
There are two potential solutions:
1. Include a mode Postgres function that has the same call signature as all the other aggregations. Of note, the 9.3 and below user-defined function suggestion at the above link does have the right call signature. So maybe we could just bundle that?
2. Allow collate Aggregates to receive a metric with the quantity already interpolated (via a {placeholder}), so users could supply this config to get to the new 9.4 syntax:
```
aggregates:
quantities:
- some_value
metrics:
- modal_value: 'mode() WITHIN GROUP (ORDER BY {quantity})
```
Contributor guide
Assessment
This issue has not been assessed yet.