Include APPROX_QUANTILES function for BigQuery median calculation.
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Is your feature request related to a problem? Please describe.**
I wanted to exclude outliers from the mean calculation and pre-aggregate the data. I looked into various methods to identify outliers, but the median seemed to be the easiest solution.
**Describe the solution you'd like**
I want to use a median calculation that could be saved in pre-aggregation. For that could be used approx BigQuery function `APPROX_QUANTILES`.
**Describe alternatives you've considered**
I tried:
- other BigQuery functions(PERCENTILE_CONT, PERCENTILE_DISC).
- tried to calculate the median with additive measures.
- considered other outliers identifying methods such as standard deviation(STDDEV).
**Additional context**
Example expected usage in cube:
```
cube(`Order`, {
sql: `
SELECT id,
customer_id
shipped_at,
delivered_at,
country
FROM order`,
measures: {
shippingAtToDeliveredAt: {
type: `number`,
sql: `TIMESTAMP_DIFF(${CUBE.deliveredAt}, ${CUBE.shippingDate}, MINUTE)
},
median: {
type: `number`,
sql: `approx_quantiles(${CUBE.shippingAtToDeliveredAt}, 100)[offset(50)]`,
}
},
dimensions: {
id: {
sql: `${CUBE}.id`,
type: `string`,
primaryKey: true
},
customerId: {
sql: `${CUBE}.customer_id`,
type: `string`
},
shippingAt: {
sql: `TIMESTAMP(${CUBE}.shipping_at)`,
type: `time`
},
deliveredAt: {
sql: `TIMESTAMP(${CUBE}.delivered_at)`,
type: `time`
},
},
dataSource: `default`
});
```
Contributor guide
Assessment
This issue has not been assessed yet.