如何在BigQuery中基于动态数据集关联两张表？

由于config_files表的名称是由workflow.configs的dataset字段动态决定的，常规静态SQL无法直接合并两次查询，但可以通过动态SQL结合数据库特性实现一次性查询，以下是主流数据库的具体实现：

PostgreSQL 实现 #

利用LATERAL连接配合EXECUTE动态执行SQL，同时关联workflow.configs的所有字段：

WITH config_records AS (
    SELECT * FROM workflow.configs
)
SELECT cr.*, cf.* 
FROM config_records cr
JOIN LATERAL (
    -- 使用%I转义表名，避免SQL注入
    EXECUTE format('SELECT * FROM %I.config_files', cr.dataset)
) cf ON true;

如果只需要聚合所有config_files数据并关联对应的dataset名称：

WITH distinct_datasets AS (
    SELECT DISTINCT dataset FROM workflow.configs
)
SELECT dd.dataset, cf.* 
FROM distinct_datasets dd
JOIN LATERAL (
    EXECUTE format('SELECT * FROM %I.config_files', dd.dataset)
) cf ON true;

MySQL 实现 #

通过存储过程遍历dataset并拼接动态SQL：

DELIMITER //
CREATE PROCEDURE FetchAllConfigFiles()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE target_dataset VARCHAR(255);
    DECLARE dataset_cursor CURSOR FOR SELECT DISTINCT dataset FROM workflow.configs;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    
    SET @combined_sql = '';
    
    OPEN dataset_cursor;
    dataset_loop: LOOP
        FETCH dataset_cursor INTO target_dataset;
        IF finished = 1 THEN
            LEAVE dataset_loop;
        END IF;
        -- 用反引号转义表名，拼接UNION ALL语句
        SET @combined_sql = CONCAT(
            @combined_sql,
            IF(@combined_sql = '', '', ' UNION ALL '),
            'SELECT ''', target_dataset, ''' AS source_dataset, * FROM `', target_dataset, '`.`config_files`'
        );
    END LOOP;
    CLOSE dataset_cursor;
    
    PREPARE stmt FROM @combined_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL FetchAllConfigFiles();

注意事项 #

• 必须使用数据库提供的表名转义机制（如PostgreSQL的%I、MySQL的反引号），避免SQL注入风险。
• 若dataset数量过多，动态SQL的性能可能不如分批次查询，需根据实际数据量权衡。
• 执行查询的用户需具备所有动态config_files表的读取权限。

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