如何在BigQuery中通过SQL实现行列转换？

问题背景 #

作为BigQuery新手，我需要将按犯罪类型和年份分组统计的数据，转换为以年份为行、各犯罪类型为列的格式。

样本数据 #

当前查询及结果 #

已实现分组统计的SQL：

select primary_type,
    year,
   count(*) as number_of_crime 
FROM
`bigquery-public-data`
group by primary_type, year
order by year asc;

查询结果：

目标结果格式 #

想要生成如下行列转换后的结果：

解决方案 #

使用BigQuery内置的PIVOT操作可以直接实现行转列，以下是两种可行的实现方式：

方式1：基于分组统计结果转列 #

先通过CTE生成基础统计数据，再进行转列：

WITH crime_summary AS (
    SELECT 
        primary_type,
        year,
        COUNT(*) AS crime_count
    FROM `bigquery-public-data`
    GROUP BY primary_type, year
)
SELECT 
    year,
    robery,
    BATTERY
FROM crime_summary
PIVOT (
    SUM(crime_count)  -- 分组后每组唯一，SUM等价于直接取值
    FOR primary_type IN ('robery' AS robery, 'BATTERY' AS BATTERY)
)
ORDER BY year ASC;

方式2：直接对原表转列聚合 #

跳过中间统计步骤，直接在原表上完成聚合与转列：

SELECT 
    year,
    robery,
    BATTERY
FROM (
    SELECT primary_type, year 
    FROM `bigquery-public-data`
)
PIVOT (
    COUNT(*)  -- 直接统计每个年份下各犯罪类型的记录数
    FOR primary_type IN ('robery' AS robery, 'BATTERY' AS BATTERY)
)
ORDER BY year ASC;

如果后续需要新增其他犯罪类型的列，只需在IN子句中添加对应项即可，例如'THEFT' AS THEFT。

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