Sum (case when ) can hit on precalculated measure since kylin 2.4 (https://issues.apache.org/jira/browse/KYLIN-3358)

For example

 

select OPS_REGION,sum(case when ops_region='Beijing' then price else 0 end) from kylin_sales

group by ops_region

 

can hit on sum(price) measure

 

but count(distinct ) is currently not supported.

 

Try

 

select 

sum(case when is_24h_feedback=1 then is_deal else 0 end)

from dm_trade.CARGO_MATCH_CARGO_TRANS_DI where "DAY"=20190802;

 

see if you can get results returned. Please make sure you have sum(is_deal) defined as measure and is_24h_feedback is defined as dimension first.

 

 

Joanna

 

 

From: "ning.wang@ymm56.com" <ning.wang@ymm56.com>
Reply-To: "user@kylin.apache.org" <user@kylin.apache.org>
Date: Tuesday, August 27, 2019 at 4:14 PM
To: user <user@kylin.apache.org>
Subject:
回复: case when问题

 

报错信息:

No realization found for OLAPContext, CUBE_UNMATCHED_AGGREGATION[FunctionDesc [expression=SUM, parameter=CASE(=($15, 1), $30, 0), returnType=null], FunctionDesc [expression=COUNT_DISTINCT, parameter=CASE(=($15, 1), $21, null), returnType=null]], rel#928645:OLAPTableScan.OLAP.[](table=[DM_TRADE, CARGO_MATCH_CARGO_TRANS_DI],ctx=,fields=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39]) while executing SQL: "select count(distinct unduplication_id) duplication_cargo_count, count(distinct case when is_24h_feedback=1 then unduplication_id else null end), sum(case when is_24h_feedback=1 then is_deal else 0 end) from dm_trade.CARGO_MATCH_CARGO_TRANS_DI where "DAY"=20190802 LIMIT 50000"

 


ning.wang@ymm56.com

 

发件人: ning.wang@ymm56.com

发送时间: 2019-08-27 15:29

收件人: user

 case when问题

我使用的kylin版本是2.5.0发现case when语法无法使用,是因我写的有问题还kylin本身不支持

select 

count(distinct unduplication_id) duplication_cargo_count,

count(distinct case when is_24h_feedback=1 then unduplication_id else null end),

sum(case when is_24h_feedback=1 then is_deal else 0 end)

from dm_trade.CARGO_MATCH_CARGO_TRANS_DI where "DAY"=20190802;

 

其中is_24h_feedback是一个度,

 

报错信息如下:

 


ning.wang@ymm56.com