SQLite查询需求：近24小时数据按日期小时覆盖并排序

问题分析 #

你的核心需求是：

• 提取teste表中**近24小时（本地+9时区）**的数据
• 同小时的记录优先保留最新日期的（如19日00时覆盖18日00时）
• 最终结果需将最新日期的0点记录放在顶部，其余时段按小时升序显示（无最新日期记录时显示前一天对应时段）

原查询的问题在于：仅做了时间范围筛选，未处理同小时的记录去重覆盖，也未按预期逻辑排序，导致返回结果混乱。

正确查询语句 #

WITH local_time_data AS (
    -- 转换原始时间到+9时区，提取关键分组字段
    SELECT
        *,
        strftime('%Y-%m-%d', dt, '+9 hours') AS local_date,
        strftime('%H', dt, '+9 hours') AS local_hour
    FROM teste
    -- 筛选本地时间在前一天0点之后的数据
    WHERE strftime('%Y-%m-%d %H:%M:%S', dt, '+9 hours') >= strftime('%Y-%m-%d %H:%M:%S', 'now', '-1 day', 'start of day')
),
ranked_hourly_data AS (
    -- 按小时分组，标记每组内的最新记录
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY local_hour 
            ORDER BY local_date DESC, dt DESC
        ) AS row_rank
    FROM local_time_data
)
-- 保留每个小时的最新记录，按要求排序
SELECT * EXCEPT (local_date, local_hour, row_rank)
FROM ranked_hourly_data
WHERE row_rank = 1
ORDER BY
    -- 最新日期的0点记录排最前
    CASE WHEN local_date = (SELECT MAX(local_date) FROM local_time_data) AND local_hour = '00' THEN 0 ELSE 1 END,
    -- 其余记录按小时升序排列
    CAST(local_hour AS INTEGER);

逻辑说明 #

1. local_time_data：将原始时间转换为+9时区，提取本地日期、小时字段，同时筛选出符合时间范围的记录（本地时间在前一天0点之后）。
2. ranked_hourly_data：使用窗口函数ROW_NUMBER()按小时分组，组内按「日期从新到旧、原始时间从新到旧」排序，每个小时的最新记录会被标记为row_rank = 1，实现同小时的覆盖逻辑。
3. 最终查询：筛选出每个小时的最新记录，排序时优先放置最新日期的0点记录，其余记录按小时从0到23升序排列，完全匹配你的预期结果。

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