BigQuery中表格的结构 [英] Structure of table in BigQuery

问题描述

我想创建下面的 JSON 表。

  {
store_nbr：1234，
sls_dt：2014-01- 01 00:00:00，
Items：[{
sku：3456，
sls_amt：9.99，
折扣： [{
disc_nbr：1，
disc_amt：0.99
}，{
disc_nbr：2，
disc_amt ：1.00
}] 
}] 
} 
  

任何人都可以帮助我在BigQuery上构建这个JSON的结构吗？以及如何使用SQL查询检索数据？

解决方案

我想知道什么是结构我的桌子？

请尝试下面的BigQuery Standard SQL

< （
SELECT
1234 AS store_nbr，
DATE（'2014-zh-CN）$ pre-class =lang-sql prettyprint-override> #standardSQL
WITH yourTable AS 'STR01（
3456 AS sku，
9.99 AS sls_amt，
[STRUCT< disc_nbr INT64，disc_amt FLOAT64>
（1，0.99），
（2，1.00）
]折扣
）] AS物品
）
SELECT *
FROM yourTable


表格的结构如下：

或者在Web UI中查看：

我如何读取它的值？

这实际上取决于您想要从中读取数据的具体方式和方式！

例如，如果您想计算每笔销售的总折扣 - 它可以看起来如下所示：

  #standardSQL 
 WITH yourTable AS（
 SELECT 
 1234 AS store_nbr，
 DATE（'2014-01-01 00 ：00：00'）AS sls_dt，
 [STRUCT（
 3456 AS sku，9.99 AS sls_amt，[STRUCT< disc_nbr INT64，disc_amt FLOAT64>（1,0.99），（2，1.00）] AS折扣
）] AS物品
）
选择
 t。*，
（SELECT SUM（disc.disc_amt）FROM UNNEST（item.discounts）AS disc）AS total_discount 
 FROM yourTable AS t，UNNEST（items）AS item 
  

我建议您先到通过创建表来完成你的练习并实际获取数据，因此你可以询问你想要构建的查询的具体问题。

但是这应该是一个新帖子，所以你不要混合在一起作为一个一体化的问题，因为这种类型的问题在这里通常不受欢迎

I want to create table of below JSON.

{
    "store_nbr": "1234",
    "sls_dt": "2014-01-01 00:00:00",
    "Items": [{
        "sku": "3456",
        "sls_amt": "9.99",
        "discounts": [{
            "disc_nbr": "1",
            "disc_amt": "0.99"
        }, {
            "disc_nbr": "2",
            "disc_amt": "1.00"
        }]
    }]
}

Can anyone help me what would be the structure of this JSON on BigQuery ? and How I can retrieve data using SQL query ?

解决方案

I am wondering what would be the structure of my table?

Try below for BigQuery Standard SQL

#standardSQL
WITH yourTable AS (
    SELECT 
        1234 AS store_nbr, 
        DATE('2014-01-01 00:00:00') AS sls_dt, 
        [STRUCT(
            3456 AS sku, 
            9.99 AS sls_amt, 
            [STRUCT<disc_nbr INT64, disc_amt FLOAT64>
                 (1, 0.99), 
                 (2, 1.00)
            ] AS discounts
        )] AS items
)
SELECT *
FROM yourTable

The structure of table here is:

or if to look in Web UI:

How I can read values of it?

It is really depends on what exactly and how you want to "read" out of this data!

For example if you want to calc total discount per each sale - it can looks as below

#standardSQL
WITH yourTable AS (
  SELECT 
    1234 AS store_nbr, 
    DATE('2014-01-01 00:00:00') AS sls_dt, 
    [STRUCT(
      3456 AS sku, 9.99 AS sls_amt, [STRUCT<disc_nbr INT64, disc_amt FLOAT64>(1, 0.99), (2, 1.00)] AS discounts
    )] AS items
)
SELECT 
  t.*, 
  (SELECT SUM(disc.disc_amt) FROM UNNEST(item.discounts) AS disc) AS total_discount
FROM yourTable AS t, UNNEST(items) AS item  

I recommend you first to complete your "exercise" with table creation and actually get data into it, so than you can ask specific questions about query you want to build.
But this should be a new post so you do not mix all together as an all-in-one question, as such type of questions usually not welcomed here on SO

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