BigQuery多指标宽表转长表：按维度聚合与数据清洗

输入数据格式 #

实际数据包含数百万User_ID、24个月数据，还有大量无需保留的维度与指标列。

需求 #

• 按Year_Month和User_ID分组，按维度对指标求和
• 将数据从宽格式转换为长格式
• 清洗维度值：例如将mobile和tablet合并为mobile，这类清洗涉及多列

现有代码（仅支持单指标） #

create table test_wide as 
(
select Year_Month,
User_ID,
sum(case when Dim_1 = 'cat' then Metric_1 end) as Cat_m1,
sum(case when Dim_1 = 'dog' then Metric_1 end) as Dog_m1,
sum(case when Dim_1 = 'mouse' then Metric_1 end) as Mouse_m1,
sum(case when Dim_2 in ('mobile', 'tablet') then Metric_1 end) as Mobile_m1,
sum(case when Dim_2  = 'desktop' then Metric_1 end) as Desktop_m1
from data
group by 1,2
) 
;

/* Convert from wide to long */

SELECT Year_Month, User_ID,
    Dim, 
    SAFE_CAST(value AS int64) value
FROM (
    SELECT Year_Month, User_ID, 
      REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(0)], r'^"|"$', '') Dim, 
      REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(1)], r'^"|"$', '') Value 
    FROM test_wide t, 
    UNNEST(SPLIT(REGEXP_REPLACE(to_json_string(t), r'{|}', ''))) pair
  )
  WHERE NOT LOWER(Dim) IN ('year_month', 'user_id')
  and SAFE_CAST(value AS int64) > 0

期望输出格式 #

以下代码针对多指标场景优化，无需中间表，性能更适配百万级数据量，同时完成维度清洗、聚合和宽转长：

WITH cleaned_dimensions AS (
  -- 处理Dim_1维度，保留原始值
  SELECT
    Year_Month,
    User_ID,
    Dim_1 AS Dimension,
    Metric_1,
    Metric_2
  FROM data
  UNION ALL
  -- 处理Dim_2维度，合并mobile和tablet为mobile
  SELECT
    Year_Month,
    User_ID,
    CASE WHEN Dim_2 IN ('mobile', 'tablet') THEN 'mobile' ELSE Dim_2 END AS Dimension,
    Metric_1,
    Metric_2
  FROM data
),
aggregated_data AS (
  -- 按年月、用户、维度分组求和
  SELECT
    Year_Month,
    User_ID,
    Dimension,
    SUM(Metric_1) AS Metric_1,
    SUM(Metric_2) AS Metric_2
  FROM cleaned_dimensions
  GROUP BY Year_Month, User_ID, Dimension
)
-- 输出最终长格式数据
SELECT * FROM aggregated_data
ORDER BY Year_Month, User_ID, Dimension;

代码说明 #

1. 维度整合与清洗：通过UNION ALL将不同维度列（Dim_1、Dim_2）合并到同一维度字段中，同时完成指定维度值的清洗合并
2. 分组聚合：直接按目标维度分组，对多指标求和，避免中间宽表的存储开销
3. 扩展性强：若有更多维度需要处理，只需在cleaned_dimensions中添加对应的UNION ALL分支即可

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