GBQ 转换数据类型 en Mass [英] GBQ Convert Data types en Mass

问题描述

我有一个包含许多列和各种数字数据类型的表格.我试图找出一种方法将所有这些数据类型转换为相同的数字类型，而无需单独列出每个数据类型并执行

I have a table wth many columns and various numerical data types. I am trying to figure out a way to convert all these data types to the same numeric type without listing each one idivudally and doing

SELECT CAST(column1 as Numeric), CAST(column2 as Numeric), 等等...

样本数据

在此示例中，Group 将保持不变，而 Apple、Bananas 和 Orange 将全部转换为相同的数据类型.

In this example, Group would stay the same and Apple, Bananas and Orange would all be converted to the same data type.

推荐答案

使用下面的方法

execute immediate (select '''
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as Numeric) 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`''');     

如果应用于您问题中的示例数据(请注意，我在上面的脚本中将 Numeric 更改为 Float64 以在下面生成，因此转换结果更明显)- 输出为

if applied to sample data in your question (note I changed Numeric to Float64 in above script to produce below so result of casting is more visible) - output is

这篇关于GBQ 转换数据类型 en Mass的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！