BigQuery重塑表格，结构从宽到长 [英] BigQuery reshape table with structs from wide to long
问题描述
删除了该帖子的先前版本，以替代此清理过的帖子，并提供了一个可复制的示例.我有以下格式的表格:
Deleted the previous version of this post in lieu of this cleaned up posting with a reproducible example. I have a table of the following format:
WITH wide_stats AS (
(
SELECT
'joe' name, 'bills' team,
struct(struct(7 as fga) as o, struct(8 as fga) as d) as t1,
struct(struct(3 as fga) as o, struct(9 as fga) as d) as t2,
struct(3 as pts, 9 as ast, 5 as reb) as t3,
7 tov, 3 blk
) UNION ALL (
SELECT 'nick' name, 'jets' team,
struct(struct(12 as fga) as o, struct(13 as fga) as d) as t1,
struct(struct(15 as fga) as o, struct(22 as fga) as d) as t2,
struct(13 as pts, 5 as ast, 15 as reb) as t3,
75 tov, 23 blk
)
)
SELECT
name, team, metric, SAFE_CAST(value AS FLOAT64) value
FROM (
SELECT
name, team,
REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(0)], r'^"|"$', '') metric,
REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(1)], r'^"|"$', '') value
FROM wide_stats,
UNNEST(SPLIT(REGEXP_REPLACE(to_json_string(wide_stats), r'{|}', ''))) pair
)
WHERE NOT LOWER(metric) IN ('name', 'team')
我正在将表格重塑为以下输出:
and I am working to reshape the table into the following output:
name team metric value
joe bills t1_o_fga 7
joe bills t1_d_fga 8
joe bills t2_o_fga 3
joe bills t2_d_fga 9
joe bills t3_pts 3
joe bills t3_ast 9
joe bills t3_reb 5
joe bills tov 7
joe bills blk 3
nick jets t1_o_fga 12
nick jets t1_d_fga 13
nick jets t2_o_fga 15
nick jets t2_d_fga 22
nick jets t3_pts 13
nick jets t3_ast 5
nick jets t3_reb 15
nick jets tov 75
nick jets blk 23
任务很容易解释-从宽到长，但表中有 struct 和嵌套的 struct .我在另一个stackoveflow帖子中所做的正则表达式工作是用错误的方式拆分了列名，并且当前输出与需要的方式不匹配.
The task is simple to explain - from wide to long, but with struct and nested structs in the table. My regex effort from another stackoveflow post is splitting up the column names in the wrong way, and the current output doesn't match how it needs to be.
行的顺序无关紧要.对于名称，只要有分隔符并且清楚地知道变量是什么，它的t1_o_fga还是t1-o-fga还是t1/o/fga都没有关系.非常感谢您的帮助或指导，谢谢！
Order of rows doesn't matter. With the names, doesn't matter if its t1_o_fga or t1-o-fga or t1/o/fga, so long as there's some separator and its clear what the variable is. Any help or direction is super appreciated, thanks!
推荐答案
以下是BigQuery标准SQL
Below is for BigQuery Standard SQL
#standardSQL
WITH wide_stats AS (
SELECT 'joe' name, 'bills' team,
STRUCT(STRUCT(7 AS fga) AS o, STRUCT(8 AS fga) AS d) AS t1,
STRUCT(STRUCT(3 AS fga) AS o, STRUCT(9 AS fga) AS d) AS t2,
STRUCT(3 AS pts, 9 AS ast, 5 AS reb) AS t3, 7 tov, 3 blk UNION ALL
SELECT 'nick' name, 'jets' team,
STRUCT(STRUCT(12 AS fga) AS o, STRUCT(13 AS fga) AS d) AS t1,
STRUCT(STRUCT(15 AS fga) AS o, STRUCT(22 AS fga) AS d) AS t2,
STRUCT(13 AS pts, 5 AS ast, 15 AS reb) AS t3, 75 tov, 23 blk
), flat_stats AS (
SELECT name, team,
t1.o.fga AS t1_o_fga,
t1.d.fga AS t1_d_fga,
t2.o.fga AS t2_o_fga,
t2.d.fga AS t2_d_fga,
t3.pts AS t3_pts,
t3.ast AS t3_ast,
t3.reb AS t3_reb,
tov, blk
FROM wide_stats
)
SELECT name, team, metric, SAFE_CAST(value AS FLOAT64) value
FROM (
SELECT name, team,
REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(0)], r'^"|"$', '') metric,
REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(1)], r'^"|"$', '') value
FROM flat_stats,
UNNEST(SPLIT(REGEXP_REPLACE(TO_JSON_STRING(flat_stats), r'{|}', ''))) pair
)
WHERE NOT LOWER(metric) IN ('name', 'team')
有输出
Row name team metric value
1 joe bills t1_o_fga 7.0
2 joe bills t1_d_fga 8.0
3 joe bills t2_o_fga 3.0
4 joe bills t2_d_fga 9.0
5 joe bills t3_pts 3.0
6 joe bills t3_ast 9.0
7 joe bills t3_reb 5.0
8 joe bills tov 7.0
9 joe bills blk 3.0
10 nick jets t1_o_fga 12.0
11 nick jets t1_d_fga 13.0
12 nick jets t2_o_fga 15.0
13 nick jets t2_d_fga 22.0
14 nick jets t3_pts 13.0
15 nick jets t3_ast 5.0
16 nick jets t3_reb 15.0
17 nick jets tov 75.0
18 nick jets blk 23.0
如果由于某种原因您在手动组装 flat_stats 临时表时遇到问题-您可以执行以下小技巧
If for some reason you have problem with assembling flat_stats temp table manually - you can do a small trick like below
第1步-只需在旧模式下使用目标表[project:dataset.flat_stats]
Step 1 - Just run below query in legacy mode with destination table [project:dataset.flat_stats]
#legacySQL
SELECT *
FROM [project:dataset.wide_stats]
令人惊讶"，这将创建具有以下结构的表 [project:dataset.flat_stats]
"Surprisingly", this will create table [project:dataset.flat_stats] with below structure
Row name team t1_o_fga t1_d_fga t2_o_fga t2_d_fga t3_pts t3_ast t3_reb tov blk
1 joe bills 7 8 3 9 3 9 5 7 3
2 nick jets 12 13 15 22 13 5 15 75 23
第2步-之后，您可以简单地在下面运行(现在在Standard SQL中)
Step 2 - After that you can simply run below (now in Standard SQL)
#standardSQL
SELECT name, team, metric, SAFE_CAST(value AS FLOAT64) value
FROM (
SELECT name, team,
REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(0)], r'^"|"$', '') metric,
REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(1)], r'^"|"$', '') value
FROM `project.dataset.flat_stats` flat_stats,
UNNEST(SPLIT(REGEXP_REPLACE(TO_JSON_STRING(flat_stats), r'{|}', ''))) pair
)
WHERE NOT LOWER(metric) IN ('name', 'team')
这篇关于BigQuery重塑表格，结构从宽到长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
