基于BigQuery Legacy SQL实现按客户统计TOP2高绩效产品类别KPI

Got it, let's break this down to get exactly what you need: a KPI report showing each customer's top 2 product categories based on performance. We'll focus on total sales revenue as the primary performance metric (since that's the most common KPI), but I'll also show you how to switch to total quantity sold if that's what you need.

Step-by-Step Query #

First, here's the complete Legacy SQL query—just replace the table path with your actual dataset and table name:

SELECT
  customerid,
  product_category,
  total_sales_amt,
  total_order_qty,
  category_rank
FROM (
  -- Inner query: Aggregate metrics and rank categories per customer
  SELECT
    customerid,
    product_category,
    SUM(product_amt) AS total_sales_amt, -- Total revenue for the category
    SUM(order_qty) AS total_order_qty,   -- Total units sold for the category
    -- Rank categories by revenue (descending) for each customer
    RANK() OVER(PARTITION BY customerid ORDER BY SUM(product_amt) DESC) AS category_rank
  FROM
    [your-project:your-dataset.your-sales-table] -- Replace with your table path
  GROUP BY
    customerid,
    product_category
) ranked_categories
-- Filter to only keep top 2 categories per customer
WHERE category_rank <= 2
-- Sort results for readability
ORDER BY customerid, category_rank

Key Explanations #

• Aggregation Layer: The inner query groups data by customerid and product_category to calculate core KPIs: total sales revenue (total_sales_amt) and total units sold (total_order_qty).
• Ranking Layer: The RANK() window function partitions the data by customerid (so rankings are per customer) and orders categories by total revenue in descending order. This assigns a rank of 1 to the highest-performing category, 2 to the second, etc.
• Filtering: The outer query keeps only categories with a rank of 1 or 2, giving you the top 2 per customer.

Switch to Quantity-Based Performance #

If you want to rank by total units sold instead of revenue, just update the ORDER BY clause in the window function:

-- Replace this line in the inner query
RANK() OVER(PARTITION BY customerid ORDER BY SUM(order_qty) DESC) AS category_rank

Notes #

• Make sure you're using BigQuery's Legacy SQL (not Standard SQL) when running this—you can toggle this in the BigQuery UI under "Query settings".
• The table path format [project:dataset.table] is specific to Legacy SQL; don't use backticks here (those are for Standard SQL).

内容的提问来源于stack exchange，提问作者bunny