我正在尋找一種方法來遍歷 bigquery 陣列并將陣列值用作回圈內 select 陳述句中的列名。以下是我嘗試過的示例
declare dimensions array<string>;
set dimensions = ["ad_group", "campaign", "campaign_type"];
for x in (select * from unnest(dimensions) as value)
do
SELECT week, x.value as dimension_name, USE ARRAY VALUE HERE AS COLUMN NAME, sum(spend) as spend FROM `table` group by 1,2,3;
end for;
陣列中的每個元素都是我從中選擇的表中的列名，除了它們所代表的列之外，我似乎無法將陣列元素參考為字串之外的任何內容。這在 BigQuery 中可行嗎？
uj5u.com熱心網友回復：
使用以下方法
declare dimensions array<string>;
set dimensions = ["ad_group", "campaign", "campaign_type"];
for x in (select * from unnest(dimensions) as value)
do
execute immediate 'select week, "' || x.value || '" as dimension_name, ' || x.value || ', sum(spend) as spend FROM `your_table` group by 1,2,3';
end for;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/414900.html
標籤：
上一篇：如何快速解碼陣列？