BigQuery单查询实现金属类红色产品统计及比率计算需求

你可以通过单次预过滤CTE结合BigQuery的多语句脚本，在一个可调度查询里完成所有操作，彻底避免重复编写WHERE material = 'metal'的过滤条件。

完整查询脚本 #

-- 预过滤所有material为metal的产品，仅执行一次过滤逻辑
WITH metal_products AS (
  SELECT product, maker, color, material
  FROM `your-project.your-dataset.products`
  WHERE material = 'metal'
)

-- 写入错误列表metal_red表
INSERT INTO `your-project.your-dataset.metal_red` (product, maker, color, material)
SELECT product, maker, color, material
FROM metal_products
WHERE color = 'red';

-- 计算比率并写入red_metal_ratio表
INSERT INTO `your-project.your-dataset.red_metal_ratio` (count_metals_all, count_metals_red, red_metals_ratio)
SELECT
  COUNT(*) AS count_metals_all,
  SUM(CASE WHEN color = 'red' THEN 1 ELSE 0 END) AS count_metals_red,
  SAFE_DIVIDE(SUM(CASE WHEN color = 'red' THEN 1 ELSE 0 END), COUNT(*)) AS red_metals_ratio
FROM metal_products;

核心优化说明 #

• 单次过滤复用：metal_products CTE只执行一次material = 'metal'的过滤，后续所有操作都基于这个已过滤的数据集，完全消除重复WHERE子句
• 单脚本多操作：BigQuery支持在单个查询脚本中执行多个INSERT语句，直接将整个脚本设为调度任务即可满足“1个可调度查询”的要求
• 数据一致性保障：所有计算和插入都基于同一批次的metal_products数据，避免多次查询原表可能出现的数据不一致问题
• 匹配业务需求：同时生成metal_red错误排查表和red_metal_ratioKPI追踪表，完全覆盖你的业务目标

注意事项 #

• 替换脚本中的your-project.your-dataset为你的实际项目和数据集名称
• 默认INSERT为追加模式，若需要覆盖表数据，可改用INSERT OVERWRITE
• SAFE_DIVIDE用于避免分母为0的报错，提升比率计算的鲁棒性

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