RANK 與 LEAD/LAG

排名可以依你指定的順序為資料排序；而 LAG 與 LEAD 函式則能在查詢的視窗中向前或向後參照資料。這些工具有助於建立有說服力的報表；在 LAG/LEAD 的情境下，也能實作更複雜的分析功能。這裡你會用我們的資料實際操作這些技巧。

本練習屬於課程

BigQuery 入門

檢視課程

動手互動練習

試著完成這個範例程式碼，體驗一下這個練習。

-- Complete the query to order customers by the total amount spent

-- First, write a CTE to group customers and find their total amount spent
WITH orders AS (
  SELECT
  -- Add the correct columns and aggregate functions
  ___,
  SUM(___) as all_items
  FROM ecommerce.ecomm_orders o, UNNEST(o.order_items) items
  JOIN ecommerce.ecomm_order_details od USING (order_id)
  GROUP BY od.customer_id
)

SELECT
	customer_id,
	all_items,
-- Fill in the RANK window function and OVER clause
RANK() ___(ORDER BY ___ DESC)
FROM orders
ORDER BY all_items DESC;

編輯並執行程式碼