kylin-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ShaoFeng Shi <shaofeng...@apache.org>
Subject Re: apache kylin left join 数据查询混乱咨询
Date Tue, 20 Nov 2018 08:40:48 GMT
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.shi@kyligence.io
Kyligence Inc: https://kyligence.io/

Apache Kylin FAQ: https://kylin.apache.org/docs/gettingstarted/faq.html
Join Kylin user mail group: user-subscribe@kylin.apache.org
Join Kylin dev mail group: dev-subscribe@kylin.apache.org




dbaztp <dbaztp@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:王林1059790601@qq.com
> Recipient:devdev@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 查询结果不对应。请问是什么原因? 这个问题需要怎么修复呢?

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message