你好，执行日志见附件。谢谢
------------------ 原始邮件 ------------------ 发件人: "ShaoFeng Shi"<shaofeng...@apache.org>; 发送时间: 2018年11月20日(星期二) 下午4:40 收件人: "dev"<dev@kylin.apache.org>; 主题: Re: apache kylin left join 数据查询混乱咨询 Hi Ling, Could you please provide the Kylin log when executing the first query? Thanks! Best regards, Shaofeng Shi 史少锋 Apache Kylin PMC Work email: shaofeng....@kyligence.io Kyligence Inc: https://kyligence.io/ Apache Kylin FAQ: https://kylin.apache.org/docs/gettingstarted/faq.html Join Kylin user mail group: user-subscr...@kylin.apache.org Join Kylin dev mail group: dev-subscr...@kylin.apache.org dbaztp <dba...@gmail.com> 于2018年11月19日周一 下午6:52写道： > 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:王林1059790...@qq.com > Recipient:dev...@kylin.apache.org > 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 查询结果不对应。请问是什么原因？ 这个问题需要怎么修复呢？
