You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "Xiaoxiang Yu (Jira)" <ji...@apache.org> on 2021/04/27 02:35:03 UTC
[jira] [Closed] (KYLIN-4658) Union all issue with regarding to
windows function & aggregation on
[ https://issues.apache.org/jira/browse/KYLIN-4658?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Xiaoxiang Yu closed KYLIN-4658.
-------------------------------
Released at kylin 3.1.2
> 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
> Fix For: v3.1.2
>
>
> 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 50000"
> {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)