[jira] [Updated] (KYLIN-4658) Union all issue with regarding to windows function & aggregation

2020-08-03 Thread Zhong Yanghong (Jira) 


 [ 
https://issues.apache.org/jira/browse/KYLIN-4658?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Zhong Yanghong updated KYLIN-4658:
--
Summary:  Union all issue with regarding to windows function & aggregation  
(was: Windows function does not work for union all)

>  Union all issue with regarding to windows function & aggregation
> -
>
> Key: KYLIN-4658
> URL: https://issues.apache.org/jira/browse/KYLIN-4658
> Project: Kylin
>  Issue Type: Improvement
>Reporter: Zhong Yanghong
>Assignee: Zhong Yanghong
>Priority: Major
>
> Test SQL:
> {code}
> select CNT, GMV, sum(GMV) over(partition by SLR_SEGMENT_CD) TOTAL_GMV, 
> SLR_SEGMENT_CD, LSTG_FORMAT_NAME
> from 
> (select sum(PRICE) GMV, sum(ITEM_COUNT) CNT, SLR_SEGMENT_CD, LSTG_FORMAT_NAME 
> from TEST_KYLIN_FACT group by SLR_SEGMENT_CD, LSTG_FORMAT_NAME 
> UNION ALL
> select sum(PRICE) GMV, sum(ITEM_COUNT) CNT, SLR_SEGMENT_CD, LSTG_FORMAT_NAME 
> from TEST_KYLIN_FACT group by SLR_SEGMENT_CD, LSTG_FORMAT_NAME) 
> order by TOTAL_GMV
> {code}
>  
> Exception:
> {code}
> Index: 2, Size: 2 while executing SQL: "select * from (select CNT, GMV, 
> sum(GMV) over(partition by SLR_SEGMENT_CD) TOTAL_GMV, SLR_SEGMENT_CD, 
> LSTG_FORMAT_NAME from (select sum(PRICE) GMV, sum(ITEM_COUNT) CNT, 
> SLR_SEGMENT_CD, LSTG_FORMAT_NAME from TEST_KYLIN_FACT group by 
> SLR_SEGMENT_CD, LSTG_FORMAT_NAME UNION ALL select sum(PRICE) GMV, 
> sum(ITEM_COUNT) CNT, SLR_SEGMENT_CD, LSTG_FORMAT_NAME from TEST_KYLIN_FACT 
> group by SLR_SEGMENT_CD, LSTG_FORMAT_NAME) order by TOTAL_GMV) limit 5"
> {code}
> Similar issue for the following sql:
> {code}
> select LSTG_FORMAT_NAME,
>SLR_SEGMENT_CD,
>CAL_DT,
>sum(CNT) as CNT
> from
>   (select LSTG_FORMAT_NAME,
>   SLR_SEGMENT_CD,
>   CAL_DT,
>   sum(ITEM_COUNT) CNT
>from TEST_KYLIN_FACT
>where LSTG_FORMAT_NAME = 'ABIN'
>group by LSTG_FORMAT_NAME,
> SLR_SEGMENT_CD,
> CAL_DT
>UNION ALL select 'NON-ABIN' as LSTG_FORMAT_NAME,
> SLR_SEGMENT_CD,
> CAL_DT,
> case
> when SLR_SEGMENT_CD > 1000 then CNT * 2
> else CNT * 3
> end as CNT
>from
>  (select SLR_SEGMENT_CD,
>  CAL_DT,
>  sum(ITEM_COUNT) CNT
>   from TEST_KYLIN_FACT
>   where LSTG_FORMAT_NAME <> 'ABIN'
>   group by SLR_SEGMENT_CD,CAL_DT))
> group by LSTG_FORMAT_NAME,
>  SLR_SEGMENT_CD,
>  CAL_DT
> order by CNT
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (KYLIN-4658) Union all issue with regarding to windows function & aggregation on

2020-08-03 Thread Zhong Yanghong (Jira) 


 [ 
https://issues.apache.org/jira/browse/KYLIN-4658?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Zhong Yanghong updated KYLIN-4658:
--
Summary:  Union all issue with regarding to windows function & aggregation 
on  (was:  Union all issue with regarding to windows function & aggregation)

>  Union all issue with regarding to windows function & aggregation on
> 
>
> Key: KYLIN-4658
> URL: https://issues.apache.org/jira/browse/KYLIN-4658
> Project: Kylin
>  Issue Type: Improvement
>Reporter: Zhong Yanghong
>Assignee: Zhong Yanghong
>Priority: Major
>
> Test SQL:
> {code}
> select CNT, GMV, sum(GMV) over(partition by SLR_SEGMENT_CD) TOTAL_GMV, 
> SLR_SEGMENT_CD, LSTG_FORMAT_NAME
> from 
> (select sum(PRICE) GMV, sum(ITEM_COUNT) CNT, SLR_SEGMENT_CD, LSTG_FORMAT_NAME 
> from TEST_KYLIN_FACT group by SLR_SEGMENT_CD, LSTG_FORMAT_NAME 
> UNION ALL
> select sum(PRICE) GMV, sum(ITEM_COUNT) CNT, SLR_SEGMENT_CD, LSTG_FORMAT_NAME 
> from TEST_KYLIN_FACT group by SLR_SEGMENT_CD, LSTG_FORMAT_NAME) 
> order by TOTAL_GMV
> {code}
>  
> Exception:
> {code}
> Index: 2, Size: 2 while executing SQL: "select * from (select CNT, GMV, 
> sum(GMV) over(partition by SLR_SEGMENT_CD) TOTAL_GMV, SLR_SEGMENT_CD, 
> LSTG_FORMAT_NAME from (select sum(PRICE) GMV, sum(ITEM_COUNT) CNT, 
> SLR_SEGMENT_CD, LSTG_FORMAT_NAME from TEST_KYLIN_FACT group by 
> SLR_SEGMENT_CD, LSTG_FORMAT_NAME UNION ALL select sum(PRICE) GMV, 
> sum(ITEM_COUNT) CNT, SLR_SEGMENT_CD, LSTG_FORMAT_NAME from TEST_KYLIN_FACT 
> group by SLR_SEGMENT_CD, LSTG_FORMAT_NAME) order by TOTAL_GMV) limit 5"
> {code}
> Similar issue for the following sql:
> {code}
> select LSTG_FORMAT_NAME,
>SLR_SEGMENT_CD,
>CAL_DT,
>sum(CNT) as CNT
> from
>   (select LSTG_FORMAT_NAME,
>   SLR_SEGMENT_CD,
>   CAL_DT,
>   sum(ITEM_COUNT) CNT
>from TEST_KYLIN_FACT
>where LSTG_FORMAT_NAME = 'ABIN'
>group by LSTG_FORMAT_NAME,
> SLR_SEGMENT_CD,
> CAL_DT
>UNION ALL select 'NON-ABIN' as LSTG_FORMAT_NAME,
> SLR_SEGMENT_CD,
> CAL_DT,
> case
> when SLR_SEGMENT_CD > 1000 then CNT * 2
> else CNT * 3
> end as CNT
>from
>  (select SLR_SEGMENT_CD,
>  CAL_DT,
>  sum(ITEM_COUNT) CNT
>   from TEST_KYLIN_FACT
>   where LSTG_FORMAT_NAME <> 'ABIN'
>   group by SLR_SEGMENT_CD,CAL_DT))
> group by LSTG_FORMAT_NAME,
>  SLR_SEGMENT_CD,
>  CAL_DT
> order by CNT
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)