如何从含重复值的机器学习预测表中查询指定提前量的预测记录

基础方案（数据完整场景） #

你的需求本质是筛选符合时间差条件的记录，不需要使用GROUP BY+HAVING组合，直接在WHERE子句中添加时间过滤条件即可，性能更高且逻辑简单。
不同SQL方言的写法如下：

标准SQL（PostgreSQL、BigQuery等支持） #

SELECT value, id, predicted_at, predicted_for
FROM your_table_name
WHERE predicted_at = predicted_for - INTERVAL '2 hours';

MySQL #

SELECT value, id, predicted_at, predicted_for
FROM your_table_name
WHERE predicted_at = DATE_SUB(predicted_for, INTERVAL 2 HOUR);

Spark SQL / Hive #

SELECT value, id, predicted_at, predicted_for
FROM your_table_name
WHERE predicted_at = timestampadd(hour, -2, predicted_for);

兼容数据缺失的方案 #

如果存在部分id+predicted_for组合没有刚好提前2小时的预测记录，需要取时间最接近提前2小时的记录，可以使用窗口函数实现：

WITH ranked_preds AS (
    SELECT 
        value, id, predicted_at, predicted_for,
        ROW_NUMBER() OVER (
            PARTITION BY id, predicted_for
            -- 按实际预测时间和目标时间（predicted_for减2小时）的差值绝对值升序排序
            ORDER BY ABS(UNIX_TIMESTAMP(predicted_at) - (UNIX_TIMESTAMP(predicted_for) - 7200)) ASC
        ) AS rn
    FROM your_table_name
    -- 可选过滤：仅保留预测时间在要预测的时间之前的记录，排除无效数据
    WHERE predicted_at < predicted_for
)
SELECT value, id, predicted_at, predicted_for
FROM ranked_preds
WHERE rn = 1;

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