[
https://issues.apache.org/jira/browse/CALCITE-6894?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17938610#comment-17938610
]
Zhen Chen commented on CALCITE-6894:
------------------------------------
Comparison Between Calcite's {{FILTER (WHERE)}} and ClickHouse's {{-If}} Syntax
# Calcite Syntax: {{sum("shelf_width") FILTER (WHERE "net_weight" > 0)}}, This
is standard *SQL:2003* syntax for *conditional aggregation*.
# ClickHouse Syntax: {{sumIf(}}shelf_width{{, }}net_weight{{> 0E0)}},
ClickHouse doesn't directly support {{FILTER (WHERE)}}, instead using the
*{{-If}} combinator* for conditional aggregation.
# Why This Conversion Works
* *Logical Equivalence*: Both expressions mean *"sum only rows meeting the
condition"*
* *Syntax Differences*:
||Calcite (SQL Standard)||ClickHouse||
|{{sum(x) FILTER (WHERE cond)}}|{{sumIf(x, cond)}}|
|Uses {{FILTER}} clause|Uses {{-If}} combinator|
|Supported by PostgreSQL, DuckDB, etc.|ClickHouse-specific syntax|
# Equivalent Conversions for Other Functions
* {{avg(x) FILTER (WHERE cond)}} → {{avgIf(x, cond)}}
* {{count(x) FILTER (WHERE cond)}} → {{countIf(x, cond)}}
* {{max(x) FILTER (WHERE cond)}} → {{maxIf(x, cond)}}
> ClickHouse dialect can support another form of writing aggFilterWhere
> ---------------------------------------------------------------------
>
> Key: CALCITE-6894
> URL: https://issues.apache.org/jira/browse/CALCITE-6894
> Project: Calcite
> Issue Type: Improvement
> Reporter: Zhen Chen
> Assignee: Zhen Chen
> Priority: Major
> Labels: pull-request-available
>
> Like this:
> Calcite:
> {code:java}
> sum("shelf_width") filter (where "net_weight" > 0) {code}
> ClickHouse:
> {code:java}
> sumIf(`shelf_width` , `net_weight` > 0E0) {code}
> ref:
> [https://clickhouse.com/docs/sql-reference/aggregate-functions/combinators#-if]
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
