本文介绍如何在 mariadb 中创建高性能视图，对数值列进行分组统计（出现频次）并动态计算各值最后一次出现 id 与全局最大 id 的差值，避免低效自连接，推荐使用窗口函数与聚合组合方案。
本文介绍如何在 mariadb 中创建高性能视图，对数值列进行分组统计（出现频次）并动态计算各值最后一次出现 id 与全局最大 id 的差值，避免低效自连接，推荐使用窗口函数与聚合组合方案。
在构建分析型视图时，常需基于原始表派生多维度指标：如唯一值枚举、频次统计，以及与位置相关的衍生差值（例如“某值最后一次出现距表末尾的距离”）。针对 numbers 表（含 id 和 number 字段），目标视图需输出三列：
- number：去重后的数值；
- occurrences：该数值在全表中出现的总次数；
- IDdifferences：该数值最大 id 与全表最大 id 的差值（即“距末尾还有几个 ID 位置”）。
直接使用 DISTINCT + 多表 JOIN 计算频次和差值，不仅逻辑复杂，更易引发笛卡尔积和全表扫描，导致性能急剧下降。正确解法是融合聚合函数与窗口函数，一次扫描完成全部计算。
✅ 推荐方案：聚合 + 窗口函数（适用于连续或稀疏 ID）
CREATE VIEW numbers_summary AS SELECT number, COUNT(*) AS occurrences, MAX(MAX(id)) OVER () - MAX(id) AS IDdifferences FROM numbers GROUP BY number ORDER BY number;
关键解析：
- GROUP BY number 实现按数值分组；
- COUNT(*) 统计每组出现次数；
- MAX(id) 在每组内获取该 number 的最大 id（即最后一次出现位置）；
- MAX(MAX(id)) OVER () 是窗口函数嵌套：内层 MAX(id) 按组计算，外层 MAX(...) OVER () 对全结果集取最大值（即全局最大 id），无需子查询；
- 差值 IDdifferences = 全局最大 id − 当前 number 的最大 id，直观反映“距离表尾的 ID 个数”。
✅ 优势：仅一次全表扫描，无 JOIN，执行计划简洁，MariaDB 10.2+ 原生支持，性能最优。
⚠️ 进阶适配：应对 ID 不连续（逻辑序号优先）
若 id 存在删除/跳号（如 id 序列为 1,2,4,7），而业务要求“按插入顺序计算距离”，则应以逻辑行序（而非物理 id）为准。此时需用 ROW_NUMBER() 构建稠密序号：
CREATE VIEW numbers_summary_logical AS SELECT number, COUNT(*) AS occurrences, MAX(MAX(rn)) OVER () - MAX(rn) AS IDdifferences FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM numbers ) t GROUP BY number ORDER BY number;
说明：
- 子查询 t 为每行分配严格递增的逻辑序号 rn（按 id 排序保证时序）；
- 后续计算均基于 rn，确保 IDdifferences 反映“在有序列表中距末尾的行数”，不受 id 空洞影响。
? 注意事项与最佳实践
-
索引优化：为提升 GROUP BY number 和 ORDER BY id 性能，建议创建复合索引：
CREATE INDEX idx_number_id ON numbers(number, id);
- NULL 安全性：若 number 可为空，GROUP BY number 会将所有 NULL 归为一组；如需单独处理，可添加 WHERE number IS NOT NULL。
- 视图可更新性：本视图含聚合与窗口函数，属于只读视图，不可 INSERT/UPDATE。
-
兼容性提示：MAX(MAX(id)) OVER () 语法在 MariaDB 10.2+ 和 MySQL 8.0+ 支持；旧版本需改用子查询（牺牲部分性能）：
-- 替代写法（兼容旧版） SELECT n.number, COUNT(*) AS occurrences, (SELECT MAX(id) FROM numbers) - MAX(n.id) AS IDdifferences FROM numbers n GROUP BY n.number ORDER BY n.number;
通过合理运用 GROUP BY 与窗口函数，你能在单条 SELECT 中优雅、高效地实现多维度聚合与跨组比较，这是构建轻量级分析视图的核心范式。
