BigQuery 基礎查詢技巧

BigQuery基礎技巧必備基礎語法｜免費BigQuery資料集導入

1 則留言 / BigQuery / 作者:

BigQuery需要使用到SQL語言來做為資料庫的查詢，另外免費用量GCP提供1TB/月。如果你對BigQuery還不熟悉，還沒有完成串接GA4與BigQuery的使用，詳細可以查看這篇「BigQuery與GA4基礎串接、必要觀念」。

本篇文章 – 持續更新最新SQL查詢用法，其中包刮「資料日期、資料事件、資料參數、來源媒介、電子商務資料、電子商務項目」。如果你是要查看PYTHON實務應用視覺化報表，也可以查看這一篇。

也有提供免費的SQL免費公開的DATASET，可以幫助我們在沒有大量資料的情況下，練習SQL的寫法。

BigQuery匯入免費資料集

如果你的BigQuery還沒有任何資料，或是你覺得目前資料太少，你可以照著以下步驟，GCP有提供公開的免費資料。

點選新增

公開資料集

在BigQuery的公開資料集中點選你要的資料；尋找一下有GA4的免費公開資料，並且匯入。

完成總表

匯入完成，從左邊選單選取GA4的資料集，再點選其中一個GA4資料表，即可看到從外部匯入的現成資料。後續就可以直接利用這個表作為查詢。

查詢、資料儲存要收費嗎

BigQuery每個月提供我們1TB的查詢用量。雖然論查詢量來說不多，因為當你查詢月的資料，基本上一下子就好幾GB的查詢量；不過基本上還是可以做滿多練習的。如果你的查詢量很大，你也可以開多個帳戶使用BigQuery；另外只要你帳戶還沒綁定帳單，基本上都不用擔心GCP跟你收費。

如何儲存當前查詢語法

在BigQuery的上方選單中，點選「查詢儲存」。

輸入你要儲存的資料表名稱

可以看到左側BigQuery過往的專案查詢，有剛剛的資料表名稱。

也可以看到剛剛的資料表名稱已經變更，你剛所輸入的內容。

配置查詢設定

配置查詢設定，可以讓我們調整當使用SQL查詢時，當前資料表的資料流向。例如暫存一張資料表，後續需要我們手動儲存，或是先設定好資料叢集、資料表，未來的查詢都會直接匯入至那一張表中。

點選BigQuery選單上方的「查詢設定」。

如果你希望未來的BigQuery資料，都會直接寫入另外一張資料表。你需要再新建一個新的資料叢集、資料表。你可以透過上方BigQuery的範例圖直接設定，下面也會有另外一個方式教你建立。

完成後，未來所查詢的資料，都會在這個資料叢集的資料表中。其中也有分成不同模式的寫入設定，要特別注意這一點。

建立資料叢集的方式2

點選BigQuery資料叢集旁邊的選單。詳見上方圖例。

輸入你要的資料及名稱，以及你的資料地區。要注意的是如果你是使用公開資料，有些資料只能使用指定地區。因此如果你在練習階段，會建議直接使用預設的「多地區」，或是看你的資料地區在哪，只接選擇對應的地區。完成後按建立。

於BigQuery中剛建立的資料叢集，再點選他的選項，「建立資料表」，就可以新增新的資料表在這邊。後續再依照這一段落最一開始的設定，設定對應資料叢集、資料表。即可將資料直接匯入至這張新的資料表中。

另存當前資料表

BigQuery 提供以上方式，可以進行我們儲存查詢完後的結果。

點選BigQuery資料表，可以將當前資料表，再開一個新的資料叢集，並且再新增一個資料表。

輸入資料集名稱、資料表名稱。

完成後你可以看到選單，已經有對應的資料叢集、資料表。

如何共享BigQuery的查詢結果

這個設定應該跟Looker Studio差不多，複製完後點選「取得連結」，對方就可以打開BigQuery的專案。

**對方需要使用有登入的Google 帳戶進行打開哦!

BigQuery資料結構說明

使用SQL查詢BigQuery時，你一定要先了解資料表下的結構，因為這會關乎在撰寫SQL時，需要使用的語法。
如果要使用BigQuery查詢「ctrl + enter」，可以做為SQL的執行快捷鍵。

BigQuery -多元輸出功能

BigQuery支援 Looker Studio的串接，可以再由LS直接製作視覺化報表。

同時BiqQuery也支援Python，透過操作Google Colab引入相關數據分析會使用的套件，例如Pandas, Pgywalker, Matplotlib，也可以直接在PYTHON應用程式中做視覺報表圖。

匯出至GoogleSheet

點選透過試算表探索。

資料已經從BigQuery引入至Gsheet。目前的資料還無法做直接引用，如果你直接引用會有錯誤顯示。如下圖。

