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

问题描述

（
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这样的结构格式，这样我就可以简单地相乘，从而得到必要的结果。

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

好吧，假设你真的有（请参阅我对你问题的评论） - 下面应该可以工作

（$ num $）
FROM（SELECT pos，num FROM UNNEST（num）） - > $ #standardSQL
SELECT ID，
（
） 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,16 25,1297,7634] num，[2013,1625,1297,7634] num_2 UNION ALL 
 SELECT 2，[1,2,3,4,5]，[1,2,3,4,5] 
 
 
 
 
 
 $ b $（b 
 $ b $ FROM 
 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 
  




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

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: 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   

you can test it with below

#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屋！