You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by 赵天烁 <zh...@meizu.com> on 2016/08/02 08:20:50 UTC
回复: support filter on a dimension which is not being grouped
right now I could achieve the same result by two step query:
first ,execute a two dimensions group by query
select distinct fid,count(1) from crash_analysis_record where package_ver='5.1' group by FID,package_ver
then execute the second query to filter the result base on fid return above:
select fid,crash_point from crash_analysis_record where fid in (1,2,3) group by fid order by count(1) desc
but it's gona be very convenient if I could just do a filter without group by it.is it possible to support this?
btw why order by field must appear in the select field list?in the first query actually I just want the distinct fid
________________________________
赵天烁
Kevin Zhao
zhaotianshuo@meizu.com<ma...@meizu.com>
珠海市魅族科技有限公司
MEIZU Technology Co., Ltd.
广东省珠海市科技创新海岸魅族科技楼
MEIZU Tech Bldg., Technology & Innovation Coast
Zhuhai, 519085, Guangdong, China
meizu.com
发件人: 赵天烁<ma...@meizu.com>
发送时间: 2016-08-02 15:49
收件人: user<ma...@kylin.apache.org>
主题: support filter on a dimension which is not being grouped
currently kylin don't support filter on a dimension which is not being grouped,for example I have a sql like this:
select fid,crash_point from crash_analysis_record where package_ver='5.1' group by fid order by count(1) desc
fid and package_ver are all predefine dimensions,it can be done if we find those cuboid data with fid and package_ver, eliminate the data which are not package_ver=5.0,then runtime aggragate to fid
I just propose some thought,this approach may have profermance issue,maybe you guys already have a better way todo this?
________________________________
赵天烁
Kevin Zhao
zhaotianshuo@meizu.com<ma...@meizu.com>
珠海市魅族科技有限公司
MEIZU Technology Co., Ltd.
广东省珠海市科技创新海岸魅族科技楼
MEIZU Tech Bldg., Technology & Innovation Coast
Zhuhai, 519085, Guangdong, China
meizu.com
Re: Re: support filter on a dimension which is not being grouped
Posted by 赵天烁 <zh...@meizu.com>.
sorry, the sql I provide is made up by myself to simulate the actual one which is to complicated , it's my fault to add crash_point column to the select list which isn't contain in the actual sql,but it still give me an sql compile error when I execute as below:
select fid from crash_analysis_record where package_ver='5.1' group by fid
already update to 1.5.3 by folow the upgrade guide,is there anything else I may miss?
________________________________
赵天烁
Kevin Zhao
zhaotianshuo@meizu.com<ma...@meizu.com>
珠海市魅族科技有限公司
MEIZU Technology Co., Ltd.
广东省珠海市科技创新海岸魅族科技楼
MEIZU Tech Bldg., Technology & Innovation Coast
Zhuhai, 519085, Guangdong, China
meizu.com
From: ShaoFeng Shi<ma...@apache.org>
Date: 2016-08-03 09:03
To: user<ma...@kylin.apache.org>
Subject: Re: support filter on a dimension which is not being grouped
Hi Tianshuo,
Apache Kylin already support the case you mentioned;
Please note the SQL you provided (in below) is invalid, because the "crash_point" should be in "group by" if it is a dimension, or it should be a measure like SUM(crash_point).
select fid,crash_point from crash_analysis_record where package_ver='5.1' group by fid
2016-08-02 16:20 GMT+08:00 赵天烁 <zh...@meizu.com>>:
right now I could achieve the same result by two step query:
first ,execute a two dimensions group by query
select distinct fid,count(1) from crash_analysis_record where package_ver='5.1' group by FID,package_ver
then execute the second query to filter the result base on fid return above:
select fid,crash_point from crash_analysis_record where fid in (1,2,3) group by fid order by count(1) desc
but it's gona be very convenient if I could just do a filter without group by it.is<http://it.is> it possible to support this?
btw why order by field must appear in the select field list?in the first query actually I just want the distinct fid
________________________________
赵天烁
Kevin Zhao
zhaotianshuo@meizu.com<ma...@meizu.com>
珠海市魅族科技有限公司
MEIZU Technology Co., Ltd.
广东省珠海市科技创新海岸魅族科技楼
MEIZU Tech Bldg., Technology & Innovation Coast
Zhuhai, 519085, Guangdong, China
meizu.com<http://meizu.com>
发件人: 赵天烁<ma...@meizu.com>
发送时间: 2016-08-02 15:49
收件人: user<ma...@kylin.apache.org>
主题: support filter on a dimension which is not being grouped
currently kylin don't support filter on a dimension which is not being grouped,for example I have a sql like this:
select fid,crash_point from crash_analysis_record where package_ver='5.1' group by fid order by count(1) desc
fid and package_ver are all predefine dimensions,it can be done if we find those cuboid data with fid and package_ver, eliminate the data which are not package_ver=5.0,then runtime aggragate to fid
I just propose some thought,this approach may have profermance issue,maybe you guys already have a better way todo this?
________________________________
赵天烁
Kevin Zhao
zhaotianshuo@meizu.com<ma...@meizu.com>
珠海市魅族科技有限公司
MEIZU Technology Co., Ltd.
广东省珠海市科技创新海岸魅族科技楼
MEIZU Tech Bldg., Technology & Innovation Coast
Zhuhai, 519085, Guangdong, China
meizu.com<http://meizu.com>
--
Best regards,
Shaofeng Shi
Re: support filter on a dimension which is not being grouped
Posted by ShaoFeng Shi <sh...@apache.org>.
Hi Tianshuo,
Apache Kylin already support the case you mentioned;
Please note the SQL you provided (in below) is invalid, because the
"crash_point" should be in "group by" if it is a dimension, or it should be
a measure like SUM(crash_point).
select fid,crash_point from crash_analysis_record where package_ver='5.1'
group by fid
2016-08-02 16:20 GMT+08:00 赵天烁 <zh...@meizu.com>:
> right now I could achieve the same result by two step query:
> first ,execute a two dimensions group by query
> select distinct fid,count(1) from crash_analysis_record where
> package_ver='5.1' group by FID,package_ver
> then execute the second query to filter the result base on fid return
> above:
> select fid,crash_point from crash_analysis_record where fid in (1,2,3)
> group by fid order by count(1) desc
>
> but it's gona be very convenient if I could just do a filter without group
> by it.is it possible to support this?
> btw why order by field must appear in the select field list?in the first
> query actually I just want the distinct fid
> ------------------------------
>
> 赵天烁
>
> Kevin Zhao
>
> *zhaotianshuo@meizu.com <zh...@meizu.com>*
>
>
>
> 珠海市魅族科技有限公司
>
> MEIZU Technology Co., Ltd.
>
> 广东省珠海市科技创新海岸魅族科技楼
>
> MEIZU Tech Bldg., Technology & Innovation Coast
>
> Zhuhai, 519085, Guangdong, China
>
> meizu.com
>
>
> *发件人:* 赵天烁 <zh...@meizu.com>
> *发送时间:* 2016-08-02 15:49
> *收件人:* user <us...@kylin.apache.org>
> *主题:* support filter on a dimension which is not being grouped
> currently kylin don't support filter on a dimension which is not being
> grouped,for example I have a sql like this:
> select fid,crash_point from crash_analysis_record where package_ver='5.1'
> group by fid order by count(1) desc
> fid and package_ver are all predefine dimensions,it can be done if we find
> those cuboid data with fid and package_ver, eliminate the data which are
> not package_ver=5.0,then runtime aggragate to fid
> I just propose some thought,this approach may have profermance issue,maybe
> you guys already have a better way todo this?
>
> ------------------------------
>
> 赵天烁
>
> Kevin Zhao
>
> *zhaotianshuo@meizu.com <zh...@meizu.com>*
>
>
>
> 珠海市魅族科技有限公司
>
> MEIZU Technology Co., Ltd.
>
> 广东省珠海市科技创新海岸魅族科技楼
>
> MEIZU Tech Bldg., Technology & Innovation Coast
>
> Zhuhai, 519085, Guangdong, China
>
> meizu.com
>
>
--
Best regards,
Shaofeng Shi