如何在SQL中向现有表指定位置添加总计、平均值汇总统计行

问题原因 #

你写的查询存在4个核心问题导致无法得到预期结果：

• 语法错误：三个子查询的SELECT字段列表末尾都多了冗余逗号，直接触发语法报错
• 原始数据排序逻辑错误：所有原始行的排序标记都设为固定值1，无法保证原7行数据的展示顺序和原表一致
• 潜在字段类型不兼容：如果原表weekday是数值类型，汇总行传入的TOTAL/AVG字符串会触发类型匹配报错
• 无列别名定义：子查询的计算字段没有设置别名，外部查询返回的列名会错乱

解决方案 #

假设你的原表7行数据的weekday字段值为0-6（对应一周7天，是唯一有序的标识），可以按如下逻辑修改查询：

SELECT 
  weekday, 
  summer_member_total, summer_member_avg_duration, summer_casual_total, summer_casual_avg_duration,
  fall_member_total, fall_member_avg_duration, fall_casual_total, fall_casual_avg_duration, 
  winter_member_total, winter_member_avg_duration, winter_casual_total, winter_casual_avg_duration,
  spring_member_total, spring_member_avg_duration, spring_casual_total, spring_casual_avg_duration,
  member_total, member_avg_duration, casual_total, casual_avg_duration
FROM
(
  -- 原始7行数据，用weekday作为排序依据，保证顺序正确
  SELECT 
    weekday AS sort_key,
    CAST(weekday AS STRING) AS weekday, -- 统一转成字符串类型，和汇总行兼容
    summer_member_total, summer_member_avg_duration, summer_casual_total, summer_casual_avg_duration,
    fall_member_total, fall_member_avg_duration, fall_casual_total, fall_casual_avg_duration, 
    winter_member_total, winter_member_avg_duration, winter_casual_total, winter_casual_avg_duration,
    spring_member_total, spring_member_avg_duration, spring_casual_total, spring_casual_avg_duration,
    member_total, member_avg_duration, casual_total, casual_avg_duration
  FROM `case-study-319921.2020_2021_Trip_Data.2020_2021_Summary_Stats` 
  
  UNION ALL    
  
  -- 总计行，排序键设为8，排在原始数据之后
  SELECT 
    8 AS sort_key,
    'TOTAL' AS weekday,
    SUM(summer_member_total), SUM(summer_member_avg_duration), SUM(summer_casual_total), SUM(summer_casual_avg_duration),
    SUM(fall_member_total), SUM(fall_member_avg_duration), SUM(fall_casual_total), SUM(fall_casual_avg_duration),
    SUM(winter_member_total), SUM(winter_member_avg_duration), SUM(winter_casual_total), SUM(winter_casual_avg_duration),
    SUM(spring_member_total), SUM(spring_member_avg_duration), SUM(spring_casual_total), SUM(spring_casual_avg_duration),
    SUM(member_total), SUM(member_avg_duration), SUM(casual_total), SUM(casual_avg_duration)
  FROM `case-study-319921.2020_2021_Trip_Data.2020_2021_Summary_Stats` 
  
  UNION ALL
  
  -- 平均值行，排序键设为9，排在总计之后
  SELECT 
    9 AS sort_key,
    'AVG' AS weekday,
    AVG(summer_member_total), AVG(summer_member_avg_duration), AVG(summer_casual_total), AVG(summer_casual_avg_duration),
    AVG(fall_member_total), AVG(fall_member_avg_duration), AVG(fall_casual_total), AVG(fall_casual_avg_duration),
    AVG(winter_member_total), AVG(winter_member_avg_duration), AVG(winter_casual_total), AVG(winter_casual_avg_duration),
    AVG(spring_member_total), AVG(spring_member_avg_duration), AVG(spring_casual_total), AVG(spring_casual_avg_duration),
    AVG(member_total), AVG(member_avg_duration), AVG(casual_total), AVG(casual_avg_duration)
  FROM `case-study-319921.2020_2021_Trip_Data.2020_2021_Summary_Stats` 
) t
ORDER BY sort_key

实现逻辑说明 #

• 新增独立的sort_key字段作为排序依据，原始行用原表weekday值（0-6）作为排序键，总计、平均值分别用8、9作为排序键，最终排序后自动满足原始7行在前，汇总行从第8行开始的要求
• 把原始行的weekday统一转成字符串类型，和汇总行的TOTAL/AVG类型兼容，避免类型报错
• 移除了所有冗余逗号，修复语法问题
• 所有字段都有明确的别名定义，返回结果列名和原表一致

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