BigQuery 不就是查詢嗎？跟資料庫差在哪裡？能做什麼分析？

許多人初次接觸 BigQuery 時會問：「這不就是一個支援 SQL 的資料庫嗎？」到底有什麼差別？

實際上，BigQuery 提供了遠超傳統 SQL 的強大分析功能，這些功能讓數據科學家和分析師能夠進行複雜的數據探索和分析。

視窗函數：時間序列和趨勢分析

視窗函數是 BigQuery 最強大的功能之一，它允許在不使用 GROUP BY 的情況下進行聚合計算：

-- 計算每日銷售額的7天移動平均
SELECT 
  date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7days,
  -- 計算與前一天的銷售增長率
  LAG(daily_sales) OVER (ORDER BY date) as prev_day_sales,
  (daily_sales - LAG(daily_sales) OVER (ORDER BY date)) 
    / LAG(daily_sales) OVER (ORDER BY date) * 100 as growth_rate
FROM daily_sales_summary
ORDER BY date;

這種功能對於財務分析、庫存管理、趨勢預測等場景極其有用。

陣列處理：處理複雜嵌套 (巢狀) 數據

BigQuery 原生支援陣列數據類型，這對處理 JSON 數據、用戶行為軌跡等特別有用：

-- 分析用戶購物車中的商品組合
SELECT 
  user_id,
  ARRAY_LENGTH(cart_items) as item_count,
  -- 提取所有商品類別
  ARRAY_AGG(DISTINCT category) as categories,
  -- 找出最貴的商品
  ARRAY_AGG(product_name ORDER BY price DESC LIMIT 1)[OFFSET(0)] as most_expensive_item
FROM users_carts
CROSS JOIN UNNEST(cart_items) as item
GROUP BY user_id, cart_items;

地理空間分析：位置數據的深度洞察

BigQuery 內建了強大的地理空間分析功能，支援點、線、面等各種地理對象：

-- 分析門店覆蓋範圍和競爭分析
SELECT 
  store_id,
  store_name,
  -- 計算門店間距離
  ST_DISTANCE(
    ST_GEOGPOINT(store_lng, store_lat),
    ST_GEOGPOINT(-73.935242, 40.730610)  -- 紐約時代廣場
  ) / 1000 as distance_to_times_square_km,
  -- 創建門店3公里服務範圍
  ST_BUFFER(ST_GEOGPOINT(store_lng, store_lat), 3000) as service_area,
  -- 統計服務範圍內的競爭對手數量
  (SELECT COUNT(*) 
   FROM competitor_stores c
   WHERE ST_DWITHIN(
     ST_GEOGPOINT(store_lng, store_lat),
     ST_GEOGPOINT(c.lng, c.lat),
     3000
   )) as competitors_nearby
FROM our_stores;

機器學習整合：BigQuery ML

BigQuery ML 讓 SQL 開發者無需學習 Python 或 R 就能建立機器學習模型：

-- 建立客戶流失預測模型
CREATE OR REPLACE MODEL `project.dataset.churn_model`
OPTIONS(
  model_type='logistic_reg',
  input_label_cols=['churned']
) AS
SELECT
  -- 特徵工程
  DATE_DIFF(CURRENT_DATE(), last_purchase_date, DAY) as days_since_purchase,
  total_purchases,
  avg_order_value,
  customer_lifetime_value,
  support_tickets_count,
  -- 標籤
  churned
FROM customer_features
WHERE partition_date > DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY);

-- 使用模型進行預測
SELECT
  customer_id,
  ML.PREDICT(MODEL `project.dataset.churn_model`, 
    (SELECT * FROM current_customers WHERE customer_id = c.customer_id)
  ).predicted_churned as churn_probability
FROM current_customers c;

時間序列分析：預測和異常檢測

BigQuery 提供了專門的時間序列分析功能：

-- 使用時間序列分解來檢測異常
SELECT
  date,
  actual_value,
  -- 季節性分析
  AVG(actual_value) OVER (
    PARTITION BY EXTRACT(DAYOFWEEK FROM date)
    ORDER BY date
    ROWS BETWEEN 4 PRECEDING AND 4 FOLLOWING
  ) as seasonal_avg,
  -- 趨勢分析
  AVG(actual_value) OVER (
    ORDER BY date
    ROWS BETWEEN 30 PRECEDING AND 30 FOLLOWING
  ) as trend,
  -- 異常檢測：偏離季節性平均超過2個標準差
  ABS(actual_value - AVG(actual_value) OVER (
    PARTITION BY EXTRACT(DAYOFWEEK FROM date)
    ORDER BY date
    ROWS BETWEEN 4 PRECEDING AND 4 FOLLOWING
  )) > 2 * STDDEV(actual_value) OVER (
    PARTITION BY EXTRACT(DAYOFWEEK FROM date)
    ORDER BY date
    ROWS BETWEEN 4 PRECEDING AND 4 FOLLOWING
  ) as is_anomaly
