BigQuery中如何实现行列转换计算分性别指标及构成占比

实现思路 #

• 先清洗原表字段冗余的尾缀点号，同时将月份格式化为需求要求的Jan-22短格式
• 用UNPIVOT函数把原表宽表中的sales、quantity两个指标列转置为行结构，方便统一聚合
• 按月份、指标维度分组，通过条件聚合计算整体汇总值、男女分群指标值，最后计算各性别指标占整体的比例

可直接运行的查询语句 #

-- 替换下方表路径为你实际的BigQuery表地址
WITH cleaned_source AS (
  SELECT
    FORMAT_DATE("%b-%y", PARSE_DATE("%b-%Y", TRIM(month))) AS month,
    TRIM(gender, '.') AS gender,
    CAST(TRIM(CAST(sales AS STRING), '.') AS FLOAT64) AS sales,
    CAST(TRIM(CAST(quantity AS STRING), '.') AS FLOAT64) AS quantity
  FROM `your_project.your_dataset.your_table`
),
unpivoted_metrics AS (
  SELECT
    month,
    gender,
    metric_name,
    metric_value
  FROM cleaned_source
  UNPIVOT(
    metric_value FOR metric_name IN (sales, quantity)
  )
)
SELECT
  month,
  CONCAT(metric_name, '.') AS metrics,
  SUM(metric_value) AS overall,
  SUM(IF(gender = 'male', metric_value, 0)) AS male,
  CONCAT(ROUND(SUM(IF(gender = 'male', metric_value, 0)) / SUM(metric_value) * 100, 0), '%') AS `composition (%)_male`,
  SUM(IF(gender = 'female', metric_value, 0)) AS female,
  CONCAT(ROUND(SUM(IF(gender = 'female', metric_value, 0)) / SUM(metric_value) * 100, 0), '%') AS `composition (%)_female`
FROM unpivoted_metrics
GROUP BY month, metric_name
ORDER BY month, metric_name

说明 #

SQL语法不支持返回同名字段，上述代码中两个占比列分别命名为composition (%)_male、composition (%)_female，分别对应男性、女性指标值占整体的比例，和需求给出的样例逻辑完全一致。

• 运行上述语句针对你给出的样例数据，sales指标会返回整体5000、男性2000占比40%、女性3000占比60%；quantity指标返回整体150、男性50占比33%、女性100占比67%
• 如果需要和样例里的66%效果完全匹配（即对66.66%这类值做截断取整而非四舍五入），把占比计算逻辑中的ROUND函数替换为TRUNC函数即可

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