You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by Hong Wei <hw...@qq.com> on 2016/07/26 11:52:09 UTC
Can Kylin support Sub-query or Case when with SQL
Dear Kylin: I want to know whether kylin is able to support sub query or case when with SQL. I have tried, but failed.
Best regards.
Re: Can Kylin support Sub-query or Case when with SQL
Posted by hongbin ma <ma...@apache.org>.
calculation on measures is something Kylin does not support well now.
I've seen many people asking about it. A simple scenario is to find the sum
of all transaction amounts where the transaction's amount is large than 5
dollars. Although we cannot allow flexible calculation on measures at
runtime, we may still predefine some "special measures" to satisfy
certain calculation on measures. For example, by predefining two special
measures " sum((case when t.EL_DATE <= '2015-01-01' then price else 0
end))" and "sum((case when price > 5 then price else 0 end))", we'll be
able to answer at least two types of "sum price with case when". However we
might be able to answer queries with "sum((case when price > *10* then
price else 0 end))" with the above two "special measures", that's the
limitation users have to accept.
Richard Calaba summrized nice JIRA
https://issues.apache.org/jira/browse/KYLIN-1886, we might think about
defining the scope soon. Please leave comments if you have same requirements
On Tue, Jul 26, 2016 at 11:17 PM, Yiming Liu <li...@gmail.com>
wrote:
> Hi Hong Wei,
>
> Currently, Kylin does not support case statement in sum function. A
> workaround for this case is to define another column who has the "case
> definition" in the view. And defines the view as the data source for Kylin
> cube.
>
> 2016-07-26 20:03 GMT+08:00 Hong Wei <hw...@qq.com>:
>
> > for example, I want to query this SQL which is wrong in kylin:
> > select test.dim1 as c0, sum((case when t.EL_DATE <= '2015-01-01' and
> > t.EL_TYPE = 0 then 1 else 0 end)) as m0 from test as t where t.dim1 =
> > 'engineer' group by t.dim1
> >
> >
> >
> >
> > ------------------ Original ------------------
> > From: "Hong Wei";<hw...@qq.com>;
> > Date: Tue, Jul 26, 2016 07:52 PM
> > To: "dev"<de...@kylin.apache.org>;
> >
> > Subject: Can Kylin support Sub-query or Case when with SQL
> >
> >
> >
> > Dear Kylin: I want to know whether kylin is able to support sub
> query
> > or case when with SQL. I have tried, but failed.
> > Best regards.
> >
>
>
>
> --
> With Warm regards
>
> Yiming Liu (刘一鸣)
>
--
Regards,
*Bin Mahone | 马洪宾*
Re: Can Kylin support Sub-query or Case when with SQL
Posted by Yiming Liu <li...@gmail.com>.
Hi Hong Wei,
Currently, Kylin does not support case statement in sum function. A
workaround for this case is to define another column who has the "case
definition" in the view. And defines the view as the data source for Kylin
cube.
2016-07-26 20:03 GMT+08:00 Hong Wei <hw...@qq.com>:
> for example, I want to query this SQL which is wrong in kylin:
> select test.dim1 as c0, sum((case when t.EL_DATE <= '2015-01-01' and
> t.EL_TYPE = 0 then 1 else 0 end)) as m0 from test as t where t.dim1 =
> 'engineer' group by t.dim1
>
>
>
>
> ------------------ Original ------------------
> From: "Hong Wei";<hw...@qq.com>;
> Date: Tue, Jul 26, 2016 07:52 PM
> To: "dev"<de...@kylin.apache.org>;
>
> Subject: Can Kylin support Sub-query or Case when with SQL
>
>
>
> Dear Kylin: I want to know whether kylin is able to support sub query
> or case when with SQL. I have tried, but failed.
> Best regards.
>
--
With Warm regards
Yiming Liu (刘一鸣)
Re: Can Kylin support Sub-query or Case when with SQL
Posted by Hong Wei <hw...@qq.com>.
for example, I want to query this SQL which is wrong in kylin:
select test.dim1 as c0, sum((case when t.EL_DATE <= '2015-01-01' and t.EL_TYPE = 0 then 1 else 0 end)) as m0 from test as t where t.dim1 = 'engineer' group by t.dim1
------------------ Original ------------------
From: "Hong Wei";<hw...@qq.com>;
Date: Tue, Jul 26, 2016 07:52 PM
To: "dev"<de...@kylin.apache.org>;
Subject: Can Kylin support Sub-query or Case when with SQL
Dear Kylin: I want to know whether kylin is able to support sub query or case when with SQL. I have tried, but failed.
Best regards.