黑皮考町

前言 與傳統的關聯式資料庫不同, BigQuery 並不支援使用 ALTER 來修改資料表內的欄位, 如果很不幸的真的需要改欄位, 我們只能重新建立新的資料表 CREATE OR REPLACE TABLE ` dataset.usr_new ` AS SELECT * EXCEPT (usrType), CAST(usrType AS INT64) AS usrType, FROM `dataset.usr`; 分區表 (Partitioned Table) 以上的方法只適合用在普通的資料表, 若想要修改分區表內的欄位資訊, 建議使用 bq query 的指令來做 bq query 支援將查詢的結果寫入到指定的資料表中, 除此之外還可以用查詢結果來建立分區表(如下) bq --location=location query \ --destination_table project_id:dataset.table \ --time_partitioning_field column \ --use_legacy_sql=false \ 'query' 分區表都會有兩個虛擬的欄位 _PARTITIONDATA, _PARTITIONTIME 用來記錄資料的時間,  這邊需要注意的是 bq query 沒辦法將舊資料表內的虛擬欄位的資訊直接寫到新的資料表中,  若想將這些資訊保留下來, 必須額外建立新的欄位來放這些資訊 SELECT * EXCEPT (usrType), CAST(usrType AS INT64) AS usrType, _PARTITIONTIME AS createdAt FROM ` dataset.usr `; 完整的指令 bq query \ --destination_table 'dataset.usr_new' \ --time_partitioning_field createdAt \ --use_legacy_sql=false --append_table \ ' SELECT * EXCEPT (usrType), CAST(usrType AS INT64) ...

前言 近年來, 商業智慧( Business Intelligence, BI )的運用已漸漸地受到企業的重視, 決策者可以藉由 BI 提供的資料來做業務的分析與預測 在做業務分析的時候, 通常會以每日活躍使用者數( Daily Active User, DAU ), 每週活躍使用者數( Weekly Active User, WAU ), 以及每月活躍使用者數( Monthly Active User, MAU )的多寡, 來暸解目前產品的營運狀況 要計算出這些資訊其實並不難, 首先以 Group By "日期"的方式算就可以先算出 DAU WITH DAUtbl AS ( SELECT _PARTITIONDATE AS date, COUNT(*) as dau FROM my_dataset.usr WHERE _PARTITIONDATE BETWEEN DATE_SUB(DATE('2020-03-16T13:15:30Z'), INTERVAL 7 - 1 DAY) AND DATE('2020-03-21T13:15:30Z') GROUP BY _PARTITIONDATE ) 有了每日人數( DAU )之後, 接下來就是來算 WAU 計算 WAU 時, 須將當日以及前六天的 Active User 數量都加總起來 而想要取得七天內的資料, 可以先透過 CROSS JOIN 的方式將每一日的資料都複製成七份 SELECT * FROM DAUtbl, UNNEST(GENERATE_ARRAY(0, 7 - 1 )) i 每個副本都會有個索引值 i (如下) 有了七份的副本之後, 再將資料移動到合適的群組(日期)做加總, 最後就可以獲得每一日 WAU 的數目 SELECT DATE_ADD(date, INTERVAL i DAY) date_grp <--- 移動資料 , SUM(DISTINCT IF( i < 7 ,dau, null)) wau FROM DAUtbl, UNNEST(GENERATE_ARRAY(0, 7 - 1 )) i GROUP BY 1 ORDER BY...