You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by "ning.wang@ymm56.com" <ni...@ymm56.com> on 2019/08/27 07:29:22 UTC
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
Re: 回复: case when问题
Posted by "Joanna He (Jingke He)" <ji...@kyligence.io>.
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" <ni...@ymm56.com>
Reply-To: "user@kylin.apache.org" <us...@kylin.apache.org>
Date: Tuesday, August 27, 2019 at 4:14 PM
To: user <us...@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<ma...@ymm56.com>
发送时间： 2019-08-27 15:29
收件人： user<ma...@kylin.apache.org>
主题： 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是一个维度，
报错信息如下：
[cid:image001.jpg@01D55CF3.D3D975B0]
________________________________
ning.wang@ymm56.com
Re: 回复: case when问题
Posted by Wang rupeng <wa...@live.cn>.
Hi Wangning,
Kylin support sum(case ... when ...) and count(case ... when...), check KYLIN-2341<https://issues.apache.org/jira/browse/KYLIN-2341> , KYLIN-3358<https://jira.apache.org/jira/browse/KYLIN-3358> ,
but do not support count(distinct case .. when..) syntax yet.
-------------------
Best wishes,
Rupeng Wang
发件人: "ning.wang@ymm56.com" <ni...@ymm56.com>
答复: "user@kylin.apache.org" <us...@kylin.apache.org>
日期: 2019年8月27日 星期二 16:14
收件人: user <us...@kylin.apache.org>
主题: 回复: 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<ma...@ymm56.com>
发送时间： 2019-08-27 15:29
收件人： user<ma...@kylin.apache.org>
主题： 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是一个维度，
报错信息如下：
[cid:image001.jpg@01D55D07.FF88FE60]
________________________________
ning.wang@ymm56.com
回复: case when问题
Posted by "ning.wang@ymm56.com" <ni...@ymm56.com>.
报错信息：
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