BigQuery存储过程按日期和渠道过滤数据失效问题求助

核心诱因是BigQuery的SQL标识符解析优先级规则：当查询语句中的标识符可同时匹配列名、存储过程入参/内部变量时，会优先匹配表的列名。即使入参名和列名不完全一致，也可能因为大小写不敏感、拼写近似导致解析器误判，最终过滤条件被识别为列=列的恒真条件，就会返回全表数据，看起来像过滤完全未生效。

给入参加上统一前缀避免解析冲突即可解决问题，修改后的存储过程定义如下：

CREATE OR REPLACE PROCEDURE `sandbox.clients.dateandchannel`
    (IN p_purchase_dt DATE, 
     IN p_channel STRING)
BEGIN
    SELECT 
        client_id,
        purchase_id,
        purchase_date,
        channel
    FROM 
        `trusted-zone.clients_purchases`
    WHERE 
        channel = p_channel
        AND purchase_date = p_purchase_dt;
END

调用代码同步调整参数即可：

CALL `sandbox.clients.dateandchannel`('2021-09-11','Ecommerce');

方案1：存储过程内直接生成临时表 #

如果坚持使用存储过程，可以在存储过程内部直接创建会话级临时表，临时表在当前会话有效期内可以任意调用，支持WITH子句等多步查询场景：

CREATE OR REPLACE PROCEDURE `sandbox.clients.dateandchannel`
    (IN p_purchase_dt DATE, 
     IN p_channel STRING)
BEGIN
    -- 临时表会话结束自动删除，无需手动清理
    CREATE OR REPLACE TEMP TABLE temp_filtered_purchases AS
    SELECT 
        client_id,
        purchase_id,
        purchase_date,
        channel
    FROM 
        `trusted-zone.clients_purchases`
    WHERE 
        channel = p_channel
        AND purchase_date = p_purchase_dt;
END

使用方式：

-- 调用存储过程生成临时表
CALL `sandbox.clients.dateandchannel`('2021-09-11','Ecommerce');

-- 后续任意查询都可直接调用临时表
WITH step1 AS (
    SELECT * FROM temp_filtered_purchases
    -- 补充自定义处理逻辑
)
SELECT * FROM step1;

方案2：使用表值函数（更推荐） #

你当前的需求是返回可复用的查询结果集，用表值函数（TVF）比存储过程更灵活，不需要CALL调用，可直接在查询语句中作为表使用，天然支持WITH子句：

CREATE OR REPLACE TABLE FUNCTION `sandbox.clients.dateandchannel`(p_purchase_dt DATE, p_channel STRING)
AS (
    SELECT 
        client_id,
        purchase_id,
        purchase_date,
        channel
    FROM 
        `trusted-zone.clients_purchases`
    WHERE 
        channel = p_channel
        AND purchase_date = p_purchase_dt
);

使用方式：

WITH step1 AS (
    SELECT * FROM `sandbox.clients.dateandchannel`('2021-09-11','Ecommerce')
    -- 补充自定义处理逻辑
)
SELECT * FROM step1;

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