循环循环列并用bigquery中的零替换零值的程序化方法？

发布于 2025-01-27 22:45:29 字数 2260 浏览 2 评论 0原文

我正在尝试在BigQuery中准备一个大数据表，以进行回归，该回归涉及许多“虚拟”（又称分类）变量。

在此过程中的最后一步之一要求我用零在表中有效地替换表中的所有零值实例。

在大查询中是否有干净，程序化的方式？例如，在下表中，我理想地希望循环在所有“ country_*”字段上，并以非硬编码方式替换为零。我有一个暗示，这可能是动态SQL的工作，但是在文档中我会迷路。任何帮助将不胜感激！

TLDR：这是我面对的数据结构的一个示例。

country	country_1	country_2	country_3
1	1--	变量	协
2-1-
3	-	1-1	1

这是我想拥有的

国家	country_1	country_2	country_3
1	0	0	2
0	1	0 1	0
3	0	0	1

Simpleton方法：

select country, 
       ifnull(country_1, 0) as country_1,
       ...
FROM TABLE

原文

I am trying to prepare a large data table in BigQuery for a regression that involves lots of "dummy" (aka categorical) variables.

One of final steps in this process requires me to effectively replace all instances of null values in the table with zeros.

Is there a clean and programmatic way to do this in Big Query? For example, in the table below, I'd ideally like to loop over all the "country_*" fields, and replace with zero in a non hard coded fashion. I have an inkling that this may be a job for dynamic SQL, but I get pretty lost swimming in the documentation. Any help would be greatly appreciated!

TLDR: This is an example of the data structure I'm facing.

country	country_1	country_2	country_3
1	1	-	-
2	-	1	-
3	-	-	1

This is what I'd like to have

country	country_1	country_2	country_3
1	1	0	0
2	0	1	0
3	0	0	1

Simpleton method:

select country, 
       ifnull(country_1, 0) as country_1,
       ...
FROM TABLE

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

油焖大侠 2025-02-03 22:45:29

请尝试

create temp function  extract_keys(input string) returns array<string> language js as "return Object.keys(JSON.parse(input));";
create temp function  extract_values(input string) returns array<string> language js as "return Object.values(JSON.parse(input));";
select * except(json)
from (
  select json, col, val
  from your_table t,
  unnest([struct(replace(to_json_string(t), ':null', ':0') as json)]),
  unnest(extract_keys(json)) col with offset
  join unnest(extract_values(json)) val with offset
  using(offset)
)
pivot (any_value(val) for col in ('country', 'country_1', 'country_2', 'country_3'))

如果应用于您的问题中的示例数据，

- 输出为

Try below

create temp function  extract_keys(input string) returns array<string> language js as "return Object.keys(JSON.parse(input));";
create temp function  extract_values(input string) returns array<string> language js as "return Object.values(JSON.parse(input));";
select * except(json)
from (
  select json, col, val
  from your_table t,
  unnest([struct(replace(to_json_string(t), ':null', ':0') as json)]),
  unnest(extract_keys(json)) col with offset
  join unnest(extract_values(json)) val with offset
  using(offset)
)
pivot (any_value(val) for col in ('country', 'country_1', 'country_2', 'country_3'))

if applied to sample data in your question - output is