BigQuery Standard SQL 語法

9 min readMar 30, 2019

最完整詳細的指令還是要看官方文件，這邊僅紀錄做 QwikLabs 時邊做的筆記。

新增刪除

1. 新增 table

空的 table

#standardSQL 
CREATE OR REPLACE TABLE [Dataset Name].[Table Name]
#schema 
(   
[Column1] STRING OPTIONS(description="Unique ID"),   
[Column2] STRING OPTIONS(description="...") 
)

從現有 table 中新增 table

#standardSQL 
CREATE OR REPLACE TABLE [Dataset Name].[Table Name 2]
#schema 
(   
[Column1] STRING OPTIONS(description="Unique ID"),   
[Column2] STRING OPTIONS(description="...") 
)  
OPTIONS(    
    description="Data for 2017/01/01"  
    ) 
AS  
SELECT [Column1], [Column2]
FROM `[Project Name].[Dataset Name].[Table Name 1]`  
WHERE date = '20170101';

新增以 date 切 partition 的 table：

在沒有切 partition 的狀況下，即便沒有符合 WHERE 條件中的日期導致搜尋結果回傳 0 筆，還是必須掃描過整張 table，因而增加不必要的成本，並且耗時。切了 partition 之後，執行時會直接先去 partition 中確認有沒有這筆日期的資料，沒有的話就不會再繼續執行下去，能夠有效節省成本並提高效能。

#standardSQL  
CREATE OR REPLACE TABLE [Dataset Name].[Table Name 2]  
PARTITION BY date_formatted  
OPTIONS(    description="a table partitioned by date"  ) 
AS  
SELECT 
DISTINCT  
PARSE_DATE("%Y%m%d", date) AS date_formatted,  
fullvisitorId  
FROM `[Project Name].[Dataset Name].[Table Name 1]`

PARSE_DATE()：將 STRING 轉成 DATE 型態

新增自動到期地切 partition 的 table：如果基於資料隱私權或是為了避免儲存不必要的資料，可以加入 partition 的到期日，這樣一旦用不到的時候，partition 也會自動消失。

#standardSQL  
CREATE OR REPLACE TABLE [Dataset Name].[Table Name 2]  
PARTITION BY date  
OPTIONS (    
    partition_expiration_days=60,    
    description="weather precipitation, partitioned by day"  
    ) 
AS  
SELECT    
DATE(CAST(year AS INT64), 
     CAST(mo AS INT64), 
     CAST(da AS INT64)) AS date,      
prcp  
FROM `[Project Name].[Dataset Name].[Table Name 1]`

確認一下

#standardSQL 
SELECT   
AVG(prcp) AS average,   
date,   
CURRENT_DATE() AS today,   
DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age,   
FROM `[Project Name].[Dataset Name].[Table Name 1]`
GROUP BY date, today, partition_age 
ORDER BY partition_age DESC

DATE_DIFF()：相減兩個 DATE 的 function。

2. 新增單筆資料

INSERT INTO [Table Name] ([Column1], [Column2], ...) 
VALUES ( [Value1], [Value2], ...);

3. 刪除

DELETE FROM [Table Name] WHERE [Conditions];

想要定期更新某個 Query 的執行結果

可以用 BigQuery scheduled queriesor with a Cloud Dataprep / Cloud Dataflow workflow.
建立 View：習慣上 view 的命名會前綴 vw_ 或是後綴 _vw、_view

#standardSQL 
CREATE OR REPLACE VIEW [Dataset Name].[View Name]
# 或是 CREATE VIEW IF NOT EXISTS 視需求而定
OPTIONS(   
    description="......",
    # 取得最近 90 天內的資料  
    expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), 
                                                    INTERVAL 90 DAY) 
    )
AS 
SELECT *
FROM `[Project Name].[Dataset Name].[Table Name 1]`  
WHERE [Column] IS NOT NULL  
ORDER BY date DESC # latest transactions  
LIMIT 100;

好用的 function

1. STRING_AGG()

取得並攤平每個訪客買的前十個產品名稱（以字母順序排列，逗點隔開）

#standardSQL 
CREATE OR REPLACE VIEW [Dataset Name].[View Name] 
AS 
SELECT DISTINCT date,
       visitId,
       STRING_AGG(
           DISTINCT productName ORDER BY productName LIMIT 10
       ) AS products_ordered  
FROM `[Project Name].[Dataset Name].[Table Name]`   
GROUP BY 1, 2  
ORDER BY date DESC # latest transactions    
LIMIT 10

2. SAFE_DIVIDE()

避免分母為 0 噴錯的情況。

合併 table

1. Column 的合併：JOIN

Cross Join：不管 Key，直接把 table2 的每一筆資料 join 到 table1 的每一筆資料。如果 table1 有 m 筆資料 table2 有 n 筆資料，join 完後會有 m X n 筆。
Inner Join：僅保留 table1 跟 table2 共同有的 Key 的資料。
Left Join：僅保留 table1 有的 Key。
Right Join：僅保留 table2 有的 Key。
Full Join：同時保留 table1 跟 table2 有的 Key。

2. Row 的合併：UNION

聯集

[Query 1]
UNION
[Query 2]

或是

[Query 1]
UNION ALL
[Query 2]

兩者的差別在於前者不包含重複的資料，後者包含。

合併的方式視需求而定，有些情況用萬用字元會更方便，假設想要查詢所有 2017 年的資料：

#standardSQL 
SELECT * FROM `ecommerce.sales_2017*`

常用情境：確認欄位的對應狀況並刪除重複值

以產品 SKU 及產品名稱舉例：

首先看 1 個產品名稱是否對應到 N 個產品 SKU

#standardSQL
SELECT
DISTINCT
COUNT(DISTINCT productSKU) AS SKU_count,
STRING_AGG(DISTINCT productSKU LIMIT 5) AS SKU,
productName
FROM `[Project Name].[Dataset Name].[Table Name]`
WHERE productSKU IS NOT NULL
GROUP BY productName
HAVING SKU_count > 1
ORDER BY SKU_count DESC

1 個產品名稱有 N 個 SKU 可能是因為同產品但不同的顏色、尺寸等等。

再看1 個產品 SKU 是否對應到 N 個產品名稱

#standardSQL  
SELECT 
DISTINCT 
COUNT(DISTINCT productName) AS product_count, 
STRING_AGG(DISTINCT productName LIMIT 5) AS product_name, 
productSKU 
FROM `[Project Name].[Dataset Name].[Table Name]` 
WHERE productName IS NOT NULL 
GROUP BY productSKU 
HAVING product_count > 1 
ORDER BY product_count DESC

1 個產品 SKU 有 N 個產品名稱，可能是因為人為不同的取名習慣造成的，因此要刪除重複的產品名稱：

#standardSQL
# 取得每個產品 SKU 以及對應的產品名稱
WITH product_query AS (
  SELECT
  DISTINCT
  productName,
  productSKU
  FROM `[Project Name].[Dataset Name].[Table Name]`
  WHERE productName IS NOT NULL
)SELECT k.* FROM (
  SELECT ARRAY_AGG(x LIMIT 1)[OFFSET(0)] k
  FROM product_query x
  GROUP BY productSKU 
);

ARRAY_AGG(x LIMIT 1)：從 product_query 的 1 個 SKU 對應到 N 個產品名稱的資料中，只取 1 個產品名稱。
[OFFSET(0)]：取第 1 個產品名稱，OFFSET 指的是位置。

以上。

看其他我寫的 BigQuery 文章：