[
https://issues.apache.org/jira/browse/KYLIN-4738?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhichao Zhang updated KYLIN-4738:
----------------------------------
Description:
Use below sql to query in kylin:
{code:java}
// code placeholder
SELECT PART_DT, LSTG_FORMAT_NAME, SUM(PRICE) AS GMV,FIRST_VALUE(SUM(PRICE))
OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS
"FIRST",LAST_VALUE(SUM(PRICE)) OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY
PART_DT) AS "CURRENT",LAG(SUM(PRICE), 1, 0.0) OVER(PARTITION BY
LSTG_FORMAT_NAME ORDER BY PART_DT) AS "PREV",LEAD(SUM(PRICE), 1, 0.0)
OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS "NEXT",NTILE(4) OVER
(PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS "QUARTER"FROM
KYLIN_SALESINNER JOIN KYLIN_ACCOUNT as SELLER_ACCOUNTON KYLIN_SALES.SELLER_ID =
SELLER_ACCOUNT.ACCOUNT_IDINNER JOIN KYLIN_COUNTRY as SELLER_COUNTRYON
SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRYWHERE PART_DT >=
'2012-12-30' and PART_DT < '2013-01-03' and SELLER_COUNTRY.COUNTRY in
('CN')GROUP BY PART_DT, LSTG_FORMAT_NAMEORDER BY PART_DT LIMIT 5;
{code}
the result is:
!image-2020-09-02-12-15-27-097.png!
and the expected result is :
!image-2020-09-02-12-16-15-719.png!
was:
Use below sql to query in kylin:
{code:java}
// code placeholder
{code}
SELECT PART_DT, LSTG_FORMAT_NAME, SUM(PRICE) AS GMV,FIRST_VALUE(SUM(PRICE))
OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS
"FIRST",LAST_VALUE(SUM(PRICE)) OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY
PART_DT) AS "CURRENT",LAG(SUM(PRICE), 1, 0.0) OVER(PARTITION BY
LSTG_FORMAT_NAME ORDER BY PART_DT) AS "PREV",LEAD(SUM(PRICE), 1, 0.0)
OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS "NEXT",NTILE(4) OVER
(PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS "QUARTER"FROM
KYLIN_SALESINNER JOIN KYLIN_ACCOUNT as SELLER_ACCOUNTON KYLIN_SALES.SELLER_ID =
SELLER_ACCOUNT.ACCOUNT_IDINNER JOIN KYLIN_COUNTRY as SELLER_COUNTRYON
SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRYWHERE PART_DT >=
'2012-12-30' and PART_DT < '2013-01-03' and SELLER_COUNTRY.COUNTRY in
('CN')GROUP BY PART_DT, LSTG_FORMAT_NAMEORDER BY PART_DT LIMIT 5;
> The order in the returned result is wrong when use window function to query
> in kylin
> ------------------------------------------------------------------------------------
>
> Key: KYLIN-4738
> URL: https://issues.apache.org/jira/browse/KYLIN-4738
> Project: Kylin
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: v4.0.0-alpha
> Reporter: Zhichao Zhang
> Assignee: Zhichao Zhang
> Priority: Minor
> Attachments: image-2020-09-02-12-15-27-097.png
>
>
> Use below sql to query in kylin:
> {code:java}
> // code placeholder
> SELECT PART_DT, LSTG_FORMAT_NAME, SUM(PRICE) AS GMV,FIRST_VALUE(SUM(PRICE))
> OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS
> "FIRST",LAST_VALUE(SUM(PRICE)) OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY
> PART_DT) AS "CURRENT",LAG(SUM(PRICE), 1, 0.0) OVER(PARTITION BY
> LSTG_FORMAT_NAME ORDER BY PART_DT) AS "PREV",LEAD(SUM(PRICE), 1, 0.0)
> OVER(PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS "NEXT",NTILE(4) OVER
> (PARTITION BY LSTG_FORMAT_NAME ORDER BY PART_DT) AS "QUARTER"FROM
> KYLIN_SALESINNER JOIN KYLIN_ACCOUNT as SELLER_ACCOUNTON KYLIN_SALES.SELLER_ID
> = SELLER_ACCOUNT.ACCOUNT_IDINNER JOIN KYLIN_COUNTRY as SELLER_COUNTRYON
> SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRYWHERE PART_DT >=
> '2012-12-30' and PART_DT < '2013-01-03' and SELLER_COUNTRY.COUNTRY in
> ('CN')GROUP BY PART_DT, LSTG_FORMAT_NAMEORDER BY PART_DT LIMIT 5;
> {code}
> the result is:
> !image-2020-09-02-12-15-27-097.png!
>
> and the expected result is :
> !image-2020-09-02-12-16-15-719.png!
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
