SQL Server实现每日每小时返回4条数据的查询方法

Hey Louis, 我刚好处理过类似的图表数据聚合需求——当每分钟一条的数据量太大没法直接展示时，按固定时间间隔提取样本是最实用的办法。针对你要每15分钟取第一条数据的需求，我给你整理了几种常用数据库的实现方案：

MySQL 解决方案 #

如果你的数据库是MySQL（包括8.0及以上版本），可以用两种方式实现：

子查询关联写法 #

SELECT m.date, m.value
FROM Messages m
INNER JOIN (
    -- 按15分钟间隔分组，找出每个时间段的第一条数据的时间
    SELECT 
        DATE_FORMAT(date, '%Y-%m-%d %H:%i:00') - INTERVAL (MINUTE(date) % 15) MINUTE AS interval_start,
        MIN(date) AS first_date
    FROM Messages
    WHERE date BETWEEN '2018-04-01 00:00:00' AND '2018-04-01 23:59:59'
    GROUP BY interval_start
) grouped ON m.date = grouped.first_date
ORDER BY m.date ASC;

思路：通过MINUTE(date) % 15计算当前时间距离最近15分钟整点的偏移量，减去这个偏移量得到每个15分钟区间的起始点；分组后用MIN(date)拿到区间内的第一条数据时间，最后关联原表取出对应的值。

窗口函数写法（MySQL 8.0+） #

如果你的MySQL版本支持窗口函数，这种写法更直观：

SELECT date, value
FROM (
    SELECT 
        date,
        value,
        -- 按15分钟区间分组，给组内数据按时间排序标记行号
        ROW_NUMBER() OVER (
            PARTITION BY DATE_FORMAT(date, '%Y-%m-%d %H:%i:00') - INTERVAL (MINUTE(date) % 15) MINUTE
            ORDER BY date ASC
        ) AS rn
    FROM Messages
    WHERE date BETWEEN '2018-04-01 00:00:00' AND '2018-04-01 23:59:59'
) t
WHERE rn = 1 -- 只取每个区间的第一条数据
ORDER BY date ASC;

SQL Server 解决方案 #

针对SQL Server，同样有两种实现方式：

子查询关联写法 #

SELECT m.date, m.value
FROM Messages m
INNER JOIN (
    SELECT 
        DATEADD(MINUTE, DATEDIFF(MINUTE, 0, date) / 15 * 15, 0) AS interval_start,
        MIN(date) AS first_date
    FROM Messages
    WHERE date BETWEEN '2018-04-01 00:00:00' AND '2018-04-01 23:59:59'
    GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, date) / 15 * 15, 0)
) grouped ON m.date = grouped.first_date
ORDER BY m.date ASC;

思路：用DATEDIFF(MINUTE, 0, date)计算从系统0时间到当前记录的总分钟数，除以15取整再乘15，得到15分钟区间的起始点；后续逻辑和MySQL一致。

窗口函数写法（SQL Server 2008+） #

SELECT date, value
FROM (
    SELECT 
        date,
        value,
        ROW_NUMBER() OVER (
            PARTITION BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, date) / 15 * 15, 0)
            ORDER BY date ASC
        ) AS rn
    FROM Messages
    WHERE date BETWEEN '2018-04-01 00:00:00' AND '2018-04-01 23:59:59'
) t
WHERE rn = 1
ORDER BY date ASC;

PostgreSQL 解决方案 #

PostgreSQL的时间函数语法略有不同，实现如下：

子查询关联写法 #

SELECT m.date, m.value
FROM Messages m
INNER JOIN (
    SELECT 
        date_trunc('minute', date) - INTERVAL '1 minute' * (EXTRACT(MINUTE FROM date) % 15) AS interval_start,
        MIN(date) AS first_date
    FROM Messages
    WHERE date BETWEEN '2018-04-01 00:00:00' AND '2018-04-01 23:59:59'
    GROUP BY interval_start
) grouped ON m.date = grouped.first_date
ORDER BY m.date ASC;

窗口函数写法 #

SELECT date, value
FROM (
    SELECT 
        date,
        value,
        ROW_NUMBER() OVER (
            PARTITION BY date_trunc('minute', date) - INTERVAL '1 minute' * (EXTRACT(MINUTE FROM date) % 15)
            ORDER BY date ASC
        ) AS rn
    FROM Messages
    WHERE date BETWEEN '2018-04-01 00:00:00' AND '2018-04-01 23:59:59'
) t
WHERE rn = 1
ORDER BY date ASC;

额外说明 #

• 我把你原SQL中的日期格式改成了YYYY-MM-DD的标准格式，避免不同数据库对MM/DD/YYYY格式的解析歧义；
• 如果你的数据中某个15分钟区间没有数据，以上方案不会返回该区间的记录。如果需要强制返回所有15分钟区间（哪怕没有数据），可以先生成一个包含所有时间点的临时表，再和你的数据左关联。

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