如何在SQL中仅选择与给定时间序列值最接近的表行？

核心逻辑 #

要实现仅保留和间隔边界最接近的行，可按三步处理：

1. 生成指定BEGIN到END范围内、步长为INTERVAL的所有间隔边界时间戳
2. 给每条符合筛选条件的业务数据，匹配距离最近的间隔边界，计算时间差
3. 每个间隔边界分组下仅保留时间差最小的行，即可得到目标结果

实现示例（PostgreSQL） #

-- 替换参数说明：{BEGIN}为开始时间，{END}为结束时间，{INTERVAL}为步长秒数，?为account_id参数
WITH interval_boundaries AS (
    -- 生成所有间隔边界
    SELECT generate_series(
        {BEGIN}::timestamp,
        {END}::timestamp,
        INTERVAL '{INTERVAL} seconds'
    ) AS boundary_ts
),
matched_data AS (
    SELECT
        f.*,
        b.boundary_ts,
        ABS(EXTRACT(EPOCH FROM (f.timestamp - b.boundary_ts))) AS diff_sec,
        -- 同一边界下按时间差排序，差相同取时间更早的行
        ROW_NUMBER() OVER (
            PARTITION BY b.boundary_ts
            ORDER BY ABS(EXTRACT(EPOCH FROM (f.timestamp - b.boundary_ts))), f.timestamp
        ) AS rn
    FROM foobar f
    -- 关联最近的边界，避免子查询重复计算
    CROSS JOIN LATERAL (
        SELECT boundary_ts
        FROM interval_boundaries
        ORDER BY ABS(EXTRACT(EPOCH FROM (f.timestamp - boundary_ts)))
        LIMIT 1
    ) b
    WHERE
        f.account_id = ?
        AND f.timestamp BETWEEN {BEGIN}::timestamp AND {END}::timestamp
)
SELECT account_id, timestamp, balance_one, balance_two
FROM matched_data
WHERE rn = 1
ORDER BY timestamp ASC;

其他数据库适配说明 #

如果使用MySQL 8.0及以上版本，仅需要调整间隔边界生成的逻辑为递归CTE即可，后续筛选逻辑一致：

WITH RECURSIVE interval_boundaries AS (
    SELECT {BEGIN} AS boundary_ts
    UNION ALL
    SELECT DATE_ADD(boundary_ts, INTERVAL {INTERVAL} SECOND)
    FROM interval_boundaries
    WHERE boundary_ts < {END}
)
-- 后续匹配、排序、筛选逻辑和上面PostgreSQL版本一致，替换时间差计算函数为TIMESTAMPDIFF即可

结果验证 #

以上实现完全匹配给出的示例效果：

• 当account_id=2、间隔10秒时，10秒边界最近的行是17:00:07的记录（距离3秒，优于17:00:05的5秒、17:00:15的5秒），和示例结果一致
• 当account_id=1、间隔10秒时，30秒边界最近的行是17:00:25的记录（距离5秒，无更近数据），和示例结果一致

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