cube-js/cube

Using Pre-Aggregation with calculated measures of types: string/time

Open

#7.896 aberto em 11 de mar. de 2024

Ver no GitHub
 (4 comments) (0 reactions) (0 assignees)Rust (1.965 forks)batch import
help wantedquestion

Métricas do repositório

Stars
 (19.563 stars)
Métricas de merge de PR
 (Mesclagem média 5d 16h) (138 fundiu PRs em 30d)

Description

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

Guia do colaborador