BigQuery自连接无结果求助：WITH子句链内执行异常

问题场景 #

在BigQuery的WITH子句链中，对filtered表执行自连接查询时返回空结果，但将filtered表导出到临时数据集后执行相同自连接逻辑却能正常得到结果。需要在WITH子句内完成该自连接操作，最终SQL会在Spring Boot应用中执行，结果存入Redis。

用户自连接代码片段：

trigger_recos AS (
     SELECT
         T.session_id trigger_session
        ,T.epoch trigger_epoch
        ,T.categoryId trigger_categoryId
        ,T.refinements_str trigger_refinements_str
        ,R.session_id reco_session
        ,R.epoch reco_epoch
        ,R.categoryId reco_categoryId
        ,R.refinements_str reco_refinements_str
     FROM filtered T
     JOIN filtered R
     ON T.session_id = R.session_id
 )
SELECT * from trigger_recos;

可能原因及解决方法 #

1. WITH子句内临时表未物化导致数据不一致 #

BigQuery默认可能对WITH子句中的临时表做懒加载处理，两次引用filtered时可能因为计算逻辑的动态性（比如分区裁剪、实时数据变化）导致数据集合不匹配。

解决方法：给filtered添加AS MATERIALIZED修饰符，强制BigQuery将该临时表物化，确保两次引用的是同一批数据：

filtered AS MATERIALIZED (
    -- 此处为原filtered表的查询逻辑
),
trigger_recos AS (
    SELECT
        T.session_id trigger_session
       ,T.epoch trigger_epoch
       ,T.categoryId trigger_categoryId
       ,T.refinements_str trigger_refinements_str
       ,R.session_id reco_session
       ,R.epoch reco_epoch
       ,R.categoryId reco_categoryId
       ,R.refinements_str reco_refinements_str
    FROM filtered T
    JOIN filtered R
    ON T.session_id = R.session_id
)
SELECT * from trigger_recos;

2. 连接字段数据类型隐式不匹配 #

WITH子句中动态计算的filtered表，其session_id字段可能存在类型模糊（比如导出临时表时自动完成了类型转换），导致自连接时因类型不匹配无法匹配数据。

解决方法：显式转换字段类型，确保连接两边类型一致：

trigger_recos AS (
    SELECT
        T.session_id trigger_session
       ,T.epoch trigger_epoch
       ,T.categoryId trigger_categoryId
       ,T.refinements_str trigger_refinements_str
       ,R.session_id reco_session
       ,R.epoch reco_epoch
       ,R.categoryId reco_categoryId
       ,R.refinements_str reco_refinements_str
    FROM filtered T
    JOIN filtered R
    ON CAST(T.session_id AS STRING) = CAST(R.session_id AS STRING) -- 根据实际类型调整，比如INT64
)
SELECT * from trigger_recos;

3. 查询优化器执行计划异常 #

BigQuery的优化器可能对WITH子句内的自连接做了不合理的逻辑裁剪，导致数据被错误过滤。

解决方法：拆分WITH子句结构，强制优化器重新生成执行计划：

filtered_materialized AS (
    SELECT * FROM filtered
),
trigger_recos AS (
    SELECT
        T.session_id trigger_session
       ,T.epoch trigger_epoch
       ,T.categoryId trigger_categoryId
       ,T.refinements_str trigger_refinements_str
       ,R.session_id reco_session
       ,R.epoch reco_epoch
       ,R.categoryId reco_categoryId
       ,R.refinements_str reco_refinements_str
    FROM filtered_materialized T
    JOIN filtered_materialized R
    ON T.session_id = R.session_id
)
SELECT * from trigger_recos;

4. NULL值导致连接失效 #

filtered表中session_id字段的NULL值，可能在WITH子句的两次查询中处理逻辑不同，导致连接时无法匹配。

解决方法：在连接条件中排除NULL值：

trigger_recos AS (
    SELECT
        T.session_id trigger_session
       ,T.epoch trigger_epoch
       ,T.categoryId trigger_categoryId
       ,T.refinements_str trigger_refinements_str
       ,R.session_id reco_session
       ,R.epoch reco_epoch
       ,R.categoryId reco_categoryId
       ,R.refinements_str reco_refinements_str
    FROM filtered T
    JOIN filtered R
    ON T.session_id = R.session_id
    WHERE T.session_id IS NOT NULL AND R.session_id IS NOT NULL
)
SELECT * from trigger_recos;

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