cube-js / cube-js/cube

(feat) `sumTopX` / `countTopX` measures

Open
#4,480 1 comment 0 reactions 0 assignees View on GitHub
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.**

When there is a need to visualize something like the “top 10 accounts sales $ + all other accounts aggregated”, something like this could be handy.

For example, let’s say I have 10,000 customers, and I want to see sales revenue per month across all 10,000 customers for 5 years. This would lead to (10k * 5 * 12) records in the result set, which exceeds the 50k limit for Cube query results, and would also create a table or chart visualization which would be excessively difficult for a human to read. So instead, I ask this time for the “top 10 customers break-down + all the other customers grouped as ‘Other’, also across 5 years per month”. Then we have ((10 + 1) * 5 * 12), which is only 660 records returned, and very visually understandable, and also highlights my 10 most significant customers.

To implement this currently, you need to do some logic and multiple queries in Cube.

**Describe the solution you'd like**

New measures like `sumTopX` or `countTopX`, which would look like:

```
sumTop10: {
sql: `amountInvoiced`
type: `sumTopX`,
top: 10,
returnOther: true
},

sumTopVariable: {
sql: `amountInvoiced`,
type: `sumTopX`,
top: ${UserValue},
returnOther: false
}
```

In the examples above, `sumTop10` would return the top 10 sums per the specified dimensions in the query using this measure, such as customerId. Then it would also return the total sum across all the remaining customerIds, as an "Other" sum.

`sumTopVariable` is similar but it would accept a user input for N in "top N", and not return the "Other" aggregation.

**Describe alternatives you've considered**
The alternative way to do this with Cube requires ~3 queries, at least, to get this same result set.

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.