本文详解如何通过 group by 配合窗口函数，在 mariadb 视图中精准计算每个数值的出现频次及与其最后一次出现 id 相关的差值，兼顾性能与逻辑正确性。
本文详解如何通过 group by 配合窗口函数，在 mariadb 视图中精准计算每个数值的出现频次及与其最后一次出现 id 相关的差值，兼顾性能与逻辑正确性。
在构建数据库视图时，常需对基础表进行聚合分析并衍生新字段。针对 numbers 表（含 id 和 number 两列），目标是创建一个视图，返回三列结果：唯一数值（number）、该数值出现次数（occurrences）、以及该数值最后一次出现的 id 与全表最大 id 的差值（即 IDdifferences）。注意：题中示例结果明确表明 IDdifferences = 最大 id - 当前 number 对应的最大 id（例如 number=41 出现在 id=2 和 id=6，取 MAX(id)=6，全表 MAX(id)=8，故 8−6=2）。
实现该逻辑的核心在于分组聚合 + 窗口函数协同。直接使用多表 JOIN 计数或子查询易引发性能退化，而 GROUP BY number 结合 COUNT(*) 和 MAX(id) 可高效完成前两项；第三项需借助窗口函数 MAX() OVER() 获取全局最大 id，再与每组 MAX(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 对应的最大 id；
- MAX(MAX(id)) OVER () 是窗口函数嵌套——内层 MAX(id) 按组计算，外层 MAX(...) OVER () 忽略分组，全量扫描获取整个结果集中的最大 id 值（即原始表最大 id）；
- ORDER BY number 确保视图结果有序，提升可读性。
⚠️ 注意事项：
- 若原始表 id 存在非连续间隙（如删除过记录导致 id 序列不完整），而业务逻辑实际要求基于“逻辑顺序位置”（即第1行、第2行…）而非物理 id 值计算差值，则需先用 ROW_NUMBER() 生成连续序号：
CREATE VIEW numbers_summary_sequential 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;
- 视图定义中避免使用未索引的 ORDER BY（除非必要），因视图本身不保证排序；若需强制排序，应在查询视图时显式添加 ORDER BY。
- 为提升性能，建议在 number 列上建立普通索引（INDEX idx_number (number)），在 id 列上确保有主键或索引（通常已满足）。
综上，该方案以简洁的 SQL 实现了需求，规避了低效 JOIN，充分利用 MariaDB 10.2+ 对窗口函数的支持，兼具可读性、可维护性与执行效率。
