获取每组中最接近指定DateTime的行的SQL实现方案

现有名为Data的表，需获取每个id分组中最接近指定DateTime（示例值为2022-03-04 12:00:00）的行。

示例表数据 #

预期返回结果 #

解决方案 #

利用窗口函数ROW_NUMBER()，通过计算每行时间与指定时间的差值绝对值排序，取每个分组的第一行即可实现需求：

MySQL 版本 #

WITH ranked_data AS (
    SELECT 
        id,
        Value,
        DateTime,
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, DateTime, '2022-03-04 12:00:00')) ASC
        ) AS rn
    FROM Data
)
SELECT id, Value, DateTime
FROM ranked_data
WHERE rn = 1;

PostgreSQL 版本 #

WITH ranked_data AS (
    SELECT 
        id,
        Value,
        DateTime,
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY ABS(EXTRACT(EPOCH FROM (DateTime - '2022-03-04 12:00:00'::timestamp))) ASC
        ) AS rn
    FROM Data
)
SELECT id, Value, DateTime
FROM ranked_data
WHERE rn = 1;

SQL Server 版本 #

WITH ranked_data AS (
    SELECT 
        id,
        Value,
        DateTime,
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY ABS(DATEDIFF(SECOND, DateTime, '2022-03-04 12:00:00')) ASC
        ) AS rn
    FROM Data
)
SELECT id, Value, DateTime
FROM ranked_data
WHERE rn = 1;

说明：通过PARTITION BY id按分组划分数据，ORDER BY子句根据时间差的绝对值从小到大排序，ROW_NUMBER()会给每个分组内的行依次编号，取rn=1的行就是每个分组中最接近指定时间的记录。

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