FROM time_series_data
ORDER BY date;

統計分析函數：深度數據探索

BigQuery 內建了豐富的統計函數，支援各種統計分析需求：

-- 客戶價值分析
SELECT
  customer_segment,
  COUNT(*) as customer_count,
  -- 描述性統計
  AVG(annual_revenue) as avg_revenue,
  STDDEV(annual_revenue) as revenue_stddev,
  MIN(annual_revenue) as min_revenue,
  MAX(annual_revenue) as max_revenue,
  -- 分位數分析
  PERCENTILE_CONT(annual_revenue, 0.25) OVER() as q1_revenue,
  PERCENTILE_CONT(annual_revenue, 0.5) OVER() as median_revenue,
  PERCENTILE_CONT(annual_revenue, 0.75) OVER() as q3_revenue,
  -- 相關性分析
  CORR(annual_revenue, satisfaction_score) OVER() as revenue_satisfaction_corr
FROM customer_analysis
GROUP BY customer_segment;

進階資料處理函數

BigQuery 還提供了許多專門的資料處理函數：

-- 文本分析和數據清理
SELECT
  -- 正規表達式提取
  REGEXP_EXTRACT(email, r'@(.+)') as domain,
  -- 數據指紋（用於去重）
  FARM_FINGERPRINT(CONCAT(name, email, phone)) as record_fingerprint,
  -- 生成唯一識別碼
  GENERATE_UUID() as record_id,
  -- 文本相似度（用於數據匹配）
  EDIT_DISTANCE(name1, name2) as name_similarity,
  -- JSON 數據解析
  JSON_EXTRACT_SCALAR(user_profile, '$.preferences.language') as preferred_language
FROM user_data;

實務應用場景

商業智慧分析

BigQuery 在商業智慧領域的應用極其廣泛。企業可以輕鬆建立複雜的銷售漏斗分析、客戶生命週期價值分析、市場區隔分析等。例如，電商公司可以分析不同客戶群體的購買行為，識別高價值客戶，並制定精準的行銷策略。

銷售趨勢分析：通過視窗函數和時間序列分析，企業可以識別銷售的季節性模式、長期趨勢，以及異常波動。這些洞察對於庫存管理、行銷預算分配、業績預測都極其重要。

客戶行為分析：利用陣列處理和路徑分析功能，企業可以深入了解客戶在網站或應用中的行為軌跡，識別轉換的關鍵節點，優化用戶體驗。

即時數據分析

BigQuery 的流式數據處理能力使其成為即時分析的理想選擇：

網站流量監控：結合 Google Analytics 和其他數據源，企業可以即時監控網站效能、用戶行為，並在發現異常時立即採取行動。

IoT 數據處理：製造業、物流業等可以利用 BigQuery 處理來自感測器、設備的海量 IoT 數據，進行預測性維護、品質控制等。

金融風險監控：金融機構可以即時分析交易數據，檢測可疑的詐欺行為，確保業務安全。

進階分析應用

BigQuery 的機器學習整合和進階分析功能開啟了更多可能性：

預測分析：從客戶流失預測到需求預測，企業可以利用歷史數據建立預測模型，為未來的決策提供數據支持。

推薦系統：電商、媒體公司可以利用 BigQuery ML 建立個人化推薦系統，提升用戶參與度和轉換率。

異常檢測：無論是網路安全、品質控制還是業務營運，BigQuery 的異常檢測功能都能幫助企業及時發現問題。

總結

BigQuery 代表了數據分析技術的一個重要里程碑。它不僅僅是一個「會下 SQL 的資料庫」，而是一個功能完整、效能卓越的現代數據分析平台。從 Dremel 的學術研究到 BigQuery 的商業成功，這個演進過程展示了雲端運算如何徹底改變了數據分析的格局。

對於企業而言，BigQuery 提供了突破傳統基礎設施限制的機會，讓組織能夠專注於數據洞察而非技術維護。其豐富的分析功能、卓越的效能表現，以及與 Google Cloud 生態系統的深度整合，使其成為現代數據驅動型企業的理想選擇。

隨著數據量的持續增長和分析需求的日益複雜，BigQuery 這樣的雲端原生數據平台將繼續發揮重要作用，推動企業數據分析能力的不斷提升。

雲端界陳浩南 – BigQuery 是什麼？功能、組成元件、特色和優勢完整介紹

把資料上傳到 BigQuery 的各種方法和步驟

BigData, BigQuery, 大數據, 資料分析

東東 GCP 教學 - 最接地氣的雲端講師