SQL如何筛选Table1中每个Item对应Abs(Time2-Time1)最小的行

需求说明 #

现有数据表Table1包含如下字段：

• 时间字段：Time1、Time2（同一Item对应的Time1值固定，Time2值随Code变化）
• 业务字段：Item、Code
  需要筛选出每个Item对应的、Abs(Time2 - Time1)差值最小的行数据。

实现方案 #

方案1：窗口函数法（支持MySQL8.0+、PostgreSQL、SQL Server、Hive等主流新版数据库） #

该方案性能最优，代码可读性高：

WITH ranked_data AS (
    SELECT 
        *,
        -- 按Item分组，按两个时间的绝对差值升序排序，差值最小的行排序为1
        ROW_NUMBER() OVER (
            PARTITION BY Item 
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, Time1, Time2)) ASC
        ) AS rn
    FROM Table1
)
-- 筛选每个分组排序第一的行
SELECT Time1, Time2, Item, Code
FROM ranked_data
WHERE rn = 1;

提示：如果同一Item下存在多条差值完全相同的行，ROW_NUMBER()会随机返回其中一条，若需要返回所有符合条件的行，将ROW_NUMBER()替换为RANK()即可。

方案2：子查询法（适配不支持窗口函数的旧版数据库） #

SELECT t1.*
FROM Table1 t1
INNER JOIN (
    -- 先计算每个Item对应的最小时间差值
    SELECT 
        Item,
        MIN(ABS(TIMESTAMPDIFF(SECOND, Time1, Time2))) AS min_diff
    FROM Table1
    GROUP BY Item
) t2 ON t1.Item = t2.Item 
    AND ABS(TIMESTAMPDIFF(SECOND, t1.Time1, t1.Time2)) = t2.min_diff;

注意事项 #

• 示例中使用的TIMESTAMPDIFF为MySQL专属时间差函数，可根据使用的数据库类型替换为对应函数：
  • PostgreSQL：替换为EXTRACT(EPOCH FROM (Time2 - Time1))
  • SQL Server：替换为DATEDIFF(SECOND, Time1, Time2)
• 时间差单位可根据实际精度需求调整，比如替换为分钟、小时等。

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