You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by you Zhuang <zh...@gmail.com> on 2021/02/20 02:11:14 UTC

count(if(a=1, 1, null)) syntax support

when does calcite support count(if(a=1, 1, null)) syntax ? spark, hive,
clickhouse, impala, presto support the syntax for a long time.

Re: count(if(a=1, 1, null)) syntax support

Posted by 段雄 <no...@gmail.com>.

Hi,you Zhuang I find clacite have SqlIfFunction,but it don't  accomplish.
you Zhuang <zh...@gmail.com> 于2021年2月21日周日 上午2:39写道：

> when does calcite support count(if(a=1, 1, null)) syntax ? spark, hive,
> clickhouse, impala, presto support the syntax for a long time.
>

Re: count(if(a=1, 1, null)) syntax support

Posted by Julian Hyde <jh...@apache.org>.

IF was added in https://issues.apache.org/jira/browse/CALCITE-3731 and
will be part of 1.27 release. IF is a dialect-specific function,
enabled in bigquery, hive and spark function tables.

There are several other ways you can achieve the same effect:
 * COUNT(*) FILTER (WHERE a = 1)
 * COUNT(CASE a WHEN 1 THEN 1 END)
 * COUNT(CASE WHEN a = 1 THEN 1 END)
 * COUNTIF(a = 1) // from 1.27, and only in the BigQuery dialect

Julian

On Sat, Feb 20, 2021 at 10:39 AM you Zhuang <zh...@gmail.com> wrote:
>
> when does calcite support count(if(a=1, 1, null)) syntax ? spark, hive,
> clickhouse, impala, presto support the syntax for a long time.