kylin-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "wangquanfu@bestpay.com.cn" <wangqua...@bestpay.com.cn>
Subject Re: Re: Does Keylin Support Having Subcluse
Date Thu, 19 Apr 2018 07:47:22 GMT
Appreciate it, Billy.

Thanks,
Quanfu
 
From: Billy Liu
Date: 2018-04-19 15:23
To: dev
Subject: Re: Does Keylin Support Having Subcluse
Yes. Kylin supports having sub-clause. There are quite a lot of use
cases in Kylin test code. For examples:
 
select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV
, count(*) as TRANS_CNT from test_kylin_fact
group by test_kylin_fact.lstg_format_name having sum(price)>5000 and
count(*)>72
 
select test_kylin_fact.lstg_format_name, sum(price) as GMV,
count(seller_id) as TRANS_CNT
from test_kylin_fact where test_kylin_fact.lstg_format_name <= 'ABZ'
group by test_kylin_fact.lstg_format_name having count(seller_id) > 2
 
select lstg_format_name, sum(price) as GMV
from test_kylin_fact
group by lstg_format_name, SLR_SEGMENT_CD
  having SLR_SEGMENT_CD > 0
 
SELECT SUM("TableauSQL"."GMV") AS "sum_GMV_ok",
SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_ok"
FROM (
SELECT test_cal_dt.week_beg_dt
,test_category_groupings.meta_categ_name
,test_category_groupings.categ_lvl2_name
,test_category_groupings.categ_lvl3_name ,sum(test_kylin_fact.price)
as GMV , count(*) as TRANS_CNT
FROM test_kylin_fact
inner JOIN edw.test_cal_dt as test_cal_dt ON test_kylin_fact.cal_dt =
test_cal_dt.cal_dt
inner JOIN test_category_groupings ON test_kylin_fact.leaf_categ_id =
test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id
= test_category_groupings.site_id
group by test_cal_dt.week_beg_dt
,test_category_groupings.meta_categ_name
,test_category_groupings.categ_lvl2_name
,test_category_groupings.categ_lvl3_name
) "TableauSQL" HAVING (COUNT(1) > 0)
 
select lstg_format_name,
sum(price) as GMV,
count(1) as TRANS_CNT,
count(distinct TEST_COUNT_DISTINCT_BITMAP) as user_count
from test_kylin_fact
where lstg_format_name='FP-GTC'
group by lstg_format_name
having count(distinct seller_id) > 50
 
With Warm regards
 
Billy Liu
 
 
2018-04-19 14:24 GMT+08:00 wangquanfu@bestpay.com.cn
<wangquanfu@bestpay.com.cn>:
> Hi,
> I heard that keylin does not support Having-clause like bellow. Does anyone can give
confirmation on it?
> If it's already supported, since which version?
>
> The example table:
> O_Id
> OrderDate
> OrderPrice
> Customer
> 12008/12/291000Bush
> 22008/11/231600Carter
> 32008/10/05700Bush
> 42008/09/28300Bush
> 52008/08/062000Adams
> 62008/07/21100Carter
>
> and the query code(select users which have sum of OerderPrice less than 2000):
> SELECT Customer,SUM(OrderPrice) FROM Orders
> GROUP BY Customer
> HAVING SUM(OrderPrice) < 2000
>
> Thanks,
> Quanfu
>
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message