本文详解如何基于原始数值表创建高性能视图，实现三类关键计算：数值去重、出现频次统计，以及各数值最后一次出现 id 与全局最大 id 的差值（或按序号对齐的间隔），避免低效自连接。
本文详解如何基于原始数值表创建高性能视图，实现三类关键计算：数值去重、出现频次统计，以及各数值最后一次出现 id 与全局最大 id 的差值（或按序号对齐的间隔），避免低效自连接。
在 MariaDB 中构建具备聚合与窗口计算能力的视图，是数据汇总与分析的常见需求。针对 numbers 表（含 id 和 number 两列），目标视图需输出三列：
- number：唯一数值（去重）；
- occurrences：该数值在全表中出现的总次数；
- IDdifferences：该数值最后一次出现的 id 与表中最大 id 的差值（即“距离末尾还有几个 ID 位置”）。
✅ 推荐方案：GROUP BY + 窗口函数（高效且语义清晰）
直接使用 GROUP BY number 进行分组，并结合聚合函数与窗口函数，可一次性完成全部计算，无需 JOIN，性能优异：
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;
✅ 执行逻辑说明：
- COUNT(*) 统计每组 number 的出现次数；
- MAX(id) 在每组内获取该 number 对应的最大 id（即最后一次出现位置）；
- MAX(MAX(id)) OVER () 是窗口函数：先对每组取 MAX(id)，再在整个结果集上取最大值——等价于 SELECT MAX(id) FROM numbers，即全局最大 ID；
- 二者相减即得 IDdifferences（例如 number = 41 出现在 id=2 和 id=6，MAX(id)=6；全局 MAX(id)=8，故差值为 8−6 = 2）。
? 验证示例：原始数据中 id=8 是最大值。number=14 仅出现在 id=8，所以 IDdifferences = 8−8 = 0；number=12 出现在 id=7，差值为 8−7 = 1——完全匹配预期结果。
⚠️ 注意事项：ID 可能存在空缺时的健壮处理
若表中 id 不连续（如删除过记录导致 id 序列存在空洞），直接用 id 计算“位置间隔”可能产生误导（例如 id 跳跃过大，不代表真实逻辑顺序）。此时应改用逻辑序号（row number） 替代物理 id：
CREATE VIEW numbers_summary_robust 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;
? 此写法通过子查询生成严格递增的逻辑序号 rn（按 id 升序排列），确保 IDdifferences 反映的是“在有序列表中的相对位置距离”，而非受空洞干扰的物理 ID 差值，显著提升业务语义准确性。
✅ 总结与最佳实践
- 拒绝自连接：原问题中尝试 LEFT JOIN 自身计数，易引发笛卡尔积与重复扫描，复杂度 O(n²)，应彻底避免；
- 优先 GROUP BY + 窗口函数：MariaDB 10.2+ 完整支持窗口函数，本方案时间复杂度为 O(n log n)（主要来自排序），性能远超关联查询；
- 明确业务含义：确认 IDdifferences 是否依赖物理 id（如日志序列号）还是逻辑顺序（如插入时序），据此选择 id 或 ROW_NUMBER()；
- 视图可索引性提示：MariaDB 视图本身不存储数据，但底层表应在 number 和 id 上建立适当索引（如 INDEX idx_number_id (number, id)）以加速 GROUP BY 和 MAX() 计算。
通过以上方法，你将获得一个轻量、可维护、高性能的分析视图，为后续报表、BI 工具或应用层调用提供坚实的数据基础。
