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 <[email protected]> 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 <[email protected]>: > > > 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";<[email protected]>; > > Date: Tue, Jul 26, 2016 07:52 PM > > To: "dev"<[email protected]>; > > > > 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 | 马洪宾*
