如何在MariaDB中查询Time列最大值偏移范围内的记录？

场景说明 #

现有Test表数据如下：

Time   Location
10      A  
1       B  
2       C  
3       D  
4       E

需求是返回Time列最大值与该最大值减去指定偏移量之间的所有记录。例如当最大值为10、偏移量为7时，需返回Time在3到10之间的记录，期望结果：

Time   Location
10      A  
4       E  
3       D

该表会持续新增记录，且Time值始终递增，属于滑动范围查询。

原SQL错误原因 #

你尝试的SQL语句报错Invalid use of group function，是因为WHERE子句在聚合函数计算前执行，无法直接使用MAX()这类聚合函数筛选行。

正确实现方案 #

方案1：子查询获取全局最大Time

通过子查询先算出当前Time的最大值，再关联原表进行范围筛选：

SELECT t.Time, t.Location
FROM Test t
CROSS JOIN (SELECT MAX(Time) AS max_time FROM Test) mt
WHERE t.Time >= mt.max_time - 7
ORDER BY t.Time DESC;

方案2：使用窗口函数（MariaDB 10.2及以上版本支持）

利用窗口函数MAX() OVER ()在每一行中获取全局最大Time，再在外层筛选符合条件的记录：

SELECT Time, Location
FROM (
    SELECT 
        Time, 
        Location,
        MAX(Time) OVER () AS max_time
    FROM Test
) t
WHERE Time >= max_time - 7
ORDER BY Time DESC;

方案3：结合Time递增特性优化

由于Time值始终递增，最新插入的记录的Time就是当前最大值，可直接取这条记录的Time来计算范围，性能更优：

SELECT Time, Location
FROM Test
WHERE Time >= (SELECT Time FROM Test ORDER BY Time DESC LIMIT 1) - 7
ORDER BY Time DESC;

性能优化建议 #

为Time列创建索引，大幅提升范围查询效率：

CREATE INDEX idx_test_time ON Test(Time);

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