考虑一个为不同生产设施捕获传感器数据的企业。对于每个设施，我们创建一个聚合查询，平均值为5分钟时隙。此查询存在于with子句的长列表之外，并将数据写入表(称为aggregation_table(。
现在我的问题是：目前我们有n个查询运行着完全相同的逻辑，唯一不同的是表名(有时还有列名，但我们暂时忽略它(。
我不想管理n个基本相同的不同脚本，而是想把它放在一个能够像这样工作的存储过程中：
CALL aggregation_query(facility_name(->解析该设施的不同表，然后在不同的with子句中使用它们
最重要的是，我不希望有一长串的子句来给我最终的结果，而是希望将它们组合在可参数化的逻辑块中。因此，例如，如果我为设备A调用前面提到的stored_procedure，我希望能够在这些不同的函数中传递/使用这个表名，其中输出可以在下一个语句中重复使用(就像您对子句所做的那样(。
另一个论点是，为什么我想把它放在可重复使用的块中，是因为我们有很多"；衍生物"；在这个聚合查询上，例如管理历史数据、更正数据或将传感器数据放在另一个聚合级别上。由于这些变得过于复杂，管理它们要容易得多，而不必每次都复制粘贴和调整它们。
在当前的设置中，知道我只能使用普通的BigQuery可能会很有用，因为我的团队不允许访问CI/CD/调度和存储库。(这意味着我无法通过部署n个不同版本的程序和功能的CI/CD来解决问题(
所以最后，我想只使用bigquery来完成这样的事情：
CREATE OR REPLACE PROCEDURE
`aggregation_function`()
BEGIN
DECLARE
tablename STRING;
DECLARE
active_table_name STRING; ##get list OF tables CREATE TEMP TABLE tableNames AS
SELECT
table_catalog,
table_schema,
table_name
FROM
`catalog.schema.INFORMATION_SCHEMA.TABLES`
WHERE
table_name = tablename;
WHILE
(
SELECT
COUNT(*)
FROM
tableNames) >= 1 DO ##build dataset + TABLE name
SET
active_table_name = CONCAT('`',table_catalog,'.',table_schema,'.' ,table_name,'`'); ##use concat TO build string AND execute
EXECUTE IMMEDIATE '''
INSERT INTO
`aggregation_table_for_facility` (timeslot, sensor_name, AVG_VALUE )
WITH
STEP_1 AS (
SELECT
*
FROM
my_table_function_step_1(active_table_name,
parameter1,
parameter2) ),
STEP_2 AS (
SELECT
*
FROM
my_table_function_step_2(STEP_1,
parameter1,
parameter2) )
SELECT * FROM STEP_2
'''
USING active_table_name as active_table_name;
DELETE
FROM
tableNames
WHERE
table_name = tablename;
END WHILE
;
END
;
我希望有人能摘录一下我如何在标准SQL/Bigquery中做到这一点，所以基本上：
存储过程，它接收字符串变量并能够将其用作表(在上述方法中部分解决，但不确定是否有更好的方法(
(table(函数，它也可以接受这个table_name参数，并返回一个可以在下一个with子句中使用的表(或者写入临时表(
我认为下面的代码片段应该在处理过程、插入和执行即时语句时为您提供一些见解。
在这里，我将创建一个过程，该过程将把值插入到信息模式中存在的表中。此外，作为我要返回的值，我使用OUTactive_table_name返回我在过程中分配的值。
CREATE OR REPLACE PROCEDURE `project-id.dataset`.custom_function(tablename STRING,OUT active_table_name STRING)
BEGIN
DECLARE query STRING;
SET active_table_name= (SELECT CONCAT('`',table_catalog,'.',table_schema,'.' ,table_name,'`')
FROM `project-id.dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_name = tablename);
#multine query can be handled by using ''' or """
Set query =
'''
insert into %s (string_field_0,string_field_1,string_field_2,string_field_3,string_field_4,int64_field_5)
with custom_query as (
select string_field_0,string_field_2,'169 BestCity',string_field_3,string_field_4,55677 from %s limit 1
)
select * from custom_query;
''';
# querys must perform operations and must be the last thing to perform
# pass parameters using format
execute immediate (format(query,active_table_name,active_table_name));
END
您还可以使用循环来迭代工作表中的记录，这样它就可以执行该过程，还可以从该过程中获取值以在其他地方使用。例如：执行删除操作的第二个过程。
DECLARE tablename STRING;
DECLARE out_value STRING;
FOR record IN
(SELECT tablename from `my-project-id.dataset.table`)
DO
SET tablename = record.tablename;
LOOP
call `project-id.dataset`.custom_function(tablename,out_value);
select out_value;
END LOOP;
END FOR;
概括一下，有一些限制，例如在execute immediate中调用过程的可能性，或者在execute立即命令中使用execute immmediate的可能性，等等。我认为这些片段应该有助于你处理目前的情况。
对于这个示例，我使用以下文档：
- 数据操作语言
- 处理输出
- 信息架构表
- 立即执行
- 对于。。。在
- 循环