You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by Santoshakhilesh <sa...@huawei.com> on 2015/03/02 13:16:45 UTC
Qury about Measures Formula
Hi All ,
While adding measures while making cube. The formulas supported are count , max , min , ...
I have a case where the measure is simply not a count , max , min or sum
I want to define based on the value of a column in fact table like below.
count(case when column1 < 5 or column2 < 5 or column3 < 80 or column < 15 then 1 else 0 end)
How do I support such measures using kylin ? Is there a way to do this ?
Regards,
Santosh Akhilesh
Bangalore R&D
HUAWEI TECHNOLOGIES CO.,LTD.
www.huawei.com
-------------------------------------------------------------------------------------------------------------------------------------
This e-mail and its attachments contain confidential information from HUAWEI, which
is intended only for the person or entity whose address is listed above. Any use of the
information contained herein in any way (including, but not limited to, total or partial
disclosure, reproduction, or dissemination) by persons other than the intended
recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
Re: FW: Qury about Measures Formula
Posted by Li Yang <li...@apache.org>.
If the count condition is dynamic then flag column won't help..
I'm not clear of your full requirement, but the below returns the count of
one condition quickly:
select count(*) from fact where condition_goes_here
You can invoke similar queries multiple times, or embed it as subqueries to
get what you want.
Cheers
Yang
On Tue, Mar 3, 2015 at 5:01 PM, Santoshakhilesh <santosh.akhilesh@huawei.com
> wrote:
> Hi Yang ,
> I could not understand your meaning correctly about sum(flag_column)
> For example following case , column1...column4 is part of my fact table ,
> so how should I map a flag column for coollowing case ?
> count(case when column1 < 5 or column2 < 5 or column3 < 80 or column4 < 15
> then 1 else 0 end)
>
> Yes subquery is one option but as you said , I will not able to take
> benifit of precalculated data.
>
> Actuall above is just one example , my queries can be dynmaic involving
> nested conditions using count , sum , max , min + , - <*,/ and / or
>
> Regards,
> Santosh Akhilesh
> Bangalore R&D
> HUAWEI TECHNOLOGIES CO.,LTD.
>
> www.huawei.com
>
> -------------------------------------------------------------------------------------------------------------------------------------
> This e-mail and its attachments contain confidential information from
> HUAWEI, which
> is intended only for the person or entity whose address is listed above.
> Any use of the
> information contained herein in any way (including, but not limited to,
> total or partial
> disclosure, reproduction, or dissemination) by persons other than the
> intended
> recipient(s) is prohibited. If you receive this e-mail in error, please
> notify the sender by
> phone or email immediately and delete it!
>
> ________________________________________
> From: Li Yang [liyang@apache.org]
> Sent: Tuesday, March 03, 2015 1:37 PM
> To: dev@kylin.incubator.apache.org
> Subject: Re: Qury about Measures Formula
>
> Minor correction -- *subquery* should fulfill this case -- well, not
> necessarily subquery, just put the count(..) on select clause and it should
> work. However this kind of work is based on runtime calculation and does
> not benefit Kylin's pre-calculate ability.
>
> Having a flag column prepared on fact table is more preferable in terms of
> performance. Kylin will be able to pre-aggregate sum(flag_column) and your
> query at runtime will be lightening fast.
>
> Cheers
> Yang
>
> On Mon, Mar 2, 2015 at 8:47 PM, Luke Han <lu...@gmail.com> wrote:
>
> > subquery should fulfill this case. please have a try and let's know if
> > there's any issue.
> >
> > And, if this is just indicate, can you process it in ETL phase when
> create
> > Hive table?
> >
> > Thanks.
> >
> > Luke
> >
> >
> >
> > Best Regards!
> > ---------------------
> >
> > Luke Han
> >
> > 2015-03-02 20:16 GMT+08:00 Santoshakhilesh <santosh.akhilesh@huawei.com
> >:
> >
> > > Hi All ,
> > >
> > >
> > >
> > > While adding measures while making cube. The formulas supported are
> > > count , max , min , ...
> > >
> > >
> > >
> > > I have a case where the measure is simply not a count , max , min
> or
> > > sum
> > >
> > >
> > >
> > > I want to define based on the value of a column in fact table like
> > > below.
> > >
> > >
> > >
> > > count(case when column1 < 5 or column2 < 5 or column3 < 80 or
> column
> > <
> > > 15 then 1 else 0 end)
> > >
> > >
> > >
> > > How do I support such measures using kylin ? Is there a way to do
> > this
> > > ?
> > >
> > >
> > >
> > > Regards,
> > > Santosh Akhilesh
> > > Bangalore R&D
> > > HUAWEI TECHNOLOGIES CO.,LTD.
> > >
> > > www.huawei.com
> > >
> > >
> >
> -------------------------------------------------------------------------------------------------------------------------------------
> > > This e-mail and its attachments contain confidential information from
> > > HUAWEI, which
> > > is intended only for the person or entity whose address is listed
> above.
> > > Any use of the
> > > information contained herein in any way (including, but not limited to,
> > > total or partial
> > > disclosure, reproduction, or dissemination) by persons other than the
> > > intended
> > > recipient(s) is prohibited. If you receive this e-mail in error, please
> > > notify the sender by
> > > phone or email immediately and delete it!
> > >
> >
>
FW: Qury about Measures Formula
Posted by Santoshakhilesh <sa...@huawei.com>.
Hi Yang ,
I could not understand your meaning correctly about sum(flag_column)
For example following case , column1...column4 is part of my fact table , so how should I map a flag column for coollowing case ?
count(case when column1 < 5 or column2 < 5 or column3 < 80 or column4 < 15 then 1 else 0 end)
Yes subquery is one option but as you said , I will not able to take benifit of precalculated data.
Actuall above is just one example , my queries can be dynmaic involving nested conditions using count , sum , max , min + , - <*,/ and / or
Regards,
Santosh Akhilesh
Bangalore R&D
HUAWEI TECHNOLOGIES CO.,LTD.
www.huawei.com
-------------------------------------------------------------------------------------------------------------------------------------
This e-mail and its attachments contain confidential information from HUAWEI, which
is intended only for the person or entity whose address is listed above. Any use of the
information contained herein in any way (including, but not limited to, total or partial
disclosure, reproduction, or dissemination) by persons other than the intended
recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
________________________________________
From: Li Yang [liyang@apache.org]
Sent: Tuesday, March 03, 2015 1:37 PM
To: dev@kylin.incubator.apache.org
Subject: Re: Qury about Measures Formula
Minor correction -- *subquery* should fulfill this case -- well, not
necessarily subquery, just put the count(..) on select clause and it should
work. However this kind of work is based on runtime calculation and does
not benefit Kylin's pre-calculate ability.
Having a flag column prepared on fact table is more preferable in terms of
performance. Kylin will be able to pre-aggregate sum(flag_column) and your
query at runtime will be lightening fast.
Cheers
Yang
On Mon, Mar 2, 2015 at 8:47 PM, Luke Han <lu...@gmail.com> wrote:
> subquery should fulfill this case. please have a try and let's know if
> there's any issue.
>
> And, if this is just indicate, can you process it in ETL phase when create
> Hive table?
>
> Thanks.
>
> Luke
>
>
>
> Best Regards!
> ---------------------
>
> Luke Han
>
> 2015-03-02 20:16 GMT+08:00 Santoshakhilesh <sa...@huawei.com>:
>
> > Hi All ,
> >
> >
> >
> > While adding measures while making cube. The formulas supported are
> > count , max , min , ...
> >
> >
> >
> > I have a case where the measure is simply not a count , max , min or
> > sum
> >
> >
> >
> > I want to define based on the value of a column in fact table like
> > below.
> >
> >
> >
> > count(case when column1 < 5 or column2 < 5 or column3 < 80 or column
> <
> > 15 then 1 else 0 end)
> >
> >
> >
> > How do I support such measures using kylin ? Is there a way to do
> this
> > ?
> >
> >
> >
> > Regards,
> > Santosh Akhilesh
> > Bangalore R&D
> > HUAWEI TECHNOLOGIES CO.,LTD.
> >
> > www.huawei.com
> >
> >
> -------------------------------------------------------------------------------------------------------------------------------------
> > This e-mail and its attachments contain confidential information from
> > HUAWEI, which
> > is intended only for the person or entity whose address is listed above.
> > Any use of the
> > information contained herein in any way (including, but not limited to,
> > total or partial
> > disclosure, reproduction, or dissemination) by persons other than the
> > intended
> > recipient(s) is prohibited. If you receive this e-mail in error, please
> > notify the sender by
> > phone or email immediately and delete it!
> >
>
Re: Qury about Measures Formula
Posted by Li Yang <li...@apache.org>.
Minor correction -- *subquery* should fulfill this case -- well, not
necessarily subquery, just put the count(..) on select clause and it should
work. However this kind of work is based on runtime calculation and does
not benefit Kylin's pre-calculate ability.
Having a flag column prepared on fact table is more preferable in terms of
performance. Kylin will be able to pre-aggregate sum(flag_column) and your
query at runtime will be lightening fast.
Cheers
Yang
On Mon, Mar 2, 2015 at 8:47 PM, Luke Han <lu...@gmail.com> wrote:
> subquery should fulfill this case. please have a try and let's know if
> there's any issue.
>
> And, if this is just indicate, can you process it in ETL phase when create
> Hive table?
>
> Thanks.
>
> Luke
>
>
>
> Best Regards!
> ---------------------
>
> Luke Han
>
> 2015-03-02 20:16 GMT+08:00 Santoshakhilesh <sa...@huawei.com>:
>
> > Hi All ,
> >
> >
> >
> > While adding measures while making cube. The formulas supported are
> > count , max , min , ...
> >
> >
> >
> > I have a case where the measure is simply not a count , max , min or
> > sum
> >
> >
> >
> > I want to define based on the value of a column in fact table like
> > below.
> >
> >
> >
> > count(case when column1 < 5 or column2 < 5 or column3 < 80 or column
> <
> > 15 then 1 else 0 end)
> >
> >
> >
> > How do I support such measures using kylin ? Is there a way to do
> this
> > ?
> >
> >
> >
> > Regards,
> > Santosh Akhilesh
> > Bangalore R&D
> > HUAWEI TECHNOLOGIES CO.,LTD.
> >
> > www.huawei.com
> >
> >
> -------------------------------------------------------------------------------------------------------------------------------------
> > This e-mail and its attachments contain confidential information from
> > HUAWEI, which
> > is intended only for the person or entity whose address is listed above.
> > Any use of the
> > information contained herein in any way (including, but not limited to,
> > total or partial
> > disclosure, reproduction, or dissemination) by persons other than the
> > intended
> > recipient(s) is prohibited. If you receive this e-mail in error, please
> > notify the sender by
> > phone or email immediately and delete it!
> >
>
Re: Qury about Measures Formula
Posted by Luke Han <lu...@gmail.com>.
subquery should fulfill this case. please have a try and let's know if
there's any issue.
And, if this is just indicate, can you process it in ETL phase when create
Hive table?
Thanks.
Luke
Best Regards!
---------------------
Luke Han
2015-03-02 20:16 GMT+08:00 Santoshakhilesh <sa...@huawei.com>:
> Hi All ,
>
>
>
> While adding measures while making cube. The formulas supported are
> count , max , min , ...
>
>
>
> I have a case where the measure is simply not a count , max , min or
> sum
>
>
>
> I want to define based on the value of a column in fact table like
> below.
>
>
>
> count(case when column1 < 5 or column2 < 5 or column3 < 80 or column <
> 15 then 1 else 0 end)
>
>
>
> How do I support such measures using kylin ? Is there a way to do this
> ?
>
>
>
> Regards,
> Santosh Akhilesh
> Bangalore R&D
> HUAWEI TECHNOLOGIES CO.,LTD.
>
> www.huawei.com
>
> -------------------------------------------------------------------------------------------------------------------------------------
> This e-mail and its attachments contain confidential information from
> HUAWEI, which
> is intended only for the person or entity whose address is listed above.
> Any use of the
> information contained herein in any way (including, but not limited to,
> total or partial
> disclosure, reproduction, or dissemination) by persons other than the
> intended
> recipient(s) is prohibited. If you receive this e-mail in error, please
> notify the sender by
> phone or email immediately and delete it!
>