引用Google Sheet資料

點選擷取。

建立一個新工作表。

Google Sheet會開啟一張工作表，讓你可以進行後續的BigQuery數據運算。不過不建議把超過1萬筆以上的資料，導入Google Sheet做處理；會非常的緩慢。

PYTHON視覺報表應用

原本官方已經寫好PYTHON與當前BigQuery的專案連接，剩下就看個人使用的目地來引入相關套件。

import pands as pd
import pygwalker as pyg

這邊我也會推薦你先用Matplotlib，也可以製作視覺化報表，只是本文利用pygwalker作為範例，結果都差不多。要注意的是引用pygwalker需要先安裝這個套件，於是上方第二條語句。

!pip install pygwalker

後續可直接由Colab 搭配pygwalker 在程式中做出報表，也能匯出圖片、連結、pdf檔案，非常方便。如果想要學習更多PYTHON製作視覺化報表，或是有哪些視覺化報表的應用，也可以查看這一篇。

BigQuery-SQL查詢技巧

接下來會有BigQuery最基本的SQL語法，我也會把SQL語言貼在這邊，務必要實際操作，這樣學習才會快速。

*符號

*符號為全選，如果你一開始想要先預覽整張BigQuery資料表，就可以透過以下語法選擇全部資料表的欄位。

SELECT * FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210130

SELECT + FROM

  • SELECT:選擇XX資料欄位。資料欄位就是前一步驟講的資料結構下的欄位名稱。
  • FROM:使用哪一個表做查詢。
SELECT event_date, event_name,event_params[0].key,event_params[0].value
--選擇哪些資料

選擇事件日期, 事件名稱, 事件參數-字串類, 事件參數-值類。

FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

從bigquery-public-data.ga4_obfuscated_sample_ecommerce
資料叢集選擇對應欄位。

有一點要注意的是我資料表的名稱目前是 * 結尾，意思是可以後續搭配WHERE指定日期範圍。不然原本應該會是字尾接續日期名稱，這樣的意思就是你只能查詢到這一天的所有資料。因為BigQuery以天數來拆分每一張資料表，也可以讓我們不會一下子把每月的CREDIT使用完。

FROM  bigquery-public-data.ga4_obfuscated_sample_ecommerce. events_20210131
--沒有指定資料範圍的字尾範例

WHERE

WHERE 條件篩選。

SELECT event_date, event_name,event_params[0].key,event_params[0].value

FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

WHERE _table_suffix BETWEEN '20210120' AND '20210130' AND event_name = 'page_view'

--WHERE為篩選資料表的條件

搭配”_table_suffix”這一個條件，以及*字尾的資料表。可以讓我們選取指定日期範圍的資料；當然where也可以使用在其他條件的篩選。

AND / OR

AND / OR 為更多條件的篩選。例如以下範例，當你想要選擇事件名稱只有page_view而且在指定時間範圍，你就可以寫入更多篩選條件。

SELECT event_date, event_name,event_params[0].key,event_params[0].value

FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

WHERE _table_suffix BETWEEN '20210120' AND '20210130' AND event_name = 'page_view' AND event_params[0].key = 'page_location'

--AND 為搭配WHERE設定更多資料表篩選條件

ORDER BY

ORDER BY為使用哪一個資料欄位做排序，有ASC遞增 / DESC遞減。如果你要查看由大到小的，你就必須在 ORDER BY 的結尾，寫上 DESC。他就會由大到小降冪排序。

SELECT event_date, event_name,event_params[0].key,event_params[0].value

FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*
WHERE _table_suffix BETWEEN '20210120' AND '20210130' AND event_name = 'page_view' AND event_params[0].key = 'page_location'

ORDER BY event_date
--由event_date作為排序對象，預設為遞增排序。

LIMIT

LIMIT 限制顯示的資料筆數。

例如你可以看本周哪一個產品帶來最多業績TOP5 。只需要寫上LIMIT 5 資料就會顯示5筆，同時也能節省SQL的查詢使用量。

SELECT event_date, event_name,event_params[0].key,event_params[0].value

FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

WHERE _table_suffix BETWEEN '20210120' AND '20210130' AND event_name = 'page_view' AND event_params[0].key = 'page_location'

ORDER BY event_date

LIMIT 1000
--指定只要顯示1000筆資料

SUM

BigQuery 基礎查詢技巧

SUM把資料加總。如何把上圖左邊的資料，依照城市(county_name)加總醫院總數範例。

SELECT 
  county_name,
  sum(total_hospital_beds) as hospital
  FROM `bigquery-public-data.covid19_aha.hospital_beds` 
  Where county_name != 'null' 
  group by county_name
  order by county_name

