an Error prompt is cluster QUERY ENGINE: unknown exception: [grammatical errors, please check the SQL] : com. Lianjia. Bigdata. Queryengine. Exception. QueryEngineException: Error while compiling the statement: FAILED: SemanticException [Error 10007] : Ambiguous column reference avg_all_am_m1 r1 hint information: in your SQL does not conform to the grammar specification, please according to the SQL standard guide to modify SQL



SELECT
R1. *
The FROM (
SELECT
Pt
, corp_name
, shop_code
And shop_name
, shop_first_del_time
The FROM rpt1
WHERE pt='20200331000000'
AND (corp_name LIKE '% DE on %' OR corp_name LIKE '% % grass OR corp_name LIKE' % % league wins OR corp_name LIKE '% s' OR corp_name LIKE' % %, central ')
Virtual AND shop_name NOT LIKE '% %'
AND (expire_date & gt; '2020-01-01' OR expire_date=' 'OR expire_date IS null)
D1)
Left the JOIN
(
SELECT
Shop_code
, the IF (link_date & gt; '2019-12-31', M_AM_M1, (M_AM_M12 + M_AM_M1)/2) avg_M_AM_M1
, the IF (link_date & gt; '2020-01-31', M_AM_M2, (M_AM_M1 + M_AM_M2)/2) avg_M_AM_M2
, the IF (link_date & gt; '2020-02-29', M_AM_M3, (M_AM_M2 + M_AM_M3)/2) avg_M_AM_M3
, the IF (link_date & gt; '2019-12-31', ALL_AM_M1, (ALL_AM_M12 + ALL_AM_M1)/2) avg_ALL_AM_M1
, the IF (link_date & gt; '2020-01-31', ALL_AM_M2, (ALL_AM_M1 + ALL_AM_M2)/2) avg_ALL_AM_M2
, the IF (link_date & gt; '2020-02-29', ALL_AM_M3, (ALL_AM_M2 + ALL_AM_M3)/2) avg_ALL_AM_M3
, the IF (link_date & gt; '2019-12-31', ALL_AMS_M1, (ALL_AMS_M12 + ALL_AMS_M1)/2) avg_ALL_AM_M1
, the IF (link_date & gt; '2020-01-31', ALL_AMS_M2, (ALL_AMS_M1 + ALL_AMS_M2)/2) avg_ALL_AM_M2
, the IF (link_date & gt; '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
The FROM
(
SELECT
R0_1. Shop_code
, r0_2 link_date
, AND COUNT (DISTINCT CASE WHEN pt='20191231000000' AND r0_2. Link_date & lt; '2020-01-01' AND substr (job_level_name, 1, 1) IN (' A ', 'M') AND substr (job_name, 1, 2) IN (' business ', 'comprehensive') THEN agent_ucid ELSE null END) M_AM_M12
, AND COUNT (DISTINCT CASE WHEN pt='20200131000000' AND r0_2. Link_date & lt; '2020-02-01' AND substr (job_level_name, 1, 1) IN (' A ', 'M') AND substr (job_name, 1, 2) IN (' business ', 'comprehensive') THEN agent_ucid ELSE null END) M_AM_M1
, AND COUNT (DISTINCT CASE WHEN pt='20200229000000' AND r0_2. Link_date & lt; '2020-03-01' AND substr (job_level_name, 1, 1) IN (' A ', 'M') AND substr (job_name, 1, 2) IN (' business ', 'comprehensive') THEN agent_ucid ELSE null END) M_AM_M2
, AND COUNT (DISTINCT CASE WHEN pt='20200331000000' AND r0_2. Link_date & lt; '2020-04-01' AND substr (job_level_name, 1, 1) IN (' A ', 'M') AND substr (job_name, 1, 2) IN (' business ', 'comprehensive') THEN agent_ucid ELSE null END) M_AM_M3
, AND count (DISTINCT CASE WHEN pt='20191231000000' AND r0_2. Link_date & lt; '2020-01-01' AND substr (job_level_name, 1, 1) IN (' A ', 'M') THEN agent_ucid ELSE null END) ALL_AM_M12
, AND count (DISTINCT CASE WHEN pt='20200131000000' AND r0_2. Link_date & lt; '2020-02-01' AND substr (job_level_name, 1, 1) IN (' A ', 'M') THEN agent_ucid ELSE null END) ALL_AM_M1
, AND count (DISTINCT CASE WHEN pt='20200229000000' AND r0_2. Link_date & lt; '2020-03-01' AND substr (job_level_name, 1, 1) IN (' A ', 'M') THEN agent_ucid ELSE null END) ALL_AM_M2
, AND count (DISTINCT CASE WHEN pt='20200331000000' AND r0_2. Link_date & lt; '2020-04-01' AND substr (job_level_name, 1, 1) IN (' A ', 'M') THEN agent_ucid ELSE null END) ALL_AM_M3
, AND count (DISTINCT CASE WHEN pt='20191231000000' AND r0_2. Link_date & lt; '2020-01-01' THEN agent_ucid ELSE null END) ALL_AMS_M12
, AND count (DISTINCT CASE WHEN pt='20200131000000' AND r0_2. Link_date & lt; '2020-02-01' THEN agent_ucid ELSE null END) ALL_AMS_M1
, AND count (DISTINCT CASE WHEN pt='20200229000000' AND r0_2. Link_date & lt; '2020-03-01' THEN agent_ucid ELSE null END) ALL_AMS_M2
, AND count (DISTINCT CASE WHEN pt='20200331000000' AND r0_2. Link_date & lt; '2020-04-01' THEN agent_ucid ELSE null END) ALL_AMS_M3
The FROM
(
Select * from dwd1 where pt='20191231000000' and is_agent=1 and on_job_status='on-the-job on-the-job'
UNION ALL
Select * from dwd1 where pt='20200131000000' and is_agent=1 and on_job_status='on-the-job on-the-job'
UNION ALL
Select * from dwd1 where pt='20200229000000' and is_agent=1 and on_job_status='on-the-job on-the-job'
UNION ALL
Select * from dwd1 where pt='20200331000000' and is_agent=1 and on_job_status='on-the-job on-the-job'
) r0_1
LEFT the 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

CodePudding user response:

The table structure to stick on the create table to form,

Sentence is too long, does not provide building table statements, won't someone see so long a string of content,

CodePudding user response:

reference 1st floor selling fruit net reply:

table structure to stick, to create table form,

Sentence is too long, does not provide building table statements, won't someone see so long a string of content,

But I don't build table I just query,,nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull