SQLite中epoch函数区间查询报错：MAX()聚合函数误用

错误原因 #

你遇到的misuse of aggregate: MAX()错误，核心原因是WHERE子句执行时机早于GROUP BY聚合计算：

• WHERE是在分组前过滤原始行，此时trip_start_time（MIN聚合结果）、trip_end_time（MAX聚合结果）还未生成，无法被WHERE引用
• 你试图用聚合后的值过滤分组，应该用HAVING子句（GROUP BY之后执行，用于过滤分组结果）

修正后的查询语句 #

方案1：用HAVING子句过滤分组 #

将原WHERE中的时间筛选逻辑移到HAVING中，同时优化当前时间的计算：

SELECT
    agg_st.trip_id,
    agg_st.trip_start_time,
    agg_st.trip_end_time,
    agg_trips.route_id,
    agg_trips.route_long_name,
    agg_st.c_time
FROM (
    SELECT 
       st.trip_id as trip_id,
       unixepoch() as c_time,
       MIN(departure_timestamp) + unixepoch('2022-08-04') + 18000 as trip_start_time,
       MAX(departure_timestamp) + unixepoch('2022-08-04') + 18000 as trip_end_time
    FROM stop_times st
    WHERE trip_id NOT IN ('2954445','2954447')
    GROUP BY trip_id 
    -- 用HAVING过滤聚合后的分组
    HAVING unixepoch() > trip_start_time AND unixepoch() < trip_end_time
) agg_st
JOIN (
    SELECT 
        t.route_id as route_id,
        t.trip_id as trip_id,
        r.route_long_name as route_long_name 
    FROM trips t
    JOIN routes r ON r.route_id = t.route_id 
    WHERE trip_id NOT IN ('2954445','2954447')
    GROUP BY t.route_id, t.trip_id, r.route_long_name
) agg_trips ON agg_trips.trip_id = agg_st.trip_id;

方案2：先聚合再过滤（逻辑更清晰） #

先计算所有符合条件的trip的起止时间，再在外层筛选当前时间在区间内的记录：

SELECT
    agg_st.trip_id,
    agg_st.trip_start_time,
    agg_st.trip_end_time,
    agg_trips.route_id,
    agg_trips.route_long_name,
    unixepoch() as c_time
FROM (
    SELECT 
       st.trip_id as trip_id,
       MIN(departure_timestamp) + unixepoch('2022-08-04') + 18000 as trip_start_time,
       MAX(departure_timestamp) + unixepoch('2022-08-04') + 18000 as trip_end_time
    FROM stop_times st
    WHERE trip_id NOT IN ('2954445','2954447')
    GROUP BY trip_id 
) agg_st
JOIN (
    SELECT 
        t.route_id as route_id,
        t.trip_id as trip_id,
        r.route_long_name as route_long_name 
    FROM trips t
    JOIN routes r ON r.route_id = t.route_id 
    WHERE trip_id NOT IN ('2954445','2954447')
    GROUP BY t.route_id, t.trip_id, r.route_long_name
) agg_trips ON agg_trips.trip_id = agg_st.trip_id
-- 外层直接筛选当前时间在起止区间内
WHERE unixepoch() > agg_st.trip_start_time AND unixepoch() < agg_st.trip_end_time;

额外优化点 #

避免重复调用unixepoch()，可以用WITH子句存储当前时间：

WITH current_time AS (SELECT unixepoch() as c_time)
SELECT
    agg_st.trip_id,
    agg_st.trip_start_time,
    agg_st.trip_end_time,
    agg_trips.route_id,
    agg_trips.route_long_name,
    ct.c_time
FROM current_time ct
JOIN (
    SELECT 
       st.trip_id as trip_id,
       MIN(departure_timestamp) + unixepoch('2022-08-04') + 18000 as trip_start_time,
       MAX(departure_timestamp) + unixepoch('2022-08-04') + 18000 as trip_end_time
    FROM stop_times st
    WHERE trip_id NOT IN ('2954445','2954447')
    GROUP BY trip_id 
) agg_st ON ct.c_time > agg_st.trip_start_time AND ct.c_time < agg_st.trip_end_time
JOIN (
    SELECT 
        t.route_id as route_id,
        t.trip_id as trip_id,
        r.route_long_name as route_long_name 
    FROM trips t
    JOIN routes r ON r.route_id = t.route_id 
    WHERE trip_id NOT IN ('2954445','2954447')
    GROUP BY t.route_id, t.trip_id, r.route_long_name
) agg_trips ON agg_trips.trip_id = agg_st.trip_id;

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