BigQuery在子查询的嵌套STRUCT中添加新列 [英] BigQuery add new columns into nested STRUCT in subquery

问题描述

我有下表，其中包含嵌套的 STRUCT ，并且在子查询中，我试图在结构级别添加其他列.到目前为止，我已经创建了一个可以重复使用的示例:

I have the following table that contains nested STRUCTs, and in a subquery, I am trying to add additional columns at the struct level. I've created a reproducible example of my efforts thus far:

WITH wide_stats AS (
  (
    SELECT 
      'joe' name, 'bills' team,
      struct(struct(7 as fga, 5 as fgm) as o, struct(8 as fga, 3 as fgm) as d) as t1,
      struct(struct(3 as fga, 4 as fgm) as o, struct(9 as fga, 2 as fgm) as d) as t2
  ) UNION ALL (
    SELECT 'nick' name, 'jets' team,
      struct(struct(12 as fga, 7 as fgm) as o, struct(13 as fga, 7 as fgm) as d) as t1,
      struct(struct(15 as fga, 7 as fgm) as o, struct(22 as fga, 7 as fgm) as d) as t2
  )
)

SELECT 
  *,
--   safe_divide(wide_stats.t1.o.fgm, wide_stats.t1.o.fga) as fg_pct,
  safe_divide(wide_stats.t1.o.fgm, wide_stats.t1.o.fga) as wide_stats.t1.o.fg_pct  

FROM wide_stats

当前代码引发错误.语法错误:意外的."在[18:70] 在第18行(带有safe_divide).如果我在第17行/第18行切换，代码可以工作，但是fg_pct 不在在t1.o结构中，我希望它在其中.

The current code throws an error Syntax error: Unexpected "." at [18:70] at line 18 (with the safe_divide). If I switch in line 17 / out line 18, the code works, but then fg_pct is not in the t1.o struct, where i'd like it to be.

有没有办法像这样将子列添加到嵌套结构的嵌套结构中?

Is there any way to add columns into nested structs in subqueries like this?

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
WITH wide_stats AS (
  SELECT 'joe' name, 'bills' team,
    STRUCT(STRUCT(7 AS fga, 5 AS fgm) AS o, STRUCT(8 AS fga, 3 AS fgm) AS d) AS t1,
    STRUCT(STRUCT(3 AS fga, 4 AS fgm) AS o, STRUCT(9 AS fga, 2 AS fgm) AS d) AS t2 UNION ALL 
  SELECT 'nick' name, 'jets' team,
    STRUCT(STRUCT(12 AS fga, 7 AS fgm) AS o, STRUCT(13 AS fga, 7 AS fgm) AS d) AS t1,
    STRUCT(STRUCT(15 AS fga, 7 AS fgm) AS o, STRUCT(22 AS fga, 7 AS fgm) AS d) AS t2
)
SELECT * REPLACE (
  (SELECT AS STRUCT t1.* REPLACE (
    (SELECT AS STRUCT t1.o.*, SAFE_DIVIDE(wide_stats.t1.o.fgm, wide_stats.t1.o.fga) AS fg_pct ) 
    AS o))
  AS t1)
FROM wide_stats   

有结果

Row name    team    t1.o.fga    t1.o.fgm    t1.o.fg_pct         t1.d.fga    t1.d.fgm    t2.o.fga    t2.o.fgm    t2.d.fga    t2.d.fgm     
1   joe     bills   7           5           0.7142857142857143  8           3           3           4           9           2    
2   nick    jets    12          7           0.5833333333333334  13          7           15          7           22          7    

这篇关于BigQuery在子查询的嵌套STRUCT中添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！