報錯提示是集群 QUERY ENGINE: 未知例外:[語法錯誤，請檢查sql]:com.lianjia.bigdata.queryengine.exception.QueryEngineException: Error while compiling statement: FAILED: SemanticException [Error 10007]: Ambiguous column reference avg_all_am_m1 in r1 提示資訊：您的SQL不符合語法規范，請按照SQL規范指南修改SQL
SELECT
r1.*
FROM(
SELECT
pt
,corp_name
,shop_code
,shop_name
,shop_first_del_time
FROM rpt1
WHERE pt = '20200331000000'
AND (corp_name LIKE '%德佑%' OR corp_name LIKE '%小草%' OR corp_name LIKE '%聯勝%' OR corp_name LIKE '%世紀%' OR corp_name LIKE '%中環%')
AND shop_name NOT LIKE '%虛擬%'
AND (expire_date > '2020-01-01' OR expire_date = '' OR expire_date IS null)
)d1
left JOIN
(
SELECT
shop_code
,IF(link_date > '2019-12-31',M_AM_M1,(M_AM_M12 + M_AM_M1)/2)avg_M_AM_M1
,IF(link_date > '2020-01-31',M_AM_M2,(M_AM_M1 + M_AM_M2)/2)avg_M_AM_M2
,IF(link_date > '2020-02-29',M_AM_M3,(M_AM_M2 + M_AM_M3)/2)avg_M_AM_M3
,IF(link_date > '2019-12-31',ALL_AM_M1,(ALL_AM_M12 + ALL_AM_M1)/2)avg_ALL_AM_M1
,IF(link_date > '2020-01-31',ALL_AM_M2,(ALL_AM_M1 + ALL_AM_M2)/2)avg_ALL_AM_M2
,IF(link_date > '2020-02-29',ALL_AM_M3,(ALL_AM_M2 + ALL_AM_M3)/2)avg_ALL_AM_M3
,IF(link_date > '2019-12-31',ALL_AMS_M1,(ALL_AMS_M12 + ALL_AMS_M1)/2)avg_ALL_AM_M1
,IF(link_date > '2020-01-31',ALL_AMS_M2,(ALL_AMS_M1 + ALL_AMS_M2)/2)avg_ALL_AM_M2
,IF(link_date > '2020-02-29',ALL_AMS_M3,(ALL_AMS_M2 + ALL_AMS_M3)/2)avg_ALL_AM_M3
,M_AM_M12
,M_AM_M1
,M_AM_M2
,M_AM_M3
,ALL_AM_M12
,ALL_AM_M1
,ALL_AM_M2
,ALL_AM_M3
,ALL_AMS_M12
,ALL_AMS_M1
,ALL_AMS_M2
,ALL_AMS_M3
FROM
(
SELECT
r0_1.shop_code
,r0_2.link_date
,COUNT(DISTINCT CASE WHEN pt = '20191231000000' AND r0_2.link_date <'2020-01-01' AND substr(job_level_name,1,1) IN ('A','M') AND substr(job_name,1,2) IN ('買賣','綜合') THEN agent_ucid ELSE null END)M_AM_M12
,COUNT(DISTINCT CASE WHEN pt = '20200131000000' AND r0_2.link_date <'2020-02-01' AND substr(job_level_name,1,1) IN ('A','M') AND substr(job_name,1,2) IN ('買賣','綜合') THEN agent_ucid ELSE null END)M_AM_M1
,COUNT(DISTINCT CASE WHEN pt = '20200229000000' AND r0_2.link_date <'2020-03-01' AND substr(job_level_name,1,1) IN ('A','M') AND substr(job_name,1,2) IN ('買賣','綜合') THEN agent_ucid ELSE null END)M_AM_M2
,COUNT(DISTINCT CASE WHEN pt = '20200331000000' AND r0_2.link_date <'2020-04-01' AND substr(job_level_name,1,1) IN ('A','M') AND substr(job_name,1,2) IN ('買賣','綜合') THEN agent_ucid ELSE null END)M_AM_M3
,count(DISTINCT CASE WHEN pt = '20191231000000' AND r0_2.link_date <'2020-01-01' AND substr(job_level_name,1,1) IN ('A','M') THEN agent_ucid ELSE null END)ALL_AM_M12
,count(DISTINCT CASE WHEN pt = '20200131000000' AND r0_2.link_date <'2020-02-01' AND substr(job_level_name,1,1) IN ('A','M') THEN agent_ucid ELSE null END)ALL_AM_M1
,count(DISTINCT CASE WHEN pt = '20200229000000' AND r0_2.link_date <'2020-03-01' AND substr(job_level_name,1,1) IN ('A','M') THEN agent_ucid ELSE null END)ALL_AM_M2
,count(DISTINCT CASE WHEN pt = '20200331000000' AND r0_2.link_date <'2020-04-01' AND substr(job_level_name,1,1) IN ('A','M') THEN agent_ucid ELSE null END)ALL_AM_M3
,count(DISTINCT CASE WHEN pt = '20191231000000' AND r0_2.link_date <'2020-01-01' THEN agent_ucid ELSE null END)ALL_AMS_M12
,count(DISTINCT CASE WHEN pt = '20200131000000' AND r0_2.link_date <'2020-02-01' THEN agent_ucid ELSE null END)ALL_AMS_M1
,count(DISTINCT CASE WHEN pt = '20200229000000' AND r0_2.link_date <'2020-03-01' THEN agent_ucid ELSE null END)ALL_AMS_M2
,count(DISTINCT CASE WHEN pt = '20200331000000' AND r0_2.link_date <'2020-04-01' THEN agent_ucid ELSE null END)ALL_AMS_M3
FROM
(
select * from dwd1 where pt = '20191231000000' and is_agent = 1 and on_job_status = '在職在崗'
UNION ALL
select * from dwd1 where pt = '20200131000000' and is_agent = 1 and on_job_status = '在職在崗'
UNION ALL
select * from dwd1 where pt = '20200229000000' and is_agent = 1 and on_job_status = '在職在崗'
UNION ALL
select * from dwd1 where pt = '20200331000000' and is_agent = 1 and on_job_status = '在職在崗'
)r0_1
LEFT JOIN
(select shop_code,link_date from dwd2 where pt = '20200410000000')r0_2
ON r0_1.shop_code = r0_2.shop_code
GROUP BY r0_1.shop_code
,r0_2.link_date
)r0
)r1
ON d1.shop_code = r1.shop_code
uj5u.com熱心網友回復：
表結構貼一下，要 create table 形式。陳述句太長了，不提供建表陳述句，不會有人看這么長一串內容的。
uj5u.com熱心網友回復：
可是我不建表呀我只查詢。。
uj5u.com熱心網友回復：
幫你把代碼美化了一下， 居然里面還有 IF ……IF 是不可能存在 SELECT 陳述句當中的。
先把 IF 改成 CASE WHEN 結構再說吧。
SELECT r1.*
FROM (
SELECT pt,
corp_name,
shop_code,
shop_name,
shop_first_del_time
FROM rpt1
WHERE pt = '20200331000000'
AND (
corp_name LIKE '%德佑%'
OR corp_name LIKE '%小草%'
OR corp_name LIKE '%聯勝%'
OR corp_name LIKE '%世紀%'
OR corp_name LIKE '%中環%'
)
AND shop_name NOT LIKE '%虛擬%'
AND (
expire_date > '2020-01-01'
OR expire_date = ''
OR expire_date IS NULL
)
)d1
LEFT JOIN (
SELECT shop_code,
IF (
link_date > '2019-12-31',
M_AM_M1,
(M_AM_M12 + M_AM_M1) / 2
)avg_M_AM_M1
,
IF (link_date > '2020-01-31', M_AM_M2, (M_AM_M1 + M_AM_M2) / 2)avg_M_AM_M2
,
IF (link_date > '2020-02-29', M_AM_M3, (M_AM_M2 + M_AM_M3) / 2)
avg_M_AM_M3
,
IF (
link_date > '2019-12-31',
ALL_AM_M1,
(ALL_AM_M12 + ALL_AM_M1) / 2
)avg_ALL_AM_M1
,
IF (
link_date > '2020-01-31',
ALL_AM_M2,
(ALL_AM_M1 + ALL_AM_M2) / 2
)avg_ALL_AM_M2
,
IF (
link_date > '2020-02-29',
ALL_AM_M3,
(ALL_AM_M2 + ALL_AM_M3) / 2
)avg_ALL_AM_M3
,
IF (
link_date > '2019-12-31',
ALL_AMS_M1,
(ALL_AMS_M12 + ALL_AMS_M1) / 2
)avg_ALL_AM_M1
,
IF (
link_date > '2020-01-31',
ALL_AMS_M2,
(ALL_AMS_M1 + ALL_AMS_M2) / 2
)avg_ALL_AM_M2
,
IF (
link_date > '2020-02-29',
ALL_AMS_M3,
(ALL_AMS_M2 + ALL_AMS_M3) / 2
)avg_ALL_AM_M3
,M_AM_M12
,M_AM_M1
,M_AM_M2
,M_AM_M3
,ALL_AM_M12
,ALL_AM_M1
,ALL_AM_M2
,ALL_AM_M3
,ALL_AMS_M12
,ALL_AMS_M1
,ALL_AMS_M2
,ALL_AMS_M3
FROM
(
SELECT r0_1.shop_code,
r0_2.link_date,
COUNT(
DISTINCT CASE
WHEN pt =
'20191231000000'
AND r0_2.link_date <
'2020-01-01'
AND substr(job_level_name, 1, 1) IN ('A', 'M')
AND substr(job_name, 1, 2) IN ('買賣', '綜合')
THEN agent_ucid ELSE NULL
END
)M_AM_M12,
COUNT(
DISTINCT CASE
WHEN pt =
'20200131000000'
AND r0_2.link_date <
'2020-02-01'
AND substr(job_level_name, 1, 1) IN ('A', 'M')
AND substr(job_name, 1, 2) IN ('買賣', '綜合')
THEN agent_ucid ELSE NULL
END
)M_AM_M1,
COUNT(
DISTINCT CASE
WHEN pt =
'20200229000000'
AND r0_2.link_date <
'2020-03-01'
AND substr(job_level_name, 1, 1) IN ('A', 'M')
AND substr(job_name, 1, 2) IN ('買賣', '綜合')
THEN agent_ucid ELSE NULL
END
)M_AM_M2,
COUNT(
DISTINCT CASE
WHEN pt =
'20200331000000'
AND r0_2.link_date <
'2020-04-01'
AND substr(job_level_name, 1, 1) IN ('A', 'M')
AND substr(job_name, 1, 2) IN ('買賣', '綜合')
THEN agent_ucid ELSE NULL
END
)M_AM_M3,
COUNT(
DISTINCT CASE
WHEN pt =
'20191231000000'
AND r0_2.link_date <
'2020-01-01'
AND substr(job_level_name, 1, 1) IN ('A', 'M')
THEN agent_ucid ELSE NULL
END
)ALL_AM_M12,
COUNT(
DISTINCT CASE
WHEN pt =
'20200131000000'
AND r0_2.link_date <
'2020-02-01'
AND substr(job_level_name, 1, 1) IN ('A', 'M')
THEN agent_ucid ELSE NULL
END
)ALL_AM_M1,
COUNT(
DISTINCT CASE
WHEN pt =
'20200229000000'
AND r0_2.link_date <
'2020-03-01'
AND substr(job_level_name, 1, 1) IN ('A', 'M')
THEN agent_ucid ELSE NULL
END
)ALL_AM_M2,
COUNT(
DISTINCT CASE
WHEN pt =
'20200331000000'
AND r0_2.link_date <
'2020-04-01'
AND substr(job_level_name, 1, 1) IN ('A', 'M')
THEN agent_ucid ELSE NULL
END
)ALL_AM_M3,
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/43067.html
標籤：疑難問題