Hi Wang Lin,
I hadencountered this problem as well, the E-mail I had sent to SQL developer
is as below. Maybe it is helpful to you too.
PS: You should adapt your SQL to recommended solution.
----------------------------------------------------------------------------------------------------
Window functions are supported in Kylin, such as: lead(), lag(), first()
Solution 1(deprecated)：
SELECT "this_year"."all_count" * 1.0 /"last_year"."all_count" AS "new_rate"
FROM (SELECT {fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")} AS
"action_month", SUM("APP_NEW_MC"."ALLCOUNT") AS "all_count", {fn EXTRACT(YEAR
FROM "APP_NEW_MC"."ACTION_DT")} AS "action_yr" FROM "ADL"."APP_NEW_MC"
"APP_NEW_MC" WHERE {fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")} = {fn
EXTRACT(MONTH FROM TIMESTAMPADD(DAY, -1, CURRENT_DATE))} AND {fn EXTRACT(YEAR
FROM "APP_NEW_MC"."ACTION_DT")} = {fn EXTRACT(YEAR FROM CURRENT_DATE)} - 1 AND
"APP_NEW_MC"."PRODUCT" = ‘xxxx' GROUP BY {fn EXTRACT(MONTH FROM
"APP_NEW_MC"."ACTION_DT")}, {fn EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")})
"last_year" LEFT JOIN (SELECT {fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")}
AS "action_month", SUM("APP_NEW_MC"."ALLCOUNT") AS "all_count", {fn
EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")} AS "action_yr" FROM
"ADL"."APP_NEW_MC" "APP_NEW_MC" WHERE {fn EXTRACT(MONTH FROM
"APP_NEW_MC"."ACTION_DT")} = {fn EXTRACT(MONTH FROM TIMESTAMPADD(DAY, -1,
CURRENT_DATE))} AND {fn EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")} = {fn
EXTRACT(YEAR FROM CURRENT_DATE)} AND "APP_NEW_MC"."PRODUCT" = ‘xxxx' GROUP BY
{fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")}, {fn EXTRACT(YEAR FROM
"APP_NEW_MC"."ACTION_DT")}) "this_year" ON "last_year"."action_month" =
"this_year"."action_month";
Solution 2(recommended)：
SELECT {fn EXTRACT(YEAR FROM "APP_NEW_MC"."ACTION_DT")} as "YEAR", {fn
EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")} as "MONTH",
SUM("APP_NEW_MC"."ALLCOUNT") / cast(lead(SUM("APP_NEW_MC"."ALLCOUNT"), 12) over
() as double) as "new_rate" FROM "ADL"."APP_NEW_MC" "APP_NEW_MC" WHERE
"APP_NEW_MC"."PRODUCT" = ‘xxxx' GROUP BY {fn EXTRACT(YEAR FROM
"APP_NEW_MC"."ACTION_DT")},{fn EXTRACT(MONTH FROM "APP_NEW_MC"."ACTION_DT")}
order by "YEAR" desc, "MONTH" desc;
Original Message
Sender:王林[email protected]
Recipient:[email protected]
Date:Monday, Nov 19, 2018 18:34
Subject:apache kylin left join 数据查询混乱咨询
你好： 通过kylin 页面查询发现如下问题： 执行如下sql： SELECT c.DT, COALESCE (C.weekActivityCount,
0), COALESCE (D.monthActivityCount, 0) FROM ( SELECT DT AS DT, count(*) AS
weekActivityCount FROM ST_DEVICE_INFO_FACT_TABLE WHERE 6 =
LAST_ONLINE_DT_DIFF_DAY AND LAST_ONLINE_DT_DIFF_DAY = 0 AND DT = '2018-10-11'
AND '2018-10-11' = DT AND IS_ACTIVATED = 1 GROUP BY dt ) C LEFT JOIN ( SELECT
DT AS DT, count(*) AS monthActivityCount FROM ST_DEVICE_INFO_FACT_TABLE WHERE
29 = LAST_ONLINE_DT_DIFF_DAY AND LAST_ONLINE_DT_DIFF_DAY = 0 AND DT =
'2018-10-11' AND '2018-10-11' = DT AND IS_ACTIVATED = 1 GROUP BY dt ) D ON C.DT
= D.DT 查询使用cube：
CUBE[name=st_device_info_cube_v140],CUBE[name=st_device_info_cube_v140] 查询结果为：
2018-10-11，996542，996542 但是单独执行C,D两个子查询： SELECT DT AS DT, count(*) AS
weekActivityCount FROM ST_DEVICE_INFO_FACT_TABLE WHERE 6 =
LAST_ONLINE_DT_DIFF_DAY AND LAST_ONLINE_DT_DIFF_DAY = 0 AND DT = '2018-10-11'
AND '2018-10-11' = DT AND IS_ACTIVATED = 1 GROUP BY dt Cubes:
CUBE[name=st_device_info_cube_v140] 查询结果为： 2018-10-11，996542 执行： SELECT DT AS
DT, count(*) AS monthActivityCount FROM ST_DEVICE_INFO_FACT_TABLE WHERE 29 =
LAST_ONLINE_DT_DIFF_DAY AND LAST_ONLINE_DT_DIFF_DAY = 0 AND DT = '2018-10-11'
AND '2018-10-11' = DT AND IS_ACTIVATED = 1 GROUP BY dt Cubes:
CUBE[name=st_device_info_cube_v140] 查询结果为： 2018-10-11，1119847 查询结果与第一个left join
查询结果不对应。请问是什么原因？ 这个问题需要怎么修复呢？
