在BigQuery的CTE中使用row_number()出现未识别名称错误如何解决？

错误原因汇总 #

两个写法的报错都是由SQL作用域规则、字段引用错误和语法细节问题导致的，具体问题如下：

• 多余尾部逗号：两处写法的SELECT字段列表最后一个字段后都加了多余的逗号，不符合SQL语法要求。
• 作用域引用错误：第一个写法中，你在最外层SELECT直接用BQ.xxx引用字段，但此时BQ表的作用域仅在子查询内部，最外层查询的数据源是内层子查询的结果，并没有名为BQ的表，因此识别失败；第二个写法同理，最外层查询的数据源是内层行号子查询的结果，并没有名为flat的表，所以无法识别flat.orderId_bq。
• 子查询字段缺失：第一个写法的内层子查询仅返回了RN和orderId_bq两个字段，就算引用正确也拿不到event_action、trx_id等其他字段。

修正后可运行的SQL #

不需要额外嵌套flat CTE，直接在原有BQ CTE的基础上加窗口函数即可：

WITH BQ AS(
    SELECT 
        (SELECT customDimensions.value FROM UNNEST(t.customDimensions)
        AS customDimensions WHERE customDimensions.index = 6) as orderId_bq, 
        hits.eventinfo.eventaction as event_action, 
        hits.transaction.transactionId as trx_id, 
        hits.page.pagePath as page, 
        hitnumber AS hitnumber 
    FROM `xxxxx-xxxx.xxxxxx.ga_sessions_20210801` t,
    UNNEST(HITS) as hits 
    WHERE (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 8) = 'se'
    AND (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 4) = 'soffadirekt'
)
SELECT 
    orderId_bq,
    event_action,
    trx_id,
    page,
    hitnumber,
    ROW_NUMBER() OVER(PARTITION BY orderId_bq ORDER BY hitnumber) as RN
FROM BQ

如果需要保留行号筛选的逻辑（比如只取每个orderId的第一条记录），再加一层嵌套即可：

WITH BQ AS(
    SELECT 
        (SELECT customDimensions.value FROM UNNEST(t.customDimensions)
        AS customDimensions WHERE customDimensions.index = 6) as orderId_bq, 
        hits.eventinfo.eventaction as event_action, 
        hits.transaction.transactionId as trx_id, 
        hits.page.pagePath as page, 
        hitnumber AS hitnumber 
    FROM `xxxxx-xxxx.xxxxxx.ga_sessions_20210801` t,
    UNNEST(HITS) as hits 
    WHERE (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 8) = 'se'
    AND (SELECT customDimensions.value FROM UNNEST(t.customDimensions) AS customDimensions WHERE customDimensions.index = 4) = 'soffadirekt'
),
ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY orderId_bq ORDER BY hitnumber) as RN
    FROM BQ
)
SELECT * FROM ranked
-- 可以加筛选条件 比如 WHERE RN = 1 取每个orderId的第一条命中记录

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