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;

编辑并运行代码