本文介绍如何在 mariadb 中创建高性能视图，对数值字段进行去重、频次统计，并动态计算各数值最后一次出现 id 与全局最大 id（或逻辑序号）的差值，避免低效自连接。
本文介绍如何在 mariadb 中创建高性能视图，对数值字段进行去重、频次统计，并动态计算各数值最后一次出现 id 与全局最大 id（或逻辑序号）的差值，避免低效自连接。
在构建分析型视图时，常需对基础表执行多维度聚合：既要统计某字段（如 number）的出现频次，又要结合其位置信息（如 id）推导相对偏移量。本例中，目标是生成一个三列视图：number（唯一值）、occurrences（该值出现次数）、IDdifferences（该值对应的最大 id 与全表最大 id 的差值）。
关键在于避免使用 JOIN 或子查询实现逐行对比——这类写法不仅可读性差，更会在大数据量下引发性能陡降。MariaDB 10.2+ 支持窗口函数，使我们能以简洁、声明式的方式完成此类计算。
✅ 推荐方案：使用聚合 + 窗口函数
若 id 连续且无缺失（即 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;
- COUNT(*) 统计每个 number 的出现次数；
- MAX(id) 在 GROUP BY number 下获取该 number 对应的最大 id（即“最后一次出现的 ID”）；
- MAX(MAX(id)) OVER () 是窗口函数：先对每组取 MAX(id)，再在整个结果集上取最大值（即全表最大 id），等价于 SELECT MAX(id) FROM numbers，但无需额外子查询；
- 差值 IDdifferences = 全局最大 id − 当前 number 的最大 id，自然反映“距末尾还有多少行”。
⚠️ 注意：处理 ID 不连续的情况
若原始表存在 id 缺失（如删除过记录、非自增主键等），则 id 不再代表逻辑顺序。此时应基于实际行序计算差值，推荐用 ROW_NUMBER() 构建稠密序号：
CREATE VIEW numbers_summary_dense 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 为每行按 id 升序分配逻辑序号 rn（1, 2, 3, …），确保序号连续；
- 后续逻辑与上一版本一致，仅将 id 替换为 rn，从而保证 IDdifferences 始终反映“在有序列表中距离最后一行的步数”。
? 验证与优化建议
- 索引优化：确保 numbers(number, id) 上存在复合索引（或至少 number 和 id 分别有索引），可显著加速 GROUP BY 和 MAX() 计算；
- 视图不可更新性：该视图含聚合与窗口函数，属于不可更新视图（UPDATE/INSERT 将报错），仅适用于只读分析场景；
-
兼容性提示：MAX(MAX(...)) OVER () 依赖嵌套窗口函数，MariaDB ≥10.2 支持；若使用旧版本，可用标量子查询替代（但性能略低）：
(SELECT MAX(id) FROM numbers) - MAX(id) AS IDdifferences
通过合理运用 GROUP BY 与窗口函数，我们以单次扫描完成全部计算，时间复杂度为 O(n)，远优于 N² 级别的自连接方案。这不仅是语法技巧，更是数据库思维从“过程式遍历”向“集合式声明”的重要跃迁。
