Bigquery - 结构体格式 [英] Bigquery- Struct format

问题描述

WITH yourTable AS (
      SELECT 1 AS id, '2013,1625,1297,7634' AS string_col UNION ALL
      SELECT 2, '1,2,3,4,5'
    )

    SELECT id, 
      (SELECT ARRAY_AGG(CAST(num AS INT64)) 
        FROM UNNEST(SPLIT(string_col)) AS num
      ) AS num,
      ARRAY(SELECT CAST(num AS INT64) 
        FROM UNNEST(SPLIT(string_col)) AS num
      ) AS num_2
    FROM yourTable

这就是我的实际表的设计方式，现在我想乘以 num*num_2，然后再求和.有没有办法把它变成像 ID、nums.num、nums.num_2 这样的结构格式，这样我就可以简单地乘以得到必要的结果.

This is how exactly my actual table is designed and Now I would like to multiply num*num_2 and then later sum it up. Is there a way to get this into struct format like ID, nums.num,nums.num_2 so that I can simply multiply which gives me the necessary result.

PS:我在上面的 select 语句中寻找解决方案，但不在with"语句中.

PS: I am looking for solution in the select statement above but not within "with" statement.

推荐答案

好吧，假设你真的有理由按照你的方式拥有你的桌子(见我对你的问题的评论) - 下面应该工作

Ok, assuming that you really have reason to have your table the way you have (see my comment on your question) - below should work

#standardSQL
SELECT id, 
  (
    SELECT SUM(num * num_2)
    FROM (SELECT pos, num FROM UNNEST(num) num WITH OFFSET pos) a
    JOIN (SELECT pos_2, num_2 FROM UNNEST(num_2) num_2 WITH OFFSET pos_2) b
    ON a.pos = b.pos_2
  ) mul
FROM yourTable   

你可以用下面的测试它

#standardSQL
WITH yourTable AS (
  SELECT 1 id, [2013,1625,1297,7634] num, [2013,1625,1297,7634] num_2 UNION ALL
  SELECT 2, [1,2,3,4,5], [1,2,3,4,5] 
) 
SELECT id, 
  (
    SELECT SUM(num * num_2)
    FROM (SELECT pos, num FROM UNNEST(num) num WITH OFFSET pos) a
    JOIN (SELECT pos_2, num_2 FROM UNNEST(num_2) num_2 WITH OFFSET pos_2) b
    ON a.pos = b.pos_2
  ) mul
FROM yourTable

这篇关于Bigquery - 结构体格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！