如何用BigQuery计算美国各州最畅销汽车品牌

原始数据表格 #

需求说明 #

忽略车型字段，按州分组统计该州出现次数最多的汽车品牌；若某州仅有一种品牌（或所有品牌出现次数相同），直接返回该品牌。

BigQuery SQL解决方案 #

WITH brand_counts AS (
  -- 统计每个州下各品牌的出现次数
  SELECT
    State,
    Brand,
    COUNT(*) AS total_count
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的实际表路径
  GROUP BY
    State, Brand
),
ranked_brands AS (
  -- 给每个州的品牌按销量降序排名
  SELECT
    State,
    Brand,
    total_count,
    -- 用ROW_NUMBER()确保每个州仅返回一个最畅销品牌（若有并列则随机选其一）
    -- 若需保留所有并列第一的品牌，替换为RANK()
    ROW_NUMBER() OVER (PARTITION BY State ORDER BY total_count DESC) AS sales_rank
  FROM
    brand_counts
)
-- 筛选每个州排名第一的品牌
SELECT
  State,
  Brand AS top_selling_brand,
  total_count
FROM
  ranked_brands
WHERE
  sales_rank = 1
ORDER BY
  State;

结果示例 #

运行上述SQL后，将得到符合需求的结果：

关键细节解释 #

• brand_counts 公共表表达式（CTE）：通过GROUP BY State, Brand忽略车型，统计每个品牌在对应州的总出现次数。
• ROW_NUMBER() 窗口函数：按州分区（PARTITION BY State），在每个州内按销量降序排序，给每个品牌分配排名。如果需要保留并列第一的多个品牌，将ROW_NUMBER()替换为RANK()即可。

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