BigQuery 使用条件从其他表创建表（管理大量列）

我面临与我的项目相关的问题。这是我想做的事情的摘要：

我有一个很大的每日文件（100 Go），其中包含以下摘录（无标题）：

ID_A|segment_1
ID_A|segment_2
ID_B|segment_2
ID_B|segment_3
ID_B|segment_4
ID_B|segment_5
ID_C|segment_1
ID_D|segment_2
ID_D|segment_4

每个 ID（从 A 到 D）都可以链接到一个或多个段（从 1 到 5）。

我想处理此文件以获得以下结果（结果文件包含标头）：

ID|segment_1|segment_2|segment_3|segment_4|segment_5
ID_A|1|1|0|0|0
ID_B|0|1|1|1|1
ID_C|1|0|0|0|0
ID_D|0|1|0|1|0

1表示ID包含在段中，0意味着事实并非如此。

我正在使用以下查询来获取结果：

select id,
       countif(segment = 'segment_1') as segment_1,
       countif(segment = 'segment_2') as segment_2,
       countif(segment = 'segment_3') as segment_3,
       countif(segment = 'segment_4') as segment_4,
       countif(segment = 'segment_5') as segment_5
from staging s cross join
     unnest(split(segments, ',')) as segment
group by id;

这个解决方案对我一直有效，直到段数变得更多（900+段，而不是我的第一个示例中的 5）。这正在创建一个巨大的查询，无法作为参数传递bq cli.

有什么我可以使用的解决方法吗？

感谢大家的帮助。

Regards

以下是 BigQuery 标准 SQL

EXECUTE IMMEDIATE '''
SELECT id, ''' || (
  SELECT STRING_AGG("COUNTIF(segment = '" || segment || "') AS " || segment ORDER BY segment)
  FROM (SELECT DISTINCT segment FROM staging)  
) || '''  
FROM staging
GROUP BY 1
ORDER BY 1
'''     

如果适用于您的问题中的样本数据 - 输出是

Row id      segment_1   segment_2   segment_3   segment_4   segment_5    
1   ID_A    1           1           0           0           0    
2   ID_B    0           1           1           1           1    
3   ID_C    1           0           0           0           0    
4   ID_D    0           1           0           1           0  

正如您所看到的，您无需担心段的数量和命名 - 它由上面的查询处理