Cumulative count over time in Clickhouse

[Grisgruis]

Describe the bug
I want to show a cumulative count over time in with Clickhouse

To Reproduce
Steps to reproduce the behavior:

1. Create Clickhouse data

create database cube;
CREATE TABLE IF NOT EXISTS `cube`.`test` (
  `created_at` Date,
  `count` Int32
)
ENGINE = MergeTree() ORDER BY (created_at) PRIMARY KEY (created_at);
insert into cube.test (*) VALUES (NOW(), 1), (DATESUB(DAY, 1, NOW()), 1)

2. Create Cube

cube(`Test`, {
    sql: `SELECT * FROM cube.test`,

    measures: {
        count: {
            sql: `count`,
            type: `count`,
        },
        cumulative_count: {
            type: `count`,
            rollingWindow: {
                trailing: `unbounded`,
            },
        },
    },

    dimensions: {
        ts: {
            sql: `${CUBE}."created_at"`,
            type: `time`,
            title: `Timestamp`
        },
    }
});

3. Go to dashboard, verify that cumulative count works. (without grouping)
   
4. Select group by day:
   
5. Clickhouse errors with the JOIN ON condition
   Code: 403. DB::Exception: Unsupported JOIN ON conditions. Unexpected 'test__ts_day <= date_to': While processing test__ts_day <= date_to. (INVALID_JOIN_ON_EXPRESSION) (version 23.2.3.17 (official build))

Expected behavior
Be able to show cumulative count over time

Version:
0.32.11

Additional context
Iam aware that the issue lies with Clickhouse. What I am not sure of is;

• This is not mentioned anywere in the docs as limitations.
• Is there a possibility to 'overwrite' the join clauses?
• Any other workarounds/things I should be aware of? Is there any code I could check to fork/alter?

[github-actions]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you.
If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines.
You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[paveltiunov]

@Grisgruis It's a known limitation for Clickhouse. The workaround is to use pre-aggregations for now.

[Grisgruis]

@Grisgruis It's a known limitation for Clickhouse. The workaround is to use pre-aggregations for now.

How does pre-aggregations helps me to achieve cumulative counts over time? (cannot seem to find any)

[Grisgruis]

Due to the lack of options, a possible (really ugly) fix what at least works -_-

Wrap clickhouse in a trino instance, and add trino as a data source, this makes the cumulative 'work'

Closing this issue

[JoeyTribbiani1995]

Due to the lack of options, a possible (really ugly) fix what at least works -_-

Wrap clickhouse in a trino instance, and add trino as a data source, this makes the cumulative 'work'

Closing this issue

How to wrap clickhouse in a trino instance ? Trino instance need catalog ?

[Grisgruis]

Due to the lack of options, a possible (really ugly) fix what at least works -_-
Wrap clickhouse in a trino instance, and add trino as a data source, this makes the cumulative 'work'
Closing this issue

How to wrap clickhouse in a trino instance ? Trino instance need catalog ?

Yes see https://trino.io/docs/current/connector/clickhouse.html