如何在BigQuery中获取各年度的最高销售额？

我来帮你搞定这个查询需求～首先得明确：你说的“最高销售额”应该是指年度总销量最高的手机，或者是年度内单月最高销量的记录？我把两种情况的解法都写出来，你可以按需选择。

情况1：查询每个年度总销量最高的手机及对应销量 #

这个场景是先统计每款手机在对应年份的总销量，再找出每个年份里总销量Top1的记录。用窗口函数来实现最方便：

WITH table1 AS (
  SELECT "iphone" AS phone, 3 AS sold_out, "Jan" AS month, 2015 AS year UNION ALL
  SELECT "iphone", 10, "Feb", 2015 UNION ALL
  SELECT "samsung", 4, "March", 2015 UNION ALL
  SELECT "Lava", 14, "June", 2016 UNION ALL
  SELECT "Lenova", 8, "July", 2016 UNION ALL
  SELECT "Lenova", 10, "Sep", 2016 UNION ALL
  SELECT "Motorola", 8, "Jan", 2017 UNION ALL
  SELECT "Nokia", 7, "Jan", 2017 UNION ALL
  SELECT "Nokia", 3, "Feb", 2017
),
-- 第一步：计算每款手机每年的总销量
annual_sales AS (
  SELECT
    year,
    phone,
    SUM(sold_out) AS total_sold
  FROM table1
  GROUP BY year, phone
),
-- 第二步：给每个年份内的总销量排名
ranked_sales AS (
  SELECT
    year,
    phone,
    total_sold,
    RANK() OVER (PARTITION BY year ORDER BY total_sold DESC) AS sales_rank
  FROM annual_sales
)
-- 第三步：筛选出每个年份排名第一的记录
SELECT
  year,
  phone AS top_selling_phone,
  total_sold AS highest_annual_sales
FROM ranked_sales
WHERE sales_rank = 1;

结果说明： #

• 2015年：iPhone总销量13，是年度最高
• 2016年：Lenova总销量18（8+10），超过Lava的14
• 2017年：Nokia总销量10（7+3），高于Motorola的8

如果同一年有多个手机总销量并列第一，RANK()会返回所有并列的记录；如果你只想取其中一条，可以换成ROW_NUMBER()，不过RANK()更符合业务逻辑。

情况2：查询每个年度内单月销量最高的记录 #

如果你的需求是找每个年份里单月销量最高的那条数据（不需要统计年度总和），解法更简单：

WITH table1 AS (
  SELECT "iphone" AS phone, 3 AS sold_out, "Jan" AS month, 2015 AS year UNION ALL
  SELECT "iphone", 10, "Feb", 2015 UNION ALL
  SELECT "samsung", 4, "March", 2015 UNION ALL
  SELECT "Lava", 14, "June", 2016 UNION ALL
  SELECT "Lenova", 8, "July", 2016 UNION ALL
  SELECT "Lenova", 10, "Sep", 2016 UNION ALL
  SELECT "Motorola", 8, "Jan", 2017 UNION ALL
  SELECT "Nokia", 7, "Jan", 2017 UNION ALL
  SELECT "Nokia", 3, "Feb", 2017
),
ranked_monthly_sales AS (
  SELECT
    year,
    month,
    phone,
    sold_out,
    RANK() OVER (PARTITION BY year ORDER BY sold_out DESC) AS monthly_sales_rank
  FROM table1
)
SELECT
  year,
  month,
  phone,
  sold_out AS highest_monthly_sales_in_year
FROM ranked_monthly_sales
WHERE monthly_sales_rank = 1;

结果说明： #

• 2015年：iPhone在2月卖出10台，是年度单月最高
• 2016年：Lava在6月卖出14台，是年度单月最高
• 2017年：Motorola在1月卖出8台，是年度单月最高

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