能否通过SQL查询BigQuery公开姓名表中每年使用频次最低的姓名？

完全可以基于该表结构实现需求，以下是分场景的实现思路和可直接运行的BigQuery标准SQL代码：

场景1：查询全美国范围内每年使用人数最少的姓名（含并列最低） #

该场景需要先按年、姓名维度聚合得到全美国年度总使用人数，再通过窗口函数排序取最低值：

WITH annual_name_total AS (
  SELECT 
    year,
    name,
    SUM(number) AS total_use_count
  FROM `bigquery-public-data.usa_names.usa_1910_2020`
  GROUP BY year, name
),
ranked_names AS (
  SELECT 
    year,
    name,
    total_use_count,
    RANK() OVER (PARTITION BY year ORDER BY total_use_count ASC) AS rk
  FROM annual_name_total
)
SELECT year, name, total_use_count
FROM ranked_names
WHERE rk = 1
ORDER BY year DESC;

场景2：查询分州、分性别维度下每年使用人数最少的姓名（含并列最低） #

该场景不需要额外聚合，直接基于原始表的维度分组排序即可：

WITH ranked_names AS (
  SELECT 
    year,
    state,
    gender,
    name,
    number AS use_count,
    RANK() OVER (PARTITION BY year, state, gender ORDER BY number ASC) AS rk
  FROM `bigquery-public-data.usa_names.usa_1910_2020`
)
SELECT year, state, gender, name, use_count
FROM ranked_names
WHERE rk = 1
ORDER BY year DESC, state, gender;

补充说明 #

• 如果不需要返回并列的最低值，仅需每个分组返回任意一个最低值，将代码中的RANK()函数替换为ROW_NUMBER()即可
• 如果需要调整统计维度，仅需修改PARTITION BY后的分组字段即可适配需求

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