Incorrect results when querying a higher order granularity from pre-aggregations for a formula based additive measure
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Describe the bug**
We’ve a pre-aggregation defined for a formula based additive measure that has granularity set to `hour`. When we query for a higher order granularity e.g., `day` cubejs seems to return incorrect additive measure results.
**To Reproduce**
1. Use the cube schema and pre-aggregations definition from **Minimally reproducible Cube Schema** section.
2. Consider using this mock data(csv) for reference:
```csv
id,physical_id,timestamp,data
13,34e25620,2022-03-21 18:26:41.394+00,"{""co2"": 1000, ""temperature"": 21.08}"
14,34e25620,2022-03-21 18:22:41.394+00,"{""co2"": 853, ""temperature"": 21.08}"
15,34e25620,2022-03-21 16:22:41.394+00,"{""co2"": 700, ""temperature"": 21.08}"
```
3. Now we have pre-aggregations built with hourly granular data points but if we query say for `avgCO2` with a higher granularity `day`, it'd send incorrect averages. We get the sum of all the hourly `avgCO2` that it has pre-aggregated for a day, it doesn’t seem to divide it by the count.
Here is an example query:
```{
"measures": ["Events.avgCO2", "Events.sumCO2", "Events.count"],
"timeDimensions": [
{
"dimension": "Events.timestamp",
"granularity": "day",
"dateRange": ["2022-03-01", "2022-03-31"]
}
],
"order": { "Events.avgCO2": "desc" },
"dimensions": ["Events.physicalId"],
"filters": [
{
"member": "Events.physicalId",
"operator": "contains",
"values": ["34e25620"]
}
]
}
```
4. Here is the parquet file that’s generated by the pre-aggregations layer:

**For the above query(in step 3) we get `avgCO2` for `2022-03-21` as 1626.5(700+926.5) which is not correct.**
**Expected behavior**
`avgCO2` should either be (1853+700)/3 or 1626.5/2
**Minimally reproducible Cube Schema**
```javascript
cube(`Events`, {
sql: `SELECT * FROM public.events`,
measures: {
count: {
type: `count`,
drillMembers: [id],
},
sumCO2: {
type: `sum`,
sql: `${dataCO2}`,
},
avgCO2: {
type: `number`,
sql: `${sumCO2}/${count}`,
},
}.
dimensions: {
id: {
sql: `id`,
type: `number`,
primaryKey: true,
},
physicalId: {
sql: `physical_id`,
type: `string`,
},
dataCO2: {
sql: `(data->>'co2')::real`,
type: `number`,
},
data: {
sql: `data`,
type: `string`,
},
timestamp: {
sql: `timestamp`,
type: `time`,
},
}.
preAggregations: {
dailyRollupFix: {
type:"rollup",
measures: [
Events.count,
Events.avgCO2,
Events.sumCO2,
],
dimensions: [Events.physicalId],
granularity: `hour`,
timeDimension: Events.timestamp,
external: true,
partitionGranularity: `day`,
buildRangeStart: {
sql: `SELECT NOW() - interval '60 day'`,
},
refreshKey: {
every: `1 day`,
incremental: true,
updateWindow: '1 day',
},
scheduledRefresh: true,
},
}
})
```
**Version:**
- "@cubejs-backend/cubestore-driver": "0.29.52"
- "@cubejs-backend/postgres-driver": "0.29.52"
- "@cubejs-backend/server": "0.29.52"
- cubestore:latest (docker image)
**Environment:**
- MacOS
- Cubejs running as a node process in Production mode
- Cubestore running on docker and storing pre-aggregations in an S3 bucket
- Redis 6.2.6 running in standalone mode
Contributor guide
Assessment
This issue has not been assessed yet.