cube
cube copied to clipboard
Using Pre-Aggregation with calculated measures of types: string/time
Problem
I have some calculated measures that either aggregate string values
to an array using array_agg
or finding a max timestamp
using `MAX(time_column).
When trying to use pre-aggregations on those measures, I noticed that the pre-agg table is created correctly, however, the select clause is using the SUM
function on those fields (string/timestamp).
Below are the Cube Schema, and both queries Im getting from cubejs,cubestore.
Was wandering if thats even possible, Thank you!
Related Cube.js schema
cube(`Cube`, {
sql: `SELECT * FROM dummy`,
preAggregations: {
main: {
dimensions: [CUBE.name],
measures: [CUBE.idsWithThatName]
}
},
measures: {
idsWithThatName: {
type: `string`,
sql: `array_agg(${CUBE.id})`
}
},
dimensions: {
name: {
sql: `name`,
type: `string`
},
id: {
sql: `id`,
type: `string`
}
}
})
Related Cube.js generated SQL
Regular query, without pre-aggregation: SELECT name, array_agg(id) FROM dummy group by 1;
Pre-Aggregation Query:
SELECT `cube__name` `cube__name`, sum(`cube__ids_with_that_name`) `cube__ids_with_that_name` FROM local_cubejs_pre_agg.pre_agg_table_with_uniq_id AS `pre_agg_table` GROUP BY 1 ORDER BY 2 DESC LIMIT 20000
@omrish10 I can confirm that it's currently not supported, but we're open to contributions here. Replacing aggregation to MAX
should do the job.
If you are interested in working on this issue, please provide go ahead and provide PR for that. We'd be happy to review it and merge it. If this is the first time you are contributing a Pull Request to Cube, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube Slack.