如何编写SQL查询从GHTorrent BigQuery数据库获取各国各年月最常用编程语言数据

我来帮你搞定这个SQL查询！结合GHTorrent BigQuery的表结构，我写了一个完全匹配你需求的语句，解决表连接和维度统计的问题：

WITH user_country AS (
  -- 从用户表提取规范的国家代码，适配常见的location格式
  SELECT
    id AS user_id,
    CASE
      WHEN location LIKE '%, USA' OR location = 'United States' THEN 'US'
      WHEN location LIKE '%, CN' OR location = 'China' THEN 'CH'
      WHEN location LIKE '%, DE' OR location = 'Germany' THEN 'DE'
      -- 可以继续添加更多国家的映射规则
      WHEN location LIKE '%, %' THEN UPPER(RIGHT(location, 2))
      ELSE UPPER(LEFT(location, 2))
    END AS country
  FROM
    `ghtorrent-bq.ghtorrent.users`
  WHERE
    location IS NOT NULL -- 过滤无位置信息的无效用户
),
commit_language_mapping AS (
  -- 关联提交、文件与用户国家，统一语言识别规则
  SELECT
    uc.country,
    EXTRACT(YEAR FROM c.created_at) AS Year,
    FORMAT_DATE('%b', c.created_at) AS Month, -- 输出Jan/Feb这类月份缩写
    -- 通过文件名后缀识别编程语言（如果GHTorrent有language字段可直接替换）
    CASE
      WHEN cf.filename LIKE '%.py' THEN 'Python'
      WHEN cf.filename LIKE '%.java' THEN 'Java'
      WHEN cf.filename LIKE '%.js' OR cf.filename LIKE '%.ts' THEN 'JavaScript/TypeScript'
      WHEN cf.filename LIKE '%.rb' THEN 'Ruby'
      WHEN cf.filename LIKE '%.go' THEN 'Go'
      WHEN cf.filename LIKE '%.cpp' OR cf.filename LIKE '%.cc' THEN 'C++'
      ELSE 'Other'
    END AS Language,
    c.id AS commit_id,
    cf.bytes AS file_bytes
  FROM
    `ghtorrent-bq.ghtorrent.commits` c
  JOIN
    user_country uc ON c.author_id = uc.user_id
  JOIN
    `ghtorrent-bq.ghtorrent.commit_files` cf ON c.id = cf.commit_id
  WHERE
    c.created_at IS NOT NULL
    AND cf.bytes IS NOT NULL
)
-- 最终按维度分组统计
SELECT
  country,
  Year,
  Month,
  Language,
  COUNT(DISTINCT commit_id) AS `Number of commits`, -- 去重统计提交数（一个提交可能包含多个同语言文件）
  SUM(file_bytes) AS total_bytes
FROM
  commit_language_mapping
GROUP BY
  country, Year, Month, Language
ORDER BY
  country, Year, Month, `Number of commits` DESC;

核心细节说明： #

• 表连接逻辑：用commits.author_id关联用户表，commits.id关联提交文件表，确保数据关联准确，不会出现连接错误
• 国家代码处理：通过CASE语句统一不同格式的location字段，把"New York, USA"、"United States"这类输入都转为标准的"US"代码
• 时间维度转换：用EXTRACT和FORMAT_DATE函数把提交时间拆分成你需要的年份和月份缩写格式
• 统计准确性：用COUNT(DISTINCT commit_id)避免同一个提交被重复统计（毕竟一个提交可能包含多个同语言文件），SUM(file_bytes)直接累加对应维度下的总字节数

你可以根据需求扩展国家映射和语言识别的CASE分支，比如添加更多国家或编程语言规则。如果GHTorrent的表结构有细微调整（比如字段名变化），只需要修改对应字段即可。

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