CASE/WHEH

當你想要查詢資料表，設定條件大於多少數值，就給予1，否則給0；並且新增至新的一欄。

  • 符合條件 => 1
  • 不符合條件=>0
SELECT *, case when load_weight > 600 then 1 else 0 end as load_weight_greater600
FROM `bigquery-public-data.austin_waste.waste_and_diversion`

如何篩選符合1的欄位

如果你沒有使用Common Table Expressions(CTE)，那麼查詢就會報錯。因此我們要搭配WITH … AS …的撰寫。

WITH的存在，為了就是減少過多的條件篩選，同時也能減少SQL的撰寫複雜度。
本次範例透過簡單的CTE方式，可以把資料表篩選完>600的，並且獨立出來成一個欄位。

當然，你也可以不用with (CTE)的表示法，但當你有CASE WHEN 與 WHERE整個語句就會非常複雜，如下。

SELECT *, case when load_weight > 600 then 1 else 0 end as load_weight_greater600
FROM `bigquery-public-data.austin_waste.waste_and_diversion`
WHERE case when load_weight > 600 then 1 else 0 end =1

如果資料大量，且要使用的CASE WHEH一多，那使用WHERE的條件就會讓查詢變得很複雜，因此才會需要使用with。

使用範例

查詢PAGE_VIEW與PAGE_LOCATION

從BigQuery查詢GA4的Page_view & page_location。

產品銷售金額TOP10

從BigQuery查詢GA4數據的TOP10產品銷售金額

SELECT (select item_name FROM unnest(items)as A LIMIT 1)as item_name,ecommerce.purchase_revenue,ecommerce.total_item_quantity,ecommerce.transaction_id

FROM bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

WHERE _table_suffix between '20210120' and '20210130'

ORDER BY ecommerce.purchase_revenue DESC

LIMIT 10

查詢近7日報表

SELECT DISTINCT
  PARSE_DATE('%Y%m%d',EVENT_DATE)AS date,
--換上你的資料表名稱
FROM `DATA.analytics_317039958.events_*`
WHERE PARSE_DATE('%Y%m%d', _table_suffix) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND PARSE_DATE('%Y%m%d', _table_suffix) < CURRENT_DATE()

查詢每日總收益來源 / 媒介

with _cte as (
  SELECT  event_date,
    ecommerce.purchase_revenue,
    user_pseudo_id,
    traffic_source.source,
    traffic_source.medium,
    event_name,
    device.mobile_model_name
--換上你的資料表名稱
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- WHERE _table_suffix between '20240101' and  '20240131'
  )

select event_date,source,medium,SUM(purchase_revenue) as Rev_daily FROM _cte
WHERE event_name = 'purchase'
GROUP BY 1,2,3
ORDER BY 1

事件總表


WITH _ctr AS (
  SELECT 
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    TIMESTAMP_MICROS(event_timestamp) AS time_stamp,
    user_pseudo_id,
    CONCAT(traffic_source.source, '/', traffic_source.name, '/', traffic_source.medium) AS Channel,
    user_pseudo_id,
    event_name,
    MAX(CASE WHEN params.key = 'ga_session_id' THEN params.value.int_value ELSE NULL END) OVER(PARTITION BY event_timestamp, user_pseudo_id) AS ga_session_id2,
    MAX(CASE WHEN params.key = 'page_title' THEN params.value.string_value ELSE NULL END) OVER(PARTITION BY event_timestamp, user_pseudo_id) AS page_title,
    MAX(CASE WHEN params.key = 'page_location' THEN params.value.string_value ELSE NULL END) OVER(PARTITION BY event_timestamp, user_pseudo_id) AS page_location,
    user_id,
    device.operating_system_version,
    device.mobile_model_name

--換上你的資料表名稱
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS events,
    UNNEST(event_params) AS params
  -- WHERE _TABLE_SUFFIX > '20240125'
)

SELECT 
  event_date,
  ga_session_id2,
  time_stamp,
  Channel,
  -- user_pseudo_id,
  max(event_name) as event_name,
  max(page_title) AS page_titles,
  MAX(page_location) AS page_location,
  max(operating_system_version) AS operating_system_versions,
  
FROM _ctr
GROUP BY event_date,ga_session_id2, time_stamp, Channel, user_id
order by 1,2 ASC
LIMIT 1000

透過SQL作為BigQuery的查詢媒介，我們可以在SQL撈取指定的資料，後續再藉由LookerStudio來做視覺報表，或是匯出至Google Sheet，BigQuery也可作為一個免費的數據倉儲站。本文將會持續更新BigQuery-SQL最新使用語法；目前還有很多語法沒有介紹到，但文章內容過於長，因此分次更新，歡迎定期關注!

error: