BigQuery多子查询运行报错：ICUSTAY_ID列存在歧义问题求助

错误原因 #

• 你执行t1 LEFT JOIN t2时使用SELECT *返回两个表的所有字段，t1（来自ICUSTAYS表）和t2（来自CHARTEVENTS表）都存在ICUSTAY_ID字段，导致最终生成的CTE t 里有两个重名的ICUSTAY_ID，数据库无法判断你要选择哪一个，因此触发歧义报错。
• 额外语法问题：你写的t2查询语句中VALUE,后面多了一个多余的逗号，也会触发语法错误，需要一并删除。

修复方案 #

推荐优先明确指定JOIN后返回的字段，避免冗余重名字段，修正后的完整代码如下：

WITH t AS 
(
SELECT 
*
FROM
    (
    SELECT *,
DATETIME_DIFF(CHARTTIME, INTIME, MINUTE) AS pi_recorded 
FROM 
        (
        SELECT 
        -- 明确指定字段，仅保留左表t1的ICUSTAY_ID避免重名
        t1.SUBJECT_ID, t1.dob, t1.hadm_id, t1.GENDER, t1.ETHNICITY, t1.ADMITTIME, 
        t1.INSURANCE, t1.ICUSTAY_ID, t1.DBSOURCE, t1.INTIME, t1.age, t1.age_group,
        t2.ITEMID, t2.CHARTTIME, t2.VALUE
        FROM 
            (
            SELECT * FROM
                (SELECT i.SUBJECT_ID, p.dob, i.hadm_id, p.GENDER, a.ETHNICITY, a.ADMITTIME, a.INSURANCE, i.ICUSTAY_ID,
                i.DBSOURCE, i.INTIME, DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) AS age, 
                    CASE 
                WHEN DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) <= 32485  
                    THEN 'adult' 
                WHEN DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) > 32485  
                    then '>89'
                END AS age_group
                FROM `project.mimic3.ICUSTAYS` AS i
                INNER JOIN `project.mimic3.PATIENTS` AS p ON i.SUBJECT_ID = p.SUBJECT_ID  
                INNER JOIN `project.mimic3.ADMISSIONS` AS a ON i.HADM_ID = a.HADM_ID)
                WHERE age >= 6570
            ) AS t1
                LEFT JOIN 
                (
                -- 删掉了VALUE后多余的逗号，同时将多个OR替换为IN简化语法
                SELECT ITEMID, ICUSTAY_ID, CHARTTIME, VALUE FROM `project.mimic3.CHARTEVENTS` 
                WHERE ITEMID IN (551,552,553,224631,224965,224966)
                ) AS t2
                ON t1.ICUSTAY_ID = t2.ICUSTAY_ID
        )
    ) 
WHERE ITEMID IN (552, 553, 224965, 224966) AND pi_recorded <= 1440
)
SELECT ICUSTAY_ID
FROM t  
GROUP BY ICUSTAY_ID;

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