Oracle 创建视图时如何在月度分组统计结果中添加全量汇总行

实现逻辑 #

直接用UNION ALL拼接两段独立的统计查询，完全基于视图的逻辑查询能力完成，不需要做任何物理数据插入，从根源上解决之前插入方式无效的问题：

• 第一段查询复用你原有编写的聚合逻辑，按分类维度完成指标统计，保留原有百分比字段的计算规则
• 第二段查询独立计算全量范围的指标总和，type字段固定返回'overall'，百分比类字段按要求直接返回'-'

可直接运行的视图代码 #

CREATE OR REPLACE VIEW v_table1 AS
-- 按分类维度的聚合统计
SELECT
  type,
  SUM(NVL(volume, 0)) AS volume,
  SUM(NVL(words, 0)) AS words,
  AVG(NVL(words_per, 0)) || '%' AS words_per,
  SUM(NVL(none, 0)) AS none,
  AVG(NVL(none_per, 0)) || '%' AS none_per
FROM table1
-- 若需要限定单月统计，在这里加和下方查询完全一致的过滤条件，例如：WHERE date = TO_DATE('07-APR-21', 'DD-MON-RR', 'NLS_DATE_LANGUAGE = American')
GROUP BY type

UNION ALL

-- 全量汇总行
SELECT
  'overall' AS type,
  SUM(NVL(volume, 0)) AS volume,
  SUM(NVL(words, 0)) AS words,
  '-' AS words_per,
  SUM(NVL(none, 0)) AS none,
  '-' AS none_per
FROM table1
-- 过滤条件必须和上方分类统计完全对齐，保证两个部分的统计范围完全一致
;

落地注意事项 #

• UNION ALL拼接的两个结果集，同位置的字段数据类型必须兼容：当前实现里，分类部分的百分比字段是字符串拼接生成的字符类型，和汇总行的'-'字符类型天然匹配，不需要额外转换；如果后续遇到数字、日期类字段的拼接，需要手动用TO_CHAR、TO_NUMBER等类型转换函数对齐，否则会触发Oracle类型不匹配的报错
• 如果需要实现按月分块的统计效果（即每个月的分类结果后面紧跟当月的overall汇总行），只需要在两段查询中都把月份维度加入分组字段、同时加入SELECT列表即可，第一段按月份、type分组，第二段按月份分组，拼接逻辑不变
• 如果需要保证overall行固定展示在分类结果的最后，可以在外层套一层排序逻辑：给分类统计部分加值为1的排序标记字段，给overall部分加值为2的排序标记字段，最终按标记字段排序即可实现目标样例的展示顺序
• 视图本身是逻辑查询的虚拟表，没有物理存储的数据，不支持直接执行插入操作，所有汇总逻辑都必须写在查询语句内部，这也是之前尝试用插入方式添加汇总行无法生效的核心原因

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