如何在BigQuery中将各类查询的总行数作为新列输出？

针对你需要合并多段SQL统计、输出用于可视化的结构化结果的需求，这里提供两种高效的BigQuery实现方案：

方案一：按天分组统计（含总计行，适合趋势可视化） #

这种方案会按星期几分组，每行输出当天的总骑行数、会员骑行数、临时用户骑行数，同时自动生成总计行，非常适合做每日趋势类的可视化。

WITH daily_stats AS (
  SELECT
    -- 将数字型星期转换为名称，更直观
    CASE day_of_week
      WHEN 1 THEN 'Sunday'
      WHEN 2 THEN 'Monday'
      WHEN 3 THEN 'Tuesday'
      WHEN 4 THEN 'Wednesday'
      WHEN 5 THEN 'Thursday'
      WHEN 6 THEN 'Friday'
      WHEN 7 THEN 'Saturday'
      ELSE 'Total' END AS day_name,
    COUNT(*) AS total_riders,
    -- 条件聚合统计会员数
    COUNT(CASE WHEN member_casual = 'member' THEN 1 END) AS member_riders,
    -- 条件聚合统计临时用户数
    COUNT(CASE WHEN member_casual = 'casual' THEN 1 END) AS casual_riders
  FROM `coreyneergaard-capstone.analyze.2021-09-times`
  -- ROLLUP用于生成总计行
  GROUP BY ROLLUP(day_of_week)
)
SELECT *
FROM daily_stats
-- 排序：先按星期顺序，最后显示总计行
ORDER BY 
  CASE day_name 
    WHEN 'Total' THEN 8 
    ELSE day_of_week END;

方案二：单行列输出所有统计值（符合你"首行输出"的需求） #

如果需要把所有统计值集中在一行输出，直接用子查询定义每个统计列即可，整个查询仅返回一行结果：

SELECT
  -- 全局统计
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times`) AS total_all_riders,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE member_casual = 'member') AS total_member_riders,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE member_casual = 'casual') AS total_casual_riders,
  
  -- 周日统计
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 1) AS sunday_total,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 1 AND member_casual = 'member') AS sunday_member,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 1 AND member_casual = 'casual') AS sunday_casual,
  
  -- 周一统计
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 2) AS monday_total,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 2 AND member_casual = 'member') AS monday_member,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 2 AND member_casual = 'casual') AS monday_casual,
  
  -- 周二统计
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 3) AS tuesday_total,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 3 AND member_casual = 'member') AS tuesday_member,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 3 AND member_casual = 'casual') AS tuesday_casual,
  
  -- 周三统计
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 4) AS wednesday_total,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 4 AND member_casual = 'member') AS wednesday_member,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 4 AND member_casual = 'casual') AS wednesday_casual,
  
  -- 周四统计
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 5) AS thursday_total,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 5 AND member_casual = 'member') AS thursday_member,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 5 AND member_casual = 'casual') AS thursday_casual,
  
  -- 周五统计
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 6) AS friday_total,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 6 AND member_casual = 'member') AS friday_member,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 6 AND member_casual = 'casual') AS friday_casual,
  
  -- 周六统计
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 7) AS saturday_total,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 7 AND member_casual = 'member') AS saturday_member,
  (SELECT COUNT(*) FROM `coreyneergaard-capstone.analyze.2021-09-times` WHERE day_of_week = 7 AND member_casual = 'casual') AS saturday_casual

对现有代码的优化说明 #

你当前的代码会因为GROUP BY ride_length生成大量重复行（每个不同的骑行时长都会重复输出全局统计值），不仅冗余还浪费查询资源。上面的两种方案都只扫描一次或少量几次表，效率更高，结果也更适合可视化使用。

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