You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2015/07/21 16:52:04 UTC
[jira] [Commented] (KYLIN-899) add "if" support in sum
[ https://issues.apache.org/jira/browse/KYLIN-899?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14635207#comment-14635207 ]
Julian Hyde commented on KYLIN-899:
-----------------------------------
Calcite already has this feature:
{code}select sum(col) filter (where col>1024) as newcol from testtabale;{code}
See https://issues.apache.org/jira/browse/CALCITE-704.
> add "if" support in sum
> -----------------------
>
> Key: KYLIN-899
> URL: https://issues.apache.org/jira/browse/KYLIN-899
> Project: Kylin
> Issue Type: Improvement
> Reporter: liangmeng
>
> add "if" support in sum,such as :
> select sum(if(col>1024,col,0)) as newcol from testtabale;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
Re: [jira] [Commented] (KYLIN-899) add "if" support in sum
Posted by "Shi, Shaofeng" <sh...@ebay.com>.
Hi meng,
Kylin doesn’t support such expression in the measures (SUM, MAX, etc); If
I understand correctly, you can easily achieve that by using view: define
a hive view over your fact table, in the view use “case” to create two
columns, like cola_>1000 and cola_<1000; Then that use the view as the
fact table of the cube, defining two SUM measures against each of the two
hive columns; Using view is a practice in Kylin, and it will give user the
flexibility in maintain the logic easily;
On 7/22/15, 8:27 AM, "liangmeng" <13...@139.com> wrote:
>其实我的意思并不是在query界面可以支持if in sum，而是在cube的配置过程中，定义度量汇聚的时候，可
以更加的灵活，例如我要根据某一列的大
>小定义两个不同的统计指标，如果用sql来写，大概就是
>select sum(if(cola>1000,cola,0)) as cola_>1000, sum(if(cola<1000,cola,0))
>as cola_<1000 from ********;
>目前kylin的cube定义只能做sum，但不能定义sum里面的条件，而filter又是针对整个sql语句的，无法做
到更灵活的配置；
>
>
>From: Julian Hyde (JIRA)
>Date: 2015-07-21 22:52
>To: dev
>Subject: [jira] [Commented] (KYLIN-899) add "if" support in sum
>
> [
>https://issues.apache.org/jira/browse/KYLIN-899?page=com.atlassian.jira.pl
>ugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14635207#comm
>ent-14635207 ]
>
>Julian Hyde commented on KYLIN-899:
>-----------------------------------
>
>Calcite already has this feature:
>
>{code}select sum(col) filter (where col>1024) as newcol from
>testtabale;{code}
>
>See https://issues.apache.org/jira/browse/CALCITE-704.
>
>> add "if" support in sum
>> -----------------------
>>
>> Key: KYLIN-899
>> URL: https://issues.apache.org/jira/browse/KYLIN-899
>> Project: Kylin
>> Issue Type: Improvement
>> Reporter: liangmeng
>>
>> add "if" support in sum,such as :
>> select sum(if(col>1024,col,0)) as newcol from testtabale;
>
>
>
>--
>This message was sent by Atlassian JIRA
>(v6.3.4#6332)
>
Re: [jira] [Commented] (KYLIN-899) add "if" support in sum
Posted by hongbin ma <ma...@apache.org>.
hi,liangmeng
Please try to keep the conversation in English, as many of the community
are not Chinese.
I understand your problem, but the essence of Kylin is about
"Precalculation", filter on metric at runtime is too expensive for
precalculation. For now the only way to perform such queries is by using
MPP fashions, but this is actually not we're good at.
If you could narrow down the scope (for example, fixing the metric filter
condition), you might be able to find some workarounds.
thanks
hongbin
On Wed, Jul 22, 2015 at 8:27 AM, liangmeng <13...@139.com> wrote:
> 其实我的意思并不是在query界面可以支持if in
> sum，而是在cube的配置过程中，定义度量汇聚的时候，可以更加的灵活，例如我要根据某一列的大小定义两个不同的统计指标，如果用sql来写，大概就是
> select sum(if(cola>1000,cola,0)) as cola_>1000, sum(if(cola<1000,cola,0))
> as cola_<1000 from ********;
> 目前kylin的cube定义只能做sum，但不能定义sum里面的条件，而filter又是针对整个sql语句的，无法做到更灵活的配置；
>
>
> From: Julian Hyde (JIRA)
> Date: 2015-07-21 22:52
> To: dev
> Subject: [jira] [Commented] (KYLIN-899) add "if" support in sum
>
> [
> https://issues.apache.org/jira/browse/KYLIN-899?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14635207#comment-14635207
> ]
>
> Julian Hyde commented on KYLIN-899:
> -----------------------------------
>
> Calcite already has this feature:
>
> {code}select sum(col) filter (where col>1024) as newcol from
> testtabale;{code}
>
> See https://issues.apache.org/jira/browse/CALCITE-704.
>
> > add "if" support in sum
> > -----------------------
> >
> > Key: KYLIN-899
> > URL: https://issues.apache.org/jira/browse/KYLIN-899
> > Project: Kylin
> > Issue Type: Improvement
> > Reporter: liangmeng
> >
> > add "if" support in sum,such as :
> > select sum(if(col>1024,col,0)) as newcol from testtabale;
>
>
>
> --
> This message was sent by Atlassian JIRA
> (v6.3.4#6332)
>
>
--
Regards,
*Bin Mahone | 马洪宾*
Apache Kylin: http://kylin.io
Github: https://github.com/binmahone
Re: [jira] [Commented] (KYLIN-899) add "if" support in sum
Posted by liangmeng <13...@139.com>.
其实我的意思并不是在query界面可以支持if in sum，而是在cube的配置过程中，定义度量汇聚的时候，可以更加的灵活，例如我要根据某一列的大小定义两个不同的统计指标，如果用sql来写，大概就是
select sum(if(cola>1000,cola,0)) as cola_>1000, sum(if(cola<1000,cola,0)) as cola_<1000 from ********;
目前kylin的cube定义只能做sum，但不能定义sum里面的条件，而filter又是针对整个sql语句的，无法做到更灵活的配置；
From: Julian Hyde (JIRA)
Date: 2015-07-21 22:52
To: dev
Subject: [jira] [Commented] (KYLIN-899) add "if" support in sum
[ https://issues.apache.org/jira/browse/KYLIN-899?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14635207#comment-14635207 ]
Julian Hyde commented on KYLIN-899:
-----------------------------------
Calcite already has this feature:
{code}select sum(col) filter (where col>1024) as newcol from testtabale;{code}
See https://issues.apache.org/jira/browse/CALCITE-704.
> add "if" support in sum
> -----------------------
>
> Key: KYLIN-899
> URL: https://issues.apache.org/jira/browse/KYLIN-899
> Project: Kylin
> Issue Type: Improvement
> Reporter: liangmeng
>
> add "if" support in sum,such as :
> select sum(if(col>1024,col,0)) as newcol from testtabale;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)