Bigquery - 使用标准SQL来平整表格 [英] Bigquery-Flatten the table using Standard SQL

问题描述

追踪我之前的问题链接 -

image-1表的示例查询

  WITH yourTable AS（
 SELECT 1 id，[A，B，C，D] Product，[2013， 1625,1297,7634] num [2013,1625,1297,7634] num_2 UNION ALL 
 SELECT 2，[A，B，C，D，E] Product， [1,2,3,4,5]，[1,2,3,4,5] 
）
 
 
 SELECT id，Product，
 Array （
 SELECT 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 
  

我有兴趣看看是否可以将它放在select语句中，而不是with语句中。

修改后的查询以获取结果集我正在寻找。
请建议是否有任何简单的方法。

$ pre $ WITH yourTable AS（
SELECT 1 id，[ A，B，C，D] Product，[2013,1625,1297,7634] num，[2013,1625,1297,7634] num_2 UNION ALL
SELECT 2，[ A，B，C，D，E]产品，[1,2,3,4,5]，[1,2,3,4,5]
）
从

中选择id，product，num，num_2，mul
（选择id，product，num，num_2，mul，pos，pos_2，pos_3，pos_4

from

（SELECT id，Product，num，num_2，
Array（
SELECT 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

$ b $），unnest（产品）作为产品WITH OFFSET pos，
unnest（num）as num WITH OFFSET pos_2，
unnest（num_2）as num_2 WITH OFFSET pos_3，
unnest（mul）as mul WITH OFFSET pos_4）

where pos = pos_2 and
pos = pos_3
and pos = pos_4


解决方案

#standardSQL
WITH yourTable AS（
SELECT 1 id，[A，B，C，D] Product，[2013,1625,1297,7634] num，[2013,1625,1297,7634 ] num_2 UNION ALL
SELECT 2，[A，B，C，D，E] Product，[1,2,3,4,5]，[1,2 ，3,4,5]
）
SELECT ID，Product，num，num_2，num * num_2 mul
FROM yourTable，UNNEST（num）num WITH OFFSET pos
JOIN UNNEST （num_2）num_2 WITH OFFSET pos_2 ON pos = pos_2
JOIN UNNEST（产品）WITH OFFSET产品pos_3 ON pos = pos_3


Follow up to my before question Link- Bigquery- Struct format

This is my current table format in image -1, and I want to fetch details of Product B as shown in image-2 but not possible as it is in Array format. So technically I want to flatten my table as shown in image-3. Is there any way that I can do it. Kindly advice.

Sample query for image-1 table

WITH yourTable AS (
  SELECT 1 id,["A","B","C","D"] Product, [2013,1625,1297,7634] num, [2013,1625,1297,7634] num_2 UNION ALL
  SELECT 2,["A","B","C","D","E"] Product, [1,2,3,4,5], [1,2,3,4,5] 
) 


    SELECT id,Product,
      Array (
        SELECT 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

I am interested to see if can flatten it in "select" statement but not in "with" statement.

Revised query to get the result set which I am looking for. Please suggest if there is any easy way.

WITH yourTable AS (
  SELECT 1 id,["A","B","C","D"] Product, [2013,1625,1297,7634] num, [2013,1625,1297,7634] num_2 UNION ALL
  SELECT 2,["A","B","C","D","E"] Product, [1,2,3,4,5], [1,2,3,4,5] 
) 
select id,product,num,num_2,mul
from

(select id,product,num,num_2,mul,pos,pos_2,pos_3,pos_4 

from

(SELECT id,Product,num,num_2,
  Array (
    SELECT 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


), unnest(product) as product WITH OFFSET pos,
unnest (num) as num WITH OFFSET pos_2,
unnest (num_2) as num_2 WITH OFFSET pos_3,
unnest (mul) as mul WITH OFFSET pos_4) 

where pos=pos_2 and 
pos=pos_3
and pos=pos_4

解决方案

#standardSQL
WITH yourTable AS (
  SELECT 1 id,["A","B","C","D"] Product, [2013,1625,1297,7634] num, [2013,1625,1297,7634] num_2 UNION ALL
  SELECT 2,["A","B","C","D","E"] Product, [1,2,3,4,5], [1,2,3,4,5] 
) 
SELECT id, Product, num, num_2, num * num_2 mul
FROM yourTable, UNNEST(num) num WITH OFFSET pos
JOIN UNNEST(num_2) num_2 WITH OFFSET pos_2 ON pos = pos_2
JOIN UNNEST(Product) Product WITH OFFSET pos_3 ON pos = pos_3

这篇关于Bigquery - 使用标准SQL来平整表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！