GBQ立即执行CTE

发布于 2025-01-28 11:29:09 字数 1001 浏览 3 评论 0原文

我正在为没有写入数据库的用户构建应用程序/脚本。通常，我会立即使用执行并将结果保存到表中，然后从该表拉动并继续使用脚本。是否有一种方法可以保存从子查询或CTE中立即执行的结果，以便脚本可以继续？

将结果放入表中的示例代码，

execute immediate (select '''create or replace table `project.dataset.table2` as 
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as float64) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''')
;

我将需要更多的内容，但它不起作用

WITH CTEtable2 as (
execute immediate (select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as float64) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''')
)

SELECT *
FROM CTEtable2

原文

I am building an application/script for users that do not have write access to the database. Normally I would use Execute Immediate and save that result into a table, and then pull from that table and continue on with the script. Is there a way to save results from Execute Immediate in either a sub query or CTE so that the script can continue on?

Example code where results are put into a table

execute immediate (select '''create or replace table `project.dataset.table2` as 
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as float64) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''')
;

I would need something more along the lines of this, but it doesn't work

WITH CTEtable2 as (
execute immediate (select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as float64) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''')
)

SELECT *
FROM CTEtable2

分享到QQ

分享到微博