PySpark SQL报missing ')' at 'in'语法错误，请问如何排查？

错误根因定位 #

该报错属于语法识别错误引发的连锁报错，实际错误有2处：

• 字段拼写错误：REGEXP_REPLACE(cte1.indicatio,'[^a-zA-Z0-9]+', '') 中字段名少写了末尾的n，正确应为cte1.indication
• 分组语法错误：第二个子查询中groupby是非法连写，标准SQL分组语句为空格分隔的GROUP BY，解析器识别到非法关键字后无法匹配语法结构，最终误报缺失右括号。

修正后完整查询语句 #

with cte1 as (
    select 
        `Project Number`, 
        indication,
        rank() over (
            partition by `Project Number`, REGEXP_REPLACE(indication,'[^a-zA-Z0-9]+', '') 
            order by `Project Number`, indication
        ) as rnk 
    from (
        select distinct `Project Number`, indication from vw_onco_pharma onco_pharma 
        union 
        select distinct `Project Number`, indication from vw_onco_cell_gene cell_gene 
        union 
        select distinct `Project Number`, indication from vw_non_onco_cell_gene onco_cell_gene 
        union 
        select distinct `Project Number`, indication from vw_non_onco_pharma non_onco_pharma 
        union 
        select distinct `Project Number`, indication from vw_plasma_protein plasma_protein
    )
),
y as (
    select max(cast(project_id as integer)) as max_prj_id from vw_project_id
) 
select 
    nvl(max_prj_id,0) + ROW_NUMBER () OVER (ORDER BY `Project Number`, indication) as project_id,
    `Project Number`,
    indication,
    date_format(current_timestamp(),'yyyy-MM-dd hh:mm:ss') as HTA_INSERT_DT 
from (
    select 
        cte1.`Project Number`, 
        cte1.indication,
        max_prj_id 
    from cte1 
    left join vw_project_id prj 
        on cte1.`Project Number` = prj.`Project Number` 
        and REGEXP_REPLACE(cte1.indication,'[^a-zA-Z0-9]+', '') = REGEXP_REPLACE(prj.indication,'[^a-zA-Z0-9]+', '') 
    left join y on 1 = 1 
    where rnk = 1 
        and prj.project_id is null 
        and cte1.`project number` in (
            select `project number` from cte1 group by `project number` having count(*) > 1
        )
    union 
    select 
        cte1.`Project Number`, 
        null as indication,
        max_prj_id 
    from cte1 
    left join vw_project_id prj on cte1.`Project Number` = prj.`Project Number` 
    left join y on 1 = 1 
    where rnk = 1 
        and prj.project_id is null 
        and cte1.`project number` in (
            select `project number` from cte1 group by `project number` having count(*) = 1
        )
)

内容的提问来源于stack exchange，提问作者Shivika