Description
Is your feature request related to a problem? Please describe.
For some databases the approach to pass measure.filters inside aggregation function, like count(CASE ... WHEN ... END), might be inefficient, the example of such database is ClickHouse. In ClickHouse case the database scheduler can't optimize such query format and just goes across all table rows that leading to query performance degradation, since the table scans all rows and don't use the underlying table index.
Also, there is a case for PostgresSQL when putting filters into where clause rapidly increases query performance.
For example, we have an order entity in our CRM Postgres database, the order might be in several statuses: CREATED, CANCELED, ASSEMBLY, PAID, ISSUED, RETURNED. And some of our bussiness metrics calculations are based on these statuses, so if we want to calculate measure countPaidOrder, that currently define in Order cube like:
type: `count`,
filters: [
{
sql: `${CUBE}.status not in (\'NOT_CREATED\', \'CREATED\', \'RETURNED\', \'CANCELED\')`
}
],
drillMembers: [productId, sellerId, shop_id]
Now cube-js generated SQL where filters are wrapped by aggregate function, but if this condition would be passed into where clause query performance will be better.
Describe the solution you'd like
We suggest adding an additional measure member that will have the same signature as already exists filters member but pass all entries into where clause on SQL generation step.
type: `count`,
filters: [
{
sql: `some_condition`
}
],
whereFilters: [
{
sql: `${CUBE}.status not in (\'NOT_CREATED\', \'CREATED\', \'RETURNED\', \'CANCELED\')`
}
],
drillMembers: [productId, sellerId, shop_id]